导入TPC-H数据 执行以下SQL语句导入数据。 1 2 3 4 5 6 7 8 INSERT INTO region SELECT * FROM region_load; INSERT INTO nation SELECT * FROM nation_load; INSERT INTO supplier SELECT * FROM supplier_load; INSERT INTO customer SELECT * FROM customer_load; INSERT INTO part SELECT * FROM part_load; INSERT INTO partsupp SELECT * FROM partsupp_load; INSERT INTO orders SELECT * FROM orders_load; INSERT INTO lineitem SELECT * FROM lineitem_load;

创建TPC-H数据集的GDS外表 连接DWS数据库后执行以下SQL语句创建。 以下每个外表的“gsfs://192.168.0.90:500x/xxx | gsfs://192.168.0.90:500x/xxx”中的IP地址和端口，请替换成安装和启动GDS中的对应的GDS的监听IP和端口。如启动两个GDS，则使用“|”区分。如果启动多个GDS，需要将所有GDS的监听IP和端口配置到外表中。 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 DROP FOREIGN TABLE IF EXISTS region_load; CREATE FOREIGN TABLE region_load ( R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT VARCHAR(152) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/region.tbl* | gsfs://192.168.0.90:5001/region.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' ); DROP FOREIGN TABLE IF EXISTS nation_load; CREATE FOREIGN TABLE nation_load ( N_NATIONKEY INT, N_NAME CHAR(25), N_REGIONKEY INT, N_COMMENT VARCHAR(152) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/nation.tbl* | gsfs://192.168.0.90:5001/nation.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' ); DROP FOREIGN TABLE IF EXISTS supplier_load; CREATE FOREIGN TABLE supplier_load ( S_SUPPKEY INT, S_NAME CHAR(25), S_ADDRESS VARCHAR(40), S_NATIONKEY INT, S_PHONE CHAR(15), S_ACCTBAL DECIMAL(15,2), S_COMMENT VARCHAR(101) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/supplier.tbl* | gsfs://192.168.0.90:5001/supplier.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' ); DROP FOREIGN TABLE IF EXISTS customer_load; CREATE FOREIGN TABLE customer_load ( C_CUSTKEY INT, C_NAME VARCHAR(25), C_ADDRESS VARCHAR(40), C_NATIONKEY INT, C_PHONE CHAR(15), C_ACCTBAL DECIMAL(15,2), C_MKTSEGMENT CHAR(10), C_COMMENT VARCHAR(117) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/customer.tbl* | gsfs://192.168.0.90:5001/customer.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' ); DROP FOREIGN TABLE IF EXISTS part_load; CREATE FOREIGN TABLE part_load ( P_PARTKEY INT, P_NAME VARCHAR(55), P_MFGR CHAR(25), P_BRAND CHAR(10), P_TYPE VARCHAR(25), P_SIZE INT, P_CONTAINER CHAR(10), P_RETAILPRICE DECIMAL(15,2), P_COMMENT VARCHAR(23) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/part.tbl* | gsfs://192.168.0.90:5001/part.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' ); DROP FOREIGN TABLE IF EXISTS partsupp_load; CREATE FOREIGN TABLE partsupp_load ( PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DECIMAL(15,2), PS_COMMENT VARCHAR(199) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/partsupp.tbl* | gsfs://192.168.0.90:5001/partsupp.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' ); DROP FOREIGN TABLE IF EXISTS orders_load; CREATE FOREIGN TABLE orders_load ( O_ORDERKEY BIGINT, O_CUSTKEY INT, O_ORDERSTATUS CHAR(1), O_TOTALPRICE DECIMAL(15,2), O_ORDERDATE DATE, O_ORDERPRIORITY CHAR(15), O_CLERK CHAR(15), O_SHIPPRIORITY INT, O_COMMENT VARCHAR(79) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/orders.tbl* | gsfs://192.168.0.90:5001/orders.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' ); DROP FOREIGN TABLE IF EXISTS lineitem_load; CREATE FOREIGN TABLE lineitem_load ( L_ORDERKEY BIGINT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DECIMAL(15,2), L_EXTENDEDPRICE DECIMAL(15,2), L_DISCOUNT DECIMAL(15,2), L_TAX DECIMAL(15,2), L_RETURNFLAG CHAR(1), L_LINESTATUS CHAR(1), L_SHIPDATE DATE, L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT CHAR(25), L_SHIPMODE CHAR(10), L_COMMENT VARCHAR(44) ) SERVER gsmpp_server OPTIONS(location 'gsfs://192.168.0.90:5000/lineitem.tbl* | gsfs://192.168.0.90:5001/lineitem.tbl*', format 'text', deLIMITer '|', encoding 'utf8', mode 'Normal' );

安装和启动GDS 参见工具下载下载GDS客户端（与gsql客户端在一个包）。 将GDS工具包上传至E CS 的/opt目录中，本例以上传Euler Kunpeng版本的工具包为例。 在工具包所在目录下，解压工具包。 cd /opt/ unzip dws_client_8.1.x_euler_kunpeng_x64.zip 创建用户gds_user及其所属的用户组gdsgrp。此用户用于启动GDS，且需要拥有读取数据源文件目录的权限。 groupadd gdsgrp useradd -g gdsgrp gds_user 修改工具包以及数据源文件目录属主为创建的用户gds_user及其所属的用户组gdsgrp。 chown -R gds_user:gdsgrp /opt/ chown -R gds_user:gdsgrp /data1 chown -R gds_user:gdsgrp /data2 切换到gds_user用户。 su - gds_user 执行环境依赖脚本（仅8.1.x版本适用）。 cd /opt/gds/bin source gds_env 启动GDS。 /opt/gds/bin/gds -d /data1/script/tpch-kit/tpch1000X -p 192.168.0.90:5000 -H 192.168.0.0/24 -l /opt/gds/gds01_log.txt -D #TPC-H使用 /opt/gds/bin/gds -d /data2/script/tpch-kit/tpch1000X -p 192.168.0.90:5001 -H 192.168.0.0/24 -l /opt/gds/gds02_log.txt -D #TPC-H使用 /opt/gds/bin/gds -d /data1/script/tpcds-kit/tpcds1000X/ -p 192.168.0.90:5002 -H 192.168.0.0/24 -l /opt/gds/gds03_log.txt -D #TPC-DS使用 /opt/gds/bin/gds -d /data2/script/tpcds-kit/tpcds1000X/ -p 192.168.0.90:5003 -H 192.168.0.0/24 -l /opt/gds/gds04_log.txt -D #TPC-DS使用 /opt/gds/bin/gds -d /data1/script/ssb-kit/ssb100X/ -p 192.168.0.90:5004 -H 192.168.0.0/24 -l /opt/gds/gds05_log.txt -D #SSB使用 命令中的斜体部分请根据实际填写，如果数据分片存放至多个数据盘目录，需要启动对应目录数量的GDS。 如果TPC-H和TPC-DS数据同时测试，需要启动以上4个GDS，如果只测试TPC-DS或TPC-H数据，请根据后面的“#xxx”备注启动对应的GDS服务即可。 -d dir：保存有待导入数据的数据文件所在目录。 -p ip:port：GDS监听IP和监听端口。IP替换为ECS的内网IP，确保 GaussDB (DWS)能通过此IP与GDS的通讯；端口对于TPC-H取5000、5001，对于TPC-DS取5002、5003。 -H address_string：允许哪些主机连接和使用GDS服务。参数需为CIDR格式。此地址配置成GaussDB(DWS)的集群内网网段（即GDS所在的ECS与GaussDB(DWS)在同一个VPC下，以内网通讯即可），例如192.168.0.0/24。 -l log_file：存放GDS的日志文件路径及文件名。 -D：后台运行GDS。仅支持Linux操作系统下使用。

创建TPC-H目标表 连接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') ) ;

TPC-H测试数据 表1 TPC-H测试数据 序号 表名 行数 表大小 1 region 5 294KB 2 nation 25 298KB 3 supplier 10,000,000 1020MB 4 customer 150,000,000 8226MB 5 part 200,000,000 5216MB 6 partsupp 800,000,000 29GB 7 orders 1,500,000,000 43GB 8 lineitem 5,999,989,709 171GB 父主题： TPC-H测试过程