MaxCompute:Apache Paimon external tables

Step 1: Prepare data in Realtime Compute for Apache Flink

Create an Apache Paimon catalog and an Apache Paimon table, and insert data into the table. This section describes the procedure. If an Apache Paimon table and data are available in Realtime Compute for Apache Flink, skip this step.

1. Log on to the Realtime Compute for Apache Flink console and create an Apache Paimon catalog. For more information, see Create an Apache Paimon catalog.

2. Create an Apache Paimon table. For more information, see Manage Apache Paimon catalogs.

   1. In the left-side navigation pane of the console of fully managed Flink, click Catalogs. In the Catalogs pane, find the Apache Paimon catalog that you create and click the default database. On the page that appears, click Create Table, as shown in the following figure.

      

   2. On the Built-in tab of the Create Table dialog box, click Apache Paimon and click Next. In the Create Table step, enter the following table creation statement and click Confirm. In this example, a table named test_tbl is created.

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

   3. On the SQL Editor page, create an SQL draft that contains the following statement, deploy the draft, and run the deployment for the draft. For more information about how to develop and run an SQL draft, see Develop an SQL draft.

      INSERT INTO `catalogname`.`default`.test_tbl VALUES ('2023-04-21', 1, 'AAA'), ('2023-04-21', 2, 'BBB'), ('2023-04-22', 1, 'CCC'), ('2023-04-22', 2, 'DDD');

      Note

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

      • If the SQL deployment is a bounded streaming deployment, you must go to the Deployments page, click Edit in the upper-right corner of the Parameters section of the Configuration tab, and then add execution.checkpointing.checkpoints-after-tasks-finish.enabled: true to the Other Configuration field. For example, if the INSERT INTO ... VALUES ... statement is included in an SQL deployment, the deployment is a bounded streaming deployment. For more information about how to configure the runtime parameters of a deployment, see Configure a deployment.

Step 2: Upload the Apache Paimon plug-in to your MaxCompute project

You can use one of the following methods to upload the Apache Paimon plug-in to the MaxCompute project.

ADD JAR <path_to_paimon_maxcompute_connector.jar>;

Step 3: Create an Apache Paimon external table on the MaxCompute client (odpscmd) or by using a tool that can execute MaxCompute SQL statements

Create an Apache Paimon external table on the MaxCompute client (odpscmd) or by using a tool that can execute MaxCompute SQL statements. In this example, an external table named oss_extable_paimon_1pt is created.

CREATE EXTERNAL TABLE oss_extable_paimon_1pt
(
    id BIGINT,
    data STRING
)
PARTITIONED BY (dt STRING )
stored BY 'org.apache.paimon.hive.PaimonStorageHandler'
WITH serdeproperties (
    'odps.properties.rolearn'='acs:ram::124*********:role/aliyunodpsdefaultrole'
)
location 'oss://oss-cn-beijing-internal.aliyuncs.com/paimon_flink/test_db_y.db/test_tbl/'
USING 'paimon_maxcompute_connector.jar'
;

Step 4: Read data from the Apache Paimon external table on the MaxCompute client (odpscmd) or by using a tool that can execute MaxCompute SQL statements

1. Run the following commands on the MaxCompute client (odpscmd) or other tools that can run MaxCompute SQL:

   SET odps.sql.common.table.planner.ext.hive.bridge = true;
   SET odps.sql.hive.compatible = true;

2. Run the following command to query data from the oss_extable_paimon_1pt table:

   SELECT * FROM oss_extable_paimon_1pt;

   The following result is returned:

   +------------+------------+------------+
   | id         | data       | dt         |
   +------------+------------+------------+
   | 1          | AAA        | 2023-04-21 |
   | 2          | BBB        | 2023-04-21 |
   +------------+------------+------------+

   Note

   If the partition information is not displayed in the command output, you can run the following command to add the partition information:

   msck repair table oss_extable_paimon_1pt add partitions;