# 创建GDS外表并导入TPC-H数据
本文介绍如何通过GDS外表导入TPC-H 1000x数据，[表1]列出了TPC-H测试数据集中的表数据行数。
![](https://support.huaweicloud.com/pwp-dws/public_sys-resources/notice_3.0-zh-cn.png)
TPC-DS请跳过本章节。
#### 表数据行数
 表1TPC-H 
| 序号 | 表名       | 行数            |
|:---|:---|:---|
| 1  | region   | 5             |
| 2  | nation   | 25            |
| 3  | supplier | 10,000,000    |
| 4  | customer | 150,000,000   |
| 5  | part     | 200,000,000   |
| 6  | partsupp | 800,000,000   |
| 7  | orders   | 1,500,000,000 |
| 8  | lineitem | 5,999,989,709 |
   
#### 操作步骤
1. 使用gsql连接DWS成功后，执行以下命令创建目标表（8张表）。 
   ```
   DROP TABLE IF EXISTS region;
   CREATE TABLE region
   (
       R_REGIONKEY  INT NOT NULL,
       R_NAME       CHAR(25) NOT NULL,
       R_COMMENT    VARCHAR(152)
   )
   with (orientation = column)
   distribute by replication;
   DROP TABLE IF EXISTS nation;
   CREATE TABLE nation
   (
       N_NATIONKEY  INT NOT NULL,
       N_NAME       CHAR(25) NOT NULL,
       N_REGIONKEY  INT NOT NULL,
       N_COMMENT    VARCHAR(152)
   )
   with (orientation = column)
   distribute by replication;
   DROP TABLE IF EXISTS supplier;
   CREATE TABLE supplier
   (
       S_SUPPKEY     INT NOT NULL,
       S_NAME        CHAR(25) NOT NULL,
       S_ADDRESS     VARCHAR(40) NOT NULL,
       S_NATIONKEY   INT NOT NULL,
       S_PHONE       CHAR(15) NOT NULL,
       S_ACCTBAL     DECIMAL(15,2) NOT NULL,
       S_COMMENT     VARCHAR(101) NOT NULL
   )
   with (orientation = column)
   distribute by hash(S_SUPPKEY);
   DROP TABLE IF EXISTS customer;
   CREATE TABLE customer
   (
       C_CUSTKEY     INT NOT NULL,
       C_NAME        VARCHAR(25) NOT NULL,
       C_ADDRESS     VARCHAR(40) NOT NULL,
       C_NATIONKEY   INT NOT NULL,
       C_PHONE       CHAR(15) NOT NULL,
       C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
       C_MKTSEGMENT  CHAR(10) NOT NULL,
       C_COMMENT     VARCHAR(117) NOT NULL
   )
   with (orientation = column)
   distribute by hash(C_CUSTKEY);
   DROP TABLE IF EXISTS part;
   CREATE TABLE part
   (
       P_PARTKEY     INT NOT NULL,
       P_NAME        VARCHAR(55) NOT NULL,
       P_MFGR        CHAR(25) NOT NULL,
       P_BRAND       CHAR(10) NOT NULL,
       P_TYPE        VARCHAR(25) NOT NULL,
       P_SIZE        INT NOT NULL,
       P_CONTAINER   CHAR(10) NOT NULL,
       P_RETAILPRICE DECIMAL(15,2) NOT NULL,
       P_COMMENT     VARCHAR(23) NOT NULL
   )
   with (orientation = column)
   distribute by hash(P_PARTKEY);
   DROP TABLE IF EXISTS partsupp;
   CREATE TABLE partsupp
   (
       PS_PARTKEY     INT NOT NULL,
       PS_SUPPKEY     INT NOT NULL,
       PS_AVAILQTY    INT NOT NULL,
       PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
       PS_COMMENT     VARCHAR(199) NOT NULL
   )
   with (orientation = column)
   distribute by hash(PS_PARTKEY);
   DROP TABLE IF EXISTS orders;
   CREATE TABLE orders
   (
       O_ORDERKEY       BIGINT NOT NULL,
       O_CUSTKEY        INT NOT NULL,
       O_ORDERSTATUS    CHAR(1) NOT NULL,
       O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
       O_ORDERDATE      DATE NOT NULL,
       O_ORDERPRIORITY  CHAR(15) NOT NULL,
       O_CLERK          CHAR(15) NOT NULL,
       O_SHIPPRIORITY   INT NOT NULL,
       O_COMMENT        VARCHAR(79) NOT NULL
   )
   with (orientation = column)
   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')
   );
   DROP TABLE IF EXISTS lineitem;
   CREATE TABLE lineitem
   (
       L_ORDERKEY    BIGINT NOT NULL,
       L_PARTKEY     INT NOT NULL,
       L_SUPPKEY     INT NOT NULL,
       L_LINENUMBER  INT 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  CHAR(1) NOT NULL,
       L_LINESTATUS  CHAR(1) NOT NULL,
       L_SHIPDATE    DATE NOT NULL,
       L_COMMITDATE  DATE NOT NULL,
       L_RECEIPTDATE DATE NOT NULL,
       L_SHIPINSTRUCT CHAR(25) NOT NULL,
       L_SHIPMODE     CHAR(10) NOT NULL,
       L_COMMENT      VARCHAR(44) NOT NULL
   )
   with (orientation = column)
   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')
   );
   ```
   
   
2. 执行以下命令创建GDS外表（8张表）。 
   ![](https://support.huaweicloud.com/pwp-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](https://support.huaweicloud.com/pwp-dws/dws_13_00023.html#ZH-CN_TOPIC_0000001291063182__zh-cn_topic_0000001290581002_section165368835217)中的对应的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* | gsfs://192.168.0.90:5001/region*',
   format 'text',
   delimiter '|',
   encoding 'utf8',
   FILL_MISSING_FIELDS 'true',
   IGNORE_EXTRA_DATA 'true',
   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* | gsfs://192.168.0.90:5001/nation*',
   format 'text',
   delimiter '|',
   encoding 'utf8',
   FILL_MISSING_FIELDS 'true',
   IGNORE_EXTRA_DATA 'true',
   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* | gsfs://192.168.0.90:5001/supplier*',
   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* | gsfs://192.168.0.90:5001/customer*',
   format 'text',
   delimiter '|',
   encoding 'utf8',
   FILL_MISSING_FIELDS 'true',
   IGNORE_EXTRA_DATA 'true',
   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.* | gsfs://192.168.0.90:5001/part.*',
   format 'text',
   delimiter '|',
   encoding 'utf8',
   FILL_MISSING_FIELDS 'true',
   IGNORE_EXTRA_DATA 'true',
   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* | gsfs://192.168.0.90:5001/partsupp*',
   format 'text',
   delimiter '|',
   encoding 'utf8',
   FILL_MISSING_FIELDS 'true',
   IGNORE_EXTRA_DATA 'true',
   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* | gsfs://192.168.0.90:5001/orders*',
   format 'text',
   delimiter '|',
   encoding 'utf8',
   FILL_MISSING_FIELDS 'true',
   IGNORE_EXTRA_DATA 'true',
   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* | gsfs://192.168.0.90:5001/lineitem*',
   format 'text',
   delimiter '|',
   encoding 'utf8',
   FILL_MISSING_FIELDS 'true',
   IGNORE_EXTRA_DATA 'true',
   mode 'Normal'
   );
   ```
   
   
3. 执行以下命令导入数据。 
   ```
   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;
   ```