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

How it works

The following figure shows how data transformation in Simple Log Service accesses an ApsaraDB RDS for MySQL database for data enrichment.

Solution overview

This solution consists of three steps:

Get the advanced parameter settings for the data transformation job: First, log on to the ApsaraDB RDS console to get the ApsaraDB RDS for MySQL instance information. Then, use OpenAPI Explorer to call an API operation and obtain the advanced parameter settings.
Start a data transformation job in the source logstore: Configure a data transformation rule to enrich the data.
Save the transformation results to the destination logstore: Store the enriched data in a destination logstore.

Prerequisites

The source logstore contains raw log data.
The ApsaraDB RDS for MySQL instance and the Simple Log Service project are in the same region.

Step 1: Get advanced parameter settings

Log on to the ApsaraDB RDS console.
Configure a whitelist. For more information, see Configure a whitelist.
1. In the left navigation bar, click Whitelist and SecGroup > Create Whitelist, enter a name for the group, and set the IP address whitelist to 100.104.0.0/16.
In the left-side navigation pane, click Instances. Find the target instance and click its ID.
Get the instance ID and internal port.
1. In the left-side navigation pane, click Basic Information. On the Basic Information page, find and copy the Instance ID.
2. In the left-side navigation pane, click Database Connection. On the Database Connection page, find and copy the Internal Port:.
Obtain the VpcId and VpcCloudInstanceId of the target instance.
1. Open the OpenAPI Explorer page. On the Parameters > Input Parameters page, find the DBInstanceId Instance ID text box and enter the copied target instance ID. For more information about the API operation, see Query instance details.
2. Click Operations, and on the Invocation Result page, copy VpcId and VpcCloudInstanceId.
```
"VSwitchId": "xxx",
"TipsLevel": 0,
"PayType": "Postpaid",
"LockMode": "Unlock",
"DeletionProtection": false,
"DBInstanceStorageType": "general_essd",
"InsId": 1,
"VpcId": "xxx",
"ConnectionMode": "Standard",
"VpcCloudInstanceId": "xxx",
"RegionId": "cn-hangzhou",
"ConnectionString": "xxx",
"ExpireTime": ""
```

Step 2: Start a data transformation job

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

Add advanced parameter settings.

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

In the Add Preview Settings panel, click Advanced Options and add the following parameters.

Important

In all configuration parameters in the table, your_name can be customized but must be kept consistent, and must also be consistent with the your_name in the processing rules.

Parameter	Value	Description
config.vpc.vpc_id.your_name	vpc-uf6mskb0b****n9yj	The VPC ID of the target instance. For instructions, see Get the VpcId of the target instance.
config.vpc.instance_id.your_name	rm-uf6e61k****ahd7-2024091513	The ID of the target instance. For instructions, see Get the VpcCloudInstanceId of the target instance. For a self-managed MySQL database in the same Alibaba Cloud region, use the `config.vpc.instance_ip.your_name` parameter to specify the IP address of the database within the VPC.
config.vpc.instance_port.your_name	3306	The internal port of the target instance. For instructions, see Get the internal port of the target instance.

After you confirm the settings, click OK.

Edit the transformation rule.

In the editor, add the following statements.

The following code provides a syntax example. For more information about data enrichment functions and resource functions, see Enrich data by using data from an ApsaraDB RDS for MySQL database and Resource functions.

/*
e_table_map()     Data enrichment function.
res_rds_mysql()   Pull function. Pulls table content or SQL execution results from an ApsaraDB RDS for MySQL database.
str_format()      String formatting function. The `your_name` in `res_local()` must match the `your_name` in the advanced parameter settings.
field             Matching field. The field used as the basis for matching log data in the logstore with data in the ApsaraDB RDS for MySQL table. A match is successful only when the value of this field is identical in both sources.
output_fields     Output fields. If a match is successful, the values of these fields are returned to generate a new log entry.
*/
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_username", 
        "database_password", 
        "database_name",
        table="table_name"
    ), 
    "field", 
    "output_fields"
)

The transformation statement is as follows:

The following rule matches the process_time_in_ms field in a Simple Log Service logstore with the process_time_in_ms field in an RDS MySQL database table. A match is successful only if the values of the process_time_in_ms field are identical. After a successful match, the rule retrieves the model and purchase_price fields and their values from the RDS MySQL database table and combines them with the data in the logstore to generate new data.

e_table_map(
  res_rds_mysql(
    str_format("{}:{}",
        res_local("config.vpc.instance_id.test"),
        res_local("config.vpc.instance_port.test")),
    "xxx",
    "xxx","xxx xxx",
    "database",
    table="bicycles",
    primary_keys="process_time_in_ms"),
  "process_time_in_ms",
  ["model","purchase_price"]
)

View the Transformation Results.

In the main table, the logs are routed to the __default__ output target, retaining original key-value fields like remote_addr and request_method. This means the transformation rule did not match these logs, and they were dispatched to the default target in their original state.

A successfully matched log record includes key fields such as model (XTC 800), purchase_price (3000.00), and process_time_in_ms (788). The full log entry is as follows:

__source__ :127.0.0.1
__tag__:__receive_time__ :1725962982
__tag__:_container_ip_ :183.227.33.184
__tag__:_container_name_ :container3
__tag__:_image_name_ :image3
__tag__:_path_ :/home/local
__topic__ :tomcat_access_log
body_bytes_sent :6344
model :XTC 800
process_time_in_ms :788
purchase_price :3000.00
referer :www.phn.mock.com
remote_addr :202.92.0.182
remote_user :v-0
request_method :GET
request_protocol :HTTP/2.0
request_uri :/request/path-3/file-1
status :200
time :10/Sep/2024:09:09:35
user_agent :Mozilla/5.0 (Windows NT 6.1) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.3 Safari/534.24

In the upper-right corner, click Preview Data to process the data.

Step 3: Save the transformation results

In the lower-right corner, click Save as Transformation Job (Old Version).

In the Create Data Transformation Job dialog box, first enter a Job Name (prefixed with etl-), a Display Name, and a Job Description. Select Default Role for the Authorization Method. Then, in the Storage Target section, configure the Target Name, Target Region, Target Project, and Target Logstore, and select Default Role for authorization. Finally, in the Processing Scope (Data Reception Time) section, select a time range, such as All, From a Specific Time, or Specific Time Range.
In the left-side navigation pane, under Logstores, click the destination logstore where the transformation results are saved. In the upper-right corner of the page, select a time range for the data.

After selecting a time range, check the Raw Logs tab to verify that the transformation results were saved.

The processed logs now contain the new model (XTC 800) and purchase_price (3000.00) fields. This confirms that the transformation results were written to the destination logstore.

Appendix: Access AnalyticDB and PolarDB for MySQL

In addition to ApsaraDB RDS for MySQL, Simple Log Service also supports accessing AnalyticDB for MySQL and PolarDB for MySQL databases through their internal endpoints. The required configurations are described below:

AnalyticDB for MySQL database

When you configure the Advanced Parameter Settings, set the value of config.vpc.instance_id.your_name to your AnalyticDB for MySQL instance ID followed by -controller. For the other settings, refer to this document.

You also need to set config.vpc.vpc_id.your_name to the corresponding VPC ID and config.vpc.instance_port.your_name to 3306.
For PolarDB for MySQL, use the same configuration as for ApsaraDB RDS for MySQL.