This topic describes how to pull data from a Hologres database to perform data enrichment by using resource functions.
Prerequisites
The endpoint, username, password, database name, and table name of the Hologres database are obtained.
The Hologres database instance resides in the same region as the Simple Log Service project that stores the data pulled from the Hologres database.
Background information
An e-commerce platform stores sales data and customer identity information in a Hologres database. When a new user logs on to the platform, the platform recommends products to the user based on the biological sex of the user. In this scenario, the platform can pull data from the Hologres database by using the data transformation feature of Simple Log Service. Then, the platform can store the data of the recommended products in the Logstore.
The platform can use the res_rds_mysql function to pull data from the Hologres database and use the e_search_table_map function to enrich data.
Use the e_search_table_map function to enrich log data
Raw data
The following table shows a database table in Hologres.
product_id
product_name
product_price
product_sales_number
sex
2
lipstick
288
2219
girl
5
watch
1399
265
boy
6
mac
4200
265
boy
3
mouse
20
2583
boy
1
basketball
35
3658
boy
4
notebook
9
5427
girl
The following example shows the sample log entries in a Logstore of Simple Log Service:
__source__:192.168.2.100 __tag__:__client_ip__:192.168.1.100 age:22 name:xiaoli profession:students sex:girl __source__:192.168.2.200 __tag__:__client_ip__:192.168.1.200 age:21 name:xiaoming profession:students sex:boy
Transformation rule
Compare the sex field in the Logstore and the sex field in the Hologres database. Both fields match only when the sex values are the same. If both fields match, the value of the product_name field is pulled from the Hologres database and concatenated with the log data in the Logstore into new data.
e_search_table_map(res_rds_mysql(address="rds-host", username="mysql-username",password="yourpassword",database="yourdatabasename",table="yourtablename", refresh_interval=60,connector='pgsql'), inpt="sex",output_fields="product_name", multi_match=True, multi_join=",")
NoteTo ensure data security and network stability, we recommend that you access the Hologres database over a virtual private cloud (VPC). You can obtain the endpoint from the network configurations of the Hologres database instance and use the endpoint to access a Hologres database over a VPC. After you obtain the endpoint, replace the value of the address field with the endpoint.
Result
__source__:192.168.2.100 __tag__:__client_ip__:192.168.1.100 __tag__:__receive_time__:1615518043 __topic__: age:22 name:xiaoli product_name:lipstick,notebook profession:students sex:girl __source__:192.168.2.200 __tag__:__client_ip__:192.168.1.200 __tag__:__receive_time__:1615518026 __topic__: age:21 name:xiaoming product_name:basketball,watch,mac,mouse profession:students sex:boy