Data Lake Formation:Manage Paimon tables

Primary key tables

Designed for stream processing, these tables use a primary key for unique identification. They support real-time data updates, inserts, and deletes, and automatically generate precise change logs for downstream consumption. Efficient data queries based on primary key conditions are also supported.

• Flink SQL

  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's default Postpone Bucket mode adaptively allocates buckets based on data volume. This optimizes read/write performance by avoiding too many or too few buckets, eliminating manual configuration. However, newly written data has latency until compaction is complete.

To eliminate the latency, do one of the following:

1. Use Ververica Runtime (VVR) 11.4+ for Flink jobs or esr-4.5+ for Spark jobs. In the Bucket Postpone mode, new versions write batches directly to buckets, which eliminates latency.

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

For other business-related configurations, you can define the following:

• Merge engine: Used for complex calculations.

• Deletion vectors: Significantly improves query performance.

  Note

  Enabling deletion vectors means new data is only visible after compaction, regardless of the bucket mode used.

• Changelog producer: A lookup changelog producer generates changelogs for downstream stream reads.

• Sequence field: Handles out-of-order data and ensures the correct update sequence.

Utilize Flink CDC or data integration products for writing CDC data to the lake. These solutions provide full database synchronization, automatic table creation, and table schema synchronization capabilities.

For optimal OLAP query performance on primary key tables, enabling deletion vectors is highly recommended. This feature provides enhanced stability and performance at the cost of increased compaction resource utilization.

Append-only tables

Append-only tables do not have a primary key. While they lack direct stream updates, they offer superior batch processing performance.

1. Support stream writes and reads. DLF automatically merges small files for better data availability and lower cost.

2. Support fine-grained DELETE, UPDATE, and MERGE INTO operations. Includes version management and time travel.

3. Optimized for OLAP engines through sorting and bitmaps, ensuring excellent direct read performance.

Append-only tables are ideal for most batch processing or stream processing without a primary key. They are simpler to use than primary key tables, with efficient writes and queries.

• Flink SQL

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

• Spark SQL

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