Simple Log Service:Use a resource function to obtain incremental data

Resources and data samples

• Simple Log Service resources

  • Project: client-project

  • Source Logstore: client-log

    The following figure shows data samples.

    

  • Destination Logstore: client-information

• ApsaraDB RDS resources

  • Database: client-db

  • Table: client

    The following figure shows data samples.

    

  • Username and password of an account that is used to connect to the database: test and test1234@@

  • Public endpoint of the database: rm-bp1k****tp8o.mysql.rds.aliyuncs.com

Procedure

1. Log on to the Simple Log Service console.

2. Go to the data transformation page.

   1. In the Projects section, find and click the client-project project.

   2. On the Log Storage > Logstores tab, find and click the client-log Logstore.

   3. On the query and analysis page, click Data Transformation.

3. In the upper-right corner of the page that appears, specify the time range of the data that you want to manage.

   Make sure that data exists on the Raw Logs tab.

4. In the editor box, enter a data transformation statement.

   For more information about the parameters, see res_rds_mysql.

   e_table_map(
       res_rds_mysql(
           "rm-bp1k****tp8o.mysql.rds.aliyuncs.com",     
           "test",
           "test1234@@",
           "client-db",
           table="client",
           fields=["c_id", "name", "telephone", "update_time"],
           refresh_interval=1,
           primary_keys="c_id",
           update_time_key="update_time",
           deleted_flag_key=None,
       ),
       "c_id",
       ["name", "telephone"],
   )

5. Preview data in quick mode.

   Check whether the specified data transformation statement is valid. For more information, see Quick preview.

   1. Select Quick.

   2. On the Data Testing > Data tab, enter the following content:

      {
        "__source__": "192.0.2.0",
        "__time__": 1624956516,
        "__topic__": "log",
        "__tag__:__client_ip__":"192.0.2.2",
        "c_id": "1",
        "staff_id": "002",
        "status": "Ongoing follow-up",
        "tag": "Follow-up visit",
      }

   3. On the Data Testing > Dimension Table tab, enter the following content:

      c_id,name,telephone,update_time,delete_flag
      1,maki,010-123,1606358931,false

   4. Click Preview Data.

      View the preview results.

6. Preview data in advanced mode.

   Check whether Simple Log Service is connected to the ApsaraDB RDS for MySQL instance. For more information, see Advanced preview.

   1. Select Advanced.

   2. Click Preview Data.

   3. In the Add Preview Settings panel, configure the Authorization Method parameter and click OK.

      

   4. View the preview results.

      

      If an error is reported, fix the error by following the instructions provided in How do I fix syntax errors that occur when I pull data from ApsaraDB RDS for MySQL?

7. Create a data transformation job.

   1. Click Save as Transformation Job.

   2. In the Create Data Transformation Job panel, configure the parameters and click OK.

      For more information, see Create a data transformation job.

      After the data transformation job is created, you can view the transformed data in the destination Logstore.

FAQ

How do I use the delete feature in the incremental pulling mode?

In the incremental pulling mode, Simple Log Service pulls only new or updated data based on the primary key and time field of the required database table. If you configure delete_flag=true for a data record in a database table to mark the record for deletion, Simple Log Service still pulls the record for transformation. To resolve this issue, Simple Log Service adds the deleted_flag_key parameter to the res_rds_mysql function. If you configure the deleted_flag_key parameter in a data transformation statement to pull data from a database table, the data transformation job deletes data rows for which the delete_flag parameter is set to true from the in-memory dimension table of the job after the job obtains the updated data from the table. This does not affect the content of the database table. The data rows that are deleted from the in-memory dimension table are excluded from the subsequent JOIN operation that is performed on log data.

For example, the name=mia and name=tom data records are inserted into a database on an ApsaraDB RDS for MySQL instance, and the delete_flag parameter is set to true for the name=mia data record. This indicates that the data record is marked for deletion. When Simple Log Service updates the in-memory dimension table, the name=mia data record is deleted from the in-memory dimension table and is not transformed.

The following sample code provides an example of the data transformation statement:

e_table_map(
    res_rds_mysql(
        "rm-bp1****l3tp.mysql.rds.aliyuncs.com",     
        "test",
        "test1234@@",
        "client-db",
        table="client",
        fields=["c_id", "name", "telephone", "update_time"],
        refresh_interval=1,
        primary_keys="c_id",
        update_time_key="update_time",
        deleted_flag_key="delete_flag",
    ),
    "c_id",
    ["name", "telephone"],
)