Last active
July 7, 2025 18:21
-
-
Save jster1357/f3f89e14f5678881993623e28e21b43a to your computer and use it in GitHub Desktop.
tpch ddl optimized by clustering and PK/FK soft constraints
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP TABLE tpch_optimized.region; | |
| CREATE OR REPLACE TABLE tpch_optimized.region ( | |
| r_regionkey INT64 NOT NULL PRIMARY KEY NOT ENFORCED, | |
| r_name STRING, | |
| r_comment STRING) | |
| CLUSTER BY r_regionkey; | |
| DROP TABLE tpch_optimized.nation; | |
| CREATE OR REPLACE TABLE tpch_optimized.nation ( | |
| n_nationkey INT64 NOT NULL PRIMARY KEY NOT ENFORCED, | |
| n_name STRING, | |
| n_regionkey INT64, | |
| n_comment STRING, | |
| FOREIGN KEY (n_regionkey) REFERENCES tpch_optimized.region (r_regionkey) NOT ENFORCED) | |
| CLUSTER BY n_nationkey; | |
| DROP TABLE tpch_optimized.customer; | |
| CREATE OR REPLACE TABLE tpch_optimized.customer ( | |
| c_custkey INT64 NOT NULL PRIMARY KEY NOT ENFORCED, | |
| c_name STRING, | |
| c_address STRING, | |
| c_nationkey INT64, | |
| c_phone STRING, | |
| c_acctbal FLOAT64, | |
| c_mktsegment STRING, | |
| c_comment STRING, | |
| FOREIGN KEY (c_nationkey) REFERENCES tpch_optimized.nation (n_nationkey) NOT ENFORCED) | |
| CLUSTER BY (c_custkey); | |
| DROP TABLE tpch_optimized.orders; | |
| CREATE OR REPLACE TABLE tpch_optimized.orders ( | |
| o_orderkey INT64 NOT NULL PRIMARY KEY NOT ENFORCED, | |
| o_custkey INT64, | |
| o_orderstatus STRING, | |
| o_totalprice FLOAT64, | |
| o_orderdate DATE, | |
| o_orderpriority STRING, | |
| o_clerk STRING, | |
| o_shippriority INT64, | |
| o_comment STRING, | |
| FOREIGN KEY (o_custkey) REFERENCES tpch_optimized.customer (c_custkey) NOT ENFORCED) | |
| CLUSTER BY o_orderdate; | |
| CREATE OR REPLACE TABLE tpch_optimized.supplier ( | |
| s_suppkey INT64 NOT NULL PRIMARY KEY NOT ENFORCED, | |
| s_name STRING, | |
| s_address STRING, | |
| s_nationkey INT64, | |
| s_phone STRING, | |
| s_acctbal FLOAT64, | |
| s_comment STRING, | |
| FOREIGN KEY (s_nationkey) REFERENCES tpch_optimized.nation (n_nationkey) NOT ENFORCED) | |
| CLUSTER BY s_suppkey; | |
| DROP TABLE tpch_optimized.lineitem; | |
| CREATE OR REPLACE TABLE tpch_optimized.lineitem ( | |
| l_orderkey INT64 NOT NULL, | |
| l_partkey INT64, | |
| l_suppkey INT64, | |
| l_linenumber INT64 NOT NULL, | |
| l_quantity INT64, | |
| l_extendedprice FLOAT64, | |
| l_discount FLOAT64, | |
| l_tax FLOAT64, | |
| l_returnflag STRING, | |
| l_linestatus STRING, | |
| l_shipdate DATE, | |
| l_commitdate DATE, | |
| l_receiptdate DATE, | |
| l_shipinstruct STRING, | |
| l_shipmode STRING, | |
| l_comment STRING, | |
| PRIMARY KEY (l_orderkey,l_linenumber) NOT ENFORCED, | |
| FOREIGN KEY (l_orderkey) REFERENCES tpch_optimized.orders (o_orderkey)NOT ENFORCED, | |
| FOREIGN KEY (l_partkey, l_suppkey) REFERENCES tpch_optimized.partsupp (ps_partkey, ps_suppkey)NOT ENFORCED) | |
| CLUSTER BY l_shipdate; | |
| DROP TABLE tpch_optimized.part; | |
| CREATE OR REPLACE TABLE tpch_optimized.part( | |
| p_partkey INT64 NOT NULL PRIMARY KEY NOT ENFORCED, | |
| p_name STRING, | |
| p_mfgr STRING, | |
| p_brand STRING, | |
| p_type STRING, | |
| p_size INT64, | |
| p_container STRING, | |
| p_retailprice FLOAT64, | |
| p_comment STRING) | |
| CLUSTER BY p_partkey; | |
| DROP TABLE tpch_optimized.partsupp; | |
| CREATE OR REPLACE TABLE tpch_optimized.partsupp ( | |
| ps_partkey INT64 NOT NULL, | |
| ps_suppkey INT64 NOT NULL, | |
| ps_availqty INT64, | |
| ps_supplycost FLOAT64, | |
| ps_comment STRING, | |
| PRIMARY KEY (ps_partkey,ps_suppkey)NOT ENFORCED, | |
| FOREIGN KEY (ps_partkey) REFERENCES tpch_optimized.part (p_partkey) NOT ENFORCED, | |
| FOREIGN KEY (ps_suppkey) REFERENCES tpch_optimized.supplier (s_suppkey) NOT ENFORCED | |
| ) | |
| CLUSTER BY ps_partkey,ps_suppkey; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment