Zero-ETL - ApsaraDB RDS - Alibaba Cloud Documentation Center

ApsaraDB RDS provides the Zero-ETL feature to synchronize data to a data warehouse, such as AnalyticDB for MySQL, AnalyticDB for PostgreSQL, or ApsaraDB for ClickHouse. This feature eliminates the need to build or maintain synchronization links. This free feature reduces your data transmission and O&M costs.

Supported regions

China (Beijing), China (Hangzhou), China (Shanghai), and China (Shenzhen)

Note

More regions will be supported. For the latest list of supported regions, see the ApsaraDB RDS console.

Overview

In the era of big data, business data is often scattered across different systems and platforms, making it a key challenge to manage and use this data efficiently. Traditionally, enterprises rely on extract-transform-load (ETL) tools to consolidate dispersed data into a data warehouse to support business decisions. However, traditional ETL processes often present the following challenges:

Increased system complexity: Maintaining ETL tools increases the O&M workload and diverts focus from core business development.
Increased resource costs: Different data sources may require different ETL tools, and building ETL links adds to your costs.
Reduced data timeliness: Periodic batch updates in some ETL processes delay analysis, making them unsuitable for near-real-time scenarios.

To address these issues, Alibaba Cloud ApsaraDB provides the Zero-ETL feature to quickly build data synchronization links between online transaction processing (OLTP) and online analytical processing (OLAP) systems. By automating data extraction, transformation, and loading, Zero-ETL integrates transaction processing with data analytics. This helps you focus on your data analytics tasks and improve efficiency.

Benefits

Easy to use: You do not need to create and maintain complex ETL data pipelines. Simply select the source data and destination instance to automatically build a real-time data synchronization link. This simplifies building and managing data pipelines, allowing you to focus on developing your applications.
Zero cost: The Zero-ETL feature is free to use, enabling real-time data analysis and reducing your resource costs.
Multi-source aggregation: You can synchronize data from multiple instances to a single destination data warehouse, such as an AnalyticDB for MySQL cluster, an AnalyticDB for PostgreSQL instance, or an ApsaraDB for ClickHouse instance, in real time to build a global analytics view.
Note
When you synchronize data from multiple instances to a single ApsaraDB for ClickHouse instance, the synchronization objects of different tasks cannot overlap.

Supported integrations

ApsaraDB RDS for MySQL to ApsaraDB for ClickHouse
ApsaraDB RDS for MySQL to AnalyticDB for MySQL 3.0
ApsaraDB RDS for MySQL to AnalyticDB for PostgreSQL

Billing

The Zero-ETL feature is free of charge.

Prerequisites

You have created a source ApsaraDB RDS for MySQL instance.
You have created an ApsaraDB for ClickHouse instance, an AnalyticDB for MySQL Data Warehouse Edition cluster, or an AnalyticDB for PostgreSQL instance in the same region as the source ApsaraDB RDS for MySQL instance.
You have created an ApsaraDB RDS for MySQL account, an ApsaraDB for ClickHouse account, an AnalyticDB for MySQL cluster account, and an AnalyticDB for PostgreSQL database account.

Preparations

Create a service-linked role and grant the required management permissions to a RAM user.

ApsaraDB RDS for MySQL to ClickHouse

Create the AliyunServiceRoleForClickHouseZeroETL service-linked role.
Note
You do not need to create this role manually. The system automatically creates it when you set up the integration.

Grant management permissions to a RAM user.

A RAM user must have the following three sets of permissions to successfully create a Zero-ETL link. For more information, see Create a custom policy.

Permissions on the source ApsaraDB RDS for MySQL instance: AliyunRDSFullAccess.

Permissions on the destination ApsaraDB for ClickHouse instance: Use the following custom policy for ApsaraDB for ClickHouse.

{
    "Version": "1",
    "Statement": [
        {
            "Action": "clickhouse:*",
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": "ram:CreateServiceLinkedRole",
            "Resource": "*",
            "Effect": "Allow",
            "Condition": {
                "StringEquals": {
                    "ram:ServiceName": "clickhouse.aliyuncs.com"
                }
            }
        }
    ]
}

Permissions on Data Transmission Service (DTS): Use the following custom policy for DTS.

{
    "Version": "1",
    "Statement": [
        {
            "Action": "dts:*",
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": "ram:PassRole",
            "Resource": "*",
            "Effect": "Allow",
            "Condition": {
                "StringEquals": {
                    "acs:Service": "dts.aliyuncs.com"
                }
            }
        }
    ]
}

ApsaraDB RDS for MySQL to AnalyticDB for MySQL 3.0

Create the AliyunServiceRoleForAnalyticDBForMySQL service-linked role.
1. Go to the RAM console and click Roles in the left-side navigation pane. Then, check for the AliyunServiceRoleForAnalyticDBForMySQL role. If it does not exist, create it.
2. In the upper-left corner, click Create Role.
3. In the Create Role dialog box, select Alibaba Cloud Service and click Next.
4. For Role Type, select Service Linked Role, and then choose AnalyticDB for MySQL.
5. Click OK. Return to the role list and verify that the service-linked role was created successfully.

Grant management permissions to a RAM user.

You can grant a RAM user permissions on all or specific ApsaraDB RDS for MySQL instances and AnalyticDB for MySQL clusters. For more information, see Create a custom policy.

Use the following custom policies:

All clusters

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:adb:*:*:*",
                "acs:rds:*:*:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig",
                "dts:DescribeConciseJobStatics",
                "dts:ListUserAuthorizationLogs",
                "dts:CreateUserAuthorization"
            ],
            "Resource": [
                "acs:dts:*:*:*"
            ]
        }
    ]
}

Specific cluster

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:adb:*:*:dbcluster/am-2zeod8ax4b9a****", // Replace with the ID of the AnalyticDB for MySQL cluster to synchronize.
                "acs:rds:*:*:dbinstance/rm-2ze6fs8ouh43****" // Replace with the ID of the ApsaraDB RDS for MySQL instance to synchronize.
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig",
                "dts:DescribeConciseJobStatics",
                "dts:ListUserAuthorizationLogs",
                "dts:CreateUserAuthorization"
            ],
            "Resource": "acs:dts:*:*:*"
        }
    ]
}

ApsaraDB RDS for MySQL to AnalyticDB for PostgreSQL

Create the AliyunServiceRoleForADBPG service-linked role.
1. Go to the RAM console and click Roles in the left-side navigation pane. Then, check for the AliyunServiceRoleForADBPG role. If it does not exist, create it.
2. In the upper-left corner, click Create Role.
3. In the Create Role dialog box, select Alibaba Cloud Service and click Next.
4. For Role Type, select Service Linked Role, and then choose AnalyticDB for PostgreSQL.
5. Click OK. Return to the role list and verify that the service-linked role was created successfully.

Grant management permissions to a RAM user.

You can grant a RAM user permissions on all or specific ApsaraDB RDS for MySQL instances and AnalyticDB for PostgreSQL instances. For more information, see Create a custom policy.

Use the following custom policies:

All instances

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:gpdb:*:*:*",
                "acs:rds:*:*:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig"
            ],
            "Resource": [
                "acs:dts:*:*:*"
            ]
        }
    ]
}

Specific instance

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:gpdb:*:*:dbinstance/gp-bp1a740l3zx4****", // Replace with the ID of the AnalyticDB for PostgreSQL instance to synchronize.
                "acs:rds:*:*:dbinstance/rm-2ze6fs8ouh43****"  // Replace with the ID of the ApsaraDB RDS for MySQL instance to synchronize.
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig"
            ],
            "Resource": "acs:dts:*:*:*"
        }
    ]
}

Procedure

Log on to the ApsaraDB RDS console. In the left-side navigation pane, click Data Integration.
On the Zero-ETL tab, click Create Zero-ETL Task.

On the Create Zero-ETL Task page, configure the source and destination database information.

Configure the source database information:

Parameter	Description
Task name	The name of the Zero-ETL task.
Database type	Select ApsaraDB RDS for MySQL.
Access method	Only Alibaba Cloud Instance is supported.
Instance region	The region of the source instance.
Instance ID	The ID of the ApsaraDB RDS for MySQL instance.
Database name	The name of the database in the ApsaraDB RDS for MySQL instance.
Database account	The database account for the ApsaraDB RDS for MySQL instance.
Database password	The password for the database account of the ApsaraDB RDS for MySQL instance.
Connection method	The connection method for the source instance. Valid values: Non-encrypted Connection and SSL-encrypted connection. Note If you select SSL-encrypted connection, you must first enable SSL encryption for the instance by using a cloud-native certificate.

Configure the destination database information:

Parameter	Description
Database type	The destination database type. Supported types include AnalyticDB for MySQL 3.0, AnalyticDB for PostgreSQL, and ApsaraDB for ClickHouse.
Access method	Only Alibaba Cloud Instance is supported.
Instance region	The region of the destination instance.
Instance ID	The ID of the destination instance.
Database account	The database account for the destination instance.
Database password	The password for the database account of the destination instance.

After you configure the parameters, click Test Connectivity and Proceed. On the page that appears, configure the following parameters:

Parameter	Description
Synchronization Type	Enables real-time data synchronization between data sources. The synchronization types are Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. Note Incremental Data Synchronization is selected by default for synchronization tasks to ensure that changes in the source database are synchronized to the destination database in real time. If you do not require incremental synchronization, use Data Migration.
Instance-level DDL and DML operations	Select the DML operations (INSERT, UPDATE, and DELETE) and DDL operations (CREATE, ALTER, DROP, RENAME, and TRUNCATE) to synchronize. All operations are selected by default.
Source objects and selected objects	Displays the source objects and allows you to select which objects to synchronize.
Advanced settings (optional)	Configure retry intervals for connection failures and other database issues.

After you configure the parameters, click Next: Configure Database and Table Fields. Configure the following information:

Parameter	Description
Database name	Select an existing database.
Table name	Select an existing data table.
Primary Key Column	The primary key column of the selected table.
Distribution Key	The distribution key of the selected table.
Type	The table type, which can be partitioned or replicated.
Definition status	Indicates whether table and field settings are configured. The status changes from Undefined to Defined after configuration.

After you configure all the parameters, click Next: Save Task Settings and Precheck.
If the precheck is successful, click Start to launch the task.

Note

On the Free Data Synchronization page, you can view information about the Zero-ETL task, such as its Name, Source/destination, and Status.