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;