Query Logstore and OSS External Tables with SQL JOIN - Simple Log Service

When you analyze logs, you may need to join them with data from external sources. This topic describes how to join a logstore in Log Service with an OSS external table.

Prerequisites

Log data is continuously collected. For more information, see Data collection.
An index is created. For more information, see Create indexes.
An OSS bucket is created. For more information, see Create buckets.

Background information

A payment company uses Log Service to collect real-time logs about user payment behaviors, including payment methods and amounts. The company also stores user attribute data, including location, age, and gender, in OSS. To analyze how these attributes affect payment habits, the query and analysis engine of Log Service allows you to join the logstore with an external store, such as a MySQL database or OSS. You can then use the SQL JOIN clause to combine user attribute data with behavioral data to analyze user-related metrics.

Joining a logstore with an OSS external table provides the following benefits:

Cost savings: You can store infrequently updated data in OSS to reduce storage costs. You can also read data over the internal network to avoid data transfer fees.
Reduced O&M workload: Eliminates the need to move data into a single storage system.
Time savings: Get analysis results in seconds by using SQL. You can also save frequent analyses as charts for instant access to the results.

Procedure

Create a CSV file and upload it to OSS.

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.
1. Create a file named user.csv and paste the following content into it.
```
userid,nick,gender,province,age
1,User A,male,Shanghai,18
2,User B,female,Zhejiang,19
3,User C,male,Guangdong,18
```
2. Upload the user.csv file to OSS. For more information, see Upload objects.
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 select a time range.

Use an SQL statement to define a virtual external store and map it to the OSS file. In this example, the external store is named user_meta1.

* | 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='testoss',objects=ARRAY['user.csv'],type='oss')

If result is true in the output, the statement was executed successfully.

外部存储

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.	(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

Verify that the external store was created.
Run the following statement. If the query returns the data from the CSV file, the external store was created successfully. Replace user_meta1 with your external store's name.
```
* | select * from user_meta1
```
Use the JOIN clause to query data across the logstore and the OSS external table.
Run the following query to join logs from your logstore with the OSS file based on the user ID. This enriches the log data. In the statement, test_accesslog is the logstore name, l is its alias, and user_meta1 is the external store that you defined. Replace these with your actual values.
```
* | select * from test_accesslog l join user_meta1 u on l.userid = u.userid
```
JOIN query examples:
- Analyze access requests from users of different genders.
```
* | select u.gender, count(1) from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.gender
```
- Analyze access requests from users of different ages.
```
* | select u.age, count(1) from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.age
```
- Analyze the access trends of users of different ages over time.
```
* | select date_trunc('minute',__time__) as minute, count(1) ,u.age from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.age,minute
```