MaxCompute:Paimon external tables

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
STORED BY 'org.apache.paimon.hive.PaimonStorageHandler'
WITH serdeproperties (
  'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
LOCATION '<oss_location>';

For a full description of external table syntax, see OSS external tables. For parameter details, see Basic syntax parameters.

Step 1: Prepare Paimon data in Flink

If you already have Paimon data in OSS, skip this step.

1.1 Create a Paimon filesystem catalog

1. Log on to the Flink console. In the upper-left corner, select a region.

2. Click the target workspace name. In the left navigation pane, select Catalogs.

3. On the Catalog List page, click Create Catalog. In the Create Catalog dialog box, select Apache Paimon, click Next, and configure the following parameters:

   Parameter	Required	Description
   metastore	Yes	Metastore type. Use filesystem.
   catalog name	Yes	A custom name, such as paimon-catalog.
   warehouse	Yes	The OSS warehouse directory, such as oss://paimon-fs/paimon-test/.
   fs.oss.endpoint	Yes	The OSS endpoint. For example, oss-cn-hangzhou-internal.aliyuncs.com for the China (Hangzhou) region.
   fs.oss.accessKeyId	Yes	The AccessKey ID for OSS access.
   fs.oss.accessKeySecret	Yes	The AccessKey Secret for OSS access.

1.2 Create a Paimon table and insert data

1. Log on to the Flink console. Select a region, then click the target workspace name.

2. In the left navigation pane, select Development > Scripts.

3. On the New Script tab, click to create a query script.

4. Run the following SQL:

   CREATE TABLE `paimon_catalog`.`default`.test_tbl (
       id BIGINT,
       data STRING,
       dt STRING,
       PRIMARY KEY (dt, id) NOT ENFORCED
   ) PARTITIONED BY (dt);
   
   INSERT INTO `paimon-catalog`.`default`.test_tbl VALUES (1,'CCC','2024-07-18'), (2,'DDD','2024-07-18');

If the job is rate-limited when running INSERT INTO ... VALUES ...:

1. In the left navigation pane, select O&M > Deployments.

2. On the Deployments page, click the target job to open its Configuration page.

3. In the Runtime Parameter Settings area, click Edit. In the Other Configuration section, add:

   execution.checkpointing.checkpoints-after-tasks-finish.enabled: true

   For details, see Configure job deployment information.

Step 2: Create a Paimon external table in MaxCompute

Run the following SQL in MaxCompute. Replace <uid> with your Alibaba Cloud account ID, and replace <table_path> with the path to your Paimon table directory in OSS (without the oss:// prefix).

CREATE EXTERNAL TABLE oss_extable_paimon_pt
(
    id BIGINT,
    data STRING
)
PARTITIONED BY (dt STRING)
STORED BY 'org.apache.paimon.hive.PaimonStorageHandler'
WITH serdeproperties (
    'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-<your region>-internal.aliyuncs.com/<table_path>';

Find the table path

<table_path> is the path of the Paimon table in OSS, for example paimon-fs/paimon-test/default.db/test_tbl. To find it:

1. In the Flink console, select the target workspace and go to Catalogs.

2. On the Metadata page, click default under the target catalog, then click View for the target table.

3. On the Table Schema tab, in the Properties area, copy the value of the path parameter. Use only the part after oss://.

Step 3: Load partition data

Because oss_extable_paimon_pt is a partitioned table, run the following command to discover and register existing partitions:

MSCK REPAIR TABLE oss_extable_paimon_pt ADD PARTITIONS;

For the full syntax, see Load partition data for OSS external tables.

Step 4: Query the Paimon external table

The following two SET statements enable Hive-compatible query execution, which is required for Paimon external tables.

SET odps.sql.common.table.planner.ext.hive.bridge = true;
SET odps.sql.hive.compatible = true;
SELECT * FROM oss_extable_paimon_pt WHERE dt='2024-07-18';

Expected output:

+------------+------------+------------+
| id         | data       | dt         |
+------------+------------+------------+
| 1          | CCC        | 2024-07-18 |
| 2          | DDD        | 2024-07-18 |
+------------+------------+------------+

Schema mismatch behavior

If the Paimon file schema differs from the external table DDL:

• Fewer columns in the file than in the DDL: Missing values are filled with NULL.

• More columns in the file than in the DDL: Extra columns are discarded.

• Data type mismatch: Reading STRING data into an INT column is not supported. Reading INT data into a STRING column is supported but not recommended.

kSIGABRT error when reading a Paimon external table

Error message:

ODPS-0123144: Fuxi job failed - kSIGABRT(errCode:6) at Odps/*****_SQL_0_1_0_job_0/M1@f01b17437.cloud.eo166#3.
  Detail error msg: CRASH_CORE, maybe caused by jvm crash, please check your java udf/udaf/udtf.
  | fatalInstance: Odps/*****_SQL_0_1_0_job_0/M1#0_0

This error occurs when reading TIMESTAMP_NTZ data in JNI mode. Before querying the table, run:

SET odps.sql.common.table.jni.disable.native=true;