This documentation is for an unreleased version of Apache Paimon. We recommend you use the latest stable version.

Table Types #

Paimon supports table types:

1. table with pk: Paimon Data Table with Primary key
2. table w/o pk: Paimon Data Table without Primary key
3. view: metastore required, views in SQL are a kind of virtual table
4. format-table: file format table refers to a directory that contains multiple files of the same format, where operations on this table allow for reading or writing to these files, compatible with Hive tables
5. object table: provides metadata indexes for unstructured data objects in the specified Object Storage directory.
6. materialized-table: aimed at simplifying both batch and stream data pipelines, providing a consistent development experience, see Flink Materialized Table

Table with PK #

See Paimon with Primary key.

Primary keys consist of a set of columns that contain unique values for each record. Paimon enforces data ordering by sorting the primary key within each bucket, allowing streaming update and streaming changelog read.

The definition of primary key is similar to that of standard SQL, as it ensures that there is only one data entry for the same primary key during batch queries.

CREATE TABLE my_table (
    a INT PRIMARY KEY NOT ENFORCED,
    b STRING
) WITH (
    'bucket'='8'
)

CREATE TABLE my_table (
    a INT,
    b STRING
) TBLPROPERTIES (
    'primary-key' = 'a',
    'bucket' = '8'
)

Table w/o PK #

See Paimon w/o Primary key.

If a table does not have a primary key defined, it is an append table. Compared to the primary key table, it does not have the ability to directly receive changelogs. It cannot be directly updated with data through streaming upsert. It can only receive incoming data from append data.

However, it also supports batch sql: DELETE, UPDATE, and MERGE-INTO.

CREATE TABLE my_table (
    a INT,
    b STRING
)

View #

View is supported when the metastore can support view, for example, hive metastore. If you don’t have metastore, you can only use temporary View, which only exists in the current session. This chapter mainly describes persistent views.

View will currently save the original SQL. If you need to use View across engines, you can write a cross engine SQL statement. For example:

CREATE VIEW [IF NOT EXISTS] [catalog_name.][db_name.]view_name
   [( columnName [, columnName ]* )] [COMMENT view_comment]
AS query_expression;

DROP VIEW  [IF EXISTS] [catalog_name.][db_name.]view_name;

SHOW VIEWS;

SHOW CREATE VIEW my_view;

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [catalog_name.][db_name.]view_name
   [( columnName [, columnName ]* )] [COMMENT view_comment]
AS query_expression;

DROP VIEW  [IF EXISTS] [catalog_name.][db_name.]view_name;

SHOW VIEWS;

Format Table #

Format table is supported when the metastore can support format table, for example, hive metastore. The Hive tables inside the metastore will be mapped to Paimon’s Format Table for computing engines (Spark, Hive, Flink) to read and write.

Format table refers to a directory that contains multiple files of the same format, where operations on this table allow for reading or writing to these files, facilitating the retrieval of existing data and the addition of new files.

Partitioned file format table just like the standard hive format. Partitions are discovered and inferred based on directory structure.

Format Table is enabled by default, you can disable it by configuring Catalog option: 'format-table.enabled'.

Currently only support CSV, Parquet, ORC formats.

CREATE TABLE my_csv_table (
    a INT,
    b STRING
) WITH (
    'type'='format-table',
    'file.format'='csv',
    'field-delimiter'=','
)

CREATE TABLE my_csv_table (
    a INT,
    b STRING
) USING csv OPTIONS ('field-delimiter' ',')

CREATE TABLE my_parquet_table (
    a INT,
    b STRING
) WITH (
    'type'='format-table',
    'file.format'='parquet'
)

CREATE TABLE my_parquet_table (
    a INT,
    b STRING
) USING parquet

Object Table #

Object Table provides metadata indexes for unstructured data objects in the specified Object Storage storage directory. Object tables allow users to analyze unstructured data in Object Storage:

1. Use Python API to manipulate these unstructured data, such as converting images to PDF format.
2. Model functions can also be used to perform inference, and then the results of these operations can be concatenated with other structured data in the Catalog.

The object table is managed by Catalog and can also have access permissions and the ability to manage blood relations.

-- Create Object Table

CREATE TABLE `my_object_table` WITH (
  'type' = 'object-table',
  'object-location' = 'oss://my_bucket/my_location' 
);

-- Refresh Object Table

CALL sys.refresh_object_table('mydb.my_object_table');

-- Query Object Table

SELECT * FROM `my_object_table`;

-- Query Object Table with Time Travel

SELECT * FROM `my_object_table` /*+ OPTIONS('scan.snapshot-id' = '1') */;

-- Create Object Table

CREATE TABLE `my_object_table` TBLPROPERTIES (
  'type' = 'object-table',
  'object-location' = 'oss://my_bucket/my_location' 
);

-- Refresh Object Table

CALL sys.refresh_object_table('mydb.my_object_table');

-- Query Object Table

SELECT * FROM `my_object_table`;

-- Query Object Table with Time Travel

SELECT * FROM `my_object_table` VERSION AS OF 1;

Materialized Table #

Materialized Table aimed at simplifying both batch and stream data pipelines, providing a consistent development experience, see Flink Materialized Table.

Now only Flink SQL integrate to Materialized Table, we plan to support it in Spark SQL too.

CREATE MATERIALIZED TABLE continuous_users_shops
PARTITIONED BY (ds)
FRESHNESS = INTERVAL '30' SECOND
AS SELECT
  user_id,
  ds,
  SUM (payment_amount_cents) AS payed_buy_fee_sum,
  SUM (1) AS PV
FROM (
  SELECT user_id, order_created_at AS ds, payment_amount_cents
    FROM json_source
  ) AS tmp
GROUP BY user_id, ds;