table_summary and partition_summary system tables - Data Lake Formation

DLF aggregates daily storage usage, storage class distribution, file size distribution, and access frequency statistics for every table and partition in the catalog into two system tables under the system database: table_summary and partition_summary. Use them to find tables with many small files, identify cold data, optimize storage class assignments, and trace recent accessors.

Overview

table_summary: table-level statistics covering basic table properties, storage usage, storage class distribution, file size distribution, and access frequency.
partition_summary: partition-level statistics covering storage usage, storage class distribution, file size distribution, and access frequency for each partition.

Note

Data is produced daily (T+1) and reflects the full physical storage under the catalog. The partition column dt of both tables retains the last 30 days.

table_summary

Schema

Note

The following is the built-in schema definition. It is shown for reference only and does not need to be executed manually.

CREATE TABLE `table_summary` (
  `database_id`              STRING  COMMENT 'Database ID',
  `database_name`            STRING  COMMENT 'Database name',
  `table_id`                 STRING  COMMENT 'Table ID',
  `table_name`               STRING  COMMENT 'Table name',
  `table_type`               STRING  COMMENT 'Table type',
  `owner`                    STRING  COMMENT 'Table owner',
  `created_by`               STRING  COMMENT 'Created by',
  `created_at`               BIGINT  COMMENT 'Creation time (epoch milliseconds)',
  `updated_by`               STRING  COMMENT 'Updated by',
  `updated_at`               BIGINT  COMMENT 'Last update time (epoch milliseconds)',
  `obj_cnt`                  BIGINT  COMMENT 'Data object count, total count = obj_cnt + meta_obj_cnt',
  `obj_size`                 BIGINT  COMMENT 'Data object size (bytes), total size = obj_size + meta_obj_size',
  `meta_obj_cnt`             BIGINT  COMMENT 'Metadata object count',
  `meta_obj_size`            BIGINT  COMMENT 'Metadata object size (bytes)',
  `ts_file_count`            BIGINT  COMMENT 'Data object count in latest snapshot',
  `ts_file_size_in_bytes`    BIGINT  COMMENT 'Data object size in latest snapshot (bytes)',
  `obj_type_standard_size`   BIGINT  COMMENT 'Data object size in Standard storage (bytes)',
  `obj_type_ia_size`         BIGINT  COMMENT 'Data object size in IA storage (bytes)',
  `obj_type_archive_size`    BIGINT  COMMENT 'Data object size in Archive storage (bytes)',
  `obj_type_coldarchive_size` BIGINT  COMMENT 'Data object size in Cold Archive storage (bytes)',
  `obj_size_tiny_cnt`        BIGINT  COMMENT 'Data object count (size <= 1 MB)',
  `obj_size_small_cnt`       BIGINT  COMMENT 'Data object count (size 1 MB - 128 MB)',
  `obj_size_middle_cnt`      BIGINT  COMMENT 'Data object count (size 128 MB - 1 GB)',
  `obj_size_large_cnt`       BIGINT  COMMENT 'Data object count (size > 1 GB)',
  `data_last_access_time`    BIGINT  COMMENT 'Last data object access time (epoch milliseconds)',
  `obj_access_num`           BIGINT  COMMENT 'Daily data object access count',
  `obj_access_num_7d`        BIGINT  COMMENT 'Data object access count in last 7 days',
  `obj_access_num_30d`       BIGINT  COMMENT 'Data object access count in last 30 days',
  `meta_last_access_time`    BIGINT  COMMENT 'Last metadata object access time (epoch milliseconds)',
  `meta_obj_access_num`      BIGINT  COMMENT 'Daily metadata object access count',
  `meta_obj_access_num_7d`   BIGINT  COMMENT 'Metadata object access count in last 7 days',
  `meta_obj_access_num_30d`  BIGINT  COMMENT 'Metadata object access count in last 30 days',
  `last_requester`           STRING  COMMENT 'Last requester',
  `top_requester`            STRING  COMMENT 'Most frequent requester of the day',
  `dt`                       STRING  COMMENT 'Date (yyyyMMdd)'
) COMMENT 'Daily table-level summary'
PARTITIONED BY (dt) WITH (
   'file.format' = 'avro',
   'partition.expiration-time' = '30 d',
   'partition.timestamp-formatter' = 'yyyyMMdd'
);

Field descriptions

Basic information

Field	Description
database_id	Database ID.
database_name	Database name.
table_id	Table ID.
table_name	Table name.
table_type	Table type, such as paimon-pk (Paimon primary key tables), paimon-append (Paimon append-only table), object-table (Object Table), or format-table (Format tables).
owner	Table owner.
created_by	Creator.
created_at	Creation time, in epoch milliseconds.
updated_by	Last updater.
updated_at	Last update time, in epoch milliseconds.

Storage statistics

Field	Description
obj_cnt	Number of data files. Total file count = obj_cnt + meta_obj_cnt.
obj_size	Total data file size, in bytes. Total storage size = obj_size + meta_obj_size.
meta_obj_cnt	Number of metadata files.
meta_obj_size	Total metadata file size, in bytes.
ts_file_count	Number of data files in the latest snapshot.
ts_file_size_in_bytes	Data file size in the latest snapshot, in bytes.

Storage class

Field	Description
obj_type_standard_size	Size of data files in Standard storage, in bytes.
obj_type_ia_size	Size of data files in Infrequent Access (IA) storage, in bytes.
obj_type_archive_size	Size of data files in Archive storage, in bytes.
obj_type_coldarchive_size	Size of data files in Cold Archive storage, in bytes.

File size distribution

Field	Description
obj_size_tiny_cnt	Number of data files of size 1 MB or less.
obj_size_small_cnt	Number of data files between 1 MB and 128 MB.
obj_size_middle_cnt	Number of data files between 128 MB and 1 GB.
obj_size_large_cnt	Number of data files larger than 1 GB.

Access statistics

Note

To populate last_requester and top_requester, set dlf.access-tracking.enabled = true on the Catalog Configuration tab > Advanced Settings section in the DLF console, and ensure that the compute engine uses Paimon >= 1.4. Only accesses that meet both conditions are tracked.

Field	Description
data_last_access_time	Time of the last data file access, in epoch milliseconds.
obj_access_num	Number of data file accesses today.
obj_access_num_7d	Number of data file accesses in the last 7 days.
obj_access_num_30d	Number of data file accesses in the last 30 days.
meta_last_access_time	Time of the last metadata file access, in epoch milliseconds.
meta_obj_access_num	Number of metadata file accesses today.
meta_obj_access_num_7d	Number of metadata file accesses in the last 7 days.
meta_obj_access_num_30d	Number of metadata file accesses in the last 30 days.
last_requester	Most recent requester. See the prerequisite note above.
top_requester	Requester with the most accesses today. See the prerequisite note above.

Partition column

Field	Description
dt	Statistics date, in `yyyyMMdd` format. The last 30 days are retained.

partition_summary

Schema

Note

The following is the built-in schema definition. It is shown for reference only and does not need to be executed manually.

CREATE TABLE `partition_summary` (
  `database_id`              STRING  COMMENT 'Database ID',
  `database_name`            STRING  COMMENT 'Database name',
  `table_id`                 STRING  COMMENT 'Table ID',
  `table_name`               STRING  COMMENT 'Table name',
  `partition_name`           STRING  COMMENT 'Partition name',
  `created_by`               STRING  COMMENT 'Created by',
  `created_at`               BIGINT  COMMENT 'Creation time (epoch milliseconds)',
  `updated_by`               STRING  COMMENT 'Updated by',
  `updated_at`               BIGINT  COMMENT 'Last update time (epoch milliseconds)',
  `obj_cnt`                  BIGINT  COMMENT 'Data object count',
  `obj_size`                 BIGINT  COMMENT 'Data object size (bytes)',
  `obj_type_standard_size`   BIGINT  COMMENT 'Data object size in Standard storage (bytes)',
  `obj_type_ia_size`         BIGINT  COMMENT 'Data object size in IA storage (bytes)',
  `obj_type_archive_size`    BIGINT  COMMENT 'Data object size in Archive storage (bytes)',
  `obj_type_coldarchive_size` BIGINT  COMMENT 'Data object size in Cold Archive storage (bytes)',
  `obj_size_tiny_cnt`        BIGINT  COMMENT 'Data object count (size <= 1 MB)',
  `obj_size_small_cnt`       BIGINT  COMMENT 'Data object count (size 1 MB - 128 MB)',
  `obj_size_middle_cnt`      BIGINT  COMMENT 'Data object count (size 128 MB - 1 GB)',
  `obj_size_large_cnt`       BIGINT  COMMENT 'Data object count (size > 1 GB)',
  `data_last_access_time`    BIGINT  COMMENT 'Last data object access time (epoch milliseconds)',
  `obj_access_num`           BIGINT  COMMENT 'Daily data object access count',
  `obj_access_num_7d`        BIGINT  COMMENT 'Data object access count in last 7 days',
  `obj_access_num_30d`       BIGINT  COMMENT 'Data object access count in last 30 days',
  `last_requester`           STRING  COMMENT 'Last requester',
  `top_requester`            STRING  COMMENT 'Most frequent requester of the day',
  `dt`                       STRING  COMMENT 'Date (yyyyMMdd)'
) COMMENT 'Daily partition-level summary'
PARTITIONED BY (dt) WITH (
   'file.format' = 'avro',
   'partition.expiration-time' = '30 d',
   'partition.timestamp-formatter' = 'yyyyMMdd'
);

Field descriptions

Basic information

Field	Description
database_id	Database ID.
database_name	Database name.
table_id	Table ID.
table_name	Table name.
partition_name	Partition name.
created_by	Creator.
created_at	Creation time, in epoch milliseconds.
updated_by	Last updater.
updated_at	Last update time, in epoch milliseconds.

Storage statistics

Field	Description
obj_cnt	Number of data files.
obj_size	Total data file size, in bytes.

Storage class

Field	Description
obj_type_standard_size	Size of data files in Standard storage, in bytes.
obj_type_ia_size	Size of data files in Infrequent Access (IA) storage, in bytes.
obj_type_archive_size	Size of data files in Archive storage, in bytes.
obj_type_coldarchive_size	Size of data files in Cold Archive storage, in bytes.

File size distribution

Field	Description
obj_size_tiny_cnt	Number of data files of size 1 MB or less.
obj_size_small_cnt	Number of data files between 1 MB and 128 MB.
obj_size_middle_cnt	Number of data files between 128 MB and 1 GB.
obj_size_large_cnt	Number of data files larger than 1 GB.

Access statistics

Note

Field	Description
data_last_access_time	Time of the last data file access, in epoch milliseconds.
obj_access_num	Number of data file accesses today.
obj_access_num_7d	Number of data file accesses in the last 7 days.
obj_access_num_30d	Number of data file accesses in the last 30 days.
last_requester	Most recent requester. See the prerequisite note above.
top_requester	Requester with the most accesses today. See the prerequisite note above.

Partition column

Field	Description
dt	Statistics date, in `yyyyMMdd` format. The last 30 days are retained.

Query examples

Use DLF or Flink SQL to query DLF system tables:

DLF data preview

You can run SQL queries directly using the DLF data preview and discovery features without setting up a separate compute engine. For details and pricing, see Data preview.

Flink SQL

To run the SQL examples using Flink, a DLF catalog must be configured in Flink. For detailed configuration steps, see Access DLF with Flink SQL.

Storage overview

View the storage trend of a catalog over the last N days

SELECT
    dt,
    COUNT(DISTINCT table_id) AS table_cnt,
    SUM(obj_size) + SUM(meta_obj_size) AS total_size,
    SUM(obj_size) AS total_data_obj_size,
    SUM(meta_obj_size) AS total_meta_obj_size,
    SUM(ts_file_size_in_bytes) AS total_latest_snapshot_data_obj_size
FROM `<yourCatalogName>`.`system`.`table_summary`
WHERE dt BETWEEN '<startDate>' AND '<endDate>'  -- Replace with actual date, format yyyyMMdd
GROUP BY dt
ORDER BY dt;

View the storage statistics of all tables on a given day

SELECT
    database_id,
    database_name,
    table_id,
    table_name,
    table_type,
    owner,
    obj_size + meta_obj_size AS total_size,
    obj_size AS data_obj_size,
    ts_file_size_in_bytes AS latest_snapshot_data_obj_size,
    ts_file_size_in_bytes * 1.0 / NULLIF(obj_size, 0) AS latest_snapshot_ratio
FROM `<yourCatalogName>`.`system`.`table_summary`
WHERE dt = '<date>'  -- Replace with actual date, format yyyyMMdd
ORDER BY total_size DESC;

View the storage breakdown of each partition in a table

SELECT
    database_id,
    database_name,
    table_id,
    table_name,
    partition_name,
    obj_cnt AS data_obj_cnt,
    obj_size AS data_obj_size
FROM `<yourCatalogName>`.`system`.`partition_summary`
WHERE dt = '<date>'  -- Replace with actual date, format yyyyMMdd
    AND table_id = '<tableID>'
ORDER BY data_obj_size DESC;

View the storage trend of a table over the last N days

SELECT
    dt,
    obj_cnt AS data_obj_cnt,
    obj_size + meta_obj_size AS total_size,
    obj_size AS data_obj_size,
    ts_file_size_in_bytes AS latest_snapshot_data_obj_size,
    ts_file_size_in_bytes * 1.0 / NULLIF(obj_size, 0) AS latest_snapshot_ratio
FROM `<yourCatalogName>`.`system`.`table_summary`
WHERE table_id = '<tableID>'
    AND dt BETWEEN '<startDate>' AND '<endDate>'  -- Replace with actual date, format yyyyMMdd
ORDER BY dt;

Small file management

Find tables with many small files

SELECT
    database_id,
    database_name,
    table_id,
    table_name,
    table_type,
    obj_cnt AS data_obj_cnt,
    obj_size AS data_obj_size,
    obj_size_tiny_cnt,
    obj_size_small_cnt,
    obj_size_tiny_cnt * 1.0 / NULLIF(obj_cnt, 0) AS tiny_obj_ratio,
    (obj_size_tiny_cnt + obj_size_small_cnt) * 1.0 / NULLIF(obj_cnt, 0) AS small_obj_ratio,
    obj_size / NULLIF(obj_cnt, 0) AS avg_data_obj_size
FROM `<yourCatalogName>`.`system`.`table_summary`
WHERE dt = '<date>'  -- Replace with actual date, format yyyyMMdd
    AND obj_cnt > 0
ORDER BY obj_size_tiny_cnt DESC;

Find partitions with many small files

SELECT
    database_id,
    database_name,
    table_id,
    table_name,
    partition_name,
    obj_cnt AS data_obj_cnt,
    obj_size AS data_obj_size,
    obj_size_tiny_cnt,
    obj_size_small_cnt,
    obj_size_tiny_cnt * 1.0 / NULLIF(obj_cnt, 0) AS tiny_obj_ratio,
    (obj_size_tiny_cnt + obj_size_small_cnt) * 1.0 / NULLIF(obj_cnt, 0) AS small_obj_ratio,
    obj_size / NULLIF(obj_cnt, 0) AS avg_data_obj_size
FROM `<yourCatalogName>`.`system`.`partition_summary`
WHERE dt = '<date>'  -- Replace with actual date, format yyyyMMdd
    AND obj_cnt > 0
ORDER BY obj_size_tiny_cnt DESC;

View the small file trend of a table over the last N days

SELECT
    dt,
    obj_cnt AS data_obj_cnt,
    obj_size_tiny_cnt,
    obj_size_small_cnt,
    obj_size_middle_cnt,
    obj_size_large_cnt,
    obj_size_tiny_cnt * 1.0 / NULLIF(obj_cnt, 0) AS tiny_obj_ratio,
    (obj_size_tiny_cnt + obj_size_small_cnt) * 1.0 / NULLIF(obj_cnt, 0) AS small_obj_ratio
FROM `<yourCatalogName>`.`system`.`table_summary`
WHERE table_id = '<tableID>'
    AND dt BETWEEN '<startDate>' AND '<endDate>'  -- Replace with actual date, format yyyyMMdd
ORDER BY dt;

Note

After identifying small files, see Storage optimization to compact small files and tune storage.

Lifecycle management

View storage class distribution

SELECT
    database_id,
    database_name,
    table_id,
    table_name,
    obj_size AS data_obj_size,
    obj_type_standard_size,
    obj_type_ia_size,
    obj_type_archive_size,
    obj_type_coldarchive_size
FROM `<yourCatalogName>`.`system`.`table_summary`
WHERE dt = '<date>'  -- Replace with actual date, format yyyyMMdd
    AND (obj_type_ia_size > 0 OR obj_type_archive_size > 0 OR obj_type_coldarchive_size > 0)
ORDER BY data_obj_size DESC;

Find tables with no file accesses in the last 30 days

SELECT
    database_id,
    database_name,
    table_id,
    table_name,
    owner,
    obj_size + meta_obj_size AS total_size,
    data_last_access_time,
    meta_last_access_time
FROM `<yourCatalogName>`.`system`.`table_summary`
WHERE dt = '<date>'  -- Replace with actual date, format yyyyMMdd
    AND obj_access_num_30d = 0
    AND meta_obj_access_num_30d = 0
ORDER BY total_size DESC;

Data Lake Formation:table_summary and partition_summary system tables

Overview

table_summary

Schema

Field descriptions

Basic information

Storage statistics

Storage class

File size distribution

Access statistics

Partition column

partition_summary

Schema

Field descriptions

Basic information

Storage statistics

Storage class

File size distribution

Access statistics

Partition column

Query examples

DLF data preview

Flink SQL

Storage overview

View the storage trend of a catalog over the last N days

View the storage statistics of all tables on a given day

View the storage breakdown of each partition in a table

View the storage trend of a table over the last N days

Small file management

Find tables with many small files

Find partitions with many small files

View the small file trend of a table over the last N days

Lifecycle management

View storage class distribution

Find tables with no file accesses in the last 30 days

Related documents