ANALYZE 문

ANALYZE 문 (ANALYZE Statements)

ANALYZE 문은 기존 테이블의 통계를 수집하고 결과를 카탈로그에 저장하는 데 사용돼요. 현재 ANALYZE TABLE 문만 지원되며, 자동이 아닌 수동으로 트리거해야 해요.

출처: 문서

본문

주의: 현재 ANALYZE TABLE은 배치 모드에서만 지원돼요. 기존 테이블만 지원되며, 테이블이 뷰이거나 존재하지 않으면 예외가 발생해요.

ANALYZE TABLE 문 실행 (Run an ANALYZE TABLE statement)

• Java: ANALYZE TABLE 문은 TableEnvironment의 executeSql() 메서드로 실행할 수 있어요.
• Scala: ANALYZE TABLE 문은 TableEnvironment의 executeSql() 메서드로 실행할 수 있어요.
• Python: ANALYZE TABLE 문은 TableEnvironment의 execute_sql() 메서드로 실행할 수 있어요.
• SQL CLI: ANALYZE TABLE 문은 SQL CLI에서 실행할 수 있어요.

다음 예제는 TableEnvironment에서 ANALYZE TABLE 문을 실행하는 방법을 보여줘요.

Java:

TableEnvironment tableEnv = TableEnvironment.create(...);

// register a non-partition table named "Store"
tableEnv.executeSql(
"CREATE TABLE Store (" +
" `id` BIGINT NOT NULl," +
" `location` VARCHAR(32)," +
" `owner` VARCHAR(32)" +
") with (...)");

// register a partition table named "Orders"
tableEnv.executeSql(
"CREATE TABLE Orders (" +
" `id` BIGINT NOT NULl," +
" `product` VARCHAR(32)," +
" `amount` INT," +
" `sold_year` BIGINT," +
" `sold_month` BIGINT," +
" `sold_day` BIGINT" +
") PARTITIONED BY (`sold_year`, `sold_month`, `sold_day`) "
") with (...)");

// Non-partition table, collect row count.
tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS");

// Non-partition table, collect row count and statistics for all columns.
tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS FOR ALL COLUMNS");

// Non-partition table, collect row count and statistics for column `location`.
tableEnv.executeSql("ANALYZE TABLE Store COMPUTE STATISTICS FOR COLUMNS location");

// Suppose table "Orders" has 4 partitions with specs:
// Partition1 : (sold_year='2022', sold_month='1', sold_day='10')
// Partition2 : (sold_year='2022', sold_month='1', sold_day='11')
// Partition3 : (sold_year='2022', sold_month='2', sold_day='10')
// Partition4 : (sold_year='2022', sold_month='2', sold_day='11')

// Partition table, collect row count for Partition1.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS");

// Partition table, collect row count for Partition1 and Partition2.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS");

// Partition table, collect row count for all partitions.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS");

// Partition table, collect row count and statistics for all columns on partition1.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR ALL COLUMNS");

// Partition table, collect row count and statistics for all columns on partition1 and partition2.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");

// Partition table, collect row count and statistics for all columns on all partitions.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");

// Partition table, collect row count and statistics for column `amount` on partition1.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR COLUMNS amount");

// Partition table, collect row count and statistics for `amount` and `product` on partition1 and partition2.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");

// Partition table, collect row count and statistics for column `amount` and `product` on all partitions.
tableEnv.executeSql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");

Python:

table_env = TableEnvironment.create(...)

# register a non-partition table named "Store"
table_env.execute_sql(
"CREATE TABLE Store (" +
" `id` BIGINT NOT NULl," +
" `location` VARCHAR(32)," +
" `owner` VARCHAR(32)" +
") with (...)");

# register a partition table named "Orders"
table_env.execute_sql(
"CREATE TABLE Orders (" +
" `id` BIGINT NOT NULl," +
" `product` VARCHAR(32)," +
" `amount` INT," +
" `sold_year` BIGINT," +
" `sold_month` BIGINT," +
" `sold_day` BIGINT" +
") PARTITIONED BY (`sold_year`, `sold_month`, `sold_day`) "
") with (...)");

# Non-partition table, collect row count.
table_env.execute_sql("ANALYZE TABLE Store COMPUTE STATISTICS");

# Non-partition table, collect row count and statistics for all columns.
table_env.execute_sql("ANALYZE TABLE Store COMPUTE STATISTICS FOR ALL COLUMNS");

# Non-partition table, collect row count and statistics for column `location`.
table_env.execute_sql("ANALYZE TABLE Store COMPUTE STATISTICS FOR COLUMNS location");

# Partition table, collect row count for Partition1.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS");

# Partition table, collect row count for Partition1 and Partition2.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS");

# Partition table, collect row count for all partitions.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS");

# Partition table, collect row count and statistics for all columns on partition1.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR ALL COLUMNS");

# Partition table, collect row count and statistics for all columns on partition1 and partition2.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");

# Partition table, collect row count and statistics for all columns on all partitions.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR ALL COLUMNS");

# Partition table, collect row count and statistics for column `amount` on partition1.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS FOR COLUMNS amount");

# Partition table, collect row count and statistics for `amount` and `product` on partition1 and partition2.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");

# Partition table, collect row count and statistics for column `amount` and `product` on all partitions.
table_env.execute_sql("ANALYZE TABLE Orders PARTITION(sold_year, sold_month, sold_day) COMPUTE STATISTICS FOR COLUMNS amount, product");

SQL CLI:

Flink SQL> ANALYZE TABLE Store COMPUTE STATISTICS;
[INFO] Execute statement succeeded.

Flink SQL> ANALYZE TABLE Store COMPUTE STATISTICS FOR ALL COLUMNS;
[INFO] Execute statement succeeded.

Flink SQL> ANALYZE TABLE Store COMPUTE STATISTICS FOR COLUMNS location;
[INFO] Execute statement succeeded.

Flink SQL> ANALYZE TABLE Orders PARTITION(sold_year='2022', sold_month='1', sold_day='10') COMPUTE STATISTICS;
[INFO] Execute statement succeeded.

구문 (Syntax)

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], …])는 파티션 테이블에 필수예요.
• 파티션이 지정되지 않으면 모든 파티션에 대한 통계가 수집돼요.
• 특정 파티션이 지정되면 그 파티션에 대해서만 통계가 수집돼요.
• 테이블이 비파티션 테이블인데 파티션이 지정되면 예외가 발생해요.
• 특정 파티션이 지정되었지만 존재하지 않으면 예외가 발생해요.
• FOR COLUMNS col1 [, col2, …] 또는 FOR ALL COLUMNS는 선택 사항이에요.
• 컬럼이 지정되지 않으면 테이블 수준 통계만 수집돼요.
• 컬럼이 존재하지 않거나 물리 컬럼이 아니면 예외가 발생해요.
• 컬럼이 하나라도 지정되면 컬럼 수준 통계가 수집돼요.

컬럼 수준 통계(column level statistics)에는 다음이 포함돼요:

• ndv: 고유 값 수 (number of distinct values)
• nullCount: null 수
• avgLen: 컬럼 값의 평균 길이
• maxLen: 컬럼 값의 최대 길이
• minValue: 컬럼 값의 최소값
• maxValue: 컬럼 값의 최대값
• valueCount: 불리언 타입에만 해당하는 값 수

지원되는 타입과 그에 해당하는 컬럼 수준 통계는 다음과 같아요("Y"는 지원, "N"은 미지원):

참고: 고정 길이 타입(BOOLEAN, INTEGER, DOUBLE 등)의 경우 원본 레코드에서 avgLen과 maxLen을 수집할 필요가 없어요.

더 알아보기 (Learn more)

• JAR 문
• SQL 참조 유틸리티
• SQL 인터페이스