System Tables #

Each table has a corresponding system table that contains its metadata and information, such as created snapshots and options used. Users can query system tables in batch mode. Currently, Flink, Spark, Trino, and StarRocks support querying system tables. In some cases, table names must be enclosed in backticks to avoid parsing conflicts, such as in the three-part access pattern:

SELECT * FROM my_catalog.my_db.`my_table$snapshots`;

Snapshots Table #

You can use the snapshots table to query the table's snapshot history, including the number of records in each snapshot.

SELECT * FROM my_table$snapshots;

/*+--------------+------------+-----------------+-------------------+--------------+-------------------------+--------------------------------+------------------------------- +--------------------------------+---------------------+---------------------+-------------------------+----------------+|  snapshot_id |  schema_id |     commit_user | commit_identifier |  commit_kind |             commit_time |             base_manifest_list |            delta_manifest_list |        changelog_manifest_list |  total_record_count |  delta_record_count |  changelog_record_count |      watermark |+--------------+------------+-----------------+-------------------+--------------+-------------------------+--------------------------------+------------------------------- +--------------------------------+---------------------+---------------------+-------------------------+----------------+|            2 |          0 | 7ca4cd28-98e... |                 2 |       APPEND | 2022-10-26 11:44:15.600 | manifest-list-31323d5f-76e6... | manifest-list-31323d5f-76e6... | manifest-list-31323d5f-76e6... |                   2 |                   2 |                       0 |  1666755855600 ||            1 |          0 | 870062aa-3e9... |                 1 |       APPEND | 2022-10-26 11:44:15.148 | manifest-list-31593d5f-76e6... | manifest-list-31593d5f-76e6... | manifest-list-31593d5f-76e6... |                   1 |                   1 |                       0 |  1666755855148 |+--------------+------------+-----------------+-------------------+--------------+-------------------------+--------------------------------+------------------------------- +--------------------------------+---------------------+---------------------+-------------------------+----------------+2 rows in set*/

By querying the snapshots table, you can learn about the table's commit and expiration details, and access historical data through time travel.

Schemas Table #

You can query the historical schema structure of a table through the schema table.

SELECT * FROM my_table$schemas;

/*+-----------+--------------------------------+----------------+--------------+---------+---------+-------------------------+| schema_id |                         fields | partition_keys | primary_keys | options | comment |       update_time       |+-----------+--------------------------------+----------------+--------------+---------+---------+-------------------------+|         0 | [{"id":0,"name":"word","typ... |             [] |     ["word"] |      {} |         | 2022-10-28 11:44:20.600 ||         1 | [{"id":0,"name":"word","typ... |             [] |     ["word"] |      {} |         | 2022-10-27 11:44:15.600 ||         2 | [{"id":0,"name":"word","typ... |             [] |     ["word"] |      {} |         | 2022-10-26 11:44:10.600 |+-----------+--------------------------------+----------------+--------------+---------+---------+-------------------------+3 rows in set*/

You can get field information for a specified snapshot by joining the snapshots and schema tables.

SELECT s.snapshot_id, t.schema_id, t.fields 
    FROM my_table$snapshots s JOIN my_table$schemas t 
    ON s.schema_id=t.schema_id where s.snapshot_id=100;

Options Table #

You can query the options specified for a table in the DDL using the options table. Undisplayed options will use their default values.

SELECT * FROM my_table$options;

/*+------------------------+--------------------+|         key            |        value       |+------------------------+--------------------+| snapshot.time-retained |         5 h        |+------------------------+--------------------+1 rows in set*/

Audit Log Table #

To audit the change logs of a table, use the audit_log system table. From this table, you can get the rowkind column of incremental data to perform operations like filtering for auditing.

rowkind has four possible values:

• +I: Insert operation
• -U: Update operation (old value)
• +U: Update operation (new value)
• -D: Delete operation

SELECT * FROM my_table$audit_log;

/*+------------------+-----------------+-----------------+|     rowkind      |     column_0    |     column_1    |+------------------+-----------------+-----------------+|        +I        |      ... |      ... |+------------------+-----------------+-----------------+|        -U        |      ... |      ... |+------------------+-----------------+-----------------+|        +U        |      ... |      ... |+------------------+-----------------+-----------------+3 rows in set*/

Read-optimized Table #

If you need extreme read performance and can accept slightly outdated data, use the ro (read-optimized) system table. This table improves performance by only scanning files that do not require merging.

For Primary Key tables, the ro system table only scans the top-level files (i.e., the latest Full Compaction results). Note that different buckets may undergo Full Compaction at different times, so the values for different keys may come from different snapshots.

For Append Tables, the ro system table behaves the same as a regular Append Table because no files require merging.

SELECT * FROM my_table$ro;

Files Table #

Queries file information for a table at a specific snapshot.

-- Query files from the latest snapshot
SELECT * FROM my_table$files;

/*+-----------+--------+--------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+------------------------+-------------------------+-------------------------+---------------------+---------------------+-----------------------+| partition | bucket |                      file_path | file_format | schema_id | level | record_count | file_size_in_bytes | min_key | max_key |      null_value_counts |         min_value_stats |         max_value_stats | min_sequence_number | max_sequence_number |         creation_time |+-----------+--------+--------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+------------------------+-------------------------+-------------------------+---------------------+---------------------+-----------------------+|       [3] |      0 | data-8f64af95-29cc-4342-adc... |         orc |         0 |     0 |            1 |                593 |     [c] |     [c] | {cnt=0, val=0, word=0} | {cnt=3, val=33, word=c} | {cnt=3, val=33, word=c} |       1691551246234 |       1691551246637 |2023-02-24T16:06:21.166||       [2] |      0 | data-8b369068-0d37-4011-aa5... |         orc |         0 |     0 |            1 |                593 |     [b] |     [b] | {cnt=0, val=0, word=0} | {cnt=2, val=22, word=b} | {cnt=2, val=22, word=b} |       1691551246233 |       1691551246732 |2023-02-24T16:06:21.166||       [2] |      0 | data-83aa7973-060b-40b6-8c8... |         orc |         0 |     0 |            1 |                605 |     [d] |     [d] | {cnt=0, val=0, word=0} | {cnt=2, val=32, word=d} | {cnt=2, val=32, word=d} |       1691551246267 |       1691551246798 |2023-02-24T16:06:21.166||       [5] |      0 | data-3d304f4a-bcea-44dc-a13... |         orc |         0 |     0 |            1 |                593 |     [c] |     [c] | {cnt=0, val=0, word=0} | {cnt=5, val=51, word=c} | {cnt=5, val=51, word=c} |       1691551246788 |       1691551246152 |2023-02-24T16:06:21.166||       [1] |      0 | data-10abb5bc-0170-43ae-b6a... |         orc |         0 |     0 |            1 |                595 |     [a] |     [a] | {cnt=0, val=0, word=0} | {cnt=1, val=11, word=a} | {cnt=1, val=11, word=a} |       1691551246722 |       1691551246273 |2023-02-24T16:06:21.166||       [4] |      0 | data-2c9b7095-65b7-4013-a7a... |         orc |         0 |     0 |            1 |                593 |     [a] |     [a] | {cnt=0, val=0, word=0} | {cnt=4, val=12, word=a} | {cnt=4, val=12, word=a} |       1691551246321 |       1691551246109 |2023-02-24T16:06:21.166|+-----------+--------+--------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+------------------------+-------------------------+-------------------------+---------------------+---------------------+-----------------------+6 rows in set*/

-- Query files in the specified snapshot
SELECT * FROM my_table$files /*+ OPTIONS('scan.snapshot-id'='1') */;

/*+-----------+--------+--------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+------------------------+-------------------------+-------------------------+---------------------+---------------------+-----------------------+| partition | bucket |                      file_path | file_format | schema_id | level | record_count | file_size_in_bytes | min_key | max_key |      null_value_counts |         min_value_stats |         max_value_stats | min_sequence_number | max_sequence_number |         creation_time |+-----------+--------+--------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+------------------------+-------------------------+-------------------------+---------------------+---------------------+-----------------------+|       [3] |      0 | data-8f64af95-29cc-4342-adc... |         orc |         0 |     0 |            1 |                593 |     [c] |     [c] | {cnt=0, val=0, word=0} | {cnt=3, val=33, word=c} | {cnt=3, val=33, word=c} |       1691551246234 |       1691551246637 |2023-02-24T16:06:21.166||       [2] |      0 | data-8b369068-0d37-4011-aa5... |         orc |         0 |     0 |            1 |                593 |     [b] |     [b] | {cnt=0, val=0, word=0} | {cnt=2, val=22, word=b} | {cnt=2, val=22, word=b} |       1691551246233 |       1691551246732 |2023-02-24T16:06:21.166||       [1] |      0 | data-10abb5bc-0170-43ae-b6a... |         orc |         0 |     0 |            1 |                595 |     [a] |     [a] | {cnt=0, val=0, word=0} | {cnt=1, val=11, word=a} | {cnt=1, val=11, word=a} |       1691551246267 |       1691551246798 |2023-02-24T16:06:21.166|+-----------+--------+--------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+------------------------+-------------------------+-------------------------+---------------------+---------------------+-----------------------+3 rows in set*/

Tags Table #

Queries the tag history of a table, including the base snapshot for a tag and some of the snapshot's historical information. You can also retrieve all tag names and time-travel to the data of a specific tag.

SELECT * FROM my_table$tags;

/*+----------+-------------+-----------+-------------------------+--------------+--------------+| tag_name | snapshot_id | schema_id |             commit_time | record_count |   branches   |+----------+-------------+-----------+-------------------------+--------------+--------------+|     tag1 |           1 |         0 | 2023-06-28 14:55:29.344 |            3 |      []      ||     tag3 |           3 |         0 | 2023-06-28 14:58:24.691 |            7 |  [branch-1]  |+----------+-------------+-----------+-------------------------+--------------+--------------+2 rows in set*/

Consumers Table #

Queries all consumers for the next snapshot ID.

SELECT * FROM my_table$consumers;

/*+-------------+------------------+| consumer_id | next_snapshot_id |+-------------+------------------+|         id1 |                1 ||         id2 |                3 |+-------------+------------------+2 rows in set*/

Manifests Table #

Queries all manifest files in the table's latest snapshot or a specified snapshot.

-- Query the list of latest snapshots
SELECT * FROM my_table$manifests;

/*+--------------------------------+-------------+------------------+-------------------+---------------+|                      file_name |   file_size |  num_added_files | num_deleted_files |     schema_id |+--------------------------------+-------------+------------------+-------------------+---------------+| manifest-f4dcab43-ef6b-4713... |        12365|               40 |                 0 |             0 || manifest-f4dcab43-ef6b-4713... |        1648 |                1 |                 0 |             0 |+--------------------------------+-------------+------------------+-------------------+---------------+2 rows in set*/

-- Query the manifest of the specified snapshot
SELECT * FROM my_table$manifests /*+ OPTIONS('scan.snapshot-id'='1') */;
/*+--------------------------------+-------------+------------------+-------------------+---------------+|                      file_name |   file_size |  num_added_files | num_deleted_files |     schema_id |+--------------------------------+-------------+------------------+-------------------+---------------+| manifest-f4dcab43-ef6b-4713... |        12365|               40 |                 0 |             0 |+--------------------------------+-------------+------------------+-------------------+---------------+1 rows in set*/

Aggregation Fields Table #

Queries the historical aggregation information for the table.

SELECT * FROM my_table$aggregation_fields;

/*+------------+-----------------+--------------+--------------------------------+---------+| field_name |      field_type |    function  |               function_options | comment |+------------+-----------------+--------------+--------------------------------+---------+| product_id | BIGINT NOT NULL |           [] |                             [] |  <NULL> ||      price |             INT | [true,count] | [fields.price.ignore-retrac... |  <NULL> ||      sales |          BIGINT |        [sum] | [fields.sales.aggregate-fun... |  <NULL> |+------------+-----------------+--------------+--------------------------------+---------+3 rows in set*/

Partitions Table #

Query information about partition files in a table.

SELECT * FROM my_table$partitions;

/*+---------------+----------------+--------------------+--------------------+------------------------+|  partition    |   record_count |  file_size_in_bytes|          file_count|        last_update_time|+---------------+----------------+--------------------+--------------------+------------------------+|  [1]          |           1    |             645    |                1   | 2024-06-24 10:25:57.400|+---------------+----------------+--------------------+--------------------+------------------------+*/

Global System Tables #

Global system tables contain statistics for all tables in Paimon. To simplify querying, we created a system database named sys. In Flink, you can use the following SQL to display all global system tables:

USE sys;
SHOW TABLES;

All Options Table #

This table is similar to the options table but displays the options for all tables across all databases.

SELECT * FROM sys.all_table_options;

/*+---------------+--------------------------------+--------------------------------+------------------+| database_name |                     table_name |                            key |            value |+---------------+--------------------------------+--------------------------------+------------------+|         my_db |                    Orders_orc  |                         bucket |               -1 ||         my_db |                        Orders2 |                         bucket |               -1 ||         my_db |                        Orders2 |               sink.parallelism |                7 ||         my_db2|                      OrdersSum |                         bucket |                1 |+---------------+--------------------------------+--------------------------------+------------------+7 rows in set*/

Catalog Options Table #

Query catalog option information using the Catalog Options Table. Options not displayed will use default values.

SELECT * FROM sys.catalog_options;

/*+-----------+---------------------------+|       key |                     value |+-----------+---------------------------+| warehouse | hdfs:///path/to/warehouse |+-----------+---------------------------+1 rows in set*/