MySQL :: HeatWave User Guide :: 11.5 Metadata Queries

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.