This topic describes how to import 100 GB of TPC-H test data to AnalyticDB for MySQL, MySQL, Presto, Apache Spark, and Apache Impala.
The following table describes the number of rows in different tables of the TPC-H dataset.
Table name | Number of rows |
---|---|
customer | 15,000,000 |
lineitem | 600,037,902 |
nation | 25 |
orders | 150,000,000 |
part | 20,000,000 |
partsupp | 80,000,000 |
region | 5 |
supplier | 1,000,000 |
AnalyticDB for MySQL and MySQL
- You can execute the LOAD DATA statement to import files that are generated by DBGEN
to AnalyticDB for MySQL and MySQL.
LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'; LOAD DATA LOCAL INFILE 'orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'; LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'; LOAD DATA LOCAL INFILE 'nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'; LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'; LOAD DATA LOCAL NFILE 'part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'; LOAD DATA LOCAL INFILE 'region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'; LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
- You can also use external tables to import data from OSS to AnalyticDB for MySQL. For more information, see Import OSS data.
Presto, Apache Spark, and Apache Impala
You can use external tables to import test data to Presto, Apache Spark, and Apache Impala.
- The customer table
insert overwrite table customer select c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment from ${source_db}.customer;
- The lineitem table
insert overwrite table lineitem select l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment from ${source_db}.lineitem;
- The nation table
insert overwrite table nation select n_nationkey, n_name, n_regionkey, n_comment from ${source_db}.nation;
- The orders table
insert overwrite table orders select o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment from ${source_db}.orders;
- The part table
insert overwrite table part select p_partkey, p_name, p_mfgr, p_brand, p_type, p_size, p_container, p_retailprice, p_comment from ${source_db}.part;
- The partsupp table
insert overwrite table partsupp select ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment from ${source_db}.partsupp;
- The region table
insert overwrite table region select r_regionkey, r_name, r_comment from ${source_db}.region;
- The supplier table
insert overwrite table supplier select s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment from ${source_db}.supplier;