Simple Log Service:Associate an OSS data source

Benefits

Procedure

1. Log on to the Simple Log Service console.

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

   

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

   

4. Enter a query and an analytic statement, and click Search & Analyze.

   Define a virtual foreign table and map it to an OSS file using an SQL statement.

   * | create table <External storage> (<Schema>) with (endpoint='oss-cn-${your_region_name}-internal.aliyuncs.com',accessid='yourAccessKeyID',accesskey ='yourAccessKeySecret',bucket='<your bucket name>',objects=ARRAY['my.csv'],type='oss')

   In the query and analytic statement, define the name, schema, and other information for the foreign table. Use the WITH clause to specify the OSS access and file information. The following table describes the parameters.

   Configuration Item	Description	Example
   External storage name	The name of the foreign table, which is the name of the virtual table.	user_meta1
   Table schema	Defines the properties of the table, including column names and formats.	(userid bigint, nick varchar, gender varchar, province varchar, age bigint)
   endpoint	The OSS endpoint. For more information, see OSS regions and endpoints. Important Use an internal endpoint. Public endpoints are not supported.	oss-cn-hangzhou-internal.aliyuncs.com
   accessid	The AccessKey ID and AccessKey secret of an Alibaba Cloud account. Use the AccessKey pair of a Resource Access Management (RAM) user that has permissions to access the OSS data source. For more information about how to grant permissions to a RAM user, see Grant permissions to a RAM user. For more information about how to obtain an AccessKey pair, see AccessKey pair.	LTAI****************
   accesskey		yourAccessKeySecret
   bucket	The name of the OSS bucket where the CSV file is stored.	examplebucket
   objects	Supports CSV files and OSS folders. The objects parameter is an array that can contain multiple files or folders. Note: When you specify a folder, the name must end with a forward slash (/). If you specify a folder, such as test_dir/, the foreign table is associated with all CSV files in that folder within the specified bucket. Limit: The total number of files of all types cannot exceed 100.	'user.csv' 'test_dir/' 'user.csv','test_dir/','my_dir/'
   type	The value is fixed to oss. This indicates that the external storage type is OSS.	oss

   The following is a sample command:

   * | create table user_meta1 ( userid bigint, nick varchar, gender varchar, province varchar, age bigint) with ( endpoint='oss-cn-hangzhou.aliyuncs.com',accessid='yourAccessKeyID',accesskey='yourAccessKeySecret',bucket='examplebucket',objects=ARRAY['my.csv'],type='oss')

   If the result field is true, the operation is successful.

   

5. Verify that the foreign table is defined.

   Execute the following statement. In the statement, `External storage` is the name of the foreign table that you defined in Step 4. If the content of the table is returned, the foreign table is successfully defined.

   * | select * from <External storage>

6. Use the JOIN clause to perform a federated query on data in SLS and OSS.

   For example, execute the following analytic statement to associate the userid field in the SLS logs with the userid field in the OSS file to enrich the log information.

   Note

   The collected logs must contain the `userid` parameter.

   * | select * from <logstore> l join <External storage> u on l.userid = u.userid

   Configuration Item	Description	Example
   Logstore	The name of the Logstore that you want to query.	test_logstore
   External storage	The name of the foreign table that you defined in Step 4.	user_meta1

   

For more information about best practices, see Associate a Logstore with an OSS foreign table for query and analysis.