/
query_iceberg_metadata_of_partitioned_table.q
87 lines (69 loc) · 4.37 KB
/
query_iceberg_metadata_of_partitioned_table.q
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
-- SORT_QUERY_RESULTS
-- Mask the file size values as it can have slight variability, causing test flakiness
--! qt:replace:/("file_size_in_bytes":)\d+/$1#Masked#/
--! qt:replace:/("total-files-size":)\d+/$1#Masked#/
--! qt:replace:/((ORC|PARQUET|AVRO)\s+\d+\s+)\d+/$1#Masked#/
set tez.mrreader.config.update.properties=hive.io.file.readcolumn.names,hive.io.file.readcolumn.ids;
set hive.query.results.cache.enabled=false;
set hive.fetch.task.conversion=none;
set hive.cbo.enable=true;
drop table if exists ice_meta_2;
drop table if exists ice_meta_3;
create external table ice_meta_2(a int) partitioned by (b string) stored by iceberg stored as orc;
insert into table ice_meta_2 values (1, 'one'), (2, 'one'), (3, 'one');
truncate table ice_meta_2;
insert into table ice_meta_2 values (4, 'two'), (5, 'two');
truncate table ice_meta_2;
insert into table ice_meta_2 values (6, 'three'), (7, 'three'), (8, 'three');
insert into table ice_meta_2 values (9, 'four');
select * from ice_meta_2;
create external table ice_meta_3(a int) partitioned by (b string, c string) stored as orc;
insert into table ice_meta_3 partition (b='one', c='Monday') values (1), (2), (3);
insert into table ice_meta_3 partition (b='two', c='Tuesday') values (4), (5);
insert into table ice_meta_3 partition (b='two', c='Friday') values (10), (11);
insert into table ice_meta_3 partition (b='three', c='Wednesday') values (6), (7), (8);
insert into table ice_meta_3 partition (b='four', c='Thursday') values (9);
insert into table ice_meta_3 partition (b='four', c='Saturday') values (12), (13), (14);
insert into table ice_meta_3 partition (b='four', c='Sunday') values (15);
alter table ice_meta_3 set tblproperties ('storage_handler'='org.apache.iceberg.mr.hive.HiveIcebergStorageHandler');
select * from ice_meta_3;
select `partition` from default.ice_meta_2.files;
select `partition` from default.ice_meta_3.files;
select `partition`.b from default.ice_meta_2.files;
select data_file.`partition` from default.ice_meta_3.entries;
select data_file.`partition` from default.ice_meta_2.entries;
select data_file.`partition`.c from default.ice_meta_3.entries;
select summary from default.ice_meta_3.snapshots;
select summary['changed-partition-count'] from default.ice_meta_2.snapshots;
select partition_spec_id, partition_summaries from default.ice_meta_2.manifests;
select partition_spec_id, partition_summaries[1].upper_bound from default.ice_meta_3.manifests;
select * from default.ice_meta_2.partitions;
select * from default.ice_meta_3.partitions;
select `partition` from default.ice_meta_2.partitions where `partition`.b='four';
select * from default.ice_meta_3.partitions where `partition`.b='two' and `partition`.c='Tuesday';
select partition_summaries from default.ice_meta_3.manifests where partition_summaries[1].upper_bound='Wednesday';
set hive.fetch.task.conversion=more;
select `partition` from default.ice_meta_2.files;
select `partition` from default.ice_meta_3.files;
select `partition`.b from default.ice_meta_2.files;
select data_file.`partition` from default.ice_meta_3.entries;
select data_file.`partition` from default.ice_meta_2.entries;
select data_file.`partition`.c from default.ice_meta_3.entries;
select summary from default.ice_meta_3.snapshots;
select summary['changed-partition-count'] from default.ice_meta_2.snapshots;
select partition_spec_id, partition_summaries from default.ice_meta_2.manifests;
select partition_spec_id, partition_summaries[1].upper_bound from default.ice_meta_3.manifests;
select * from default.ice_meta_2.partitions;
select * from default.ice_meta_3.partitions;
select `partition` from default.ice_meta_2.partitions where `partition`.b='four';
select * from default.ice_meta_3.partitions where `partition`.b='two' and `partition`.c='Tuesday';
select partition_summaries from default.ice_meta_3.manifests where partition_summaries[1].upper_bound='Wednesday';
drop table ice_meta_2;
drop table ice_meta_3;
CREATE EXTERNAL TABLE `partevv`( `id` int, `ts` timestamp, `ts2` timestamp) STORED BY ICEBERG STORED AS ORC TBLPROPERTIES ('format-version'='1');
ALTER TABLE partevv SET PARTITION SPEC (id);
INSERT INTO partevv VALUES (1, '2022-04-29 16:32:01', '2022-04-29 16:32:01');
INSERT INTO partevv VALUES (2, '2022-04-29 16:32:02', '2022-04-29 16:32:02');
ALTER TABLE partevv SET PARTITION SPEC (day(ts));
INSERT INTO partevv VALUES (100, '2022-04-29 16:32:03', '2022-04-29 16:32:03');
select * from default.partevv.partitions;