Test results
This topic provides reference query performance data for the scenario described in the test plan, measured on a 1 TB TPC-DS dataset stored in Hologres internal tables and in Apache Iceberg external tables.
The tests compare query performance for two storage formats on the same Hologres instance:
-
Internal tables: Data is stored on the local storage of the instance in the native Hologres columnar format.
-
Iceberg external tables: Data is stored in Object Storage Service (OSS) in the Apache Iceberg table format, mounted through a Data Lake Formation (DLF) REST catalog, and queried directly by the Hologres compute engine.
Test preparations
Hologres configuration
|
Item |
Value |
|
Engine version |
Hologres V4.2.12 |
|
Compute resources |
64 CU |
Both test runs use the same 64 CU instance with identical client, network, and concurrency settings. The storage format of the queried data is the only variable.
Storage formats
|
Item |
Internal table |
Iceberg external table |
|
Storage format |
Native Hologres columnar format |
Apache Iceberg with Parquet data files |
|
Metadata catalog |
Built into Hologres |
DLF REST catalog ( |
|
Storage location |
Local storage of the instance |
Object storage (data lake) |
|
Query engine |
Hologres |
Hologres (queries the external table directly) |
Dataset
The TPC-DS dataset is generated with a scale factor of 1000, which produces approximately 1 TB of raw data in 24 tables. The benchmark comprises 99 queries. The internal tables and the external tables are loaded with the same logical dataset.
Test method
Both test runs use the hot run method, which is consistent with common industry benchmarks:
-
Clear the data cache of the instance before each run so that the warm-up run of every query starts from a cold cache.
-
Run the queries one by one over a persistent connection.
-
Run each query once as a warm-up run, which is not timed, and then run it three more times and take the average elapsed time.
-
For external tables, run
ANALYZEon all 24 tables before querying to collect statistics. External tables do not supportVACUUM. For internal tables, run bothVACUUMandANALYZE.
For the internal table test procedure, see Test plan.
To run the external table test, first prepare the Iceberg data in the data lake by using the DDL statements in Appendix: DDL statements for Iceberg TPC-DS tables. Then use the same test toolkit, set db to the name of the external database when you configure the Hologres connection, and run only the query test.
Test results
The following table lists the average response time of each of the 99 TPC-DS queries, in milliseconds (ms).
|
Query |
Internal table on V4.2.12 (ms) |
Iceberg external table on V4.2.12 (ms) |
|
Total |
263,047 |
340,286 |
|
q1 |
627 |
781 |
|
q2 |
1,732 |
2,035 |
|
q3 |
318 |
711 |
|
q4 |
14,008 |
16,364 |
|
q5 |
569 |
1,109 |
|
q6 |
373 |
609 |
|
q7 |
2,674 |
2,372 |
|
q8 |
285 |
588 |
|
q9 |
7,889 |
7,518 |
|
q10 |
573 |
897 |
|
q11 |
10,480 |
10,849 |
|
q12 |
118 |
198 |
|
q13 |
2,911 |
3,852 |
|
q14 |
10,010 |
15,457 |
|
q15 |
671 |
1,098 |
|
q16 |
326 |
653 |
|
q17 |
922 |
1,295 |
|
q18 |
1,150 |
2,573 |
|
q19 |
318 |
697 |
|
q20 |
154 |
347 |
|
q21 |
118 |
209 |
|
q22 |
257 |
369 |
|
q23 |
37,056 |
50,806 |
|
q24 |
9,605 |
12,665 |
|
q25 |
748 |
1,146 |
|
q26 |
333 |
832 |
|
q27 |
956 |
1,699 |
|
q28 |
9,537 |
10,041 |
|
q29 |
1,430 |
1,329 |
|
q30 |
424 |
609 |
|
q31 |
2,372 |
3,011 |
|
q32 |
67 |
289 |
|
q33 |
533 |
835 |
|
q34 |
999 |
2,409 |
|
q35 |
1,464 |
1,800 |
|
q36 |
435 |
1,057 |
|
q37 |
100 |
209 |
|
q38 |
4,458 |
5,029 |
|
q39 |
530 |
859 |
|
q40 |
194 |
496 |
|
q41 |
56 |
92 |
|
q42 |
111 |
282 |
|
q43 |
715 |
1,075 |
|
q44 |
151 |
286 |
|
q45 |
798 |
879 |
|
q46 |
4,828 |
5,466 |
|
q47 |
3,786 |
4,692 |
|
q48 |
1,969 |
2,203 |
|
q49 |
409 |
998 |
|
q50 |
1,598 |
1,800 |
|
q51 |
2,305 |
3,044 |
|
q52 |
114 |
365 |
|
q53 |
304 |
885 |
|
q54 |
347 |
842 |
|
q55 |
111 |
362 |
|
q56 |
384 |
718 |
|
q57 |
1,800 |
2,203 |
|
q58 |
278 |
667 |
|
q59 |
3,281 |
5,096 |
|
q60 |
559 |
999 |
|
q61 |
573 |
919 |
|
q62 |
697 |
933 |
|
q63 |
282 |
671 |
|
q64 |
3,752 |
5,534 |
|
q65 |
2,642 |
3,751 |
|
q66 |
1,067 |
1,598 |
|
q67 |
26,594 |
31,735 |
|
q68 |
1,901 |
5,904 |
|
q69 |
337 |
537 |
|
q70 |
908 |
1,564 |
|
q71 |
1,415 |
1,817 |
|
q72 |
4,727 |
4,860 |
|
q73 |
526 |
1,565 |
|
q74 |
8,123 |
8,056 |
|
q75 |
8,931 |
8,595 |
|
q76 |
4,862 |
5,769 |
|
q77 |
533 |
810 |
|
q78 |
10,916 |
9,704 |
|
q79 |
4,525 |
6,845 |
|
q80 |
1,194 |
1,665 |
|
q81 |
824 |
1,006 |
|
q82 |
176 |
402 |
|
q83 |
275 |
464 |
|
q84 |
234 |
464 |
|
q85 |
889 |
1,430 |
|
q86 |
183 |
340 |
|
q87 |
4,794 |
5,803 |
|
q88 |
3,987 |
4,458 |
|
q89 |
577 |
1,228 |
|
q90 |
497 |
1,329 |
|
q91 |
216 |
555 |
|
q92 |
67 |
314 |
|
q93 |
2,641 |
4,356 |
|
q94 |
424 |
992 |
|
q95 |
867 |
2,102 |
|
q96 |
3,045 |
4,054 |
|
q97 |
5,500 |
7,283 |
|
q98 |
527 |
654 |
|
q99 |
1,191 |
1,564 |
Conclusion
On the same 64 CU Hologres instance, internal tables complete the 1 TB TPC-DS workload faster than Iceberg external tables. The total elapsed time of the external tables is approximately 1.29 times that of the internal tables. Internal tables benefit from the native columnar format, complete statistics, and local caching. At the same time, Hologres queries Iceberg external tables directly and delivers analytical performance comparable to internal tables without any data import.
These results demonstrate the data lakehouse capability of Hologres: a single engine queries both internal tables and lake tables. For latency-critical workloads, use internal tables to obtain maximum performance. When the data lake is your primary storage and you need open formats and ad hoc access, query the external tables directly. The performance difference between the two options is moderate, so you can choose based on your requirements.
Appendix: DDL statements for Iceberg TPC-DS tables
The following DDL statements define the 24 Iceberg tables used in this test. You can load the data in any way you prefer, as long as the data in the data lake matches this schema.
Dimension table DDL
CREATE TABLE IF NOT EXISTS customer_address (
ca_address_sk INT NOT NULL,
ca_address_id STRING NOT NULL,
ca_street_number STRING,
ca_street_name STRING,
ca_street_type STRING,
ca_suite_number STRING,
ca_city STRING,
ca_county STRING,
ca_state STRING,
ca_zip STRING,
ca_country STRING,
ca_gmt_offset DECIMAL(5,2),
ca_location_type STRING
) USING iceberg
PARTITIONED BY (bucket(64, ca_address_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS customer_demographics (
cd_demo_sk INT NOT NULL,
cd_gender STRING,
cd_marital_status STRING,
cd_education_status STRING,
cd_purchase_estimate INT,
cd_credit_rating STRING,
cd_dep_count INT,
cd_dep_employed_count INT,
cd_dep_college_count INT
) USING iceberg
PARTITIONED BY (bucket(64, cd_demo_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS date_dim (
d_date_sk INT NOT NULL,
d_date_id STRING NOT NULL,
d_date DATE NOT NULL,
d_month_seq INT,
d_week_seq INT,
d_quarter_seq INT,
d_year INT,
d_dow INT,
d_moy INT,
d_dom INT,
d_qoy INT,
d_fy_year INT,
d_fy_quarter_seq INT,
d_fy_week_seq INT,
d_day_name STRING,
d_quarter_name STRING,
d_holiday STRING,
d_weekend STRING,
d_following_holiday STRING,
d_first_dom INT,
d_last_dom INT,
d_same_day_ly INT,
d_same_day_lq INT,
d_current_day STRING,
d_current_week STRING,
d_current_month STRING,
d_current_quarter STRING,
d_current_year STRING
) USING iceberg
PARTITIONED BY (bucket(64, d_date_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS warehouse (
w_warehouse_sk INT NOT NULL,
w_warehouse_id STRING NOT NULL,
w_warehouse_name STRING,
w_warehouse_sq_ft INT,
w_street_number STRING,
w_street_name STRING,
w_street_type STRING,
w_suite_number STRING,
w_city STRING,
w_county STRING,
w_state STRING,
w_zip STRING,
w_country STRING,
w_gmt_offset DECIMAL(5,2)
) USING iceberg
PARTITIONED BY (bucket(64, w_warehouse_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS ship_mode (
sm_ship_mode_sk INT NOT NULL,
sm_ship_mode_id STRING NOT NULL,
sm_type STRING,
sm_code STRING,
sm_carrier STRING,
sm_contract STRING
) USING iceberg
PARTITIONED BY (bucket(64, sm_ship_mode_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS time_dim (
t_time_sk INT NOT NULL,
t_time_id STRING NOT NULL,
t_time INT NOT NULL,
t_hour INT,
t_minute INT,
t_second INT,
t_am_pm STRING,
t_shift STRING,
t_sub_shift STRING,
t_meal_time STRING
) USING iceberg
PARTITIONED BY (bucket(64, t_time_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS reason (
r_reason_sk INT NOT NULL,
r_reason_id STRING NOT NULL,
r_reason_desc STRING
) USING iceberg
PARTITIONED BY (bucket(64, r_reason_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS income_band (
ib_income_band_sk INT NOT NULL,
ib_lower_bound INT,
ib_upper_bound INT
) USING iceberg
PARTITIONED BY (bucket(64, ib_income_band_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS item (
i_item_sk INT NOT NULL,
i_item_id STRING NOT NULL,
i_rec_start_date DATE,
i_rec_end_date DATE,
i_item_desc STRING,
i_current_price DECIMAL(7,2),
i_wholesale_cost DECIMAL(7,2),
i_brand_id INT,
i_brand STRING,
i_class_id INT,
i_class STRING,
i_category_id INT,
i_category STRING,
i_manufact_id INT,
i_manufact STRING,
i_size STRING,
i_formulation STRING,
i_color STRING,
i_units STRING,
i_container STRING,
i_manager_id INT,
i_product_name STRING
) USING iceberg
PARTITIONED BY (bucket(64, i_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS store (
s_store_sk INT NOT NULL,
s_store_id STRING NOT NULL,
s_rec_start_date DATE,
s_rec_end_date DATE,
s_closed_date_sk INT,
s_store_name STRING,
s_number_employees INT,
s_floor_space INT,
s_hours STRING,
s_manager STRING,
s_market_id INT,
s_geography_class STRING,
s_market_desc STRING,
s_market_manager STRING,
s_division_id INT,
s_division_name STRING,
s_company_id INT,
s_company_name STRING,
s_street_number STRING,
s_street_name STRING,
s_street_type STRING,
s_suite_number STRING,
s_city STRING,
s_county STRING,
s_state STRING,
s_zip STRING,
s_country STRING,
s_gmt_offset DECIMAL(5,2),
s_tax_percentage DECIMAL(5,2)
) USING iceberg
PARTITIONED BY (bucket(64, s_store_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS call_center (
cc_call_center_sk INT NOT NULL,
cc_call_center_id STRING NOT NULL,
cc_rec_start_date DATE,
cc_rec_end_date DATE,
cc_closed_date_sk INT,
cc_open_date_sk INT,
cc_name STRING,
cc_class STRING,
cc_employees INT,
cc_sq_ft INT,
cc_hours STRING,
cc_manager STRING,
cc_mkt_id INT,
cc_mkt_class STRING,
cc_mkt_desc STRING,
cc_market_manager STRING,
cc_division INT,
cc_division_name STRING,
cc_company INT,
cc_company_name STRING,
cc_street_number STRING,
cc_street_name STRING,
cc_street_type STRING,
cc_suite_number STRING,
cc_city STRING,
cc_county STRING,
cc_state STRING,
cc_zip STRING,
cc_country STRING,
cc_gmt_offset DECIMAL(5,2),
cc_tax_percentage DECIMAL(5,2)
) USING iceberg
PARTITIONED BY (bucket(64, cc_call_center_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS customer (
c_customer_sk INT NOT NULL,
c_customer_id STRING NOT NULL,
c_current_cdemo_sk INT,
c_current_hdemo_sk INT,
c_current_addr_sk INT,
c_first_shipto_date_sk INT,
c_first_sales_date_sk INT,
c_salutation STRING,
c_first_name STRING,
c_last_name STRING,
c_preferred_cust_flag STRING,
c_birth_day INT,
c_birth_month INT,
c_birth_year INT,
c_birth_country STRING,
c_login STRING,
c_email_address STRING,
c_last_review_date_sk INT
) USING iceberg
PARTITIONED BY (bucket(64, c_customer_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS web_site (
web_site_sk INT NOT NULL,
web_site_id STRING NOT NULL,
web_rec_start_date DATE,
web_rec_end_date DATE,
web_name STRING,
web_open_date_sk INT,
web_close_date_sk INT,
web_class STRING,
web_manager STRING,
web_mkt_id INT,
web_mkt_class STRING,
web_mkt_desc STRING,
web_market_manager STRING,
web_company_id INT,
web_company_name STRING,
web_street_number STRING,
web_street_name STRING,
web_street_type STRING,
web_suite_number STRING,
web_city STRING,
web_county STRING,
web_state STRING,
web_zip STRING,
web_country STRING,
web_gmt_offset DECIMAL(5,2),
web_tax_percentage DECIMAL(5,2)
) USING iceberg
PARTITIONED BY (bucket(64, web_site_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS household_demographics (
hd_demo_sk INT NOT NULL,
hd_income_band_sk INT,
hd_buy_potential STRING,
hd_dep_count INT,
hd_vehicle_count INT
) USING iceberg
PARTITIONED BY (bucket(64, hd_demo_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS web_page (
wp_web_page_sk INT NOT NULL,
wp_web_page_id STRING NOT NULL,
wp_rec_start_date DATE,
wp_rec_end_date DATE,
wp_creation_date_sk INT,
wp_access_date_sk INT,
wp_autogen_flag STRING,
wp_customer_sk INT,
wp_url STRING,
wp_type STRING,
wp_char_count INT,
wp_link_count INT,
wp_image_count INT,
wp_max_ad_count INT
) USING iceberg
PARTITIONED BY (bucket(64, wp_web_page_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS promotion (
p_promo_sk INT NOT NULL,
p_promo_id STRING NOT NULL,
p_start_date_sk INT,
p_end_date_sk INT,
p_item_sk INT,
p_cost DECIMAL(15,2),
p_response_target INT,
p_promo_name STRING,
p_channel_dmail STRING,
p_channel_email STRING,
p_channel_catalog STRING,
p_channel_tv STRING,
p_channel_radio STRING,
p_channel_press STRING,
p_channel_event STRING,
p_channel_demo STRING,
p_channel_details STRING,
p_purpose STRING,
p_discount_active STRING
) USING iceberg
PARTITIONED BY (bucket(64, p_promo_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS catalog_page (
cp_catalog_page_sk INT NOT NULL,
cp_catalog_page_id STRING NOT NULL,
cp_start_date_sk INT,
cp_end_date_sk INT,
cp_department STRING,
cp_catalog_number INT,
cp_catalog_page_number INT,
cp_description STRING,
cp_type STRING
) USING iceberg
PARTITIONED BY (bucket(64, cp_catalog_page_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
Fact table DDL
CREATE TABLE IF NOT EXISTS inventory (
inv_date_sk INT NOT NULL,
inv_item_sk INT NOT NULL,
inv_warehouse_sk INT NOT NULL,
inv_quantity_on_hand INT
) USING iceberg
PARTITIONED BY (bucket(64, inv_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS store_returns (
sr_returned_date_sk INT,
sr_return_time_sk INT,
sr_item_sk INT NOT NULL,
sr_customer_sk INT,
sr_cdemo_sk INT,
sr_hdemo_sk INT,
sr_addr_sk INT,
sr_store_sk INT,
sr_reason_sk INT,
sr_ticket_number INT NOT NULL,
sr_return_quantity INT,
sr_return_amt DECIMAL(7,2),
sr_return_tax DECIMAL(7,2),
sr_return_amt_inc_tax DECIMAL(7,2),
sr_fee DECIMAL(7,2),
sr_return_ship_cost DECIMAL(7,2),
sr_refunded_cash DECIMAL(7,2),
sr_reversed_charge DECIMAL(7,2),
sr_store_credit DECIMAL(7,2),
sr_net_loss DECIMAL(7,2)
) USING iceberg
PARTITIONED BY (bucket(64, sr_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS catalog_returns (
cr_returned_date_sk INT,
cr_returned_time_sk INT,
cr_item_sk INT NOT NULL,
cr_refunded_customer_sk INT,
cr_refunded_cdemo_sk INT,
cr_refunded_hdemo_sk INT,
cr_refunded_addr_sk INT,
cr_returning_customer_sk INT,
cr_returning_cdemo_sk INT,
cr_returning_hdemo_sk INT,
cr_returning_addr_sk INT,
cr_call_center_sk INT,
cr_catalog_page_sk INT,
cr_ship_mode_sk INT,
cr_warehouse_sk INT,
cr_reason_sk INT,
cr_order_number INT NOT NULL,
cr_return_quantity INT,
cr_return_amount DECIMAL(7,2),
cr_return_tax DECIMAL(7,2),
cr_return_amt_inc_tax DECIMAL(7,2),
cr_fee DECIMAL(7,2),
cr_return_ship_cost DECIMAL(7,2),
cr_refunded_cash DECIMAL(7,2),
cr_reversed_charge DECIMAL(7,2),
cr_store_credit DECIMAL(7,2),
cr_net_loss DECIMAL(7,2)
) USING iceberg
PARTITIONED BY (bucket(64, cr_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS web_returns (
wr_returned_date_sk INT,
wr_returned_time_sk INT,
wr_item_sk INT NOT NULL,
wr_refunded_customer_sk INT,
wr_refunded_cdemo_sk INT,
wr_refunded_hdemo_sk INT,
wr_refunded_addr_sk INT,
wr_returning_customer_sk INT,
wr_returning_cdemo_sk INT,
wr_returning_hdemo_sk INT,
wr_returning_addr_sk INT,
wr_web_page_sk INT,
wr_reason_sk INT,
wr_order_number INT NOT NULL,
wr_return_quantity INT,
wr_return_amt DECIMAL(7,2),
wr_return_tax DECIMAL(7,2),
wr_return_amt_inc_tax DECIMAL(7,2),
wr_fee DECIMAL(7,2),
wr_return_ship_cost DECIMAL(7,2),
wr_refunded_cash DECIMAL(7,2),
wr_reversed_charge DECIMAL(7,2),
wr_account_credit DECIMAL(7,2),
wr_net_loss DECIMAL(7,2)
) USING iceberg
PARTITIONED BY (bucket(64, wr_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS web_sales (
ws_sold_date_sk INT,
ws_sold_time_sk INT,
ws_ship_date_sk INT,
ws_item_sk INT NOT NULL,
ws_bill_customer_sk INT,
ws_bill_cdemo_sk INT,
ws_bill_hdemo_sk INT,
ws_bill_addr_sk INT,
ws_ship_customer_sk INT,
ws_ship_cdemo_sk INT,
ws_ship_hdemo_sk INT,
ws_ship_addr_sk INT,
ws_web_page_sk INT,
ws_web_site_sk INT,
ws_ship_mode_sk INT,
ws_warehouse_sk INT,
ws_promo_sk INT,
ws_order_number INT NOT NULL,
ws_quantity INT,
ws_wholesale_cost DECIMAL(7,2),
ws_list_price DECIMAL(7,2),
ws_sales_price DECIMAL(7,2),
ws_ext_discount_amt DECIMAL(7,2),
ws_ext_sales_price DECIMAL(7,2),
ws_ext_wholesale_cost DECIMAL(7,2),
ws_ext_list_price DECIMAL(7,2),
ws_ext_tax DECIMAL(7,2),
ws_coupon_amt DECIMAL(7,2),
ws_ext_ship_cost DECIMAL(7,2),
ws_net_paid DECIMAL(7,2),
ws_net_paid_inc_tax DECIMAL(7,2),
ws_net_paid_inc_ship DECIMAL(7,2),
ws_net_paid_inc_ship_tax DECIMAL(7,2),
ws_net_profit DECIMAL(7,2)
) USING iceberg
PARTITIONED BY (bucket(64, ws_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS catalog_sales (
cs_sold_date_sk INT,
cs_sold_time_sk INT,
cs_ship_date_sk INT,
cs_bill_customer_sk INT,
cs_bill_cdemo_sk INT,
cs_bill_hdemo_sk INT,
cs_bill_addr_sk INT,
cs_ship_customer_sk INT,
cs_ship_cdemo_sk INT,
cs_ship_hdemo_sk INT,
cs_ship_addr_sk INT,
cs_call_center_sk INT,
cs_catalog_page_sk INT,
cs_ship_mode_sk INT,
cs_warehouse_sk INT,
cs_item_sk INT NOT NULL,
cs_promo_sk INT,
cs_order_number INT NOT NULL,
cs_quantity INT,
cs_wholesale_cost DECIMAL(7,2),
cs_list_price DECIMAL(7,2),
cs_sales_price DECIMAL(7,2),
cs_ext_discount_amt DECIMAL(7,2),
cs_ext_sales_price DECIMAL(7,2),
cs_ext_wholesale_cost DECIMAL(7,2),
cs_ext_list_price DECIMAL(7,2),
cs_ext_tax DECIMAL(7,2),
cs_coupon_amt DECIMAL(7,2),
cs_ext_ship_cost DECIMAL(7,2),
cs_net_paid DECIMAL(7,2),
cs_net_paid_inc_tax DECIMAL(7,2),
cs_net_paid_inc_ship DECIMAL(7,2),
cs_net_paid_inc_ship_tax DECIMAL(7,2),
cs_net_profit DECIMAL(7,2)
) USING iceberg
PARTITIONED BY (bucket(64, cs_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
CREATE TABLE IF NOT EXISTS store_sales (
ss_sold_date_sk INT,
ss_sold_time_sk INT,
ss_item_sk INT NOT NULL,
ss_customer_sk INT,
ss_cdemo_sk INT,
ss_hdemo_sk INT,
ss_addr_sk INT,
ss_store_sk INT,
ss_promo_sk INT,
ss_ticket_number INT NOT NULL,
ss_quantity INT,
ss_wholesale_cost DECIMAL(7,2),
ss_list_price DECIMAL(7,2),
ss_sales_price DECIMAL(7,2),
ss_ext_discount_amt DECIMAL(7,2),
ss_ext_sales_price DECIMAL(7,2),
ss_ext_wholesale_cost DECIMAL(7,2),
ss_ext_list_price DECIMAL(7,2),
ss_ext_tax DECIMAL(7,2),
ss_coupon_amt DECIMAL(7,2),
ss_net_paid DECIMAL(7,2),
ss_net_paid_inc_tax DECIMAL(7,2),
ss_net_profit DECIMAL(7,2)
) USING iceberg
PARTITIONED BY (bucket(64, ss_item_sk))
TBLPROPERTIES (
'format-version' = '2',
'write.format.default' = 'parquet',
'write.parquet.compression-codec' = 'snappy'
);
In-file sorting (WRITE LOCALLY ORDERED BY)
The following statements use the Spark write-ordering syntax to specify the in-file sort key of each table.
ALTER TABLE customer_address WRITE LOCALLY ORDERED BY ca_address_sk;
ALTER TABLE customer_demographics WRITE LOCALLY ORDERED BY cd_demo_sk;
ALTER TABLE date_dim WRITE LOCALLY ORDERED BY d_date_sk;
ALTER TABLE warehouse WRITE LOCALLY ORDERED BY w_warehouse_sk;
ALTER TABLE ship_mode WRITE LOCALLY ORDERED BY sm_ship_mode_sk;
ALTER TABLE time_dim WRITE LOCALLY ORDERED BY t_time_sk;
ALTER TABLE reason WRITE LOCALLY ORDERED BY r_reason_sk;
ALTER TABLE income_band WRITE LOCALLY ORDERED BY ib_income_band_sk;
ALTER TABLE item WRITE LOCALLY ORDERED BY i_item_sk;
ALTER TABLE store WRITE LOCALLY ORDERED BY s_store_sk;
ALTER TABLE call_center WRITE LOCALLY ORDERED BY cc_call_center_sk;
ALTER TABLE customer WRITE LOCALLY ORDERED BY c_customer_sk;
ALTER TABLE web_site WRITE LOCALLY ORDERED BY web_site_sk;
ALTER TABLE household_demographics WRITE LOCALLY ORDERED BY hd_demo_sk;
ALTER TABLE web_page WRITE LOCALLY ORDERED BY wp_web_page_sk;
ALTER TABLE promotion WRITE LOCALLY ORDERED BY p_promo_sk;
ALTER TABLE catalog_page WRITE LOCALLY ORDERED BY cp_catalog_page_sk;
ALTER TABLE inventory WRITE LOCALLY ORDERED BY inv_date_sk, inv_item_sk, inv_warehouse_sk;
ALTER TABLE store_returns WRITE LOCALLY ORDERED BY sr_returned_date_sk, sr_item_sk, sr_ticket_number;
ALTER TABLE catalog_returns WRITE LOCALLY ORDERED BY cr_returned_date_sk, cr_item_sk, cr_order_number;
ALTER TABLE web_returns WRITE LOCALLY ORDERED BY wr_returned_date_sk, wr_item_sk, wr_order_number;
ALTER TABLE web_sales WRITE LOCALLY ORDERED BY ws_sold_date_sk, ws_item_sk, ws_order_number;
ALTER TABLE catalog_sales WRITE LOCALLY ORDERED BY cs_sold_date_sk, cs_item_sk, cs_order_number;
ALTER TABLE store_sales WRITE LOCALLY ORDERED BY ss_sold_date_sk, ss_item_sk, ss_ticket_number;