Data Lake Formation:Paimon Table

Primary key tables

A primary key table uses a primary key as its unique identifier. It is designed for stream processing scenarios. It supports real-time updates, inserts, and deletes on records and automatically generates precise change logs for downstream stream consumption. In addition, primary key tables support efficient data queries based on primary key conditions.

• Flink SQL example

  CREATE TABLE orders (
    order_id BIGINT,
    price    BIGINT,
    customer STRING,
    PRIMARY KEY NOT ENFORCED(order_id)
  );

• Spark SQL example

  CREATE TABLE orders (
    order_id BIGINT,
    price    BIGINT,
    customer STRING
  ) TBLPROPERTIES (
    'primary-key' = 'order_id'
  );

DLF uses Postpone Bucket mode by default. This is an adaptive bucket allocation strategy that dynamically adjusts the number of buckets based on partition data volume. It avoids performance issues caused by too many buckets—reduced read performance—or too few buckets—reduced write performance. You do not need to configure buckets manually. However, Postpone mode introduces data latency. Newly written data is not visible until compaction completes.

To avoid latency, do one of the following:

1. Use Flink with Ververica Runtime (VVR) 11.4+ or Spark with esr-4.5+. These versions write batches directly to buckets in Postpone mode, eliminating latency.

2. For latency-sensitive tables, explicitly set the number of buckets. For example: 'bucket' = '5'. We recommend one bucket per 1 GB of partition data.

The system dynamically adjusts the number of buckets based on data characteristics and traffic loads. It performs automatic scaling as needed to maintain storage efficiency and read and write performance. Read the following section to learn more about the scaling strategy.

For other business-related configurations, define the following:

• Merge engine (merge-engine) for complex calculations.

• Deletion vectors (deletion-vectors.enabled) to significantly improve query performance.

  Note

  After you enable this feature, all newly written data must be compacted before it becomes visible, regardless of the bucket mode.

• Changelog producer (changelog-producer) set to 'lookup' to generate changelogs for downstream stream reads.

• Sequence field (sequence.field) to handle out-of-order data and ensure correct update order.

If your upstream data is CDC data, use Flink CDC or a data integration product to write data to the lake. These tools provide full-database synchronization, automatic table creation, and table schema synchronization.

To achieve high-performance OLAP queries on primary key tables, we highly recommend enabling deletion vectors. Although this consumes more compaction resources, it delivers more stable and higher-performing OLAP queries.

Append-only tables

An append-only table has no primary key. Unlike primary key tables, it does not support direct stream updates. However, its batch processing performance is significantly better.

1. Supports stream writes and stream reads. DLF automatically merges small files to improve data timeliness at low compute cost.

2. Supports fine-grained operations such as DELETE, UPDATE, and MERGE INTO. Also provides version management and time travel to meet diverse business needs.

3. Supports query acceleration through sorting and bitmaps. OLAP engines deliver excellent direct-read performance.

Use append-only tables for most batch processing scenarios or stream processing without a primary key. Compared to primary key tables, append-only tables are simpler to use and still deliver efficient data writes and queries.

• Flink SQL example

  CREATE TABLE orders (
    order_id BIGINT,
    price    BIGINT,
    customer STRING
  );

• Spark SQL example

  CREATE TABLE orders (
    order_id BIGINT,
    price    BIGINT,
    customer STRING
  );