MaxCompute:Paimon external table

Syntax structure

For details about the syntax of external tables in various formats, see OSS external tables.

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>';

Common parameters

For more information about common parameters, see Basic syntax parameters.

Step 1: Prerequisites

1. You have created a MaxCompute project.

2. You have prepared an OSS bucket and OSS directory. For more information, see Create a bucket and Manage folders.

   Because MaxCompute is available only in specific regions, cross-region connectivity issues may occur. We recommend using an OSS bucket in the same region as your MaxCompute project.

3. Authorization

   1. You have permissions to access OSS. An Alibaba Cloud account (primary account), Resource Access Management (RAM) user, or RAM role can access OSS external tables. For authorization details, see STS-mode authorization for OSS.

   2. You have CreateTable permissions in the MaxCompute project. For details about table operation permissions, see MaxCompute permissions.

Step 2: Prepare data in Flink

Create a Paimon catalog and a Paimon table, then insert data into the table as shown in the following example.

1. Create a Paimon Filesystem Catalog

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

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

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

      Parameter	Required	Description
      metastore	Required.	The metastore type. In this example, select filesystem.
      catalog name	Required	A custom catalog name, such as paimon-catalog.
      warehouse	Required	The data warehouse directory in OSS. In this example, use oss://paimon-fs/paimon-test/.
      fs.oss.endpoint	Required	The OSS endpoint. For example, the endpoint for the China (Hangzhou) region is oss-cn-hangzhou-internal.aliyuncs.com.
      fs.oss.accessKeyId	Required	The AccessKey ID required to access OSS.
      fs.oss.accessKeySecret	Required	The AccessKey Secret required to access OSS.

2. Create a Paimon table

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

   2. Click the name of the target workspace. In the navigation pane on the left, select Development > Scripts.

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

      Enter the following commands and click Run.

      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');

3. If your SQL job is rate-limited—for example, when running an INSERT INTO ... VALUES ... statement—perform the following steps:

   1. Click the name of the target workspace. In the navigation pane on the left, select O&M > Deployments.

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

   3. In the Runtime Parameter Settings area, click Edit. Set the execution.checkpointing.checkpoints-after-tasks-finish.enabled: true configuration in the Other Configuration section and save your changes.

      For details about configuring job runtime parameters, see Configure job deployment information.

Step 3: Create a Paimon external table in MaxCompute

Run the following SQL code in MaxCompute to create a Paimon external table.

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>'
;

In the preceding code, table_path is the path of the Paimon table created in Flink, such as paimon-fs/paimon-test/default.db/test_tbl. To obtain this path:

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

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

3. On the Metadata page, click default under the target catalog. On the default page, click View in the Actions column for the target table.

4. On the Table Schema tab, in the Properties area, obtain the value of the path parameter. For table_path, enter only the path that follows oss://.

Step 4: Load partition data

If the OSS external table you created is partitioned, you must load partition data separately. For more information, see Syntax for loading partition data for OSS external tables.

MSCK REPAIR TABLE oss_extable_paimon_pt ADD PARTITIONS;

Step 5: Read the Paimon external table from MaxCompute

Run the following commands in MaxCompute to query the Paimon external table oss_extable_paimon_pt.

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';

The result is as follows:

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

Error kSIGABRT 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 

• Cause:

  This error occurs when reading TIMESTAMP_NTZ data in JNI mode.

• Solution:

  Before you read from a table, disable the Native feature by running the command SET odps.sql.common.table.jni.disable.native=true;.