2.16.4 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]                                                             |
  +-----------------------------------------------------------------------------+