# 建表与导入TPC-H数据
#### 创建TPC-H目标表
连接DWS数据库后执行以下命令创建目标表。
```
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,bucketnums=16384)
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,bucketnums=16384)
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,bucketnums=16384,secondary_part_column='s_suppkey',secondary_part_num=72)
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,bucketnums=16384,secondary_part_column='c_custkey',secondary_part_num=72)
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,bucketnums=16384,secondary_part_column='p_partkey',secondary_part_num=72)
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,bucketnums=16384,secondary_part_column='ps_partkey',secondary_part_num=72)
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,bucketnums=16384,secondary_part_column='o_orderkey',secondary_part_num=72)
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,bucketnums=16384,secondary_part_column='l_orderkey',secondary_part_num=72)
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')
) ;
```
 #### 安装和启动GDS
1. 参见[工具下载](https://support.huaweicloud.com/tg-dws/dws_07_0002.html)下载GDS客户端（与gsql客户端在一个包）。
2. 将GDS工具包上传至ECS的/opt目录中，本例以上传Euler Kunpeng版本的工具包为例。
3. 在工具包所在目录下，解压工具包。 
   ```
   cd /opt/
   unzip dws_client_8.1.x_euler_kunpeng_x64.zip
   ```
   
   
4. 创建用户gds_user及其所属的用户组gdsgrp。此用户用于启动GDS，且需要拥有读取数据源文件目录的权限。 
   ```
   groupadd gdsgrp
   useradd -g gdsgrp gds_user
   ```
   
   
5. 修改工具包以及数据源文件目录属主为创建的用户gds_user及其所属的用户组gdsgrp。 
   ```
   chown -R gds_user:gdsgrp /opt/
   chown -R gds_user:gdsgrp /data1
   chown -R gds_user:gdsgrp /data2
   ```
   
   
6. 切换到gds_user用户。 
   ```
   su - gds_user
   ```
   
   
7. 执行环境依赖脚本（仅8.1.x版本适用）。 
   ```
   cd /opt/gds/bin
   source gds_env
   ```
   
   
8. 启动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使用
   ```
   
   ![](https://support.huaweicloud.com/pwp-910-dws/public_sys-resources/notice_3.0-zh-cn.png)
   - 命令中的斜体部分请根据实际填写，如果数据分片存放至多个数据盘目录，需要启动对应目录数量的GDS。
   
   - 如果TPC-H和TPC-DS数据同时测试，需要启动以上4个GDS，如果只测试TPC-DS或TPC-H数据，请根据命令中的注释启动对应的GDS服务即可。
   
   - -d dir：保存有待导入数据的数据文件所在目录。
   
   - -p ip:port：GDS监听IP和监听端口。IP替换为ECS的内网IP，确保DWS能通过此IP与GDS进行通信；端口对于TPC-H取5000、5001，对于TPC-DS取5002、5003。
   
   - -H address_string：允许哪些主机连接和使用GDS服务。参数需为CIDR格式。此地址配置成DWS的集群内网网段（即GDS所在的ECS与DWS在同一个VPC下，以内网通讯即可），例如192.168.0.0/24。
   
   - -l log_file：存放GDS的日志文件路径及文件名。
   
   - -D：后台运行GDS。仅支持Linux操作系统下使用。
    
   
   
 
#### 创建TPC-H数据集的GDS外表
连接DWS数据库后执行以下SQL语句创建。
![](https://support.huaweicloud.com/pwp-910-dws/public_sys-resources/notice_3.0-zh-cn.png)
以下每个外表的"**gsfs://192.168.0.90:500x/xxx \| gsfs://192.168.0.90:500x/xxx**"中的IP地址和端口，请替换成安装和启动GDS中的对应的GDS的监听IP和端口。如启动两个GDS，则使用"\|"区分。如果启动多个GDS，需要将所有GDS的监听IP和端口配置到外表中。
```
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'
);
```
#### 导入TPC-H数据
执行以下SQL语句导入数据。
```
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;
```
#### 设置GUC参数
在执行测试之前，可以设置如下几个参数，这将给性能带来5-10%的提升。
```
cpu_tuple_cost=0.0058401054702699184
cpu_operator_cost=0.002450424712151289
cpu_index_tuple_cost=0.004948411136865616
seq_page_cost=0.3447857201099396
random_page_cost=2.3901453018188477
allocate_mem_cost=0.5275554060935974
effective_cache_size=502
smp_thread_cost=278.525634765625
stream_multiple=0.5654585361480713
```