MySQL :: MySQL HeatWave User Guide :: 2.14 Metadata Queries

MySQL HeatWave User Guide / HeatWave / Metadata Queries

2.14 Metadata Queries

This section provides Information Schema and Performance Schema queries that you can use to retrieve HeatWave metadata.

Note

For queries that you can use to monitor HeatWave node status, memory usage, data loading, change propagation, and queries, see Section 5.2, “HeatWave 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. Tables with SECONDARY_ENGINE="RAPID" are loaded into HeatWave, and changes to them are automatically propagated to their counterpart tables in the HeatWave Cluster.

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

Before release 8.0.31, 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 in those releases. 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.

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. Columns defined with the NOT SECONDARY column attribute are not loaded into HeatWave when executing a table load operation.

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 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 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 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 2.7.2, “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]                                                             |
+-----------------------------------------------------------------------------+
```

PREV HOME UP NEXT