Use Simple Log Service to connect to an ApsaraDB RDS for MySQL instance for data enrichment - Simple Log Service

If data is stored in a Logstore of Simple Log Service and a database created on an ApsaraDB RDS for MySQL instance, you can use the data transformation feature of Simple Log Service to access the database by using the internal endpoint of the instance and obtain the data for data enrichment.

How it works

The following figure shows how to use the data transformation feature of Simple Log Service to access an ApsaraDB RDS for MySQL database by using the internal endpoint of the ApsaraDB RDS for MySQL instance on which the database is created and how to obtain data for data enrichment.

Solution overview

You can use the data transformation feature of Simple Log Service to access an ApsaraDB RDS for MySQL database by using the internal endpoint of the ApsaraDB RDS for MySQL instance on which the database is created and obtain data for data enrichment. Procedure:

Query the advanced parameters required for a data transformation job: Log on to the ApsaraDB RDS console to query the information about the ApsaraDB RDS for MySQL instance. Then, call an API operation in OpenAPI Explorer to query the advanced parameters required for a data transformation job.
Enable a data transformation job in the source Logstore of the job: Configure a data transformation rule to enrich data.
Save the transformation results to the destination Logstore of the job: Save the transformation results to the destination Logstore.

Prerequisites

Raw log data is obtained.
Your ApsaraDB RDS for MySQL instance resides in the same region as your Simple Log Service project.

Step 1: Query the advanced parameters required for a data transformation job

Log on to the ApsaraDB RDS console.
In the left-side navigation pane, click Instances. On the Instances page, find and click the instance that you want to manage.
Configure an IP address whitelist. For more information, see Configure a whitelist.
1. In the left-side navigation pane of the instance page, click Whitelist and SecGroup. On the Whitelist Settings tab of the page that appears, click Create Whitelist. In the Create Whitelist dialog box, configure the Whitelist Name and IP Addresses parameters. Set the IP Addresses parameter to 100.104.0.0/16.
Query the ID and internal port of the instance.
1. In the left-side navigation pane of the instance page, click Basic Information. On the Basic Information page, find and copy the instance ID.
2. In the left-side navigation pane of the instance page, click Database Connection. On the Database Connection page, view and copy the internal port.
Query the values of the VpcId and VpcCloudInstanceId parameters for the instance.
1. Visit OpenAPI Explorer. In the Parameters > Request Parameters section, find the DBInstanceId parameter and paste the copied instance ID. For more information about the API operation, see DescribeDBInstanceAttribute.
2. Click Initiate Call. On the Response tab, copy the values of the VpcId and VpcCloudInstanceId parameters.

Step 2: Enable a data transformation job in the source Logstore of the job

Log on to the Simple Log Service console.
Go to the data transformation page.
1. In the Projects section, click the project that you want to manage.
2. On the Log Storage > Logstores tab, click the Logstore that you want to manage.
3. On the query and analysis page of the Logstore, click Data Transformation.
4. On the data transformation page, click Switch to Data Transformation(Old Version).

Configure the advanced parameters that are obtained.

In the upper-right corner of the page, select Advanced. Then, click Preview Data.

In the Add Preview Settings panel, click Advanced Options and configure the advanced parameters. The following table describes the parameters.

Important

You can replace your_name in the following parameters with a custom value. Make sure that the same value is used for the parameters. You must also use the value to replace your_name in the code of the data transformation rule that you configure. For more information, see Transformation rules.

Parameter	Value	Description
config.vpc.vpc_id.your_name	vpc-uf6mskb0b****n9yj	The virtual private cloud (VPC) ID of the instance. For more information, see Query the VPC ID of an instance.
config.vpc.instance_id.your_name	rm-uf6e61k****ahd7-2024091513	The ID of the instance. For more information, see Query an instance ID.
config.vpc.instance_port.your_name	3306	The internal port of the instance. For more information, see Obtain an internal port.

The following figure shows the sample configuration.

Confirm the configuration and click OK.

Configure a data transformation rule.

Specify a transformation statement in the code editor based on the following syntax.

For more information about data enrichment functions and resource functions, see Pull data from an ApsaraDB RDS for MySQL database and Resource functions.

/*
e_table_map()     The data enrichment function. 
res_rds_mysql()   The function that is used to pull table data or SQL execution results from an ApsaraDB RDS for MySQL database. 
str_format()      The function that is used to format strings. The value of your_name in the res_local() function must be the same as the value of your_name in the advanced parameters. 
field             The field that is used to match the log data in the source Logstore against the table data in the ApsaraDB RDS for MySQL database. If the field value in the log data is the same as the field value in the table data, the match is considered successful. 
output_fields     The output fields. If the match is successful, the values of the output fields are returned and a new log is generated. 
*/
e_table_map(
    res_rds_mysql(
        str_format("{}:{}", 
                   res_local("config.vpc.instance_id.your_name"), 
                   res_local("config.vpc.instance_port.your_name")), 
        "Database account", 
        "Database password", 
        "Database name",
        table="Name of the required table in the database"
    ), 
    "field", 
    "output_fields"
)

The following figure shows the transformation statement.

After the data transformation rule is created, the system attempts to match the value of the process_time_in_ms field in the log data of the source Logstore against the value of the process_time_in_ms field in the table data of the ApsaraDB RDS for MySQL database. If the values are the same, the match is considered successful. If the match is successful, the values of the model and purchase_price fields in the table data are returned. A new log is generated based on the raw log data in the source Logstore and the values of the output fields.

In the upper-right corner of the data transformation page, click Preview Data to transform data.
View the transformation results.
The following figure provides an example of transformation results returned after the match failed.
The following figure provides an example of transformation results returned after the match is successful.

Step 3: Save the transformation results to the destination Logstore of the job

Click Save as Transformation Job(Old Version). In the panel that appears, configure the parameters and click OK.
On the Logstores tab, click the destination Logstore. In the upper-right corner of the page that appears, specify a query time range.
After you specify the query time range, you can check whether the transformation results are saved on the Raw Logs tab. The following figure shows that the transformation results are saved.

Appendix: Access an AnalyticDB for MySQL database or a PolarDB for MySQL database over an internal endpoint

Simple Log Service allows you to access ApsaraDB RDS for MySQL databases over an internal endpoint. Simple Log Service also allows you to access AnalyticDB for MySQL databases and PolarDB for MySQL databases over an internal endpoint. The following list describes the related settings:

AnalyticDB for MySQL databases
When you configure parameters in the Advanced Parameter Settings field, set the config.vpc.instance_id.your_name parameter to a value in the AnalyticDB for MySQL instance ID-controller format and configure other parameters based on the instructions in this topic.
PolarDB for MySQL databases: Configure parameters based on the instructions in this topic.