将dbgen生成的TPC-H测试数据从OSS导入AnalyticDB for MySQL之前,需要先在AnalyticDB for MySQL中创建对应的TPC-H OSS外表。创建外表时,需要指定对应表的OSS存储地址和OSS的访问认证信息。以下列出各个TPC-H OSS外表创建语句,您只需要更改相应的OSS存储信息即可。

CREATE TABLE oss_region(
r_regionkey int NOT NULL COMMENT'',
r_name varchar NOT NULL COMMENT'',
r_comment varchar COMMENT'',`dummy`varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/region/"
}';       
Create Table oss_customer(
c_custkey int NOT NULL COMMENT'',
c_name varchar NOT NULL COMMENT'',
c_address varchar NOT NULL COMMENT'',
c_nationkey int NOT NULL COMMENT'',
c_phone varchar NOT NULL COMMENT'',
c_acctbal decimal(15,2)NOT NULL COMMENT'',
c_mktsegment varchar NOT NULL COMMENT'',
c_comment varchar NOT NULL COMMENT''
,dummy varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/customer/"
}';     
Create Table oss_lineitem(
l_orderkey bigint NOT NULL COMMENT'',
l_partkey int NOT NULL COMMENT'',
l_suppkey int NOT NULL COMMENT'',
l_linenumber int NOT NULL COMMENT'',
l_quantity decimal(15,2) NOT NULL COMMENT'',
l_extendedprice decimal(15,2) NOT NULL COMMENT'',
l_discount decimal(15,2)NOT NULL COMMENT'',
l_tax decimal(15,2)NOT NULL COMMENT'',
l_returnflag varchar NOT NULL COMMENT'',
l_linestatus varchar NOT NULL COMMENT'',
l_shipdate date NOT NULL COMMENT'',
l_commitdate date NOT NULL COMMENT'',
l_receiptdate date NOT NULL COMMENT'',
l_shipinstruct varchar NOT NULL COMMENT'',
l_shipmode varchar NOT NULL COMMENT'',
l_comment varchar NOT NULL COMMENT''
,dummy varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/lineitem/"
}';     
Create Table oss_nation(
n_nationkey int NOT NULL COMMENT'',
n_name varchar NOT NULL COMMENT'',
n_re_ionkey int NOT NULL COMMENT'',
n_comment varchar COMMENT''
,dummy varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/nation/"
}';      
Create Table oss_orders(
o_orderkey bigint NOT NULL COMMENT'',
o_custkey int NOT NULL COMMENT'',
o_orderstatus varchar NOT NULL COMMENT'',
o_totalprice decimal(15,2)NOT NULL COMMENT'',
o_orderdate date NOT NULL COMMENT'',
o_orderpriority varchar NOT NULL COMMENT'',
o_clerk varchar NOT NULL COMMENT'',
o_shippriority int NOT NULL COMMENT'',
o_comment varchar NOT NULL COMMENT''
,dummy varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/orders/"
}';     
Create Table oss_part(
p_partkey int NOT NULL COMMENT'',
p_name varchar NOT NULL COMMENT'',
p_mfgr varchar NOT NULL COMMENT'',
p_brand varchar NOT NULL COMMENT'',
p_type varchar NOT NULL COMMENT'',
p_size int NOT NULL COMMENT'',
p_container varchar NOT NULL COMMENT'',
p_retailprice decimal(15,2)NOT NULL COMMENT'',
p_comment varchar NOT NULL COMMENT''
,dummy varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/part/"
}';     
Create Table oss_partsupp(
ps_partkey int NOT NULL COMMENT'',
ps_suppkey int NOT NULL COMMENT'',
ps_availqty int NOT NULL COMMENT'',
ps_supplycost decimal(15,2)NOT NULL COMMENT'',
ps_comment varchar NOT NULL COMMENT''
,dummy varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/partsupp/"
}';      
Create Table oss_supplier(
s_suppkey int NOT NULL COMMENT'',
s_name varchar NOT NULL COMMENT'',
s_address varchar NOT NULL COMMENT'',
s_nationkey int NOT NULL COMMENT'',
s_phone varchar NOT NULL COMMENT'',
s_acctbal decimal(15,2)NOT NULL COMMENT'',
s_comment varchar NOT NULL COMMENT''
,dummy varchar
)engine='oss'
TABLE_PROPERTIES='{
"endpoint":"oss-cn-beijing.aliyuncs.com",
"accessid":"MyAccessID",
"accesskey":"MyAccessKey",
"delimiter":"|",
"URL":"oss://tpchtest100g/supplier/"
}';