Create a foreign table to associate Simple Log Service (SLS) with Object Storage Service (OSS) for federated queries. - Simple Log Service

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

Prerequisites

Logs are collected. For more information, see Data collection.
An index is enabled and configured. For more information, see Create an index.
An OSS bucket is created. For more information, see Create a bucket in the console.
A CSV file is uploaded to the 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. Compressed files are not supported. Regular expressions are not supported in parameters.

Benefits

Using federated queries with Object Storage Service (OSS) provides the following benefits:

Cost savings: Store infrequently updated data in OSS. You pay only for low-cost storage and can read data over the internal network to avoid data transfer fees.
Reduced operational overhead: Use a lightweight federated analytics platform without needing to move data into a single storage system.
Time savings: Analyze data by using SQL and view the results in seconds. You can save frequently used analytic queries as reports and view the results on demand.

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 and analysis statement, and then click Search & Analyze.

Use SQL to define a virtual foreign table that maps to files in OSS.

* | create table <foreign_table_name> (<schema>) with (endpoint='oss-cn-<your_region>-internal.aliyuncs.com',accessid='<your_accesskey_id>',accesskey='<your_accesskey_secret>',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 the connection details for OSS and the target file information. The following table describes the parameters.

Parameter	Description	Example
Foreign table name	The name of the foreign table. This is the name of the virtual table.	user_meta1
Schema	Defines the table properties, including column names and data types. The supported column types are bigint, boolean, double, and varchar.	(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 endpoint. Public endpoints are not supported.	oss-cn-hangzhou-internal.aliyuncs.com
Accessid	The AccessKey ID and AccessKey secret of your Alibaba Cloud account. We recommend using the AccessKey pair of a RAM user with permissions to access the OSS data. To learn how to grant permissions to a RAM user, see Grant permissions to a RAM user. To learn how to obtain an AccessKey pair, see AccessKey pair.	LTAI****************
Accesskey		yourAccessKeySecret
Bucket	The name of the OSS bucket that stores the CSV file.	examplebucket
Objects	The target files or directories in the bucket. The objects parameter is an `ARRAY` type and can contain multiple files or directories. Note the following: When you specify a directory, it must end with a `/`. If you specify a directory such as `test_dir/`, the external table is associated with all the CSV files in that OSS directory under the corresponding bucket. Limitation: The total number of files cannot exceed 100.	'user.csv' 'test_dir/' 'user.csv','test_dir/','my_dir/'
Type	Set to oss, which indicates that the foreign table type is Object Storage Service (OSS).	oss

The following code provides a 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')

The operation is successful if the result field in the output is true.

外部存储

Verify that the foreign table was created successfully.

Run the following statement. Replace <foreign_table_name> with the foreign table name that you defined in Step 4. If the command returns the table content, the foreign table was created successfully.
```
* | select * from <foreign_table_name>
```

Use a JOIN clause to run a federated query across SLS and OSS.

For example, run the following query to join logs in a Logstore with data in an OSS file. The join is performed on the userid field to enrich your log data.

Note

The collected logs must contain a userid field.

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

Parameter	Description	Example
Logstore	The name of the Logstore that you want to query.	test_LogStore
Foreign table	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.