文档首页/ 数据仓库服务 GaussDB(DWS)/ 性能白皮书(9.1.0.x)/ SSB性能测试/ 测试过程/ 导入数据
更新时间：2024-09-24 GMT+08:00
查看PDF
分享

导入数据

创建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
 
DROP FOREIGN TABLE IF EXISTS customer_load;
CREATE FOREIGN TABLE customer_load
(
   C_CUSTKEY     BIGINT NOT NULL
  , C_NAME        VARCHAR(25) NOT NULL
  , C_ADDRESS     VARCHAR(40) NOT NULL
  , C_CITY        VARCHAR(25) NOT NULL
  , C_NATION      VARCHAR(25) NOT NULL
  , C_REGION      VARCHAR(25) NOT NULL
  , C_PHONE       VARCHAR(15) NOT NULL
  , C_MKTSEGMENT  VARCHAR(10) NOT NULL)
SERVER gsmpp_server
OPTIONS(location 'gsfs://192.168.0.90:5004/customer.tbl*',
format 'text',
delimiter '|',
encoding 'utf8',
mode 'Normal'
);
 
DROP FOREIGN TABLE IF EXISTS supplier_load;
CREATE FOREIGN TABLE supplier_load
(
 S_SUPPKEY  BIGINT NOT NULL
  , S_NAME  VARCHAR(25) NOT NULL
  , S_ADDRESS  VARCHAR(40) NOT NULL
  , S_CITY        VARCHAR(25) NOT NULL
  , S_NATION      VARCHAR(25) NOT NULL
  , S_REGION      VARCHAR(25) NOT NULL
  , S_PHONE VARCHAR(15) NOT NULL)
SERVER gsmpp_server
OPTIONS(location 'gsfs://192.168.0.90:5004/supplier.tbl*',
format 'text',
delimiter '|',
encoding 'utf8',
mode 'Normal'
);
 
DROP FOREIGN TABLE IF EXISTS part_load;
CREATE FOREIGN TABLE part_load
(
 P_PARTKEY  BIGINT NOT NULL
  , P_NAME  VARCHAR(55) NOT NULL
  , P_MFGR  VARCHAR(25) NOT NULL
  , P_CATEGORY   VARCHAR(25) NOT NULL
  , P_BRAND VARCHAR(10) NOT NULL
  , P_COLOR VARCHAR(20) NOT NULL
  , P_TYPE  VARCHAR(25) NOT NULL
  , P_SIZE  BIGINT NOT NULL
  , P_CONTAINER   VARCHAR(10) NOT NULL)
SERVER gsmpp_server
OPTIONS(location 'gsfs://192.168.0.90:5004/part.tbl*',
format 'text',
delimiter '|',
encoding 'utf8',
mode 'Normal'
);
 
DROP FOREIGN TABLE IF EXISTS lineorder_load;
CREATE FOREIGN TABLE lineorder_load
(
    LO_ORDERKEY             BIGINT NOT NULL,
    LO_LINENUMBER           BIGINT NOT NULL,
    LO_CUSTKEY              BIGINT NOT NULL,
    LO_PARTKEY              BIGINT NOT NULL,
    LO_SUPPKEY              BIGINT NOT NULL,
    LO_ORDERDATE            DATE NOT NULL,
    LO_ORDERPRIORITY        VARCHAR(15) NOT NULL,
    LO_SHIPPRIORITY         BIGINT NOT NULL,
    LO_QUANTITY             BIGINT NOT NULL,
    LO_EXTENDEDPRICE        BIGINT NOT NULL,
    LO_ORDTOTALPRICE        BIGINT NOT NULL,
    LO_DISCOUNT             BIGINT NOT NULL,
    LO_REVENUE              BIGINT NOT NULL,
    LO_SUPPLYCOST           BIGINT NOT NULL,
    LO_TAX                  BIGINT NOT NULL,
    LO_COMMITDATE           DATE NOT NULL,
    LO_SHIPMODE             VARCHAR(10) NOT NULL)
SERVER gsmpp_server
OPTIONS(location 'gsfs://192.168.0.90:5004/lineorder.tbl*',
format 'text',
delimiter '|',
encoding 'utf8',
mode 'Normal'
);

导入数据

执行以下命令导入数据。

 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
 
INSERT INTO customer SELECT * FROM customer_load;
INSERT INTO supplier SELECT * FROM supplier_load;
INSERT INTO part SELECT * FROM part_load;
INSERT INTO lineorder SELECT * FROM lineorder_load;
 
INSERT INTO lineorder_flat
SELECT
 l.LO_ORDERKEY AS LO_ORDERKEY,
 l.LO_LINENUMBER AS LO_LINENUMBER,
 l.LO_CUSTKEY AS LO_CUSTKEY,
 l.LO_PARTKEY AS LO_PARTKEY,
 l.LO_SUPPKEY AS LO_SUPPKEY,
 l.LO_ORDERDATE AS LO_ORDERDATE,
 l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
 l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
 l.LO_QUANTITY AS LO_QUANTITY,
 l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
 l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
 l.LO_DISCOUNT AS LO_DISCOUNT,
 l.LO_REVENUE AS LO_REVENUE,
 l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
 l.LO_TAX AS LO_TAX,
 l.LO_COMMITDATE AS LO_COMMITDATE,
 l.LO_SHIPMODE AS LO_SHIPMODE,
 c.C_NAME AS C_NAME,
 c.C_ADDRESS AS C_ADDRESS,
 c.C_CITY AS C_CITY,
 c.C_NATION AS C_NATION,
 c.C_REGION AS C_REGION,
 c.C_PHONE AS C_PHONE,
 c.C_MKTSEGMENT AS C_MKTSEGMENT,
 s.S_NAME AS S_NAME,
 s.S_ADDRESS AS S_ADDRESS,
 s.S_CITY AS S_CITY,
 s.S_NATION AS S_NATION,
 s.S_REGION AS S_REGION,
 s.S_PHONE AS S_PHONE,
 p.P_NAME AS P_NAME,
 p.P_MFGR AS P_MFGR,
 p.P_CATEGORY AS P_CATEGORY,
 p.P_BRAND AS P_BRAND,
 p.P_COLOR AS P_COLOR,
 p.P_TYPE AS P_TYPE,
 p.P_SIZE AS P_SIZE,
 p.P_CONTAINER AS P_CONTAINER
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;
父主题： 测试过程

相关文档