Zero-ETL

Overview

Enterprises often distribute business data across various systems and platforms. To manage and use this data effectively, they typically rely on ETL tools for centralized management.

ETL is the process of extracting, transforming, and loading data from upstream business systems into a data warehouse. The goal is to consolidate scattered upstream data into a destination data warehouse for further computation and analysis to support effective business decisions.

Traditional ETL processes often have the following drawbacks:

• Increased resource costs: Different data sources may require different ETL tools, and building the required pipelines incurs additional costs.

• Increased system complexity: Maintaining ETL tools increases operational complexity and diverts focus from application development.

• Reduced data freshness: Some ETL processes involve periodic batch updates and cannot generate analysis results quickly enough for near-real-time applications.

To address these challenges, Alibaba Cloud offers the Zero-ETL feature. You can use this feature to quickly build a data synchronization pipeline between an online transaction processing (OLTP) system and an online analytical processing (OLAP) data warehouse. Zero-ETL automatically extracts, transforms, cleanses, and loads data from the OLTP system into the OLAP data warehouse. This enables one-stop data synchronization and management, integrates transaction processing with data analysis, and allows you to focus on your data analytics business.

AnalyticDB for PostgreSQL supports only the following Zero-ETL data synchronization pipelines:

• PolarDB for PostgreSQL to AnalyticDB for PostgreSQL.

• PolarDB for MySQL to AnalyticDB for PostgreSQL.

• ApsaraDB RDS for PostgreSQL to AnalyticDB for PostgreSQL.

• ApsaraDB RDS for MySQL to AnalyticDB for PostgreSQL.

Benefits

• Easy to use: Avoid creating and maintaining complex data pipelines for extract, transform, and load (ETL) operations. Simply select the source data and the destination instance to automatically create a real-time data synchronization pipeline. This reduces the challenges of building and managing data pipelines and allows you to focus on developing your applications.

• Zero cost: The data synchronization pipelines are free of charge. You can analyze upstream data in AnalyticDB for PostgreSQL at no extra cost.

• Multi-source aggregation: Synchronize data in real time from multiple source instances to a single AnalyticDB for PostgreSQL instance to build a global analytical view.

Notes

Notes on data synchronization pipelines:

• For notes on pipelines from PolarDB for PostgreSQL, see Synchronize data from PolarDB for PostgreSQL to AnalyticDB for PostgreSQL.

• For notes on pipelines from PolarDB for MySQL, see Synchronize data from PolarDB for MySQL to AnalyticDB for PostgreSQL.

• For notes on pipelines from ApsaraDB RDS for PostgreSQL, see Synchronize data from ApsaraDB RDS for PostgreSQL to AnalyticDB for PostgreSQL.

• For notes on pipelines from ApsaraDB RDS for MySQL, see Synchronize data from ApsaraDB RDS for MySQL to AnalyticDB for PostgreSQL.

Supported regions

The Zero-ETL feature is available in the following regions: China (Shenzhen), China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Qingdao), China (Guangzhou), Shanghai Finance, China (Chengdu), China (Hong Kong), Malaysia (Kuala Lumpur), US (Virginia), US (Silicon Valley), Germany (Frankfurt), Indonesia (Jakarta), Japan (Tokyo), and Singapore.

Billing

The Zero-ETL feature is free of charge. You are charged only for the ApsaraDB RDS for PostgreSQL, PolarDB for MySQL, PolarDB for PostgreSQL, ApsaraDB RDS for MySQL, and AnalyticDB for PostgreSQL instances.

Prerequisites

• The required instances are created:

  • An AnalyticDB for PostgreSQL instance is created. For more information, see Create an instance.

  • An ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for PostgreSQL, or PolarDB for MySQL instance is created. For more information, see Create an ApsaraDB RDS for PostgreSQL instance, Create an RDS MySQL intance, Create a PolarDB for PostgreSQL cluster, and Create a PolarDB for MySQL cluster.

• The wal_level parameter of the ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, or PolarDB for PostgreSQL source instance is set to logical. For more information, see Synchronize data from ApsaraDB RDS for PostgreSQL to AnalyticDB for PostgreSQL.

• The destination AnalyticDB for PostgreSQL instance must have more available storage space than is used by the source ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, or PolarDB for PostgreSQL instance.

Preparations

Create an AnalyticDB for PostgreSQL service-linked role

1. Go to the Role page of the RAM console.

2. If the service-linked role named AliyunServiceRoleForADBPG does not exist in the role list, create it.

3. Click Create Role.

4. In the Create Role dialog box, select Alibaba Cloud Service and click Next.

5. Select Service-Linked Role for Role Type and select AnalyticDB for PostgreSQL for Cloud Service.

6. Click Finish. Return to the role list and make sure that the service-linked role is created.

Grant management permissions to a RAM user

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

Grant permissions to manage AnalyticDB for PostgreSQL

The RAM user must have the permissions to manage AnalyticDB for PostgreSQL (AliyunGPDBFullAccess) to create and manage Zero-ETL pipelines for AnalyticDB for PostgreSQL. For more information, see Grant permissions to a RAM user.

Grant permissions to manage Zero-ETL

To create and manage Zero-ETL pipelines, a RAM user must have permissions to create data synchronization pipelines for both the source and destination (AnalyticDB for PostgreSQL) instances. You can create a custom policy to grant the RAM user permissions to create pipelines for all or specified source and AnalyticDB for PostgreSQL instances, and then attach the custom policy to the RAM user.

1. To grant permissions on all ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB for PostgreSQL, and AnalyticDB for PostgreSQL instances, use the following policy:

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

2. To grant permissions on specified ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB for PostgreSQL, and AnalyticDB for PostgreSQL instances, use the following policy:

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

Create a custom policy in the RAM console. For more information, see Create a custom policy.

Procedure

1. Log on to the AnalyticDB for PostgreSQL console.

2. In the upper-left corner of the console, select the region where your instance is located.

3. Find the destination instance and click its ID.

4. In the left-side navigation pane, choose Zero-ETL and then click Create Zero-ETL Task in the upper-left corner.

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

   1. Configure the source database parameters:

      Parameter	Description	Example
      Task name	The name of the Zero-ETL task.	Zeroetltest
      Database type	The supported source database types are ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, and PolarDB for PostgreSQL.	ApsaraDB RDS for PostgreSQL
      Access method	Only Alibaba Cloud instances are supported.	Cloud Instance
      instance region	The region where the source instance is located.	China (Shanghai)
      Instance ID	The ID of the source instance.	pgm-2ze****
      Database name	The name of the database in the source instance.	testdb
      Database account	The account for the database in the source instance.	test
      Database password	The password for the database account in the source instance.	password****
      Connection method	SSL-encrypted or unencrypted connection. Note An SSL connection adds a layer of certificate-based encryption. Data is encrypted during transmission, so its content remains unreadable even if intercepted at the network layer.	SSL-encrypted connection

   2. Configure the destination database parameters:

      Parameter	Description	Example
      Database type	Currently, only AnalyticDB for PostgreSQL is supported as the destination.	AnalyticDB for PostgreSQL
      Access method	Only Alibaba Cloud instances are supported.	Cloud Instance
      Instance region	The region of the destination AnalyticDB for PostgreSQL instance.	China (Shanghai)
      Instance ID	The ID of the AnalyticDB for PostgreSQL instance.	gp-2zeod****
      Database name	The name of the database in the AnalyticDB for PostgreSQL instance.	testdb
      Database account	The account for the database in the AnalyticDB for PostgreSQL instance.	test
      Database password	The password for the database account in the AnalyticDB for PostgreSQL instance.	password****

6. Test the connection to proceed. Configure the following parameters:

   Parameter	Description	Example
   DDL and DML operations to synchronize	Select the DML operations (insert, update, and delete) to synchronize. Select the DDL operations (create, alter, drop, rename, and truncate) to synchronize.	DML operations: insert, update, and delete. DDL operations: create, alter, drop, rename, and truncate.
   Source objects and selected objects	The source and synchronized objects in the database.	public
   Advanced settings (optional)	Set the retry interval for connection failures and other issues.	Retry interval for connection issues: 10 minutes. Retry interval for other issues: 10 minutes.

7. Configure the database and table fields.

   Parameter	Description	Example
   Database name	Select an existing database.	public
   Table name	Select an existing table.	table1
   Primary key field	The primary key field in the selected table.	id
   Distribution key	One or more fields in a table that determine the storage location of data in a distributed environment.	id
   Type	The data distribution type.	Hash-distributed table
   Definition status	After the database and table field settings are defined, the status changes from Undefined to Defined.	Defined

8. After you configure all resources, click Next: Save Task Settings and Precheck.

9. After the precheck passes, the Zero-ETL task starts automatically. You can view the task progress in the Task List.

Monitor and configure alerts for Zero-ETL

After you create and start a Zero-ETL task, you can set alert rules in the Cloud Monitor console to monitor the task status in real time. Follow these steps: