How to use resource functions to pull data from a Hologres database and perform data enrichment - Simple Log Service

This topic describes how to use resource functions to pull data from a Hologres database to perform data enrichment.

Prerequisites

The endpoint, username, password, database name, and table name of the Hologres database that you want to use are obtained.
The Hologres instance on which the database is created resides in the same region as the Simple Log Service project that you want to use to store 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, you can pull data from the Hologres database by using the data transformation feature of Simple Log Service, use enrichment functions to associate the pulled data with the recommended products, and then store the enrichment results in a Logstore of Simple Log Service. This way, other services can query the data in the Logstore.

In this example, the res_rds_mysql function is used to pull data from the Hologres database, and the e_search_table_map function is used to enrich data.

Use the e_search_table_map function to enrich data

Raw data
- The following example shows a table in a Hologres database.
  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 sample logs 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. If the values of the sex fields are the same, the two fields match. If the fields match, the value of the product_name field is pulled from the Hologres database and concatenated with the data in the Logstore into a new log.
Scenario 1: Access a Hologres database by using a public endpoint.
```
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=",")
```
Note
You can obtain the public endpoint of the Hologres database from the network information of the Hologres instance. Then, set the address parameter to this endpoint.
Scenario 2: Access a Hologres database over a virtual private cloud (VPC). We recommend that you use this method, which improves the security and stability of access to Hologres instances. When you configure advanced preview settings, you can add the config.vpc.vpc_id.name, config.vpc.instance_id.name, and config.vpc.instance_port.name parameters. You must specify the same value for the name part in the parameters, and you can specify a custom value. Then, you can obtain the VPC ID, VPC instance ID, and VPC instance port for the Hologres database from the network information of the Hologres instance and add the obtained information to the values of the parameters. For more information, see Advanced preview.
```
e_search_table_map(res_rds_mysql("{}:{}",res_local("config.vpc.instance_id.name"),res_local("config.vpc.instance_port.name")), 
                                 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=",")
```
Advanced Parameter Settings

Transformation 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

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