Use the zero-ETL feature to synchronize ApsaraDB for MongoDB data in real time - AnalyticDB

Overview

In the big data era, enterprises have business data scattered across different systems and platforms. To effectively manage and utilize this data, enterprises often rely on ETL tools for centralized data management.

ETL is the process of extracting, transforming (cleaning), and loading data from upstream business systems into a data warehouse. The purpose is to integrate scattered data from upstream sources into the target data warehouse for further computation and analysis to support effective business decisions.

Traditional ETL processes usually face the following challenges:

Increased resource costs: Different data sources may require different ETL tools, and building ETL pipelines incurs additional resource costs.
Increased system complexity: Users need to maintain ETL tools on their own, which increases O&M difficulty and prevents them from focusing on business application development.
Reduced data timeliness: Some ETL processes involve periodic batch updates, which cannot produce analysis results quickly in near-real-time application scenarios.

To address these issues, Alibaba Cloud Database provides the zero-ETL feature. This feature allows you to quickly build data synchronization pipelines between business systems (OLTP) and data warehouses (OLAP). Data from business systems (OLTP) is automatically extracted, transformed, cleaned, and loaded into data warehouses (OLAP). This way, you can complete data synchronization and management in a one-stop manner, integrate transaction processing with data analysis, and help customers focus on data analysis business.

Benefits

Easy to use: You do not need to create and maintain complex data pipelines that perform ETL (extract, transform, and load) operations. You only need to select the source data and the destination instance to automatically create real-time data synchronization pipelines. This reduces the challenges of building and managing data pipelines and allows you to focus on upper-layer application development.
Zero cost: Zero-ETL pipelines do not incur additional fees. You can analyze upstream data in AnalyticDB for MySQL for free.
Multi-source convergence: You can use Zero-ETL pipelines to synchronize data from multiple instances to an AnalyticDB for MySQL cluster in real time to build a global analysis perspective.

Supported data synchronization tasks

Synchronization paths from RDS for MySQL to AnalyticDB for MySQL. For more information, see Use zero-ETL to synchronize data.
Synchronization path from DTS to AnalyticDB for MySQL.
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.

Usage notes

The zero-ETL feature is available 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).
When the number of Zero-ETL pipelines for an AnalyticDB for MySQL cluster exceeds the limit, you cannot create new Zero-ETL pipelines. You can use DTS to create new synchronization pipelines, or delete unused Zero-ETL pipelines to create new ones. The Zero-ETL pipeline limits are as follows:
- If the total number of ACUs for reserved resources in the cluster is less than 24 ACUs, you can create 1 Zero-ETL pipeline.
- If the total number of ACUs for reserved resources in the cluster is greater than or equal to 24 ACUs, you can create 3+3*[(Total ACUs-24)/50] Zero-ETL pipelines.
  
  The result of [(Total ACUs-24)/50] is rounded down. For example, if the total number of ACUs is 48, the result is 0.48, which is rounded down to 0. In this case, you can create 3 Zero-ETL pipelines.
Note
- For the Lakehouse Edition, the total number of ACUs for reserved resources is the sum of ACUs for reserved compute resources and reserved storage resources.
- For the Data Warehouse Edition, 1 core equals 1 ACU. For elastic mode, the total number of ACUs for reserved resources is the sum of cores for compute resources and elastic I/O resources. For reserved mode, the total number of ACUs for reserved resources is the number of cores for compute resources.

Preparations

Create the AnalyticDB for MySQL service-linked role

Go to the Roles list in the RAM console.
Check whether the service-linked role AliyunServiceRoleForAnalyticDBForMySQL already exists in the role list. If not, create the role.
Click Create Role.
In the Create Role dialog box, select Alibaba Cloud Service and click Next.
Set the role type to Alibaba Cloud Service and select AnalyticDB for MySQL.
Click Complete. Return to the role list and verify that the service-linked role is created.

Grant management permissions to a RAM user

RAM users need the following two types of permissions to create and manage Zero-ETL pipelines.

A RAM user must have the management permissions (AliyunADBFullAccess) on AnalyticDB for MySQL to create and manage Zero-ETL pipelines. For more information, see Grant permissions to a RAM user.

The following section describes the scripts of custom policies:

Grant permissions on all ApsaraDB for MongoDB instances and AnalyticDB for MySQL clusters

{
    "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:*:*:*"
            ]
        }
    ]
}

Grant permissions on a specific ApsaraDB for MongoDB instance and a specific AnalyticDB for MySQL cluster

{
    "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

Log on to the AnalyticDB for MySQL console. In the upper-left corner, select the region where the cluster resides. In the left-side navigation pane, click Clusters, and then click the ID of the target cluster.
In the left-side navigation pane, choose Data Transmission Service > Free Data Sync.

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.

Configure the destination database:

Destination Database	Description
Database Type	The destination database supports only AnalyticDB MySQL 3.0.
Access Method	Only Alibaba Cloud Instance is supported.
Instance Region	The region where the AnalyticDB for MySQL cluster resides.
Instance ID	The ID of the AnalyticDB for MySQL cluster.
Database Account	The database account of the AnalyticDB for MySQL cluster.
Database Password	The password of the database account of the AnalyticDB for MySQL cluster.

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.

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

On the Free Data Sync page, you can view the Name, Source/Target, Status, and other information of the target Zero-ETL task.

Zero-ETL task monitoring and alerting

After you create and start a Zero-ETL task, you can set alert rules for the Zero-ETL task in the CloudMonitor console to monitor the running status of the Zero-ETL task in real time. Perform the following steps:

Log on to the CloudMonitor console.
View monitoring information.
1. In the left-side navigation pane, choose Cloud Resource Monitoring > Cloud Service Monitoring.
2. Move the pointer over the AnalyticDB for MySQL card and click AnalyticDB for MySQL 3.0 - ZeroETL Latency.
3. On the Clusters page, click a cluster ID to view the monitoring information of Zero-ETL tasks under the cluster.
Create an alert rule.
1. In the left-side navigation pane, choose Alert Service > Alert Rules.
2. Click Create Alert Rule. In the Create Alert Rule panel, configure the alert rule for the Zero-ETL task. For more information, see Create a threshold-triggered alert rule.
  
  Note
  Set Product to AnalyticDB for MySQL 3.0 - ZeroETL Latency.
Create a subscription.
1. In the left-side navigation pane, choose Event Center > Event Subscriptions.
2. Click Create Subscription. On the Create Subscription page, configure the subscription for the Zero-ETL task. For more information, see Create event subscriptions.
  Note
  - Product: Select AnalyticDB for MySQL 3.0.
  - Event Type: Select Exception or Restore.
  - Event Name: Select ZeroETL Task Exception or ZeroETL Task Recovery.