SHOW DATA SKEW
SHOW-DATA-SKEWβ
Nameβ
SHOW DATA SKEW
Descriptionβ
This statement is used to view the data skew of a table or the specified partitions.
grammar:
SHOW DATA SKEW FROM [db_name.]tbl_name [PARTITION (partition_name, ...)];
Description:
1. The result will show row count and data volume of each bucket under the specified partition, and the proportion of the data volume of each bucket in the total data volume.
2. For non-partitioned tables, the partition name in result is the same as the table name.
Exampleβ
- For partitioned tables
- CREATE-TABLE
CREATE TABLE test_show_data_skew
(
id int,
name string,
pdate date
)
PARTITION BY RANGE(pdate)
(
FROM ("2023-04-16") TO ("2023-04-20") INTERVAL 1 DAY
)
DISTRIBUTED BY HASH(id) BUCKETS 5
PROPERTIES (
"replication_num" = "1"
); - View the data skew of the table
mysql> SHOW DATA SKEW FROM test_show_data_skew;
+---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
| PartitionName | BucketIdx | AvgRowCount | AvgDataSize | Graph | Percent |
+---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
| p_20230416 | 0 | 1 | 648 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 49.77 % |
| p_20230416 | 1 | 2 | 654 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 50.23 % |
| p_20230416 | 2 | 0 | 0 | | 00.00 % |
| p_20230416 | 3 | 0 | 0 | | 00.00 % |
| p_20230416 | 4 | 0 | 0 | | 00.00 % |
| p_20230417 | 0 | 0 | 0 | | 00.00 % |
| p_20230417 | 1 | 0 | 0 | | 00.00 % |
| p_20230417 | 2 | 0 | 0 | | 00.00 % |
| p_20230417 | 3 | 0 | 0 | | 00.00 % |
| p_20230417 | 4 | 2 | 656 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 100.00% |
| p_20230418 | 0 | 0 | 0 | | 00.00 % |
| p_20230418 | 1 | 1 | 648 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 100.00% |
| p_20230418 | 2 | 0 | 0 | | 00.00 % |
| p_20230418 | 3 | 0 | 0 | | 00.00 % |
| p_20230418 | 4 | 0 | 0 | | 00.00 % |
| p_20230419 | 0 | 0 | 0 | | 00.00 % |
| p_20230419 | 1 | 1 | 648 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 49.96 % |
| p_20230419 | 2 | 0 | 0 | | 00.00 % |
| p_20230419 | 3 | 1 | 649 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 50.04 % |
| p_20230419 | 4 | 0 | 0 | | 00.00 % |
+---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+ - View the data skew of the specified partitions.
mysql> SHOW DATA SKEW FROM test_show_data_skew PARTITION(p_20230416, p_20230418);
+---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
| PartitionName | BucketIdx | AvgRowCount | AvgDataSize | Graph | Percent |
+---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
| p_20230416 | 0 | 1 | 648 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 49.77 % |
| p_20230416 | 1 | 2 | 654 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 50.23 % |
| p_20230416 | 2 | 0 | 0 | | 00.00 % |
| p_20230416 | 3 | 0 | 0 | | 00.00 % |
| p_20230416 | 4 | 0 | 0 | | 00.00 % |
| p_20230418 | 0 | 0 | 0 | | 00.00 % |
| p_20230418 | 1 | 1 | 648 | >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | 100.00% |
| p_20230418 | 2 | 0 | 0 | | 00.00 % |
| p_20230418 | 3 | 0 | 0 | | 00.00 % |
| p_20230418 | 4 | 0 | 0 | | 00.00 % |
+---------------+-----------+-------------+-------------+------------------------------------------------------------------------------------------------------+---------+
- For non-partitioned tables
- CREATE-TABLE
CREATE TABLE test_show_data_skew2
(
id int,
name string,
pdate date
)
DISTRIBUTED BY HASH(id) BUCKETS 5
PROPERTIES (
"replication_num" = "1"
); - View the data skew of the table
mysql> SHOW DATA SKEW FROM test_show_data_skew2;
+----------------------+-----------+-------------+-------------+---------------------------+---------+
| PartitionName | BucketIdx | AvgRowCount | AvgDataSize | Graph | Percent |
+----------------------+-----------+-------------+-------------+---------------------------+---------+
| test_show_data_skew2 | 0 | 1 | 648 | >>>>>>>>>>>>>>>>>>>>>>>> | 24.73 % |
| test_show_data_skew2 | 1 | 4 | 667 | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.46 % |
| test_show_data_skew2 | 2 | 0 | 0 | | 00.00 % |
| test_show_data_skew2 | 3 | 1 | 649 | >>>>>>>>>>>>>>>>>>>>>>>> | 24.77 % |
| test_show_data_skew2 | 4 | 2 | 656 | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.04 % |
+----------------------+-----------+-------------+-------------+---------------------------+---------+
mysql> SHOW DATA SKEW FROM test_show_data_skew2 PARTITION(test_show_data_skew2);
+----------------------+-----------+-------------+-------------+---------------------------+---------+
| PartitionName | BucketIdx | AvgRowCount | AvgDataSize | Graph | Percent |
+----------------------+-----------+-------------+-------------+---------------------------+---------+
| test_show_data_skew2 | 0 | 1 | 648 | >>>>>>>>>>>>>>>>>>>>>>>> | 24.73 % |
| test_show_data_skew2 | 1 | 4 | 667 | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.46 % |
| test_show_data_skew2 | 2 | 0 | 0 | | 00.00 % |
| test_show_data_skew2 | 3 | 1 | 649 | >>>>>>>>>>>>>>>>>>>>>>>> | 24.77 % |
| test_show_data_skew2 | 4 | 2 | 656 | >>>>>>>>>>>>>>>>>>>>>>>>> | 25.04 % |
+----------------------+-----------+-------------+-------------+---------------------------+---------+
```
Keywordsβ
SHOW, DATA, SKEW
