Build Cross-Project Paimon Pipelines with DLF and Flink - MaxCompute - Alibaba Cloud - MaxCompute

Step 1: Prepare the source data

This step simulates real-time business data updates. Flink writes the data to the data lake in Paimon format.

Sign in to the RDS console.
In the left navigation bar, select Instances, and in the top-left corner, select the region.
On the Instances page, click the target instance's Instance ID/Name to open the Instance Details page.
In the left navigation bar, click Databases.

Click Create Database. Configure the following parameters:

Parameter	Required	Description	Example
Database Name	Required	The name must be 2 to 64 characters in length. It must start with a letter and end with a letter or a digit. It can contain lowercase letters, digits, underscores (_), and hyphens (-). The database name must be unique within the instance. If the database name contains a hyphen (`-`), the hyphen (`-`) in the folder name of the created database is replaced with `@002d`.	`dlf25_paimon`
Supported Character Set	Required	Select a character set as needed.	`utf8`
Authorized By	Optional	Select the accounts that need to access this database. You can leave this parameter empty and attach accounts after creating the database. Only standard accounts are displayed here. Privileged accounts have all permissions on all databases and do not require authorization.	`Default`
Description	Optional	Remarks about the database for future management. Maximum length: 256 characters.	`Create a test database for the external project DLF 2.5.`

Click Log On to Database. In the left navigation bar, select Instance and double-click the created database. On the SQL Console page on the right, execute the following statements to create a test table and write test data.

If the instance exists but you cannot find the target database after you expand the instance, possible causes include:

The logon account does not have access permissions for the target database: You can go to the Accounts page on the RDS instance details page to manually modify account permissions or change the logon database account.
The directory is not displayed because metadata is not synchronized: Hover over the instance, and click the icon to the right of the instance name to refresh the list and display the database.

CREATE TABLE emp (
    empno    INT PRIMARY KEY,
    ename    VARCHAR(20),
    job      VARCHAR(20),
    mgr      INT,
    hiredate DATE,
    sal      DECIMAL(10,2),
    comm     DECIMAL(10,2),
    deptno   INT
);
INSERT INTO emp VALUES
(7369,'SMITH','CLERK',7902,'2020-12-17', 800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'2021-02-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'2021-02-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'2021-04-02',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'2021-09-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'2021-05-01',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'2021-06-09',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'2021-12-09',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'2021-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'2021-09-08',1500.00,0.00,30),
(7876,'ADAMS','CLERK',7788,'2022-01-12',1100.00,NULL,20),
(7900,'JAMES','CLERK',7698,'2021-12-03', 950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'2021-12-03',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'2022-01-23',1300.00,NULL,10),
(8001,'DUKE','ENGINEER',7788,'2023-03-15',3500.00,NULL,20),
(8002,'DANIEL','ENGINEER',7788,'2023-04-01',3400.00,NULL,20),
(8003,'SANDRA','CONSULTANT',7698,'2023-05-10',2700.00,NULL,30),
(8004,'KAREN','CLERK',7902,'2023-06-11',1200.00,NULL,20),
(8005,'JOHN','MANAGER',7839,'2023-07-12',4000.00,NULL,10),
(8006,'LISA','DESIGNER',7698,'2023-08-13',2200.00,NULL,30);

Query the data in the test table.
```
SELECT * FROM emp;
```
The following result is returned:

The query returns 20 records with eight fields: empno (employee ID), ename (name), job (job title), mgr (manager ID), hiredate (hire date), sal (salary), comm (commission), and deptno (department ID). The department IDs are 10, 20, and 30. The comm value for some employees is NULL.

Step 2: Prepare a DLF metadatabase

Log on to the Data Lake Formation (DLF) console, and in the upper-left corner, select a region.
In the navigation pane on the left, choose Catalog List.
On the Catalogs page, click Create Catalog.

Go to the Data Lake Formation (DLF) page, configure the following parameters, and then click Create Catalog:

Parameter	Required	Description
Catalog Name	Required	A custom catalog name. The name must start with a letter and can be 1 to 256 characters in length. It can contain letters, digits, and underscores (_). For example, `db_dlf25_oss`.
Description	Optional	A custom description.
Storage Type	Required	Standard.
Storage Redundancy Type	Required	Locally redundant storage: Data is stored in a single zone. If the zone where the data is stored becomes unavailable, the data becomes inaccessible. We recommend that you use zone-redundant storage. Zone-redundant storage: Provides data redundancy across multiple availability zones (AZs) in the same region. This ensures data availability even if a single AZ fails. After a catalog is created, its storage type cannot be changed from zone-redundant to locally redundant. Compared to locally redundant storage, zone-redundant storage provides higher availability at a higher cost. Use zone-redundant storage for data that requires high reliability.

Step 3: Create Paimon and MySQL catalogs using Flink

Create a Paimon catalog

Log in to the real-time compute for Apache Flink console and select a region in the upper-left corner.
Click the name of the target workspace, and then in the left-side navigation pane, select Data Management.

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	Required	The metastore type. Select `dlf` for this example.
catalog name	Required	The DLF Catalog version to associate. Select DLF V2.5. In this example, select `db_dlf25_oss` created in DLF.

Create a MySQL catalog

Log in to the real-time compute for Apache Flink console and select a region in the upper-left corner.
Add an IP address to the whitelist.
1. In the Actions column of the target workspace, click Details.
  
  In the pop-up Workspace Details dialog box, copy the CIDR Block of the VSwitch.
2. Sign in to the RDS console.
  
  In the left navigation bar, select Instances, and in the top-left corner, select the region.
  
  On the Instances page, click the target instance's Instance ID/Name to open the Instance Details page.
3. In the left navigation bar, click Whitelist and SecGroup.
  
  On the Whitelist Settings tab, click Modify.
4. In the Edit Whitelist dialog box that appears, add the copied IP addresses to the IP Addresses field, and click OK.
Log in to the real-time compute for Apache Flink console and select a region in the upper-left corner.

Click the name of the target workspace, and then in the left-side navigation pane, select Data Management.

On the catalog list page, click Create Catalog . In the Create Catalog dialog box, select MySQL, click Next , and configure the following parameters:

Parameter	Required	Description
catalog name	Required	A custom name for the MySQL catalog. For example, `mysql-catalog-dlf25`.
hostname	Required	The IP address or hostname of the MySQL database. Log on to the RDS console. On the instance details page, click Database Connection to view the Internal Endpoint, Public Endpoint, and Internal Port:. When accessing across VPCs or over the Internet, you need to establish network connectivity. For more information, see Network Connectivity.
port	Default	The port for connecting to the server. Default: 3306.
default database	Required	The default database name. For example, `dlf25_paimon`.
username	Required	The username to connect to the MySQL database server. To find the username, log on to the RDS console, navigate to the instance details page, and click Accounts.
password	Required	The password to connect to the MySQL database server. To find the password, log on to the RDS console, navigate to the instance details page, and click Accounts.

Step 4: Read MySQL data and write it to a Paimon table in DLF using Flink

Log in to the real-time compute for Apache Flink console and select a region in the upper-left corner.
Click the target workspace name, and then in the left navigation pane, select Development > ETL.
On the Drafts tab, click to create a new folder.
Right-click a folder and select New Blank Stream Draft. In the New Draft dialog box, enter a Name and select an Engine Version.
Write and run the following SQL statement in the file. Modify the names based on your actual environment.
```
CREATE TABLE IF NOT EXISTS `db_dlf25_oss`.`default`.`emp`
WITH (
  'bucket' = '4',
  'changelog-producer' = 'input'
) AS TABLE `mysql-catalog-dlf25`.`dlf25_paimon`.`emp`;
```
1. (Optional) Click Validate in the upper-right corner to check for syntax errors in the job's Flink SQL statement.
2. Click Deploy in the upper-right corner, and in the Deploy New Version dialog box that appears, enter a Comment, Job Label, and Deployment Target, and then click Confirm.
Click the name of the target workspace. In the navigation pane on the left, choose O&M > Deployments.
On the Deployments page, click the target job to open the deployment details page.
In the upper-right corner of the page, click Start, select stateless start, and then click Start.
Query the Paimon data

In the left navigation pane, select Development > Data Query.

On the Query Script tab, click to create a new query script.
```
SELECT * FROM `db_dlf25_oss`.`default`.`emp`;
```
The query returns 22 records from the EMP table with the empno, ename, job, mgr, hiredate, sal, comm, and deptno fields, confirming that Flink successfully wrote the MySQL data to the Paimon table in DLF.
Log on to the Data Lake Formation (DLF) console. In the navigation pane, choose Catalog List > Catalogs. Click the data catalog to go to the database and view the details of the synchronized table.

The synchronized emp table appears in the table list. Its owner is root (root account), the table type is Managed, the table format is Paimon PK Table, and the file format is parquet.

Step 5: Create a Paimon_DLF external data source in MaxCompute

Log in to the MaxCompute console and select a region in the upper-left corner.
In the left-side navigation pane, choose Manage Configurations > External Data Source .
On the External Data Source page, click Create External Data Source.

In the Create External Data Source dialog box, configure the parameters. The following tables describe the parameters.

Parameter	Required	Description
External Data Source Type	Yes	Select Paimon_DLF.
External Data Source Name	Yes	A name for the external data source. The name must meet the following requirements: It must start with a letter and can contain only lowercase letters, underscores (_), and digits. It can be up to 128 characters in length. Example: `paimon_dlf`.
Description	No	Optional. A description of the external data source.
Region	Yes	Defaults to the current region.
Authentication and Authorization	Yes	The default value is Alibaba Cloud RAM role.
Service-linked Role	Yes	This is automatically generated.
Endpoint	Yes	Automatically generated. For example, the endpoint for the China (Hangzhou) region is `cn-hangzhou-intranet.dlf.aliyuncs.com`.
Foreign Server Supplemental Properties	No	Optional. Specifies additional attributes for the data source. These attributes define how tasks that use this data source access the source system. Note Refer to the official documentation for a complete list of supported parameters. More parameters may be added in future updates.

Click OK to create the external data source.
On the External Data Source page, find the data source and click Details in the Actions column to view its details.

Step 6: Create a Paimon_DLF external project in MaxCompute

Log in to the MaxCompute console and select a region in the upper-left corner.
In the left-side navigation pane, choose Manage Configurations > Projects.
On the External Project tab, click Create Project.

In the Create Project dialog box, configure the settings and click OK.

Parameter	Required	Description
Project Type	Required	The default value is External Project.
Region	Required	The default value is the current region. You cannot change it here.
Project Name (Globally Unique)	Required	The name must start with a letter, contain letters, digits, and underscores (_), and be 3 to 28 characters in length.
MaxCompute Foreign Server Type	Optional	The default value is Paimon_DLF.
MaxCompute Foreign Server	Optional	Use Existing: A list of created external data sources appears. Create Foreign Server: Create and use a new external data source.
MaxCompute Foreign Server Name	Required	Use Existing: Select the name of a created external data source from the drop-down list. New external data source: Use the name of the new external data source.
Authentication and Authorization	Required	The identity of the task executor. If no service-linked role exists, create one before using this mode.
Service-linked Role	Required	Generated by default.
Endpoint	Required	Generated by default.
Data Catalog	Required	The DLF data catalog.
Billing Method	Required	Subscription or Pay-as-you-go.
Default Quota	Required	Select an existing quota.
Description	Optional	A custom project description.

Step 7: Analyze data

Use a connection tool to log on to the external project.

List the schemas in the external project.

-- Enable schema syntax at the session level.
SET odps.namespace.schema=true;
SHOW schemas;
-- The following result is returned.
ID = 20250919****am4qb
default
system
OK

List the tables in the schema of the external project.

-- Enable schema syntax at the session level.
SET odps.namespace.schema=true;
USE schema default;
SHOW tables;
-- The following result is returned.
ID = 20250919****am4qb
acs:ram::<uid>:root  emp
OK

Read data from the Paimon table in DLF.

-- Enable schema syntax at the session level.
SET odps.namespace.schema=true;
USE schema default;
SELECT * FROM emp;

Click to view the execution result

-- The following result is returned.
+------------+------------+------------+------------+------------+------------+------------+------------+
| empno      | ename      | job        | mgr        | hiredate   | sal        | comm       | deptno     | 
+------------+------------+------------+------------+------------+------------+------------+------------+
| 7521       | WARD       | SALESMAN   | 7698       | 2021-02-22 | 1250       | 500        | 30         | 
| 7844       | TURNER     | SALESMAN   | 7698       | 2021-09-08 | 1500       | 0          | 30         | 
| 7876       | ADAMS      | CLERK      | 7788       | 2022-01-12 | 1100       | NULL       | 20         | 
| 7900       | JAMES      | CLERK      | 7698       | 2021-12-03 | 950        | NULL       | 30         | 
| 7934       | MILLER     | CLERK      | 7782       | 2022-01-23 | 1300       | NULL       | 10         | 
| 8005       | JOHN       | MANAGER    | 7839       | 2023-07-12 | 4000       | NULL       | 10         | 
| 7369       | SMITH      | CLERK      | 7902       | 2020-12-17 | 800        | NULL       | 20         | 
| 7566       | JONES      | MANAGER    | 7839       | 2021-04-02 | 2975       | NULL       | 20         | 
| 7654       | MARTIN     | SALESMAN   | 7698       | 2021-09-28 | 1250       | 1400       | 30         | 
| 7698       | BLAKE      | MANAGER    | 7839       | 2021-05-01 | 2850       | NULL       | 30         | 
| 7839       | KING       | PRESIDENT  | NULL       | 2021-11-17 | 5000       | NULL       | 10         | 
| 8002       | DANIEL     | ENGINEER   | 7788       | 2023-04-01 | 3400       | NULL       | 20         | 
| 8006       | LISA       | DESIGNER   | 7698       | 2023-08-13 | 2200       | NULL       | 30         | 
| 7499       | ALLEN      | SALESMAN   | 7698       | 2021-02-20 | 1600       | 300        | 30         | 
| 7782       | CLARK      | MANAGER    | 7839       | 2021-06-09 | 2450       | NULL       | 10         | 
| 7788       | SCOTT      | ANALYST    | 7566       | 2021-12-09 | 3000       | NULL       | 20         | 
| 7902       | FORD       | ANALYST    | 7566       | 2021-12-03 | 3000       | NULL       | 20         | 
| 8001       | DUKE       | ENGINEER   | 7788       | 2023-03-15 | 3500       | NULL       | 20         | 
| 8003       | SANDRA     | CONSULTANT | 7698       | 2023-05-10 | 2700       | NULL       | 30         | 
| 8004       | KAREN      | CLERK      | 7902       | 2023-06-11 | 1200       | NULL       | 20         | 
+------------+------------+------------+------------+------------+------------+------------+------------+

Query the emp table to retrieve full information about the highest-paid and lowest-paid employees in each department.

-- Enable schema syntax at the session level.
SET odps.namespace.schema=true;
USE schema default;
WITH ranked AS (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn_desc,
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal ASC)  AS rn_asc
    FROM emp e
)
SELECT *
FROM ranked
WHERE rn_desc = 1 
   OR rn_asc  = 1
ORDER BY deptno, sal DESC;

Click to view the execution result

-- The following result is returned.
+-------+--------+-----------+------+------------+------+------+--------+------------+------------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno | rn_desc    | rn_asc     |
+-------+--------+-----------+------+------------+------+------+--------+------------+------------+
| 7839  | KING   | PRESIDENT | NULL | 2021-11-17 | 5000 | NULL | 10     | 1          | 4          |
| 7934  | MILLER | CLERK     | 7782 | 2022-01-23 | 1300 | NULL | 10     | 4          | 1          |
| 8001  | DUKE   | ENGINEER  | 7788 | 2023-03-15 | 3500 | NULL | 20     | 1          | 8          |
| 7369  | SMITH  | CLERK     | 7902 | 2020-12-17 | 800  | NULL | 20     | 8          | 1          |
| 7698  | BLAKE  | MANAGER   | 7839 | 2021-05-01 | 2850 | NULL | 30     | 1          | 8          |
| 7900  | JAMES  | CLERK     | 7698 | 2021-12-03 | 950  | NULL | 30     | 8          | 1          |
+-------+--------+-----------+------+------------+------+------+--------+------------+------------+

Step 8: Write the analysis results back to DLF

In the external project from the previous step, create a table to store the analysis results.

-- Enable schema syntax at the session level.
SET odps.namespace.schema=true;
use schema default;
CREATE TABLE emp_detail (
    empno    INT,
    ename    VARCHAR(20),
    job      VARCHAR(20),
    mgr      INT,
    hiredate DATE,
    sal      DECIMAL(10,2),
    comm     DECIMAL(10,2),
    deptno   INT
);

Write the analysis results from Step 5 to the new table.

-- Enable schema syntax at the session level.
SET odps.namespace.schema=true;
use schema default;
WITH ranked AS (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn_desc,
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal ASC)  AS rn_asc
    FROM emp e
)
insert into emp_detail 
SELECT 
    empno,ename,job,mgr,
    hiredate,sal,comm,deptno
FROM ranked
WHERE rn_desc = 1 
   OR rn_asc  = 1
ORDER BY deptno, sal DESC;

Query the new table.

-- Enable schema syntax at the session level.
SET odps.namespace.schema=true;
use schema default;
SELECT * FROM emp_detail;

Execution result

-- The result is as follows.
+------------+------------+------------+------------+------------+------------+------------+------------+
| empno      | ename      | job        | mgr        | hiredate   | sal        | comm       | deptno     | 
+------------+------------+------------+------------+------------+------------+------------+------------+
| 7839       | KING       | PRESIDENT  | NULL       | 2021-11-17 | 5000       | NULL       | 10         | 
| 7934       | MILLER     | CLERK      | 7782       | 2022-01-23 | 1300       | NULL       | 10         | 
| 8001       | DUKE       | ENGINEER   | 7788       | 2023-03-15 | 3500       | NULL       | 20         | 
| 7369       | SMITH      | CLERK      | 7902       | 2020-12-17 | 800        | NULL       | 20         | 
| 7698       | BLAKE      | MANAGER    | 7839       | 2021-05-01 | 2850       | NULL       | 30         | 
| 7900       | JAMES      | CLERK      | 7698       | 2021-12-03 | 950        | NULL       | 30         | 
+------------+------------+------------+------------+------------+------------+------------+------------+

Log on to the Data Lake Formation (DLF) console. In the navigation pane on the left, choose Catalog List. The newly created emp_detail table appears in the list.

MaxCompute:Access Paimon data with DLF and external projects

Prerequisites

Procedure