Paimon catalog

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
[COMMENT <comment>]
PROPERTIES
(
    "type" = "paimon",
    CatalogParams,
    StorageCredentialParams
);

Parameters

"aliyun.oss.endpoint" = "<YourAliyunOSSEndpoint>"

Examples

CREATE EXTERNAL CATALOG paimon_catalog
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "hive",
    "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083"
);

CREATE EXTERNAL CATALOG paimon_catalog
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "filesystem",
    "paimon.catalog.warehouse" = "oss://<yourBucketName>/<yourPath>",
    "aliyun.oss.endpoint" = "cn-hangzhou.oss-dls.aliyuncs.com"
);

CREATE EXTERNAL CATALOG paimon_catalog
PROPERTIES
(
    "type" = "paimon",
    "paimon.catalog.type" = "dlf",
    "paimon.catalog.warehouse" = "oss://<yourBucketName>/<yourPath>",
    "dlf.catalog.id" = "paimon_dlf_test"
);

CREATE EXTERNAL CATALOG dlf_catalog
PROPERTIES
(
    "type" = "paimon",
    "uri" = "http://cn-hangzhou-vpc.dlf.aliyuncs.com",
    "paimon.catalog.type" = "rest",
    "paimon.catalog.warehouse" = "test_paimon",
    "token.provider" = "dlf"
);

Syntax

CREATE TABLE IF NOT EXISTS <catalog_name>.<database_name>.<table_name>
(
   id STRING,
   name STRING,
   day INT
)
PRIMARY KEY (id, day)        -- Omit for append-only tables
PARTITION BY (`day`)         -- Omit for non-partitioned tables
PROPERTIES (                 -- Omit if no properties are needed
  key = value
);

PROPERTIES parameters

Limitations

• For partitioned tables, all partition key columns must appear at the end of the column definition.

• For partitioned tables, all partition key columns must be included in the primary key definition.

• bucket_key cannot be part of a partition key or primary key.

Example

CREATE TABLE dlf_catalog.sr_dlf_db.ads_age_pvalue_analytics(
    final_gender_code STRING COMMENT 'gender',
    age_level STRING COMMENT 'age_level',
    pvalue_level STRING COMMENT 'consumption_level',
    clicks INT COMMENT 'clicks',
    total_behaviors INT COMMENT 'total_behaviors'
);

Usage notes

StarRocks writes to Paimon tables in batches (Batch Write). Due to Paimon's limitations, you cannot write to tables whose bucket mode is HASH_DYNAMIC or CROSS_PARTITION. This includes primary key tables where the bucket property is not set—the default value is -1 (Dynamic Bucket Mode), which prevents write operations.

The Paimon software development kit (SDK) requires that partition key or primary key columns in the data being written are not null. StarRocks does not precheck data before writing, so an exception is thrown if a null value is encountered.

Write to a Paimon table

INSERT INTO <catalog_name>.<database_name>.<table_name> (column1, column2, ...) VALUES (value1, value2, ...);

Example—insert rows into ads_age_pvalue_analytics:

INSERT INTO dlf_catalog.sr_dlf_db.ads_age_pvalue_analytics (final_gender_code, age_level, pvalue_level, clicks, total_behaviors)
VALUES
('M', '18-24', 'Low', 1500, 2500),
('F', '25-34', 'Medium', 2200, 3300),
('M', '35-44', 'High', 2800, 4000);

Import Paimon data into an internal table

Transform and load data from a Paimon table into a StarRocks OLAP table:

INSERT INTO default_catalog.olap_db.olap_tbl SELECT * FROM <paimon_catalog>.<db_name>.<table_name>;