如何关联LogStore与OSS外表进行查询和分析 - Simple Log Service

ログデータのクエリと分析を行う際、分析をより充実させるために外部テーブルのデータと結合することがよくあります。このトピックでは、Simple Log Service (SLS) の Logstore のデータと Object Storage Service (OSS) の外部テーブルのデータを JOIN クエリで結合する方法について説明します。

前提条件

ログデータが継続的に収集されていること。詳細については、「データ収集」をご参照ください。
ログデータにインデックスが作成されていること。詳細については、「インデックスの作成」をご参照ください。
OSS バケットが作成されていること。詳細については、「バケットの作成」をご参照ください。

背景情報

たとえば、ある決済会社が、ユーザーの年齢、地域、性別などの要因が決済習慣にどのように影響するかを分析したいと考えています。この会社は SLS を使用して、決済方法や金額などのリアルタイムの決済行動ログを収集しています。ユーザーの地域、年齢、性別などの属性データは OSS に保存されています。このようなシナリオでは、SLS のクエリ・分析エンジンを使用して、Logstore のデータと、MySQL データベースや OSS などの外部データソースのデータを JOIN クエリで結合できます。 SQL の JOIN 構文を使用して、ユーザー属性データを行動データと結合し、ユーザー属性に関連するメトリクスを分析できます。

ログデータと OSS のデータを結合すると、次のようなメリットがあります：

コスト削減：更新頻度の低いデータを OSS に保存することで、ストレージコストを削減できます。また、内部ネットワーク経由でデータを読み取ることで、アウトバウンドトラフィック料金を回避できます。
運用オーバーヘッドの低減：データを単一のストレージシステムに移行する必要がありません。
時間節約：SQL を使用してデータを分析し、数秒で結果を取得できます。また、頻繁に使用するクエリをグラフとして保存し、結果に素早くアクセスすることもできます。

操作手順

CSV ファイルを作成し、OSS にアップロードします。

重要
複数の OSS ファイルを関連付けることができます。 1 つのファイルの最大サイズは 50 MB です。圧縮はサポートされていません。パラメーターに正規表現はサポートされていません。
1. user.csv という名前のファイルを作成し、次の内容を貼り付けます。
```
userid,nick,gender,province,age
1,User A,male,Shanghai,18
2,User B,female,Zhejiang,19
3,User C,male,Guangdong,18
```
2. user.csv ファイルを OSS にアップロードします。詳細については、「オブジェクトのアップロード」をご参照ください。
Log Serviceコンソールにログインします。
[プロジェクト] セクションで、管理するプロジェクトをクリックします。
[ログストレージ] > [ログストア] タブで、管理するログストアをクリックします。

クエリ文を入力し、時間範囲を選択します。

SQL ステートメントを使用して、OSS オブジェクトにマッピングする仮想外部テーブルを定義します。この例では、外部テーブル名は 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='<ご使用の AccessKey ID>',accesskey='<ご使用の AccessKey シークレット>',bucket='testoss',objects=ARRAY['user.csv'],type='oss')

クエリの結果が [result] として [true] であれば、ステートメントは正常に実行されています。

クエリと分析文で、外部テーブルの名前、スキーマ、およびその他の情報を定義します。 WITH 句を使用して、OSS のアクセス情報とファイル情報を指定します。次の表にパラメーターを示します。

設定項目	説明	例
外部ストレージ名	外部テーブルの名前。仮想テーブルの名前です。	user_meta1
テーブルスキーマ	列名やフォーマットなど、テーブルのプロパティを定義します。	(userid bigint, nick varchar, gender varchar, province varchar, age bigint)
endpoint	OSS エンドポイント。詳細については、「リージョンとエンドポイント」をご参照ください。重要内部エンドポイントを使用してください。パブリックエンドポイントはサポートされていません。	oss-cn-hangzhou-internal.aliyuncs.com
accessid	Alibaba Cloud アカウントの AccessKey ID と AccessKey Secret。 OSS データソースへのアクセス権限を持つ Resource Access Management (RAM) ユーザーの AccessKey ペアを使用します。 RAM ユーザーに権限を付与する方法の詳細については、「RAM ユーザーへの権限付与」をご参照ください。 AccessKey ペアの取得方法の詳細については、「AccessKey ペア」をご参照ください。	LTAI****************
accesskey		yourAccessKeySecret
bucket	CSV ファイルが保存されている OSS バケットの名前。	examplebucket
objects	CSV ファイルと OSS フォルダをサポートします。 objects パラメーターは、複数のファイルまたはフォルダを含むことができる配列です。注意: フォルダを指定する場合、名前はスラッシュ (/) で終わる必要があります。 `test_dir/` などのフォルダを指定すると、外部テーブルは指定されたバケット内のそのフォルダにあるすべての CSV ファイルに関連付けられます。制限: すべてのタイプのファイルの総数は 100 を超えることはできません。	'user.csv' 'test_dir/' 'user.csv','test_dir/','my_dir/'
type	値は oss に固定されています。これは、外部ストレージタイプが OSS であることを示します。	oss

外部テーブルが定義されていることを確認します。
次のステートメントを実行します。返された結果が以前に定義したテーブルの内容と一致する場合、外部テーブルは正常に定義されています。このステートメントでは、user_meta1 は定義した外部テーブルです。実際の状況に応じて置き換えてください。
```
* | select * from user_meta1
```
たとえば、結果に userid、nick、gender、province、age などのフィールドとそれに対応するデータが含まれている場合、外部テーブルは正常に定義されています。
Logstore と OSS 外部テーブルで JOIN クエリを実行します。
次のクエリ文を実行して、SLS のログ ID と OSS ファイル内の userid を関連付け、ログ情報を補完します。このステートメントでは、test_accesslog は Logstore 名、l は Logstore エイリアス、user_meta1 は定義した外部テーブルです。これらを実際の値に置き換えてください。
```
* | select * from test_accesslog l join user_meta1 u on l.userid = u.userid
```
JOIN クエリの例：
- 性別ごとのアクセスリクエスト数をカウントします。
```
* | 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
```