Learn how to create a foreign table to associate Simple Log Service (SLS) with Object Storage Service (OSS). - Simple Log Service

This topic describes how to create a foreign table to associate Simple Log Service (SLS) with Object Storage Service (OSS).

Prerequisites

You have collected logs. For more information, see Data collection.
You have enabled and configured an index. For more information, see Create an index.
You have created an OSS bucket. For more information, see Create a bucket.
A CSV file has been uploaded to an OSS Bucket. For more information, see Upload files.

Important
You can associate multiple OSS files. The maximum size of a single file is 50 MB. Compression is not supported. Regular expressions are not supported for parameters.

Benefits

Federated queries on data in Object Storage Service (OSS) provide the following benefits:

Cost savings: Store infrequently updated data in OSS to minimize storage costs. You can also read data over the internal network to avoid data transfer fees.
Reduced operational workload: Use a lightweight federated analytics platform without migrating data into a single storage system.
Time savings: Analyze data by using SQL and get results in seconds. You can save frequently used queries as a report to access results directly.

Procedure

Log on to the Simple Log Service console.
In the Projects section, click the one you want.
On the Log Storage > Logstores tab, click the logstore you want.

Enter a query statement and click Search & Analyze.

Use an SQL statement to define a virtual foreign table that maps to an OSS file.

* | create table <foreign table> (<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 statement, define the foreign table name, table schema, and other parameters. Use the WITH clause to specify OSS access information and file information. The following table describes the parameters.

Parameter	Description	Example
Foreign table name	The name of the virtual foreign table.	user_meta1
Table schema	The column names and data types of the table.	(userid bigint, nick varchar, gender varchar, province varchar, age bigint)
Endpoint	The endpoint for accessing OSS. For more information, see Regions and endpoints. Important You must use an internal network endpoint. Public endpoints are not supported.	oss-cn-hangzhou-internal.aliyuncs.com
Accessid	The AccessKey ID and AccessKey Secret of an Alibaba Cloud account. We recommend that you use the AccessKey pair of a 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	The CSV files or OSS directories to query. The objects parameter is an array that can contain multiple files or directories. Note the following: When you specify a directory, its name must end with a forward slash (`/`). For example, if you specify `test_dir/`, the foreign table is associated with all CSV files in the test_dir/ directory of the specified bucket. Limitation: The total number of files cannot exceed 100.	'user.csv' 'test_dir/' 'user.csv','test_dir/','my_dir/'
Type	The type of the external data source. The value must be oss.	oss

Sample command:

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

If the value of the result field in the output is true, the statement is successfully executed.

Verify that the foreign table is created.

Execute the following statement, where External storage is the foreign table name that you defined in Step 4. If the command returns the content of the table that you previously defined, the foreign table is successfully defined.
```
* | select * from <foreign table>
```

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

For example, run the following query to join the userid from your logs with the userid in the OSS file to enrich the log data.

Note

The collected logs must include the userid field.

* | select * from <logstore> l join <foreign table> u on l.userid = u.userid

Parameter	Description	Example
Logstore	The name of the Logstore that you want to query.	test_logstore
Foreign table name	The name of the foreign table that you defined in Step 4.	user_meta1

After you run the federated query, the results contain both log fields (such as action, blood, magic, money, network, payment, status, and useragent) and user attribute fields from the foreign table (such as nick, gender, province, and age). This indicates that the log data is successfully enriched by joining on userid.

For a best practice example, see Query and analyze data by associating a Logstore with an OSS foreign table.