CREATE EXTERNAL TABLE IF NOT EXISTS `aqs_hourly`(  
    `cache_status`      string     COMMENT 'Cache status',  
    `http_status`       string     COMMENT 'HTTP status of response',  
    `http_method`       string     COMMENT 'HTTP method of request',  
    `response_size`     bigint     COMMENT 'Response size',  
    `uri_host`          string     COMMENT 'Host of request',  
    `uri_path`          string     COMMENT 'Path of request',  
    `request_count`     bigint     COMMENT 'Number of requests',  
    `hour`              timestamp  COMMENT 'The aggregated hour. Covers from minute 00 to 59'  
)  
USING ICEBERG
PARTITIONED BY (days(hour))
;

spark3-sql --master yarn --executor-memory 8G --executor-cores 4 --driver-memory 2G --conf spark.dynamicAllocation.maxExecutors=64 \
-f aqs_hourly_iceberg.hql  \
-d source_table=wmf.webrequest \
-d webrequest_source=text \
-d destination_table=tchin.aqs_hourly \
-d coalesce_partitions=1 \
-d year=2023 \
-d month=12 \
-d day=3 \
-d hour=0

spark-sql (default)> use tchin;
spark-sql (default)> select count(1) from aqs_hourly;
count(1)
523018

spark-sql (default)> select count(1) from wmf.aqs_hourly where year=2023 and month=12 and day=3 and hour=0;
count(1)
523018

tchin@stat1004:~$ ls -a /mnt/hdfs/user/hive/warehouse/tchin.db/aqs_hourly_coalesce/data/hour_day\=2023-12-03/ | grep parquet | wc -l
1
tchin@stat1004:~$ ls -a /mnt/hdfs/user/hive/warehouse/tchin.db/aqs_hourly_no_coalesce/data/hour_day\=2023-12-03/ | grep parquet | wc -l
200