全部产品
云市场

创建TPC-H OSS外表

更新时间:2019-11-25 15:45:37

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

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