Associate a PostgreSQL data source

1. Configure a whitelist

2. Create an ExternalStore

1. Log on to the CLI server. Run touch to create /home/shell/config.json. Add the following content to config.json and replace the region, vpc-id, host, port, username, password, db, and table values with your own.

   Parameter description	Example { "externalStoreName":"sls_join_meta_store", "storeType":"postgresql", "parameter":{ "region":"cn-qingdao", "vpc-id":"vpc-m5eq4irc1pucp*******", "host":"localhost", "port":"3306", "username":"user", "password":"****", "db":"scmc", "table":"join_meta" } }
   externalStoreName The ExternalStore name. Must be lowercase.
   storeType The data source type. Set to postgresql.
   region The region, such as cn-wulanchabu or cn-hangzhou. Important The RDS instance, Hologres instance, or ADB instance must be in the same region as the SLS project.
   vpc-id The VPC ID of the database. Varies by database type: RDS for PostgreSQL: set vpc-id to the VPC ID of the instance. ADB for PostgreSQL: set vpc-id to the VPC ID of the instance. Hologres: set vpc-id to the VPC ID of the instance.
   host   The internal endpoint of the database.
   port The database port.
   username The database username.
   password The database password.
   db The name of the database.
   table The database table name. Supported formats: table_name, for example, test. schema_name.table_name, for example, public.test.

2. Run the following command to create an ExternalStore. Replace project_name with your SLS project name. This example uses log-rds-demo.

   aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///home/shell/config.json" 

3. If the command succeeds, no response is returned. Run aliyunlog log get_external_store --project_name="log-rds-demo" --store_name="sls_join_meta_store" --format-output=json to verify. Expected output:

   {
    "externalStoreName": "sls_join_meta_store",
    "parameter": {
    "db": "postgres",
    "host": "rm-bp1******rm76.pg.rds.aliyuncs.com",
    "port": "5432",
    "region": "cn-wulanchabu",
    "table": "test",
    "timezone": "",
    "username": "user",
    "vpc-id": "vpc-m5eq4irc1pucp*******"
    },
    "storeType": "postgresql"
   }

3. Query the PostgreSQL data

In the SLS console, open the log-rds-demo project, select any Logstore, and run * | select * from sls_join_meta_store to query the PostgreSQL data source.

The query returns two rows from the dimension table: ID 123 (name: Bob) and ID 124 (name: Lucy).