HeatWave User Guide  /  ...  /  Creating the tpch Sample Database and Importing Data

8.1.3 Creating the tpch Sample Database and Importing Data

This topic describes how to create the tpch sample database on the MySQL DB System and import the sample data. The sample data must be available on the MySQL DB System before it can be loaded into the HeatWave Cluster.

Sample database creation and import operations are performed using MySQL Shell. The MySQL Shell Parallel Table Import Utility provides fast data import for large data files. The utility analyzes an input data file, divides it into chunks, and uploads the chunks to the target MySQL DB System using parallel connections. The utility is capable of completing a large data import many times faster than a standard single-threaded upload using a LOAD DATA statement. For additional information, see Parallel Table Import Utility.

To create the tpch sample database on the MySQL DB System and import data:

  1. Start MySQL Shell and connect to the MySQL DB System endpoint:

    $> mysqlsh --mysql Username@DBSystem_IP_Address_or_Host_Name

    The --mysql option opens a ClassicSession, which is required when using the MySQL Shell Parallel Table Import Utility.

    MySQL Shell opens in JavaScript execution mode by default.

    MySQL>JS>
  2. Change the MySQL Shell execution mode from JavaScript to SQL:

    MySQL>JS> \sql
  3. Create the tpch sample database and tables:

    mysql> CREATE DATABASE tpch character set utf8mb4;
    mysql> USE tpch;
    
    mysql> CREATE TABLE nation  ( N_NATIONKEY INTEGER primary key,
        N_NAME       CHAR(25) NOT NULL,
        N_REGIONKEY  INTEGER NOT NULL,
        N_COMMENT    VARCHAR(152));
    							
    mysql> CREATE TABLE region  ( R_REGIONKEY INTEGER primary key,
        R_NAME       CHAR(25) NOT NULL,
        R_COMMENT    VARCHAR(152));
    							
    mysql> CREATE TABLE part  ( P_PARTKEY INTEGER primary key,
        P_NAME        VARCHAR(55) NOT NULL,
        P_MFGR        CHAR(25) NOT NULL,
        P_BRAND       CHAR(10) NOT NULL,
        P_TYPE        VARCHAR(25) NOT NULL,
        P_SIZE        INTEGER NOT NULL,
        P_CONTAINER   CHAR(10) NOT NULL,
        P_RETAILPRICE DECIMAL(15,2) NOT NULL,
        P_COMMENT     VARCHAR(23) NOT NULL );
    
    mysql> CREATE TABLE supplier  ( S_SUPPKEY INTEGER primary key,
        S_NAME        CHAR(25) NOT NULL,
        S_ADDRESS     VARCHAR(40) NOT NULL,
        S_NATIONKEY   INTEGER NOT NULL,
        S_PHONE       CHAR(15) NOT NULL,
        S_ACCTBAL     DECIMAL(15,2) NOT NULL,
        S_COMMENT     VARCHAR(101) NOT NULL);
    							 
    mysql> CREATE TABLE partsupp  ( PS_PARTKEY INTEGER NOT NULL,
        PS_SUPPKEY     INTEGER NOT NULL,
        PS_AVAILQTY    INTEGER NOT NULL,
        PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
        PS_COMMENT     VARCHAR(199) NOT NULL, primary key (ps_partkey, ps_suppkey) );
    						
    mysql> CREATE TABLE customer  ( C_CUSTKEY INTEGER primary key,
        C_NAME        VARCHAR(25) NOT NULL,
        C_ADDRESS     VARCHAR(40) NOT NULL,
        C_NATIONKEY   INTEGER NOT NULL,
        C_PHONE       CHAR(15) NOT NULL,
        C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
        C_MKTSEGMENT  CHAR(10) NOT NULL,
        C_COMMENT     VARCHAR(117) NOT NULL);
    							 
    mysql> CREATE TABLE orders  ( O_ORDERKEY INTEGER primary key,
        O_CUSTKEY        INTEGER NOT NULL,
        O_ORDERSTATUS    CHAR(1) NOT NULL,
        O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
        O_ORDERDATE      DATE NOT NULL,
        O_ORDERPRIORITY  CHAR(15) NOT NULL,
        O_CLERK          CHAR(15) NOT NULL,
        O_SHIPPRIORITY   INTEGER NOT NULL,
        O_COMMENT        VARCHAR(79) NOT NULL);
    
    mysql> CREATE TABLE lineitem ( L_ORDERKEY INTEGER NOT NULL,
        L_PARTKEY     INTEGER NOT NULL,
        L_SUPPKEY     INTEGER NOT NULL,
        L_LINENUMBER  INTEGER NOT NULL,
        L_QUANTITY    DECIMAL(15,2) NOT NULL,
        L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
        L_DISCOUNT    DECIMAL(15,2) NOT NULL,
        L_TAX         DECIMAL(15,2) NOT NULL,
        L_RETURNFLAG  CHAR(1) NOT NULL,
        L_LINESTATUS  CHAR(1) NOT NULL,
        L_SHIPDATE    DATE NOT NULL,
        L_COMMITDATE  DATE NOT NULL,
        L_RECEIPTDATE DATE NOT NULL,
        L_SHIPINSTRUCT CHAR(25) NOT NULL,
        L_SHIPMODE     CHAR(10) NOT NULL,
        L_COMMENT      VARCHAR(44) NOT NULL,
        primary key(L_ORDERKEY,L_LINENUMBER));
  4. Verify that the tpch schema and tables were created:

    MySQL>SQL> SHOW TABLES;
    +----------------+
    | Tables_in_tpch |
    +----------------+
    | customer       |
    | lineitem       |
    | nation         |
    | orders         |
    | part           |
    | partsupp       |
    | region         |
    | supplier       |
    +----------------+
  5. Change back to JavaScript execution mode to use the Parallel Table Import Utility:

    MySQL>SQL> \js
  6. Execute the following operations to import the data into the tpch database on the MySQL DB System.

    Note

    For information about the util.importTable() options used in the following commands, see Parallel Table Import Utility. The number of parallel threads specified using the threads option depends on the number of CPU cores of the shape. It is assumed that sample data fields are terminated by the pipe "|" character.

    MySQL>JS> util.importTable("nation.tbl",{table: "nation", fieldsTerminatedBy:"|", 
    bytesPerChunk:"100M", threads:16})
    
    MySQL>JS> util.importTable("region.tbl",{table: "region", fieldsTerminatedBy:"|", 
    bytesPerChunk:"100M", threads:16})
    
    MySQL>JS> util.importTable("part.tbl",{table: "part", fieldsTerminatedBy:"|", 
    bytesPerChunk:"100M", threads:16})
    
    MySQL>JS> util.importTable("supplier.tbl",{table: "supplier", fieldsTerminatedBy:"|" , 
    bytesPerChunk:"100M", threads:16})
    
    MySQL>JS> util.importTable("partsupp.tbl",{table: "partsupp", fieldsTerminatedBy:"|", 
    bytesPerChunk:"100M", threads:16})
    
    MySQL>JS> util.importTable("customer.tbl",{table: "customer", fieldsTerminatedBy:"|", 
    bytesPerChunk:"100M", threads:16})
    
    MySQL>JS> util.importTable("orders.tbl",{table: "orders", fieldsTerminatedBy:"|", 
    bytesPerChunk:"100M", threads:16})
    
    MySQL>JS> util.importTable("lineitem.tbl",{table: "lineitem", fieldsTerminatedBy:"|", 
    bytesPerChunk:"100M", threads:16})