更新时间:2024-09-24 GMT+08:00
建表
- 连接DWS数据库后执行以下命令创建目标表。
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 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
CREATE TABLE REGION ( R_REGIONKEY INT NOT NULL , R_NAME VARCHAR(25) NOT NULL , R_COMMENT VARCHAR(152) ) WITH (orientation=column, colversion=2.0, enable_hstore=true, enable_hstore_opt=true) DISTRIBUTE BY replication; CREATE TABLE NATION ( N_NATIONKEY INT NOT NULL , N_NAME VARCHAR(25) NOT NULL , N_REGIONKEY INT NOT NULL , N_COMMENT VARCHAR(152) ) WITH (orientation=column, colversion=2.0, enable_hstore=true, enable_hstore_opt=true) DISTRIBUTE BY replication; CREATE TABLE SUPPLIER ( S_SUPPKEY BIGINT NOT NULL , S_NAME VARCHAR(25) NOT NULL , S_ADDRESS VARCHAR(40) NOT NULL , S_NATIONKEY INT NOT NULL , S_PHONE VARCHAR(15) NOT NULL , S_ACCTBAL DECIMAL(15,2) NOT NULL , S_COMMENT VARCHAR(101) NOT NULL ) WITH (orientation=column, colversion=2.0,enable_hstore=true,enable_hstore_opt=true) DISTRIBUTE BY hash(S_SUPPKEY) PARTITION BY RANGE(S_NATIONKEY) ( PARTITION S_NATIONKEY_1 VALUES LESS THAN(1), PARTITION S_NATIONKEY_2 VALUES LESS THAN(2), PARTITION S_NATIONKEY_3 VALUES LESS THAN(3), PARTITION S_NATIONKEY_4 VALUES LESS THAN(4), PARTITION S_NATIONKEY_5 VALUES LESS THAN(5), PARTITION S_NATIONKEY_6 VALUES LESS THAN(6), PARTITION S_NATIONKEY_7 VALUES LESS THAN(7), PARTITION S_NATIONKEY_8 VALUES LESS THAN(8), PARTITION S_NATIONKEY_9 VALUES LESS THAN(9), PARTITION S_NATIONKEY_10 VALUES LESS THAN(10), PARTITION S_NATIONKEY_11 VALUES LESS THAN(11), PARTITION S_NATIONKEY_12 VALUES LESS THAN(12), PARTITION S_NATIONKEY_13 VALUES LESS THAN(13), PARTITION S_NATIONKEY_14 VALUES LESS THAN(14), PARTITION S_NATIONKEY_15 VALUES LESS THAN(15), PARTITION S_NATIONKEY_16 VALUES LESS THAN(16), PARTITION S_NATIONKEY_17 VALUES LESS THAN(17), PARTITION S_NATIONKEY_18 VALUES LESS THAN(18), PARTITION S_NATIONKEY_19 VALUES LESS THAN(19), PARTITION S_NATIONKEY_20 VALUES LESS THAN(20), PARTITION S_NATIONKEY_21 VALUES LESS THAN(21), PARTITION S_NATIONKEY_22 VALUES LESS THAN(22), PARTITION S_NATIONKEY_23 VALUES LESS THAN(23), PARTITION S_NATIONKEY_24 VALUES LESS THAN(24), PARTITION S_NATIONKEY_25 VALUES LESS THAN(25) ); CREATE TABLE CUSTOMER ( C_CUSTKEY BIGINT NOT NULL , C_NAME VARCHAR(25) NOT NULL , C_ADDRESS VARCHAR(40) NOT NULL , C_NATIONKEY INT NOT NULL , C_PHONE VARCHAR(15) NOT NULL , C_ACCTBAL DECIMAL(15,2) NOT NULL , C_MKTSEGMENT VARCHAR(10) NOT NULL , C_COMMENT VARCHAR(117) NOT NULL ) WITH (orientation=column, colversion=2.0,enable_hstore=true,enable_hstore_opt=true) DISTRIBUTE BY hash(C_CUSTKEY) PARTITION BY RANGE(C_NATIONKEY) ( PARTITION C_NATIONKEY_1 VALUES LESS THAN(1), PARTITION C_NATIONKEY_2 VALUES LESS THAN(2), PARTITION C_NATIONKEY_3 VALUES LESS THAN(3), PARTITION C_NATIONKEY_4 VALUES LESS THAN(4), PARTITION C_NATIONKEY_5 VALUES LESS THAN(5), PARTITION C_NATIONKEY_6 VALUES LESS THAN(6), PARTITION C_NATIONKEY_7 VALUES LESS THAN(7), PARTITION C_NATIONKEY_8 VALUES LESS THAN(8), PARTITION C_NATIONKEY_9 VALUES LESS THAN(9), PARTITION C_NATIONKEY_10 VALUES LESS THAN(10), PARTITION C_NATIONKEY_11 VALUES LESS THAN(11), PARTITION C_NATIONKEY_12 VALUES LESS THAN(12), PARTITION C_NATIONKEY_13 VALUES LESS THAN(13), PARTITION C_NATIONKEY_14 VALUES LESS THAN(14), PARTITION C_NATIONKEY_15 VALUES LESS THAN(15), PARTITION C_NATIONKEY_16 VALUES LESS THAN(16), PARTITION C_NATIONKEY_17 VALUES LESS THAN(17), PARTITION C_NATIONKEY_18 VALUES LESS THAN(18), PARTITION C_NATIONKEY_19 VALUES LESS THAN(19), PARTITION C_NATIONKEY_20 VALUES LESS THAN(20), PARTITION C_NATIONKEY_21 VALUES LESS THAN(21), PARTITION C_NATIONKEY_22 VALUES LESS THAN(22), PARTITION C_NATIONKEY_23 VALUES LESS THAN(23), PARTITION C_NATIONKEY_24 VALUES LESS THAN(24), PARTITION C_NATIONKEY_25 VALUES LESS THAN(25) ); CREATE TABLE PART ( P_PARTKEY BIGINT NOT NULL , P_NAME VARCHAR(55) NOT NULL , P_MFGR VARCHAR(25) NOT NULL , P_BRAND VARCHAR(10) NOT NULL , P_TYPE VARCHAR(25) NOT NULL , P_SIZE BIGINT NOT NULL , P_CONTAINER VARCHAR(10) NOT NULL , P_RETAILPRICE DECIMAL(15,2) NOT NULL , P_COMMENT VARCHAR(23) NOT NULL ) WITH (orientation=column, colversion=2.0,enable_hstore=true,enable_hstore_opt=true) DISTRIBUTE BY hash(P_PARTKEY) PARTITION BY RANGE(P_SIZE) ( PARTITION P_SIZE_1 VALUES LESS THAN(11), PARTITION P_SIZE_2 VALUES LESS THAN(21), PARTITION P_SIZE_3 VALUES LESS THAN(31), PARTITION P_SIZE_4 VALUES LESS THAN(41), PARTITION P_SIZE_5 VALUES LESS THAN(51) ); CREATE TABLE PARTSUPP ( PS_PARTKEY BIGINT NOT NULL , PS_SUPPKEY BIGINT NOT NULL , PS_AVAILQTY BIGINT NOT NULL , PS_SUPPLYCOST DECIMAL(15,2) NOT NULL , PS_COMMENT VARCHAR(199) NOT NULL ) WITH (orientation=column, colversion=2.0,enable_hstore=true,enable_hstore_opt=true) DISTRIBUTE BY hash(PS_PARTKEY) PARTITION BY RANGE(PS_AVAILQTY) ( PARTITION PS_AVAILQTY_1 VALUES LESS THAN(1000), PARTITION PS_AVAILQTY_2 VALUES LESS THAN(2000), PARTITION PS_AVAILQTY_3 VALUES LESS THAN(3000), PARTITION PS_AVAILQTY_4 VALUES LESS THAN(4000), PARTITION PS_AVAILQTY_5 VALUES LESS THAN(5000), PARTITION PS_AVAILQTY_6 VALUES LESS THAN(6000), PARTITION PS_AVAILQTY_7 VALUES LESS THAN(7000), PARTITION PS_AVAILQTY_8 VALUES LESS THAN(8000), PARTITION PS_AVAILQTY_9 VALUES LESS THAN(9000), PARTITION PS_AVAILQTY_10 VALUES LESS THAN(10000) ) ; CREATE TABLE ORDERS ( O_ORDERKEY BIGINT NOT NULL , O_CUSTKEY BIGINT NOT NULL , O_ORDERSTATUS VARCHAR(1) NOT NULL , O_TOTALPRICE DECIMAL(15,2) NOT NULL , O_ORDERDATE DATE NOT NULL , O_ORDERPRIORITY VARCHAR(15) NOT NULL , O_CLERK VARCHAR(15) NOT NULL , O_SHIPPRIORITY BIGINT NOT NULL , O_COMMENT VARCHAR(79) NOT NULL ) WITH (orientation=column, colversion=2.0,enable_hstore=true,enable_hstore_opt=true) DISTRIBUTE BY hash(O_ORDERKEY) PARTITION BY RANGE(O_ORDERDATE) ( PARTITION O_ORDERDATE_1 VALUES LESS THAN('1993-01-01 00:00:00'), PARTITION O_ORDERDATE_2 VALUES LESS THAN('1994-01-01 00:00:00'), PARTITION O_ORDERDATE_3 VALUES LESS THAN('1995-01-01 00:00:00'), PARTITION O_ORDERDATE_4 VALUES LESS THAN('1996-01-01 00:00:00'), PARTITION O_ORDERDATE_5 VALUES LESS THAN('1997-01-01 00:00:00'), PARTITION O_ORDERDATE_6 VALUES LESS THAN('1998-01-01 00:00:00'), PARTITION O_ORDERDATE_7 VALUES LESS THAN('1999-01-01 00:00:00') ) ; CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL , L_PARTKEY BIGINT NOT NULL , L_SUPPKEY BIGINT NOT NULL , L_LINENUMBER BIGINT NOT NULL , L_QUANTITY DECIMAL(15,2) NOT NULL , L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL , L_DISCOUNT DECIMAL(15,2) NOT NULL , L_TAX DECIMAL(15,2) NOT NULL , L_RETURNFLAG VARCHAR(1) NOT NULL , L_LINESTATUS VARCHAR(1) NOT NULL , L_SHIPDATE DATE NOT NULL , L_COMMITDATE DATE NOT NULL , L_RECEIPTDATE DATE NOT NULL , L_SHIPINSTRUCT VARCHAR(25) NOT NULL , L_SHIPMODE VARCHAR(10) NOT NULL , L_COMMENT VARCHAR(44) NOT NULL ) WITH (orientation=column, colversion=2.0,enable_hstore=true,enable_hstore_opt=true) DISTRIBUTE BY hash(L_ORDERKEY) PARTITION BY RANGE(L_SHIPDATE) ( PARTITION L_SHIPDATE_1 VALUES LESS THAN('1993-01-01 00:00:00'), PARTITION L_SHIPDATE_2 VALUES LESS THAN('1994-01-01 00:00:00'), PARTITION L_SHIPDATE_3 VALUES LESS THAN('1995-01-01 00:00:00'), PARTITION L_SHIPDATE_4 VALUES LESS THAN('1996-01-01 00:00:00'), PARTITION L_SHIPDATE_5 VALUES LESS THAN('1997-01-01 00:00:00'), PARTITION L_SHIPDATE_6 VALUES LESS THAN('1998-01-01 00:00:00'), PARTITION L_SHIPDATE_7 VALUES LESS THAN('1999-01-01 00:00:00') ) ;
父主题: 测试过程