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.
Data synchronization tasks from PolarDB for MySQL to AnalyticDB for MySQL. For more information, see Use zero-ETL to synchronize data.
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
Create a service-linked role for AnalyticDB for MySQL
Log on to the Resource Access Management (RAM) console. In the left-side navigation pane, choose Identities > Roles.
Check whether a service-linked role named AliyunServiceRoleForAnalyticDBForMySQL exists in the role list. If the service-linked role does not exist, create the role.
In the upper-left corner of the Roles page, click Create Role.
In the Select Role Type step, select Alibaba Cloud Service and click Next.
In the Configure Role step, select Service Linked Role for the Role Type parameter and select AnalyticDB for MySQL from the Select Service drop-down list.
Click OK. Return to the Roles page and check whether the service-linked role is created.
Grant management permissions to a RAM user
To allow a RAM user to create and manage zero-ETL tasks, you must perform the following operations:
Grant the RAM user the permissions to manage AnalyticDB for MySQL
Before a RAM user can create and manage zero-ETL tasks of AnalyticDB for MySQL, you must attach the AliyunADBFullAccess policy of AnalyticDB for MySQL to the RAM user. For more information, see Grant permissions to a RAM user.
Grant the RAM user the permissions to manage the zero-ETL feature
Before a RAM user can create and manage zero-ETL tasks, the RAM user must have the permissions to create data synchronization tasks between the data source and AnalyticDB for MySQL. You can configure a custom policy that allows RAM users to create data synchronization tasks for all instances or clusters of the data source and AnalyticDB for MySQL. You can also configure a custom policy that allows RAM users to create data synchronization tasks for specific instances or clusters. Then, you can attach the custom policy to the RAM user. For more information, see Create custom policies.
The following section describes the scripts of custom policies:
Grant permissions on all ApsaraDB RDS for MySQL instances and AnalyticDB for MySQL clusters
Grant permissions on a specific ApsaraDB RDS for MySQL instance and a specific AnalyticDB for MySQL cluster
{
"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"
],
"Resource": [
"acs:dts:*:*:*"
]
}
]
}
{
"Version": "1",
"Statement": [
{
"Effect": "Allow",
"Action": "dts:*",
"Resource": [
"acs:adb:*:*:dbcluster/am-2zeod8ax4b9a****",
"acs:rds:*:*:dbinstance/rm-2ze6fs8ouh43****",
]
},
{
"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 of the console, select a region. In the left-side navigation pane, click Clusters. On the Clusters page, click an edition tab. Find the cluster that you want to manage and click the cluster ID.
Go to the zero-ETL configuration page based on the cluster edition.
For a Data Lakehouse Edition cluster: In the left-side navigation pane, choose .
For a Data Warehouse Edition cluster: In the left-side navigation pane, choose .
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.
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) and DDL operations (CREATE, ALTER, DROP, and TRUNCATE) 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 the preceding parameters, click Next: Configure Database and Table Fields. In the Configure Database and Table Fields step, configure the parameters that are described in the following table.
Parameter | Description |
Database Name | The name of the selected database. |
Table Name | The name of the selected table. |
Primary Key Column | The primary key column of the selected table. |
Distribution Key | The distribution key column of the selected table. |
Type | The type of the selected table, which can be a partitioned table or a replicated table. |
Definition Status | The status of the selected table. After you configure the table fields, the status of the table changes from Undefined to Defined. |
After you configure the preceding parameters, click Next: Save Task Settings and Precheck.
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:
Log on to the CloudMonitor console.
View the monitoring information.
In the left-side navigation pane, choose .
Move the pointer over the AnalyticDB for MySQL card and click AnalyticDB for mysql 3.0 - ZeroETL Latency.
Find the cluster that you want to manage and click the cluster ID to view the monitoring information about zero-ETL tasks.
Create an alert rule.
In the left-side navigation pane, choose .
Click Create Alert Rule. In the Create Alert Rule panel, configure alert rule parameters for zero-ETL tasks. For more information, see Create an alert rule.
Note
You must select AnalyticDB for mysql 3.0 - ZeroETL Latency for the Product parameter.
Create a subscription policy.
In the left-side navigation pane, choose .
Click Create Subscription Policy. On the Create Subscription Policy page, configure subscription policy parameters for zero-ETL tasks. For more information, see the "Create a subscription policy" section of the Manage event subscription policies (recommended) topic.
Note
Products: Select AnalyticDB for MySQL V3.0.
Event Type: Select Abnormal or Restore.
Event Name: Select zero_etl_job_abnormal or zero_etl_job_restore.
Elastic Compute Service (ECS)
Container Compute Service (ACS)
