Zero-ETL - ApsaraDB RDS - Alibaba Cloud Documentation Center

ApsaraDB RDS provides the Zero-ETL feature to synchronize data to an AnalyticDB for PostgreSQL data warehouse. This eliminates the need to build and maintain a data pipeline. The data pipeline is free, reducing both data transfer and operational costs.

Supported regions

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

Note

Support for more regions is being rolled out. For the latest information, refer to 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 data pipelines that perform ETL operations. Simply select the source data and the destination instance to automatically create a real-time data pipeline. This simplifies data pipeline management, allowing you to focus on application development.
Zero cost: The Zero-ETL data pipeline is free. You can analyze upstream data in the data warehouse at no additional cost.
Multi-source aggregation: You can use Zero-ETL pipelines to synchronize data from multiple instances to a single AnalyticDB for PostgreSQL instance in real time to build a global analytics view.

Supported integrations

ApsaraDB RDS for PostgreSQL to AnalyticDB for PostgreSQL

Billing

The Zero-ETL data pipeline is free of charge.

Prerequisites

An AnalyticDB for PostgreSQL instance is created. For more information, see Create an AnalyticDB for PostgreSQL instance.
The ApsaraDB RDS for PostgreSQL instance and the AnalyticDB for PostgreSQL instance must be in the same region.
A database is created in the AnalyticDB for PostgreSQL instance to receive data. For more information, see Manage databases in an AnalyticDB for PostgreSQL instance.
You have a privileged account for the ApsaraDB RDS for PostgreSQL instance. This account must be set as the owner of the destination database. For more information, see Create an account for an ApsaraDB RDS for PostgreSQL instance.
You have a database account for the AnalyticDB for PostgreSQL instance. For more information, see Create a database account for an AnalyticDB for PostgreSQL instance.
Note
You can also use the initial account or an account with SUPERUSER permissions.
The wal_level parameter of the ApsaraDB RDS for PostgreSQL instance is set to logical. For more information about how to modify parameters, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
Logical Replication Slot Failover is enabled for the ApsaraDB RDS for PostgreSQL instance. This feature is enabled by default. To check whether the feature is enabled, refer to Logical Replication Slot Failover.
Note
After you enable this feature, the data pipeline runs as expected, and logical subscriptions are not interrupted during a primary/secondary failover.

Preparations

Create a service-linked role

Go to the RAM console's Roles list.
Check whether a service-linked role named AliyunServiceRoleForADBPG exists. If not, proceed to the next step to create the role.
Note
You must create the service-linked role before you create a Zero-ETL data pipeline.
Go to the service-linked role creation page, and set Select Service to AliyunServiceRoleForADBPG.
Click Create Service-linked Role.
Return to the Roles page and make sure that the service-linked role is created.

Grant permissions to a RAM user

A RAM user needs the following two types of permissions to create and manage Zero-ETL pipelines.

Permission 1: Manage the destination instance

A RAM user must have the AliyunGPDBFullAccess administrative permission (permission to fully manage AnalyticDB PostgreSQL) for the target instance to create and manage its Zero-ETL links. For detailed steps, see Manage RAM user permissions.

Permission 2: Manage Zero-ETL

You can grant these permissions by creating a custom policy to create data pipelines for all or specific instances, and then attaching the policy to the RAM user. For more information about how to create and attach a custom policy, see Create a custom policy.

The following code provides sample 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",
                "dts:DescribeConciseJobStatics",
                "dts:ListUserAuthorizationLogs",
                "dts:CreateUserAuthorization"
            ],
            "Resource": [
                "acs:dts:*:*:*"
            ]
        }
    ]
}

Specific instances

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:gpdb:*:*:dbcluster/gp-bp13e375cd8x2****",
                "acs:rds:*:*:dbinstance/pgm-2zeyjzi91g53****"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig",
                "dts:DescribeConciseJobStatics",
                "dts:ListUserAuthorizationLogs",
                "dts:CreateUserAuthorization"
            ],
            "Resource": "acs:dts:*:*:*"
        }
    ]
}

Procedure

Log in to the RDS Management Console, and in the left-side navigation pane, click Data Integration.
On the Data Integration tab, click Create Zero-ETL Task.

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

Configure the source database:

Source	Description
Task Name	The name of the Zero-ETL task.
Database Engine	Select RDS for PostgreSQL.
Access Method	Only Alibaba Cloud Instance are supported.
Instance Region	The region of the source instance.
Instance ID	The ID of the ApsaraDB RDS for PostgreSQL instance.
Database Name	The name of the database in the ApsaraDB RDS for PostgreSQL instance.
Database account	The database account for the ApsaraDB RDS for PostgreSQL instance.
Database Password	The password for the database account for the ApsaraDB RDS for PostgreSQL instance.
Connection Method	The connection encryption method for the ApsaraDB RDS for PostgreSQL instance.

Configure the destination database:

Destination	Description
Database Type	The destination database only supports AnalyticDB for PostgreSQL.
Access Method	Only supports Alibaba Cloud Instance connection.
Instance Region	The region of the destination instance.
Instance ID	The ID of the destination instance.
Database Name	The name of the database in the destination instance to receive data.
Database account	The database account for the destination instance.
Database Password	The password for the database account.

After you configure the preceding parameters, click Test Connectivity and Proceed to go to the Configure Zero-ETL page and configure the following parameters:

Parameter	Description
DDL and DML Operations to Be Synchronized	Select the DML operations (insert, update, and delete) and DDL operations (create, alter, drop, rename, and truncate) that you want to synchronize. By default, all operations are selected.
Source Objects and Selected Objects	The objects in the source database and the objects selected for synchronization.
Advanced Settings (Optional)	The retry interval for connectivity issues, such as when a database is unreachable.

After you configure the preceding parameters, select the Zero-ETL O&M-free Description checkbox, and then click Next: Configure Database and Table Fields. The configuration information is as follows:

Field mapping	Description
Database Name	Select an existing database.
Table Name	Select an existing table.
Primary Key Column	The primary key column in the selected table.
Distribution key	The distribution key column in the selected table.
Type	The table type. Valid values: partitioned table and replicated table.
Definition Status	The status changes from Undefined to Defined after you complete the configuration.

After you configure all the preceding parameters, click Next: Save Task Settings and Precheck.
After the precheck passes, click Start to start the Zero-ETL task.

Note

On the Data Integration page, you can click the Source type tab to view information about the target Zero-ETL task, such as its ID/Name, Source/Destination, and Instance Status.