如何關聯Logstore與OSS外表進行查詢和分析 - Simple Log Service

在進行日誌資料查詢和分析時，經常需要結合外部表格對日誌資料進行分析。本文介紹如何在Log Service中聯合OSS外表進行資料分析。

前提條件

持續採集日誌。具體操作，請參見資料擷取。
已建立索引。具體操作，請參見建立索引。
已建立OSS Bucket。具體操作，請參見控制台建立儲存空間。

說明

關聯的OSS檔案目前只支援一個檔案，檔案最大為10M,不支援壓縮，參數不支援Regex。

背景資訊

某支付公司，想要分析使用者年齡、地區、性別等因素對支付習慣的影響。該公司已通過Log Service即時採集使用者支付行為（支付方式、支付費用等）日誌，並將使用者屬性（地區、年齡、性別等）資訊儲存在OSS中。針對該情境，Log Service查詢和分析引擎提供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。具體操作，請參見控制台上傳檔案。
登入Log Service控制台。
在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外表的聯集查詢。
執行如下查詢和分析語句關聯Log Service中日誌的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
```