MaxCompute:Paimon external tables

Syntax

For more information about the syntax for external tables of 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 information about common parameters, see Basic syntax parameters.

Step 1: Prepare data in Flink

Create a Paimon catalog and a Paimon table, and then insert data into the table. The following example shows how.

1. Log on to the Realtime Compute for Apache Flink console and create a Paimon Filesystem Catalog. For this example, the catalog is named paimoncatalog. For more information, see Create an Apache Paimon Filesystem catalog.

2. Create a Paimon table. For more information, see Manage Apache Paimon tables.

   1. In the text editor on the Data Query page, enter the following statement, select the code, and then click Run.

      CREATE TABLE `paimoncatalog`.`default`.test_tbl (
          id BIGINT,
          data STRING,
          dt STRING,
          PRIMARY KEY (dt, id) NOT ENFORCED
      ) PARTITIONED BY (dt);

   2. On the Data Development > ETL page, create an SQL job that contains the following statement. Then, deploy and run the job. For more information, see Develop an SQL draft.

      INSERT INTO `paimoncatalog`.`default`.test_tbl VALUES (1,'CCC','2024-07-18'), (2,'DDD','2024-07-18');

      Important

      • Make sure that the engine version for the SQL job is vvr-8.0.1-flink-1.17 or later.

      • If the SQL job is a bounded stream job, for example, a job that executes an INSERT INTO ... VALUES ... statement, perform the following steps:

        1. On the O&M page, click the target job to go to its Deployment Details tab.

        2. Edit the Runtime Parameter Configuration. In the Other Configurations section, set execution.checkpointing.checkpoints-after-tasks-finish.enabled: true, and then save the configuration. For more information about how to configure runtime parameters for a job, see Configure job deployment information.

Step 2: Create a Paimon external table in MaxCompute

In MaxCompute, execute the following SQL statement to create a MaxCompute 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-hangzhou-internal.aliyuncs.com/<table_path>'
;

MSCK REPAIR TABLE oss_extable_paimon_pt ADD PARTITIONS;

In the preceding statement, table_path is the path of the Paimon table that you created in Flink, for example, oss-mc-test/paimon/default.db/test_tbl. To obtain the path, perform the following steps:

1. Log on to the Realtime Compute for Apache Flink console and click the name of your workspace.

2. In the navigation pane on the left, click Data Management.

3. On the Metadata page, click **default** in the target catalog. On the default page, click View in the target table's Actions column.

4. In the Table Properties section on the Table Schema Details tab, obtain the value of the path parameter. Use only the part of the path that follows oss:// as the value for table_path.

Step 3: Import partition data

If the OSS external table that you created is a partitioned table, you must also import the partition data. For more information, see Syntax for adding partition data to an OSS external table.

MSCK REPAIR TABLE oss_extable_paimon_pt ADD PARTITIONS;

Step 4: Read the Paimon external table using MaxCompute

In MaxCompute, execute the following statement to query the MaxCompute 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 following result is returned:

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