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

How it works

The following figure shows how the data transformation feature accesses an ApsaraDB RDS for MySQL database through its internal endpoint to enrich log data.

Solution overview

To enrich log data using the Simple Log Service data transformation feature and an ApsaraDB RDS for MySQL internal endpoint, complete the following three steps:

1. Query the advanced parameters required for a data transformation job: Log on to the ApsaraDB RDS console to retrieve the ApsaraDB RDS for MySQL instance information. Then, call an API operation in OpenAPI Explorer to obtain the advanced parameters.

2. Enable a data transformation job in the source Logstore: Configure a data transformation rule to enrich the log data.

3. Save the transformation results to the destination Logstore: Save the enriched data to the destination Logstore.

Prerequisites

• You have raw log data.

• Your ApsaraDB RDS for MySQL instance and your Simple Log Service project are located in the same region.

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

1. Log on to the ApsaraDB RDS console.

2. In the left navigation pane, click Instances. On the Instances page, locate and click the target instance.

   

3. Configure an IP address whitelist. For more information, see Configure a whitelist.

   1. In the left navigation pane, click Whitelist and Security Group > Create Whitelist Group. Enter a group name and the IP addresses in the group. Set the IP addresses to 100.104.0.0/16.

      

4. Retrieve the instance ID and internal port.

   1. In the left navigation pane, click Basic Information. On the Basic Information page, locate and copy the Instance ID.

      

   2. In the left navigation pane, click Database Connection. On the Database Connection page, view and copy the Internal Port.

      

5. Retrieve the VpcId and VpcCloudInstanceId of the instance.

   1. Visit OpenAPI Explorer. In the Parameters > Request Parameters section, locate the DBInstanceIdInstance ID text box and paste the copied instance ID. For more information about the API operation, see DescribeDBInstanceAttribute.

      

   2. Click Call. On the Response tab, copy the values of VpcId and VpcCloudInstanceId.

      

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

1. Log on to the Simple Log Service console.

2. 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 (Legacy).

      

3. Add the advanced parameter configuration.

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

      

   2. On the Add Preview Settings page, click Advanced Options and add the following configuration.

      Important

      You can replace your_name in all parameters with a custom value. Use the same value across all parameters. Also use this value to replace your_name in the transformation rule.

      Parameter	Value	Description
      config.vpc.vpc_id.your_name	vpc-uf6mskb0b****n9yj	The VPC ID of the instance. For more information, see Get the VPC ID of the instance.
      config.vpc.instance_id.your_name	rm-uf6e61k****ahd7-2024091513	The instance ID. For more information, see Get the VpcCloudInstanceId of the instance. For a self-managed MySQL database in the same Alibaba Cloud region, use config.vpc.instance_ip.your_name to specify its VPC IP address.
      config.vpc.instance_port.your_name	3306	The port number of the destination instance's private network address. For more information, see Get the internal port of the instance.

      The following figure shows a sample configuration.

      

   3. After confirming the configuration, click OK in the lower-right corner.

      

4. Edit the transformation rule.

   1. Add the following statement in the editor.

      For syntax examples and details about enrichment functions and resource functions, see Enrich data by pulling from an ApsaraDB RDS for MySQL database and Resource functions.

      /*
      e_table_map()     The data enrichment function.
      res_rds_mysql()   The pull function that retrieves table data or SQL execution results from an ApsaraDB RDS for MySQL database.
      str_format()      The string formatting function. The value of your_name in res_local() must match the value of your_name in the advanced parameters.
      field             The matching field. This field links log data in the Logstore to table data in the ApsaraDB RDS for MySQL database. A match succeeds only when the field value in the log matches the field value in the table.
      output_fields     The output fields. When a match succeeds, 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 account", 
              "Database password", 
              "Database name",
              table="Name of the required table in the database"
          ), 
          "field", 
          "output_fields"
      )

      The following figure shows the transformation statement.

      This rule matches the process_time_in_ms field in a Simple Log Service Logstore with the process_time_in_ms field in an RDS for MySQL table. A match is successful only if the values of the process_time_in_ms field are identical. If the match is successful, the rule returns the model and purchase_price fields and their values from the RDS for MySQL table. This data is then concatenated with the data from the Logstore to generate new data.

      

5. In the upper-right corner of the data transformation page, click Preview Data to transform the data.

   

6. View the transformation results.

   The following figure shows results when the rule fails to match.

   

   The following figure shows results when the rule matches successfully.

   

Step 3: Save the transformation results to the destination Logstore

1. Click Save as Transformation Job (Legacy) in the lower-right corner. In the panel that appears, enter the required information and click OK.

   

2. On the Logstores tab, click the destination Logstore. In the upper-right corner of the page that appears, specify a query time range.

   After selecting the time range, check whether the transformation results appear on the Raw Logs tab. The following figure confirms successful saving.

   

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

Simple Log Service supports accessing ApsaraDB RDS for MySQL, AnalyticDB for MySQL, and PolarDB for MySQL databases over internal endpoints. The following list describes the settings:

• AnalyticDB for MySQL databases

  When configuring Advanced Parameter Settings, set config.vpc.instance_id.your_name to the AnalyticDB for MySQL instance ID followed by -controller, as shown in the following figure. Configure other parameters as described in this topic.

  

• PolarDB for MySQL databases: Configure parameters as described in this topic.