AnalyticDB:Use zero-ETL to synchronize data

Overview

In the era of big data, enterprises must use extract, transform, load (ETL) tools to efficiently manage and use large amounts of business data distributed across different systems and platforms.

An ETL tool extracts data from an upper-level business system, transforms the data, and then loads the data to data warehouses. This process incorporates distributed data into data warehouses for further computing, analysis, and business decision-making.

Traditional ETL processes encounter the following challenges:

• Increased resource costs: Different data sources may require different ETL tools, and you are charged additional fees for creating ETL tasks.

• Increased system complexity: The maintenance of ETL tools increases O&M difficulty and prevents you from focusing on business application development.

• Reduced data timeliness: Specific ETL processes involve periodic batch updates. In near-real-time scenarios, analysis results cannot be quickly generated.

To resolve the preceding issues, Alibaba Cloud ApsaraDB provides the zero-ETL feature that allows you to create data synchronization tasks between online transaction processing (OLTP) and online analytical processing (OLAP) systems. The zero-ETL feature extracts data from OLTP systems, transforms the data, and then loads the data to OLAP systems to help you synchronize and manage data in an end-to-end manner, integrate transaction processing with data analysis, and focus on data analysis.

Benefits

• Ease of use: You do not need to create or maintain complex data pipelines to perform ETL operations. You need to only select the source and destination instances or clusters to create real-time data synchronization tasks. This reduces the challenges of building and managing data pipelines and allows you to focus on application development.

• Zero costs: You are not charged additional fees for zero-ETL tasks. The zero-ETL feature allows you to analyze the data synchronized to AnalyticDB for MySQL free of charge.

• Multi-source aggregation: The zero-ETL feature allows you to synchronize data in real time from multiple source instances or clusters to an AnalyticDB for MySQL cluster for global analysis.

Supported data synchronization tasks

• Data synchronization tasks from ApsaraDB RDS for MySQL to AnalyticDB for MySQL. For more information, see Use zero-ETL to synchronize data.

• Data synchronization tasks from PolarDB for MySQL to AnalyticDB for MySQL. For more information, see Use zero-ETL to synchronize data.

• Data synchronization tasks from ApsaraDB for MongoDB to AnalyticDB for MySQL.

Prerequisites

• An AnalyticDB for MySQL cluster and an ApsaraDB for MongoDB instance are created in the same region.

• Database accounts are created for the AnalyticDB for MySQL cluster and the ApsaraDB for MongoDB instance.

Billing rules

You are not charged for data synchronization tasks.

Usage notes

• The zero-ETL feature is supported only in the following regions: China (Beijing), China (Hangzhou), China (Shanghai), China (Shenzhen), China (Zhangjiakou), China (Qingdao), China (Guangzhou), China (Hong Kong), Singapore, US (Silicon Valley), and US (Virginia).

• If the number of zero-ETL tasks created for an AnalyticDB for MySQL cluster reaches the upper limit, you cannot create zero-ETL tasks in the AnalyticDB for MySQL console. You can create additional data synchronization tasks in the DTS console or delete zero-ETL tasks that you no longer need in the AnalyticDB for MySQL console. The following limits are imposed on the maximum number of zero-ETL tasks that you can create for an AnalyticDB for MySQL cluster in the AnalyticDB for MySQL console:

  • If the total amount of reserved resources in an AnalyticDB for MySQL cluster is less than 24 AnalyticDB compute units (ACUs), you can create only one zero-ETL task.

  • If the total amount of reserved resources in an AnalyticDB for MySQL cluster is greater than or equal to 24 ACUs, the maximum number of zero-ETL tasks that you can create is calculated by using the following formula: 3 + 3 × [(Total number of ACUs - 24)/50].

    In the formula, the result of [(Total number of ACUs - 24)/50] must be rounded down. For example, if the total number of ACUs in a cluster is 48, the result of [(Total number of ACUs - 24)/50] is 0 after you round down the original result 0.48. In this case, up to three zero-ETL tasks can be created.

  Note

  • The total number of ACUs for reserved resources of a Data Lakehouse Edition cluster is the sum of the ACUs for reserved computing resources and reserved storage resources.

  • For Data Warehouse Edition clusters, each ACU is equal to 1 core. The total number of ACUs for reserved resources of a Data Warehouse Edition cluster in elastic mode is the sum of computing resource cores and elastic I/O resource cores. The total number of ACUs for reserved resources of a Data Warehouse Edition cluster in reserved mode is the number of computing resource cores.

Preparations

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

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "dts:*",
            "Resource": [
                "acs:adb:*:*:dbcluster/am-2zeod8ax4b9a****",
                "acs:dds:*:*:dbinstance/dds-t4n8aaa4dcdb****"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "dts:DescribeRegions",
                "dts:DescribeConfigRelations",
                "dts:DescribeSrcLinkConfig",
                "dts:DescribeDestLinkConfig",
                "dts:DescribeLinkConfig"
            ],
            "Resource": "acs:dts:*:*:*"
        }
    ]
}

Procedure

1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.

2. Go to the zero-ETL configuration page based on the cluster edition.

   • For an Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster: In the left-side navigation pane, choose Data Ingestion > Database Data Synchronization.

   • For a Data Warehouse Edition cluster: In the left-side navigation pane, choose Data Transmission Service > Zero-ETL.

3. Click Create Zero-ETL Task. In the Configure Source and Destination Databases step of the Create Zero-ETL Task page, configure the parameters of the source instance and the destination cluster.

   • The following table describes the parameters of the source instance.

     Parameter	Description
     Task Name	The name of the zero-ETL task.
     Database Type	The database engine of the source instance. Select MongoDB.
     Access Method	The access method of the source instance. The value is automatically set to Alibaba Cloud Instance.
     Instance Region	The region in which the ApsaraDB for MongoDB instance resides.
     Architecture	The architecture of the ApsaraDB for MongoDB instance. Only ReplicaSet is supported.
     Migration Method	The method that you want to use to perform incremental data synchronization. Valid values: Oplog (recommended): This option is available if the oplog feature is enabled for the source instance. Note Oplog is enabled by default for self-managed MongoDB databases and ApsaraDB for MongoDB instances. If you use this method to synchronize incremental data, the latency of incremental synchronization tasks is low because logs are pulled at a high speed. Therefore, we recommend that you select Oplog. ChangeStream: This option is available if change streams are enabled for the source instance. Note If the source instance is Amazon DocumentDB (non-elastic cluster), you can select only ChangeStream.
     Instance ID	The ID of the ApsaraDB for MongoDB instance.
     Authentication Database	The name of the database to which the database account of the ApsaraDB for MongoDB instance belongs. If you have not modified the database, admin is used.
     Database Account	The name of the database account of the ApsaraDB for MongoDB instance.
     Database Password	The password of the database account of the ApsaraDB for MongoDB instance.
     Encryption	The encryption method that is used to connect to the ApsaraDB for MongoDB instance. Select Non-encrypted or SSL-encrypted. Note If you select SSL-encrypted, you must first enable the SSL encryption feature for the ApsaraDB for MongoDB instance.

   • The following table describes the parameters of the destination cluster.

     Parameter	Description
     Database Type	The database engine of the destination cluster. The value is automatically set to AnalyticDB for MySQL 3.0.
     Access Method	The access method of the destination cluster. The value is automatically set to Alibaba Cloud Instance.
     Instance Region	The region in which the AnalyticDB for MySQL cluster resides.
     Instance ID	The ID of the AnalyticDB for MySQL cluster.
     Database Account	The name of the database account of the AnalyticDB for MySQL cluster.
     Database Password	The password of the database account of the AnalyticDB for MySQL cluster.

4. After you configure the preceding parameters, click Test Connectivity and Proceed. In the Configure Zero-ETL step, configure the parameters that are described in the following table.

   Parameter	Description
   DDL and DML Operations to Be Synchronized	The DML operations (INSERT, UPDATE, and DELETE) that you want to synchronize. By default, all operations are selected.
   Source Objects and Selected Objects	The source objects and the objects that you want to synchronize.
   Advanced Settings (Optional)	The retry time for failed connections between the source and destination instances and clusters and the retry time for other issues that occur on the source and destination instances and clusters.

5. After you configure the preceding parameters, click Next: Save Task Settings and Precheck.

6. If the precheck is successful, click Start to start the zero-ETL task.

   You can view the Name, Source/Destination, and Status parameters of the zero-ETL task on the zero-ETL configuration page.

Monitoring and alerting of zero-ETL tasks

After you create and start a zero-ETL task, you can configure alert rules for the task and monitor the running status of the task in the CloudMonitor console. Perform the following steps: