如何关联Logstore与OSS外表进行查询和分析 - 日志服务

在进行日志数据查询和分析时，经常需要结合外部表格对日志数据进行分析。本文介绍如何在日志服务中联合OSS外表进行数据分析。

前提条件

持续采集日志。具体操作，请参见数据采集。
已创建索引。具体操作，请参见创建索引。
已创建OSS Bucket。具体操作，请参见控制台创建存储空间。

说明

关联的OSS文件目前只支持一个文件，文件最大为10M,不支持压缩，参数不支持正则表达式。

背景信息

某支付公司，想要分析用户年龄、地域、性别等因素对支付习惯的影响。该公司已通过日志服务实时采集用户支付行为（支付方式、支付费用等）日志，并将用户属性（地域、年龄、性别等）信息保存在OSS中。针对该场景，日志服务查询和分析引擎提供Logstore和外部数据源（ExternalStore，例如MySQL数据库、OSS等）联合查询和分析功能。您可以使用SQL的JOIN语法把用户属性数据和行为数据进行联合，分析与用户属性相关的指标。

与OSS进行关联查询和分析，具有如下优势：

节省费用：将更新频率低的数据保存在OSS上，只需要支付少量的存储费用，并且可以通过内网读数据，免去流量费用。
降低运维工作：在轻量级的联合分析平台中，不需要将数据搬迁到同一个存储系统中。
节省时间：使用SQL分析数据，分析结果秒级可见，并可以将常用的分析结果定义为报表，打开即可看到结果。

操作步骤

创建CSV文件并上传到OSS。
1. 创建名为user.csv的文件。
```
userid,nick,gender,province,age
1,用户A,male,上海,18
2,用户B,female,浙江,19
3,用户C,male,广东,18
```
2. 上传user.csv文件到OSS。具体操作，请参见控制台上传文件。
登录日志服务控制台。
在Project列表区域，单击目标Project。
在控制台左侧，单击日志存储，在日志库列表中单击目标Logstore。

输入查询和分析语句，然后选择时间范围。

通过SQL定义虚拟外部存储（此处以user_meta1为例），映射到OSS文件，如果执行结果中的result为true，表示执行成功。

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

外部存储

在查询和分析语句中定义外部存储名称、表的Schema等信息，并通过WITH语法指定OSS访问信息及文件信息，详细信息如下表所示。

配置项	说明	示例
外部存储名称	外部存储名称，即虚拟表的名称。	user_meta1
表的Schema	定义表的属性，包括表的列名及格式，例如(userid bigint, nick varchar, gender varchar, province varchar, age bigint)。	(userid bigint, nick varchar, gender varchar, province varchar, age bigint)
endpoint	OSS内网访问域名。更多信息，请参见访问域名和数据中心。	oss-cn-hangzhou.aliyuncs.com
accessid	您的AccessKey ID。更多信息，请参见访问密钥。	LT****7V
accesskey	您的AccessKey Secret。更多信息，请参见访问密钥。	Y4****ZR
bucket	CSV文件所在的OSS Bucket名称。	testoss
objects	CSV文件路径。说明 objects为array类型，目前只支持一个OSS文件。	user.csv
type	固定为oss，表示外部存储类型为OSS。	oss

验证是否成功定义外部存储。
执行如下语句，如果返回结果为您之前定义的表内容，则表示定义外部存储成功。其中，user_meta1为您定义的外部存储，请根据实际情况替换。
```
* | select * from user_meta1
```
通过JOIN语法完成Logstore和OSS外表的联合查询。
执行如下查询和分析语句关联日志服务中日志的ID和OSS文件中的userid，补全日志信息。其中，test_accesslog为Logstore名称，l为Logstore别名，user_meta1为您定义的外部存储表，请根据实际情况替换。
```
* | select * from test_accesslog l join user_meta1 u on l.userid = u.userid
```
联合查询示例：
- 统计不同性别用户的访问情况。
```
* | select u.gender, count(1) from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.gender
```
- 统计不同年龄段用户的访问情况。
```
* | select u.age, count(1) from test_accesslog l join user_meta1 u on l.userid = u.userid group by u.age
```
- 统计不同年龄段在时间维度上的访问趋势。
```
* | 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
```