HeatWave User Guide  /  ...  /  Loading tpch Data Into HeatWave MySQL

7.1.4 Loading tpch Data Into HeatWave MySQL

To load the tpch sample data into the HeatWave Cluster:

Note

For HeatWave on AWS, load data into HeatWave using the HeatWave Console. See Manage Data in HeatWave with Workspaces in the HeatWave on AWS Service Guide.

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

    $> mysqlsh Username@DBSystem_IP_Address_or_Host_Name

    MySQL Shell opens in JavaScript execution mode by default.

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

    MySQL>JS> \sql
  3. Execute the following operations to prepare the tpch sample database tables and load them into the HeatWave Cluster. The operations performed include defining string column encodings, defining the secondary engine, and executing SECONDARY_LOAD operations.

    mysql> USE tpch;
    
    mysql> ALTER TABLE nation modify `N_NAME` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE nation modify `N_COMMENT` VARCHAR(152) COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE nation SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE nation SECONDARY_LOAD;
    
    mysql> ALTER TABLE region modify `R_NAME` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE region modify `R_COMMENT` VARCHAR(152) COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE region SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE region SECONDARY_LOAD;
    
    mysql> ALTER TABLE part modify `P_MFGR` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE part modify `P_BRAND` CHAR(10) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE part modify `P_CONTAINER` CHAR(10) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE part modify `P_COMMENT` VARCHAR(23) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE part SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE part SECONDARY_LOAD;
    
    mysql> ALTER TABLE supplier modify `S_NAME` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE supplier modify `S_ADDRESS` VARCHAR(40) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE supplier modify `S_PHONE` CHAR(15) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    
    mysql> ALTER TABLE supplier SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE supplier SECONDARY_LOAD;
    mysql> ALTER TABLE partsupp modify `PS_COMMENT` VARCHAR(199) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE partsupp SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE partsupp SECONDARY_LOAD;
    
    mysql> ALTER TABLE customer modify `C_NAME` VARCHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE customer modify `C_ADDRESS` VARCHAR(40) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE customer modify `C_MKTSEGMENT` CHAR(10) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE customer modify `C_COMMENT` VARCHAR(117) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    
    mysql> ALTER TABLE customer SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE customer SECONDARY_LOAD;
    
    mysql> ALTER TABLE orders modify `O_ORDERSTATUS` CHAR(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE orders modify `O_ORDERPRIORITY` CHAR(15) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE orders modify `O_CLERK` CHAR(15) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE orders SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE orders SECONDARY_LOAD;
    
    mysql> ALTER TABLE lineitem modify `L_RETURNFLAG` CHAR(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE lineitem modify `L_LINESTATUS` CHAR(1) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE lineitem modify `L_SHIPINSTRUCT` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE lineitem modify `L_SHIPMODE` CHAR(10) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE lineitem modify `L_COMMENT` VARCHAR(44) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
    mysql> ALTER TABLE lineitem SECONDARY_ENGINE=RAPID;
    mysql> ALTER TABLE lineitem SECONDARY_LOAD;
  4. Verify that the tpch sample database tables are loaded in the HeatWave Cluster by querying LOAD_STATUS data from the HeatWave Performance Schema tables. Loaded tables have an AVAIL_RPDGSTABSTATE load status.

    MySQL>SQL> USE performance_schema;
    MySQL>SQL> SELECT NAME, LOAD_STATUS
              FROM rpd_tables,rpd_table_id
              WHERE rpd_tables.ID = rpd_table_id.ID;
    +------------------------------+---------------------+
    | NAME                         | LOAD_STATUS         |
    +------------------------------+---------------------+
    | tpch.supplier                | AVAIL_RPDGSTABSTATE |
    | tpch.partsupp                | AVAIL_RPDGSTABSTATE |
    | tpch.orders                  | AVAIL_RPDGSTABSTATE |
    | tpch.lineitem                | AVAIL_RPDGSTABSTATE |
    | tpch.customer                | AVAIL_RPDGSTABSTATE |
    | tpch.nation                  | AVAIL_RPDGSTABSTATE |
    | tpch.region                  | AVAIL_RPDGSTABSTATE |
    | tpch.part                    | AVAIL_RPDGSTABSTATE |
    +------------------------------+---------------------+