# 建表与导入TPC-H数据
#### 创建TPC-H目标表
连接DWS数据库后执行以下命令创建目标表。
```
CREATE TABLE region 
(
    r_regionkey   int           not null,
    r_name        varchar(25)   not null,
    r_comment     varchar(152),
    PRIMARY KEY (r_regionkey) NOT ENFORCED ENABLE QUERY OPTIMIZATION
)
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),
    PRIMARY KEY (n_nationkey) NOT ENFORCED ENABLE QUERY OPTIMIZATION
)
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,
    PRIMARY KEY (s_suppkey) NOT ENFORCED ENABLE QUERY OPTIMIZATION,
    CONSTRAINT supplier_fk FOREIGN KEY (s_nationkey)
        REFERENCES nation (n_nationkey)
)
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,
    PRIMARY KEY (c_custkey) NOT ENFORCED ENABLE QUERY OPTIMIZATION,
    CONSTRAINT customer_fk FOREIGN KEY (c_nationkey)
        REFERENCES nation (n_nationkey)
)
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,
    PRIMARY KEY (p_partkey) NOT ENFORCED ENABLE QUERY OPTIMIZATION
)
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,
    PRIMARY KEY (ps_partkey, ps_suppkey) NOT ENFORCED ENABLE QUERY OPTIMIZATION,
    CONSTRAINT partsupp_fk1 FOREIGN KEY (ps_suppkey)
        REFERENCES supplier (s_suppkey),
    CONSTRAINT partsupp_fk2 FOREIGN KEY (ps_partkey)
        REFERENCES part (p_partkey)
)
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,
    PRIMARY KEY (o_orderkey) NOT ENFORCED ENABLE QUERY OPTIMIZATION,
    CONSTRAINT orders_fk FOREIGN KEY (o_custkey)
        REFERENCES customer (c_custkey)
)
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,
    PRIMARY KEY (l_orderkey, l_linenumber) NOT ENFORCED ENABLE QUERY OPTIMIZATION,
    CONSTRAINT lineitem_fk1 FOREIGN KEY (l_orderkey)
        REFERENCES orders(o_orderkey),
    CONSTRAINT lineitem_fk2 FOREIGN KEY (l_partkey, l_suppkey)
        REFERENCES partsupp(ps_partkey, ps_suppkey)
)
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-911-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-911-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
```