Associate a managed CSV data source

Limitations

• You can associate a Logstore with only one CSV file.

• The original CSV file can be up to 50 MB. The SDK compresses the file before uploading it to Simple Log Service. The compressed file must be less than 9.9 MB.

Sample data

For example, a Logstore records user login events, and a CSV file stores basic user information, such as gender and age. After you associate the Logstore with the CSV file, you can analyze metrics related to user attributes.

• Logstore

  userid:100001
  action:login
  __time__:1637737306

• The CSV file contains a user data table with five fields: userid, nick, gender, province, and age. Sample data includes: 100001/User_A/male/Liaoning/24, 100002/User_B/male/Beijing/23, 100003/User_C/female/Zhejiang/22, 100004/User_D/female/Jiangxi/21, and 100005/User_E/male/Guangxi/20.

Procedure

1. Use the Python SDK to create an ExternalStore.

   For details about the Python SDK, see Python SDK overview.

   from aliyun.log import *
   
   # The endpoint of Simple Log Service.
   endpoint='cn-shanghai.log.aliyuncs.com'
   # AccessKey ID and AccessKey secret for authentication.
   accessKeyId='test-project'
   accessKey='TAI****YDw'
   # The names of the Project and ExternalStore.
   project='lr****VM'
   ext_logstore='user_meta'
   # The path of the local CSV file.
   csv_file='./user.csv'
   client = LogClient(endpoint, accessKeyId, accessKey)
   
   # Create the ExternalStore and define its table schema.
   res = client.create_external_store(project,
       ExternalStoreCsvConfig(ext_logstore, csv_file,
           [
               {"name" : "userid", "type" : "bigint"},
               {"name" : "nick", "type" : "varchar"},
               {"name" : "gender", "type" : "varchar"},
               {"name" : "province", "type" : "varchar"},
               {"name" : "age", "type" : "bigint"}
           ]))
   res.log_print()

   Parameter	Description
   endpoint	The service endpoint for Simple Log Service. For more information, see Endpoints.
   accessKeyId	The AccessKey ID of your Alibaba Cloud account. For more information, see AccessKey pair. Warning To minimize security risks, use an AccessKey pair from a RAM user.
   accessKey	The AccessKey Secret of your Alibaba Cloud account. For more information, see AccessKey pair.
   project	The Project that contains the destination Logstore.
   ext_logstore	The name of the ExternalStore, which is also the name of the virtual table. The name must meet the following requirements: The name can contain only lowercase letters, digits, hyphens (-), and underscores (_). The name must start and end with a lowercase letter or a digit. The name must be 3 to 63 characters in length.
   csv_file	The path and file name of the local CSV file.
   Table schema	The table schema defines the columns and data types of the virtual table. The following script is an example. Modify it to suit your business requirements. [ {"name" : "userid", "type" : "bigint"}, {"name" : "nick", "type" : "varchar"}, {"name" : "gender", "type" : "varchar"}, {"name" : "province", "type" : "varchar"}, {"name" : "age", "type" : "bigint"} ]

2. Log on to the Simple Log Service console.

3. In the Projects section, click the one you want.

   

4. On the Log Storage > Logstores tab, click the logstore you want.

   

5. Run the following query to verify the ExternalStore.

   Here, user_meta is the name of the external storage. Replace it with the actual name.

   * | SELECT * FROM user_meta

   If the returned result is the content of the CSV file, the ExternalStore is created successfully. After this query statement is executed, the associated data from the user_meta table is returned, including fields such as userid, nick, gender, province, and age.

6. Run the following query to perform a federated query that joins the Logstore with the CSV file.

   In this example, a federated query is set up by using the userid field in the Logstore and the userid field in the CSV file. In this case, website_log is the Logstore name and user_meta is your user-defined ExternalStore. Replace these names based on your actual configuration.

   * | SELECT * FROM website_log JOIN user_meta ON website_log.userid = user_meta.userid

   After you run the JOIN statement, the query result displays the merged fields from website_log and user_meta, including columns such as action, userid, __time__, nick, gender, province, and age.