Paimon external table

Syntax

For details about the syntax for creating external tables in different 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 directory. For more information, see Create a bucket and Manage directories.

   Ensure your bucket is in the same region as your MaxCompute project.

3. Grant permissions.

   1. You have permission to access OSS. You can access an OSS external table by using an Alibaba Cloud account, a RAM user, or a RAM role. For more information about how to grant permissions, see STS-mode authorization for OSS.

   2. You have the CreateTable permission in the MaxCompute project. For more information about table-related permissions, see MaxCompute permissions.

Step 2: Prepare data in Flink

Create a Paimon catalog and a Paimon table, and then insert data into the table.

1. Create a Paimon filesystem catalog

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

   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 on the right. In the Create Catalog dialog box, select Apache Paimon, click Next , and configure the following parameters:

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

2. Create a Paimon table

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

   2. Click the target workspace name, and then in the navigation pane on the left, select Development > Scritps.

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

      Enter and run the following commands:

      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. For SQL jobs that finish automatically, such as those that run an INSERT INTO ... VALUES ... statement, perform the following operations:

   1. Click the target workspace name. 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 its Configuration page.

   3. In the Parameters section, click Edit, and in Other Configuration set the execution.checkpointing.checkpoints-after-tasks-finish.enabled: true code and save.

      For more information about how to configure runtime parameters for a job, see Configure job deployment information.

Step 3: Create a Paimon external table

Run the following SQL statement 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 statement, table_path is the path to the Paimon table that you created in Flink, such as paimon-fs/paimon-test/default.db/test_tbl. To find this path, perform the following steps:

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

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

3. On the Catalogs page, click default under the target Catalog, and on the default page, click View in the Actions column of the target table.

4. On the Table Schema tab, find the value of the parameter in the PropertiesPath section. For the table_path, use only the portion of the path that follows oss://.

Step 4: Load partition data

If the OSS external table that you created is a partitioned table, you must load its partitions. For more information, see OSS external tables.

MSCK REPAIR TABLE oss_extable_paimon_pt ADD PARTITIONS;

Step 5: Query the Paimon external table

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

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 following result is returned:

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

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 

• Cause:

  This error occurs when you read a TIMESTAMP_NTZ column in JNI mode.

• Solution:

  Disable the JNI bridge by running the following command before your query: SET odps.sql.common.table.jni.disable.native=true;