How to obtain data from an ApsaraDB RDS for MySQL database for data enrichment - Simple Log Service

The data transformation feature of Simple Log Service allows you to obtain data from ApsaraDB RDS for MySQL databases and enrich the data based on data transformation rules.

Background information

When you analyze data, you may need to obtain data from different storage sources. For example, the data of user operations and user behavior is stored in Simple Log Service, and the data of user properties and registration is stored in an ApsaraDB RDS for MySQL database. In this case, you can use the data transformation feature to obtain data from the database and store the data in a Logstore.

You can use the res_rds_mysql function to obtain data from an ApsaraDB RDS for MySQL database and then use the e_table_map function to enrich the data.

Note

The instance on which your ApsaraDB RDS for MySQL database is created must reside in the same region as your Simple Log Service project. Otherwise, you cannot obtain data from the database.
You can access and obtain data from an ApsaraDB RDS for MySQL database by using an internal endpoint of the instance on which the database is created. For more information, see Use Simple Log Service to connect to an ApsaraDB RDS for MySQL instance for data enrichment.

Use the e_table_map function to enrich data

In this example, the e_table_map and res_rds_mysql functions are used to enrich data.

Raw data
- Sample data records in a table of 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
- Sample logs in 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
You can configure a transformation rule to match the cid field in the Logstore against the cid field in the table. If a value of the cid field is the same between the Logstore and the table, a log matches a data record. Then, the system returns the province, city, and population fields and the field values for the matched data record in the table, and concatenates the returned data with the matched log in the Logstore to generate a new log.
Note
- If multiple values of a field are matched in the table, the e_table_map function obtains only the first data record. In this example, the cid field in the table has multiple values of 1.
```
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"])
```
For more information about how to configure an ApsaraDB RDS for MySQL database in the res_rds_mysql function, see res_rds_mysql.

Transformation results

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

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

time:"1566379111"
data:"test_three"
cid:"2"
eid:"12345"
province:"Henan"
city:"Zhengzhou"
population:"3000"

time:"1566379113"
data:"test_four"
cid:"2"
eid:"12345"
province:"Henan"
city:"Zhengzhou"
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