Related Documentation Download this Manual
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb


HeatWave User Guide  /  Reference  /  Metadata Queries

11.5 Metadata Queries

This section provides metadata queries that you can use to retrieve information about data, queries, and HeatWave.

  • To identify table columns defined as NOT SECONDARY on the MySQL DB System, query the EXTRA column of the INFORMATION_SCHEMA.COLUMNS table. For example:

    mysql> SELECT COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME LIKE 't1' AND EXTRA LIKE '%NOT SECONDARY%';
    +-------------+---------------+
    | COLUMN_NAME | EXTRA         |
    +-------------+---------------+
    | O_COMMENT   | NOT SECONDARY |
    +-------------+---------------+

    You can also view columns defined as NOT SECONDARY for an individual table using SHOW CREATE TABLE.

  • To identify explicitly encoded string columns in tables on the MySQL DB System, query the COLUMN_COMMENT column of the INFORMATION_SCHEMA.COLUMNS table. For example:

    mysql> SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME LIKE 'orders' AND COLUMN_COMMENT LIKE '%ENCODING%';
    +-----------------+------------------------------+
    | COLUMN_NAME     | COLUMN_COMMENT               |
    +-----------------+------------------------------+
    | O_CLERK         | RAPID_COLUMN=ENCODING=SORTED |
    | O_ORDERPRIORITY | RAPID_COLUMN=ENCODING=SORTED |
    | O_ORDERSTATUS   | RAPID_COLUMN=ENCODING=SORTED |
    | O_CLERK         | RAPID_COLUMN=ENCODING=SORTED |
    | O_ORDERPRIORITY | RAPID_COLUMN=ENCODING=SORTED |
    | O_ORDERSTATUS   | RAPID_COLUMN=ENCODING=SORTED |
    +-----------------+------------------------------+

    You can also view explicitly defined column encodings for an individual table using SHOW CREATE TABLE.

  • To identify columns defined as data placement keys in tables on the MySQL DB System, query the COLUMN_COMMENT column of the INFORMATION_SCHEMA.COLUMNS table. For example:

    mysql> SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME LIKE 'orders' AND COLUMN_COMMENT LIKE '%DATA_PLACEMENT_KEY%';
    +-----------------+-----------------------------------+
    | COLUMN_NAME     | COLUMN_COMMENT                    |
    +-----------------+-----------------------------------+
    | O_CUSTKEY       | RAPID_COLUMN=DATA_PLACEMENT_KEY=1 |
    +-----------------+-----------------------------------+

    You can also view data placement keys for an individual table using SHOW CREATE TABLE.

  • To identify columns defined as data placement keys in tables that are loaded in HeatWave, query the DATA_PLACEMENT_INDEX column of the performance_schema.rpd_columns table for columns with a DATA_PLACEMENT_INDEX value greater than 0, which indicates that the column is defined as a data placement key. For example:

    mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_PLACEMENT_INDEX 
           FROM performance_schema.rpd_columns r1 
           JOIN performance_schema.rpd_column_id r2 ON r1.COLUMN_ID = r2.ID 
           WHERE r1.TABLE_ID = (SELECT ID FROM performance_schema.rpd_table_id 
           WHERE TABLE_NAME = 'orders') AND r2.TABLE_NAME = 'orders' 
           AND r1.DATA_PLACEMENT_INDEX > 0 ORDER BY r1.DATA_PLACEMENT_INDEX;
    +------------+--------------+----------------------+
    | TABLE_NAME | COLUMN_NAME | DATA_PLACEMENT_INDEX  |
    +------------+--------------+----------------------+
    | orders     | O_TOTALPRICE |                    1 |
    | orders     | O_ORDERDATE  |                    2 |
    | orders     | O_COMMENT    |                    3 |
    +------------+--------------+----------------------+

    For information about data placement key index values, see Section 3.4, “Defining Data Placement Keys”.

  • To determine if data placement partitions were used by a JOIN or GROUP BY query, you can query the QEP_TEXT column of the performance_schema.rpd_query_stats table to view prepart data. (prepart is short for pre-partitioning.) The prepart data for a GROUP BY operation contains a single value; for example: "prepart":#, where # represents the number of HeatWave nodes. A value greater than 1 indicates that data placement partitions were used. The prepart data for a JOIN operation has two values that indicate the number of HeatWave nodes; one for each JOIN branch; for example: "prepart":[#,#]. A value greater than 1 for a JOIN branch indicates that the JOIN branch used data placement partitions. (A value of "prepart":[1,1] indicates that data placement partitions were not used by either JOIN branch.) prepart data is only generated if a GROUP BY or JOIN operation is executed. To query QEP_TEXT prepart data for the last executed query:

    mysql> SELECT CONCAT( '"prepart":[', (JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][0]')), 
           "," ,(JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][1]')) , ']' ) 
           FROM performance_schema.rpd_query_stats WHERE query_id = (select max(query_id) 
           FROM performance_schema.rpd_query_stats);
    +-----------------------------------------------------------------------------+
    | concat( '"prepart":[', (JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][0]')), |
    |"," ,(JSON_EXTRACT(QEP_TEXT->>"$**.prepart", '$[0][1]')) , ']' )             |
    +-----------------------------------------------------------------------------+
    | "prepart":[2,2]                                                             |
    +-----------------------------------------------------------------------------+
  • To identify tables on the MySQL DB System that are defined with a secondary engine, query the CREATE_OPTIONS column of the INFORMATION_SCHEMA.TABLES table. The CREATE_OPTIONS column shows the SECONDARY_ENGINE clause, if defined.

    mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES 
           WHERE CREATE_OPTIONS LIKE '%SECONDARY_ENGINE%' AND TABLE_SCHEMA LIKE 'tpch';
    +--------------+------------+--------------------------+
    | TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS           |
    +--------------+------------+--------------------------+
    | tpch         | customer   | SECONDARY_ENGINE="RAPID" |
    | tpch         | lineitem   | SECONDARY_ENGINE="RAPID" |
    | tpch         | nation     | SECONDARY_ENGINE="RAPID" |
    | tpch         | orders     | SECONDARY_ENGINE="RAPID" |
    | tpch         | part       | SECONDARY_ENGINE="RAPID" |
    | tpch         | partsupp   | SECONDARY_ENGINE="RAPID" |
    | tpch         | region     | SECONDARY_ENGINE="RAPID" |
    | tpch         | supplier   | SECONDARY_ENGINE="RAPID" |
    +--------------+------------+--------------------------+

    You can also view create options for an individual table using SHOW CREATE TABLE.

    Note

    You can use the show_create_table_skip_secondary_engine variable to exclude the SECONDARY ENGINE clause from SHOW CREATE TABLE output, and from CREATE TABLE statements dumped by the mysqldump utility. mysqldump also provides a --show-create-skip-secondary-engine option that enables the show_create_table_skip_secondary_engine system variable for the duration of the dump operation. It may be necessary to exclude the SECONDARY ENGINE option from CREATE TABLE statements when creating a dump file, as DDL operations cannot be performed on tables defined with a secondary engine.

  • The time required to load a table into HeatWave depends on data size. You can monitor load progress by issuing the following query, which returns a percentage value indicating load progress.

    mysql> SELECT VARIABLE_VALUE
           FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'rapid_load_progress';
    +----------------+
    | VARIABLE_VALUE |
    +----------------+
    | 100.000000     |
    +----------------+
  • To check the load status of a table in the HeatWave cluster, query the LOAD_STATUS data from HeatWave Performance Schema tables. For example:

    mysql> USE performance_schema;
    mysql> SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id
           WHERE rpd_tables.ID = rpd_table_id.ID AND SCHEMA_NAME LIKE 'tpch';
    +------------------------------+---------------------+
    | 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 |
    +------------------------------+---------------------+

    For information about load statuses, see Section 11.10.4, “The rpd_tables Table”.

  • To check whether change propagation is enabled or disabled for a particular table, query the POOL_TYPE data from the HeatWave Performance Schema tables. RAPID_LOAD_POOL_TRANSACTIONAL indicates that change propagation is enabled for the table. RAPID_LOAD_POOL_SNAPSHOT indicates that change propagation is disabled.

    mysql> SELECT NAME, POOL_TYPE FROM rpd_tables,rpd_table_id 
           WHERE rpd_tables.ID = rpd_table_id.ID AND SCHEMA_NAME LIKE 'tpch';
    +---------------+-------------------------------+
    | NAME          | POOL_TYPE                     |
    +---------------+-------------------------------+
    | tpch.orders   | RAPID_LOAD_POOL_TRANSACTIONAL |
    | tpch.region   | RAPID_LOAD_POOL_TRANSACTIONAL |
    | tpch.lineitem | RAPID_LOAD_POOL_TRANSACTIONAL |
    | tpch.supplier | RAPID_LOAD_POOL_TRANSACTIONAL |
    | tpch.partsupp | RAPID_LOAD_POOL_TRANSACTIONAL |
    | tpch.part     | RAPID_LOAD_POOL_TRANSACTIONAL |
    | tpch.customer | RAPID_LOAD_POOL_TRANSACTIONAL |
    +---------------+-------------------------------+

    To check the global change propagation status, query the rapid_change_propagation_status variable:

    mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status 
           WHERE VARIABLE_NAME = 'rapid_change_propagation_status';
    +----------------+
    | VARIABLE_VALUE |
    +----------------+
    | ON             |
    +----------------+
  • To view the number of queries offloaded to the HeatWave cluster for execution:

    mysql> SELECT VARIABLE_VALUE
           FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'rapid_query_offload_count';
    +----------------+
    | VARIABLE_VALUE |
    +----------------+
    | 62             |
    +----------------+
  • To view HeatWave query history including query start time, end time, and wait time in the scheduling queue, as discussed in Auto Scheduling.

    SELECT QUERY_ID,
        CONNECTION_ID,
        QUERY_START,
        QUERY_END,
        QUEUE_WAIT,
        SUBTIME(
            SUBTIME(QUERY_END, SEC_TO_TIME(RPD_EXEC / 1000)),
            SEC_TO_TIME(GET_RESULT / 1000)
        ) AS EXEC_START
    FROM (
            SELECT QUERY_ID,
                STR_TO_DATE(
                    JSON_UNQUOTE(
                        JSON_EXTRACT(QEXEC_TEXT->>"$**.queryStartTime", '$[0]')
                    ),
                    '%Y-%m-%d %H:%i:%s.%f'
                ) AS QUERY_START,
                JSON_EXTRACT(QEXEC_TEXT->>"$**.timeBetweenMakePushedJoinAndRpdExec", '$[0]') 
                AS QUEUE_WAIT,
                STR_TO_DATE(
                    JSON_UNQUOTE(
                        JSON_EXTRACT(QEXEC_TEXT->>"$**.queryEndTime", '$[0]')
                    ),
                    '%Y-%m-%d %H:%i:%s.%f'
                ) AS QUERY_END,
                JSON_EXTRACT(QEXEC_TEXT->>"$**.rpdExec.msec", '$[0]') AS RPD_EXEC,
                JSON_EXTRACT(QEXEC_TEXT->>"$**.getResults.msec", '$[0]') AS GET_RESULT,
                JSON_EXTRACT(QEXEC_TEXT->>"$**.thread", '$[0]') AS CONNECTION_ID
            FROM performance_schema.rpd_query_stats
        ) tmp;

    The query returns the following data:

    • QUERY_ID

      The ID assigned to the query by HeatWave. IDs are assigned in first in first out (FIFO) order.

    • CONNECTION_ID

      The connection ID of the client that issued the query.

    • QUERY_START

      The time the query was issued.

    • QUERY_END

      The time the query finished executing.

    • QUEUE_WAIT

      The amount of time the query waited in the scheduling queue.

    • EXEC_START

      The time that HeatWave started executing the query.