ANALYZE Statements

ANALYZE statements are used to collect statistics for existing tables and store the result to catalog. Only ANALYZE TABLE statements are supported now, and need to be triggered manually instead of automatically.

Attention Currently, ANALYZE TABLE only supports in batch mode. Only existing table is supported, and an exception will be thrown if the table is a view or table not exists.

Run an ANALYZE TABLE statement

Java

ANALYZE TABLE statements can be executed with the executeSql() method of the TableEnvironment.

The following examples show how to run a ANALYZE TABLE statement in TableEnvironment.

Scala

ANALYZE TABLE statements can be executed with the executeSql() method of the TableEnvironment.

The following examples show how to run a ANALYZE TABLE statement in TableEnvironment.

Python

ANALYZE TABLE statements can be executed with the execute_sql() method of the TableEnvironment.

The following examples show how to run a ANALYZE TABLE statement in TableEnvironment.

SQL CLI

ANALYZE TABLE statements can be executed in SQL CLI.

The following examples show how to run a ANALYZE TABLE statement in SQL CLI.

Java

  1. TableEnvironment tableEnv = TableEnvironment.create(...);
  3. // register a non-partition table named "Store"
  4. tableEnv.executeSql(
  5.         "CREATE TABLE Store (" +
  6.         " `id` BIGINT NOT NULl," +
  7.         " `location` VARCHAR(32)," +
  8.         " `owner` VARCHAR(32)" +
  9.         ") with (...)");
  11. // register a partition table named "Orders"
  12. tableEnv.executeSql(
  13.         "CREATE TABLE Orders (" +
  14.         " `id` BIGINT NOT NULl," +
  15.         " `product` VARCHAR(32)," +
  16.         " `amount` INT," +
  17.         " `sold_year` BIGINT," +
  18.         " `sold_month` BIGINT," +
  19.         " `sold_day` BIGINT" +
  20.         ") PARTITIONED BY (`sold_year`, `sold_month`, `sold_day`) "
  21.         ") with (...)");
  23. // Non-partition table, collect row count.
  24. tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS");
  26. // Non-partition table, collect row count and statistics for all columns.
  27. tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS FOR ALL COLUMNS");
  29. // Non-partition table, collect row count and statistics for column `location`.
  30. tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS FOR COLUMNS location");
  33. // Suppose table "Orders" has 4 partitions with specs:
  34. // Partition1 : (sold_year='2022', sold_month='1', sold_day='10')
  35. // Partition2 : (sold_year='2022', sold_month='1', sold_day='11')
  36. // Partition3 : (sold_year='2022', sold_month='2', sold_day='10')
  37. // Partition4 : (sold_year='2022', sold_month='2', sold_day='11')
  40. // Partition table, collect row count for Partition1.
  41. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS");
  43. // Partition table, collect row count for Partition1 and Partition2.
  44. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS");
  46. // Partition table, collect row count for all partitions.
  47. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS");
  49. // Partition table, collect row count and statistics for all columns on partition1.
  50. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR ALL COLUMNS");
  52. // Partition table, collect row count and statistics for all columns on partition1 and partition2.
  53. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");
  55. // Partition table, collect row count and statistics for all columns on all partitions.
  56. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");
  58. // Partition table, collect row count and statistics for column `amount` on partition1.
  59. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR COLUMNS amount");
  61. // Partition table, collect row count and statistics for `amount` and `product` on partition1 and partition2.
  62. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");
  64. // Partition table, collect row count and statistics for column `amount` and `product` on all partitions.
  65. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");

Scala

  1. val tableEnv = TableEnvironment.create(...)
  3. // register a non-partition table named "Store"
  4. tableEnv.executeSql(
  5.   "CREATE TABLE Store (" +
  6.           " `id` BIGINT NOT NULl," +
  7.           " `location` VARCHAR(32)," +
  8.           " `owner` VARCHAR(32)" +
  9.           ") with (...)");
  11. // register a partition table named "Orders"
  12. tableEnv.executeSql(
  13.   "CREATE TABLE Orders (" +
  14.           " `id` BIGINT NOT NULl," +
  15.           " `product` VARCHAR(32)," +
  16.           " `amount` INT," +
  17.           " `sold_year` BIGINT," +
  18.           " `sold_month` BIGINT," +
  19.           " `sold_day` BIGINT" +
  20.           ") PARTITIONED BY (`sold_year`, `sold_month`, `sold_day`) "
  21. ") with (...)");
  23. // Non-partition table, collect row count.
  24. tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS");
  26. // Non-partition table, collect row count and statistics for all columns.
  27. tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS FOR ALL COLUMNS");
  29. // Non-partition table, collect row count and statistics for column `location`.
  30. tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS FOR COLUMNS location");
  33. // Suppose table "Orders" has 4 partitions with specs:
  34. // Partition1 : (sold_year='2022', sold_month='1', sold_day='10')
  35. // Partition2 : (sold_year='2022', sold_month='1', sold_day='11')
  36. // Partition3 : (sold_year='2022', sold_month='2', sold_day='10')
  37. // Partition4 : (sold_year='2022', sold_month='2', sold_day='11')
  40. // Partition table, collect row count for Partition1.
  41. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS");
  43. // Partition table, collect row count for Partition1 and Partition2.
  44. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS");
  46. // Partition table, collect row count for all partitions.
  47. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS");
  49. // Partition table, collect row count and statistics for all columns on partition1.
  50. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR ALL COLUMNS");
  52. // Partition table, collect row count and statistics for all columns on partition1 and partition2.
  53. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");
  55. // Partition table, collect row count and statistics for all columns on all partitions.
  56. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");
  58. // Partition table, collect row count and statistics for column `amount` on partition1.
  59. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR COLUMNS amount");
  61. // Partition table, collect row count and statistics for `amount` and `product` on partition1 and partition2.
  62. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");
  64. // Partition table, collect row count and statistics for column `amount` and `product` on all partitions.
  65. tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");

Python

  1. table_env = TableEnvironment.create(...)
  3. # register a non-partition table named "Store"
  4. table_env.execute_sql(
  5.   "CREATE TABLE Store (" +
  6.           " `id` BIGINT NOT NULl," +
  7.           " `location` VARCHAR(32)," +
  8.           " `owner` VARCHAR(32)" +
  9.           ") with (...)");
  11. # register a partition table named "Orders"
  12. table_env.execute_sql(
  13.   "CREATE TABLE Orders (" +
  14.           " `id` BIGINT NOT NULl," +
  15.           " `product` VARCHAR(32)," +
  16.           " `amount` INT," +
  17.           " `sold_year` BIGINT," +
  18.           " `sold_month` BIGINT," +
  19.           " `sold_day` BIGINT" +
  20.           ") PARTITIONED BY (`sold_year`, `sold_month`, `sold_day`) "
  21. ") with (...)");
  23. # Non-partition table, collect row count.
  24. table_env.execute_sql("ANALYZE TABLE Store COMPUTE STATISTICS");
  26. # Non-partition table, collect row count and statistics for all columns.
  27. table_env.execute_sql("ANALYZE TABLE Store COMPUTE STATISTICS FOR ALL COLUMNS");
  29. # Non-partition table, collect row count and statistics for column `location`.
  30. table_env.execute_sql("ANALYZE TABLE Store COMPUTE STATISTICS FOR COLUMNS location");
  33. # Suppose table "Orders" has 4 partitions with specs:
  34. # Partition1 : (sold_year='2022', sold_month='1', sold_day='10')
  35. # Partition2 : (sold_year='2022', sold_month='1', sold_day='11')
  36. # Partition3 : (sold_year='2022', sold_month='2', sold_day='10')
  37. # Partition4 : (sold_year='2022', sold_month='2', sold_day='11')
  40. # Partition table, collect row count for Partition1.
  41. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS");
  43. # Partition table, collect row count for Partition1 and Partition2.
  44. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS");
  46. # Partition table, collect row count for all partitions.
  47. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS");
  49. # Partition table, collect row count and statistics for all columns on partition1.
  50. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR ALL COLUMNS");
  52. # Partition table, collect row count and statistics for all columns on partition1 and partition2.
  53. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");
  55. # Partition table, collect row count and statistics for all columns on all partitions.
  56. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");
  58. # Partition table, collect row count and statistics for column `amount` on partition1.
  59. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR COLUMNS amount");
  61. # Partition table, collect row count and statistics for `amount` and `product` on partition1 and partition2.
  62. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");
  64. # Partition table, collect row count and statistics for column `amount` and `product` on all partitions.
  65. table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");

SQL CLI

  1. Flink SQL> CREATE TABLE Store (
  2. > `id` BIGINT NOT NULl,
  3. > `location` VARCHAR(32),
  4. > `owner` VARCHAR(32)
  5. > ) with (
  6. > ...
  7. > );
  8. [INFO] Table has been created.
  10. Flink SQL> CREATE TABLE Orders (
  11. > `id` BIGINT NOT NULl,
  12. > `product` VARCHAR(32),
  13. > `amount` INT,
  14. > `sold_year` BIGINT,
  15. > `sold_month` BIGINT,
  16. > `sold_day` BIGINT   
  17. > ) PARTITIONED BY (`sold_year`, `sold_month`, `sold_day`)
  18. > ) with (
  19. > ...
  20. > );
  21. [INFO] Table has been created.
  23. Flink SQL> ANALYZE TABLE Store COMPUTE STATISTICS;
  24. [INFO] Execute statement succeed.
  26. Flink SQL> ANALYZE TABLE Store COMPUTE STATISTICS FOR ALL COLUMNS;
  27. [INFO] Execute statement succeed.
  29. Flink SQL> ANALYZE TABLE Store COMPUTE STATISTICS FOR COLUMNS location;
  30. [INFO] Execute statement succeed.
  32. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS;
  33. [INFO] Execute statement succeed.
  35. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS;
  36. [INFO] Execute statement succeed.
  38. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS;
  39. [INFO] Execute statement succeed.
  41. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR ALL COLUMNS;
  42. [INFO] Execute statement succeed.    
  44. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR ALL COLUMNS;
  45. [INFO] Execute statement succeed.
  47. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR ALL COLUMNS;
  48. [INFO] Execute statement succeed.
  50. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR COLUMNS amount;
  51. [INFO] Execute statement succeed.
  53. Flink SQL> ANALYZE TABLE Orders PARTITION (sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product;
  54. [INFO] Execute statement succeed.
  56. Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product;
  57. [INFO] Execute statement succeed.

Syntax

  1. ANALYZE TABLE [catalog_name.][db_name.]table_name PARTITION(partcol1[=val1] [, partcol2[=val2], ...]) COMPUTE STATISTICS [FOR COLUMNS col1 [, col2, ...] | FOR ALL COLUMNS]

  • PARTITION(partcol1[=val1] [, partcol2[=val2], …]) is required for the partition table

    • If no partition is specified, the statistics will be gathered for all partitions
    • If a certain partition is specified, the statistics will be gathered only for specific partition
    • If the table is non-partition table , while a partition is specified, an exception will be thrown
    • If a certain partition is specified, but the partition does not exist, an exception will be thrown

  • FOR COLUMNS col1 [, col2, …] or FOR ALL COLUMNS are optional

    • If no column is specified, only the table level statistics will be gathered
    • If a column does not exist, or column is not a physical column, an exception will be thrown.
    • If a column or any column is specified, the column level statistics will be gathered
      • the column level statistics include:
        • ndv: the number of distinct values
        • nullCount: the number of nulls
        • avgLen: the average length of column values
        • maxLen: the max length of column values
        • minValue: the min value of column values
        • maxValue: the max value of column values
        • valueCount: the value count only for boolean type
      • the supported types and its corresponding column level statistics are as following sheet lists(“Y” means support, “N” means unsupported):
TypesndvnullCountavgLenmaxLenmaxValueminValuevalueCount
BOOLEANNYNNNNY
TINYINTYYNNYYN
SMALLINTYYNNYYN
INTEGERYYNNYYN
FLOATYYNNYYN
DATEYYNNYYN
TIME_WITHOUT_TIME_ZONEYYNNYYN
BIGINTYYNNYYN
DOUBLEYYNNYYN
DECIMALYYNNYYN
TIMESTAMP_WITH_LOCAL_TIME_ZONEYYNNYYN
TIMESTAMP_WITHOUT_TIME_ZONEYYNNYYN
CHARYYYYNNN
VARCHARYYYYNNN
other typesNYNNNNN

NOTE: For the fix length types (like BOOLEAN, INTEGER, DOUBLE etc.), we need not collect the avgLen and maxLen from the original records.