This topic describes how to import 100 GB of TPC-H test data to AnalyticDB for MySQL and Presto.

The following table lists the number of rows in different tables of the TPC-H dataset.

Table name Number of rows
customer 15000000
lineitem 600037902
nation 25
orders 150000000
part 20000000
partsupp 80000000
region 5
supplier 1000000

AnalyticDB for MySQL

  • You can execute the LOAD DATA statement to import files that are generated by DBGEN to AnalyticDB for 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 to AnalyticDB for MySQL by using external tables.

Presto

You can use external tables to import test data to Presto.

  • A 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;
  • A 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;
  • A nation table
    insert overwrite table nation
    select
            n_nationkey,
            n_name,
            n_regionkey,
            n_comment
    from ${source_db}.nation;
  • An 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;
  • A 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;
  • A partsupp table
    insert overwrite table partsupp
    select
            ps_partkey,
            ps_suppkey,
            ps_availqty,
            ps_supplycost,
            ps_comment
    from ${source_db}.partsupp;
  • A region table
    insert overwrite table region
    select
            r_regionkey,
            r_name,
            r_comment
    from ${source_db}.region;
  • A 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;