AnalyticDB:Synchronize SLS data using APS (Recommended)

Prerequisites

• An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.

• A job resource group is created for the AnalyticDB for MySQL cluster.

• A database account is created for the AnalyticDB for MySQL cluster.

  • If you use an Alibaba Cloud account, you need to only create a privileged account.

  • If you use a Resource Access Management (RAM) user, you must create a privileged account and a standard account and associate the standard account with the RAM user.

• You have activated Simple Log Service (SLS), and created a project and a Logstore in the same region as your AnalyticDB for MySQL cluster. For more information, see Use LoongCollector to collect and analyze text logs of ECS instances.

Usage notes

• Each table in an AnalyticDB for MySQL cluster can be synchronized with only one Logstore from SLS.

• After data ingestion, a commit operation is required to make the data visible. To ensure job stability and optimal read/write performance, the AnalyticDB for MySQL data synchronization feature uses a default 5-minute commit interval. Consequently, after you create and start a data synchronization job, you must wait at least 5 minutes to see the first batch of data.

Billing

Data synchronization through AnalyticDB for MySQL incurs the following fees:

• Elastic resource fees for the ACUs of AnalyticDB for MySQL. For more information, see Data Lakehouse Edition billing and Enterprise Edition and Basic Edition billing.

• Fees for OSS, including storage fees and fees for GET, PUT, and other requests. For more information, see Billing overview.

Procedure

• Step 1 (Optional): Configure Resource Access Management (RAM) authorization.

• Step 2: Create a data source.

• Step 3: Create a synchronization job.

• Step 4: Start the synchronization job.

• Step 5: Analyze data.

• Step 6 (Optional): Manage data sources.

Configure Resource Access Management (RAM) authorization

To synchronize SLS data to AnalyticDB for MySQL across Alibaba Cloud accounts, you must create a RAM role in the source account, grant the required permissions, and modify the trust policy. If you synchronize SLS data within the same account, skip this step and proceed to Create a data source.

1. Create a RAM role. For more information, see Create a RAM role for a trusted Alibaba Cloud account.

   Note

   When you configure the Select Trusted Alibaba Cloud Account parameter, select Another Alibaba Cloud Account and enter the ID of the Alibaba Cloud account to which the AnalyticDB for MySQL cluster belongs. You can go to the Account Center and view the Account ID on the Overview page.

2. Grant the AliyunAnalyticDBAccessingLogRolePolicy permission to the RAM role. For more information, see Grant permissions to a RAM role.

3. Modify the trust policy of the RAM role. For more information, see Modify the trust policy of a RAM role.

   {
     "Statement": [
       {
         "Action": "sts:AssumeRole",
         "Effect": "Allow",
         "Principal": {
           "RAM": [
               "acs:ram::<Alibaba Cloud Account ID>:root"
           ],
           "Service": [
               "<Alibaba Cloud Account ID>@ads.aliyuncs.com"
           ]
         }
       }
     ],
     "Version": "1"
   }

   Note

   The Alibaba Cloud Account ID is the one you entered in Step 1. Do not include the angle brackets (<>) when you configure the parameter.

Create a data source

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. In the left-side navigation pane, choose Data Ingestion>Data Sources.

3. In the upper-left corner, click Create Data Source.

4. On the Create Data Source page, configure the following parameters.

   Parameter	Description
   Data Source Type	Select SLS.
   Data Source Name	Auto-generated based on the data source type and current time. You can change the name as needed.
   Data Source Description	A description of the data source, such as the application scenario and business constraints.
   Deployment Mode	Only Alibaba Cloud Instance is supported.
   Region of Simple Log Service Project	The region where the SLS project resides.
   Across Alibaba Cloud Accounts	Whether to synchronize data from an SLS data source in a different Alibaba Cloud account. Same account: Synchronize SLS data from the current account to AnalyticDB for MySQL. Cross-account: Synchronize SLS data from a different account to AnalyticDB for MySQL. When you choose to synchronize data across accounts, you must enter the Alibaba Cloud Account and RAM Role. Note Alibaba Cloud Account: The ID of the Alibaba Cloud account that owns the source data. RAM Role: The name of the RAM role created in the source account. This is the RAM role that you created in Step 1 of the "Configure RAM authorization" section.
   Simple Log Service Project	The source SLS project. Important The SLS Project list displays all projects that belong to the Alibaba Cloud account and its RAM users. If you select a project of the Alibaba Cloud account, make sure that the RAM user has permissions on the project. Otherwise, data cannot be synchronized to AnalyticDB for MySQL.
   Simple Log Service Logstore	The source SLS Logstore.

5. After you configure the parameters, click Create.

Create a synchronization job

1. In the left-side navigation pane, click Simple Log Service/Kafka Data Synchronization.

2. In the upper-left corner, click Create Synchronization Job.

3. On the Create Synchronization Job page, configure the parameters in the Source and Destination Settings, Destination Database and Table Settings, and Synchronization Settings steps.

   • The following table describes the parameters for Source and Destination.

     Parameter	Description
     Job Name	The name of the synchronization job. Auto-generated based on the data source type and current time. You can change the name as needed.
     Data Source	Select an existing SLS data source or create a new one.
     Destination Type	Supported options: Data Lake - User OSS. Data Lake - AnalyticDB Lake Storage (recommended). Important If you select Data Lake - AnalyticDB Lake Storage, you must first enable lake storage.
     ADB Lake Storage	The name of the lake storage where the AnalyticDB for MySQL data resides. Select the destination lake storage from the drop-down list. If no lake storage exists, click Automatically Created in the drop-down list to create one. Important This parameter is required only when you set Destination Type to Data Lake - AnalyticDB Lake Storage.
     OSS Path	The storage path in OSS for the AnalyticDB for MySQL lakehouse data. Important This parameter is required only when Destination Type is set to Data Lake - User OSS. The drop-down list displays all buckets in the same region as the AnalyticDB for MySQL cluster. You can select any of them. Plan the storage path carefully. You cannot change this path after the job is created. We recommend that you select an empty directory. The OSS path cannot be a prefix of the OSS path for another synchronization job, or vice versa. This prevents data from being overwritten. For example, if two synchronization jobs have the OSS paths oss://testBucketName/test/sls1/ and oss://testBucketName/test/, the paths have a prefix relationship, and data might be overwritten during synchronization.
     Storage Format	The data storage format. Supported options: PAIMON. Important This format is supported only when Destination Type is set to Data Lake - User OSS. ICEBERG.

   • The following table describes the parameters for Destination Database and Table Settings.

     Parameter	Description
     Database Name	The name of the destination database in AnalyticDB for MySQL. If a database with the specified name does not exist, a new database is created. If a database with the specified name already exists, data is synchronized to the existing database. For more information about naming conventions, see Limits. Important In the Source and Destination Settings section, if Storage Format is set to PAIMON, an existing database must meet the following conditions. Otherwise, the synchronization job fails: The database must be an external database. The CREATE DATABASE statement must be CREATE EXTERNAL DATABASE <database_name>. The DBPROPERTIES clause in the CREATE DATABASE statement must include the catalog property, and the value of catalog must be paimon. The DBPROPERTIES clause must include the adb.paimon.warehouse property. Example: adb.paimon.warehouse=oss://testBucketName/aps/data. The DBPROPERTIES clause must include the LOCATION property, and you must append .db to the database name in the path. Otherwise, XIHE queries fail. Example: LOCATION='oss://testBucketName/aps/data/kafka_paimon_external_db.db/'. For the OSS path specified by LOCATION, the bucket and directory must exist. Otherwise, the database creation fails.
     Table Name	The name of the destination table in AnalyticDB for MySQL. If a table with the specified name does not exist in the database, a new table is created. If a table with the specified name already exists in the database, the data synchronization fails. For more information about naming conventions, see Limits.
     Schema Field Mapping	By default, fields are retrieved from the shipping job configuration of SLS. If no shipping job is configured for the Logstore, fields are retrieved from the latest log data. Supported data types: BOOLEAN, INT, BIGINT, FLOAT, DOUBLE, and STRING. SLS reserved fields can also be synchronized. For more information, see Reserved fields. Important You cannot modify destination field names. If the job has already run (including jobs that are running or have completed), you cannot modify existing column information but you can add new columns. If the job is created but has not been started, you can modify it as needed.
     Partition Key Settings	Set a partition key for the destination table. We recommend that you partition by log time or business logic to improve data ingestion and query performance. If you do not set this parameter, the destination table is not partitioned by default. You can format the destination partition key by time or by a specified partition field. To partition data by date and time, select a date/time field as the partition key field. For the formatting method, select time formatting, and then specify the source field format and the destination partition format. AnalyticDB for MySQL uses the source field format to identify the field's value and then converts it to the destination partition format. For example, if the source field is gmt_created with a value of 1711358834, the source field format is a timestamp with second precision, and the destination partition format is yyyyMMdd, the data is partitioned by 20240325. To partition data by field value, select Specify partition field as the formatting method.

   • The following table describes the parameters for Synchronization Settings.

     Parameter	Description
     Starting Consumer Offset for Incremental Synchronization	The offset from which the synchronization job starts consuming SLS data. Options: Earliest offset (begin_cursor): Consumes data from the earliest available offset in the Logstore. Latest offset (end_cursor): Consumes data starting from the latest offset in the Logstore. Custom: You can select any time point. The system starts consuming data from the first record in SLS that is at or after that time point.
     Job Resource Group	The resource group that runs the synchronization job.
     ACUs for Incremental Synchronization	Specify the number of AnalyticDB Compute Units (ACUs) in the Job Resource Group for the job. The minimum value is 2. The maximum value is the number of available compute resources in the Job Resource Group. We recommend allocating more ACUs to improve ingestion performance and job stability. Note When you create a data synchronization job, the job uses elastic resources in the Job Resource Group. Data synchronization jobs occupy resources for a long time, so the system deducts the resources used by the job from the Job Resource Group. For example, if a Job Resource Group has a maximum of 48 ACUs and an existing synchronization job uses 8 ACUs, the maximum number of ACUs available for another job in the same Job Resource Group is 40.
     Advanced Settings	Advanced settings for customizing the synchronization job. Contact technical support if you need to make custom configurations.

4. After you configure the parameters, click Submit.

Start the data synchronization task

1. On the Simple Log Service/Kafka Data Synchronization page, select the data synchronization task and click Start in the Actions column.

2. Click Search in the upper-left corner. The task has started successfully when its status changes to Running.