This section provides Information Schema and Performance Schema queries that you can use to retrieve HeatWave metadata.
For queries that you can use to monitor HeatWave node status, memory usage, data loading, change propagation, and queries, see Section 2.13, “Monitoring”.
Metadata queries are organized into the following categories.
Secondary Engine Definitions
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
.
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.
Excluded Columns
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
.
String Column Encoding
-
To identify explicitly encoded string columns in tables on the MySQL DB System, query the
COLUMN_COMMENT
column of theINFORMATION_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 view the dictionary size for dictionary-encoded columns, in bytes:
mysql> USE performance_schema; mysql> SELECT rpd_table_id.TABLE_NAME, rpd_columns.COLUMN_ID, rpd_columns.DICT_SIZE_BYTES FROM rpd_table_id, rpd_columns WHERE rpd_table_id.ID = rpd_columns.TABLE_ID AND rpd_columns.DICT_SIZE_BYTES > 0 ORDER BY rpd_table_id.TABLE_NAME; ------------+-----------+-----------------+ | TABLE_NAME | COLUMN_ID | DICT_SIZE_BYTES | +------------+-----------+-----------------+ | orders | 3 | 25165912 | +------------+-----------+-----------------+
Data Placement
-
To identify columns defined as data placement keys in tables on the MySQL DB System, query the
COLUMN_COMMENT
column of theINFORMATION_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 theperformance_schema.rpd_columns
table for columns with aDATA_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 2.7.2, “Defining Data Placement Keys”.
-
To determine if data placement partitions were used by a
JOIN
orGROUP BY
query, you can query theQEP_TEXT
column of theperformance_schema.rpd_query_stats
table to viewprepart
data. (prepart
is short for “pre-partitioning”.) Theprepart
data for aGROUP 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. Theprepart
data for aJOIN
operation has two values that indicate the number of HeatWave nodes; one for eachJOIN
branch; for example:"prepart":[
. A value greater than 1 for a#,#
]JOIN
branch indicates that theJOIN
branch used data placement partitions. (A value of"prepart":[1,1]
indicates that data placement partitions were not used by eitherJOIN
branch.)prepart
data is only generated if aGROUP BY
orJOIN
operation is executed. To queryQEP_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] | +-----------------------------------------------------------------------------+