how to obtain data from an apsaradb rds for mysql database for data enrichment - Simple Log Service

Simple Log Service's data manipulation feature enables the retrieval of data from Alibaba Cloud RDS MySQL databases and enhances it using transformation rules.

Background information

Analyzing data often requires sourcing from various storage systems. For instance, while user operations and behaviors are logged in Simple Log Service, user attributes and registration details are stored in an ApsaraDB RDS for MySQL database. You can leverage the data transformation feature to extract this information from the database and deposit it into a Logstore.

The res_rds_mysql function facilitates data retrieval from the RDS MySQL database, which can then be enriched using the e_table_map function.

Note

The ApsaraDB RDS for MySQL database instance must be in the same region as your Simple Log Service project to facilitate data retrieval.
For internal endpoint access to the RDS MySQL database for data enrichment, refer to Access RDS MySQL for Data Enrichment through Simple Log Service.

Use the e_table_map function to enrich data

This example demonstrates the use of e_table_map and res_rds_mysql functions to enrich data.

Raw Data
- Below is a sample of data records from a table in an ApsaraDB RDS for MySQL database.
  
  province
  city
  population
  cid
  eid
  Shanghai
  Shanghai
  2000
  1
  00001
  Tianjin
  Tianjin
  800
  1
  00002
  Beijing
  Beijing
  4000
  1
  00003
  Henan
  Zhengzhou
  3000
  2
  00004
  Jiangsu
  Nanjing
  1500
  2
  00005
- The following example displays sample logs from a Simple Log Service Logstore.
```
time:"1566379109"
data:"test-one"
cid:"1"
eid:"00001"

time:"1566379111"
data:"test_second"
cid:"1"
eid:"12345"

time:"1566379111"
data:"test_three"
cid:"2"
eid:"12345"

time:"1566379113"
data:"test_four"
cid:"2"
eid:"12345"
```
Transformation Rule
Match the cid field in the Simple Log Service Logstore with the corresponding cid field in the RDS MySQL database table. An exact match of the cid values is required for a successful match. Upon matching, return the province, city, and population fields with their values from the RDS MySQL table, and merge them with the Logstore data to create enriched records.
Note
- In cases where multiple identical values exist in the RDS MySQL table fields (e.g., several cid fields with the value 1), the e_table_map function retrieves only the first matching row.
```
e_table_map(res_rds_mysql(address="rds-host", username="mysql-username",password="xxx",database="xxx",table="xx",refresh_interval=60),"cid",["province","city","population"])
```
Set up the RDS MySQL database details in the res_rds_mysql function. For more details, see res_rds_mysql.

Transformation Result

time:"1566379109"
data:"test-one"
cid:"1"
eid:"00001"
province:"上海"
city:"上海"
population:"2000"

time:"1566379111"
data:"test_second"
cid:"1"
eid:"12345"
province:"上海"
city:"上海"
population:"2000"

time:"1566379111"
data:"test_three"
cid:"2"
eid:"12345"
province:"河南"
city:"郑州"
population:"3000"

time:"1566379113"
data:"test_four"
cid:"2"
eid:"12345"
province:"河南"
city:"郑州"
population:"3000"

province	city	population	cid	eid
Shanghai	Shanghai	2000	1	00001
Tianjin	Tianjin	800	1	00002
Beijing	Beijing	4000	1	00003
Henan	Zhengzhou	3000	2	00004
Jiangsu	Nanjing	1500	2	00005