Simple Log Service (SLS) Logstore と MySQL を結合した連合クエリ分析 - Simple Log Service

Simple Log Service (SLS) の Logstore データと MySQL データベースを結合し、連合クエリ分析を実行します。本トピックでは、ゲーム会社のシナリオを例として使用します。

前提条件

Logstore にログが収集されていること。詳細については、「データ収集」をご参照ください。
ログフィールドのインデックスが作成されていること。詳細については、「インデックスの作成」をご参照ください。
利用可能な MySQL データベースがあること。詳細については、「データベースとアカウントの作成」をご参照ください。

シナリオ

あるゲーム会社には、SLS がリアルタイムで収集するユーザーのゲームログ (アクション、ターゲット、体力ポイント、魔力ポイント、ネットワーク状態、支払い、クリック位置、ステータスコード、ユーザー ID) と、MySQL データベースに保存されているユーザーメタデータ (性別、登録時間、地域) という 2 種類のデータがあります。同社は、これらのデータセットを結合して、ゲーム運営を最適化したいと考えています。

SLS は、Logstore と、MySQL データベースや OSS バケットなどの ExternalStore との連合クエリをサポートしています。SQL の JOIN を使用してゲームログとユーザーメタデータを結合し、ユーザー指標を分析します。結果を ExternalStore に書き戻すこともできます。 Scenario

操作手順

MySQL データベースに、ユーザープロパティ用のテーブルを作成します。

join_meta という名前のテーブルを作成し、ユーザー ID、ニックネーム、性別、年齢、登録時間、アカウント残高、登録地域を格納します。

CREATE TABLE `join_meta` ( 
  `uid` int(11) NOT NULL DEFAULT '0', 
  `user_nick` text, 
  `gender` tinyint(1) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `balance` float DEFAULT NULL, 
  `region` text, PRIMARY KEY (`uid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ホワイトリストに IP アドレスを追加します。

RDS for MySQL データベース
CIDR ブロック 100.104.0.0/16、11.194.0.0/16、および 11.201.0.0/16 をホワイトリストに追加します。詳細については、「IP アドレスホワイトリストの構成」をご参照ください。
ECS インスタンス上の自己管理 MySQL データベース
100.104.0.0/16、11.194.0.0/16、および 11.201.0.0/16 CIDR ブロックからのアクセスを許可するセキュリティグループルールを追加します。詳細については、「セキュリティグループルールの追加」をご参照ください。
AnalyticDB for MySQL データベース
CIDR ブロック 100.104.0.0/16、11.194.0.0/16、および 11.201.0.0/16 をホワイトリストに追加します。詳細については、「ホワイトリストの構成」をご参照ください。

ExternalStore を作成します。

CLI がインストールされているサーバーにログインし、touch コマンドを実行して設定ファイル /home/shell/config.json を作成します。次のスクリプトを config.json ファイルに追加します。region、vpc-id、host、port、username、password、db、table の値を実際の値に置き換えます。

{
    "externalStoreName":"sls_join_meta_store",
    "storeType":"rds-vpc",
    "parameter":{
        "region":"cn-qingdao",
        "vpc-id":"vpc-m5eq4irc1pucp*******",
        "host":"rm-bp1******rm76.mysql.rds.aliyuncs.com",
        "port":"3306",
        "username":"user",
        "password":"****",
        "db":"scmc",
        "table":"join_meta"
    }
}

パラメーター	説明
`externalStoreName`	ExternalStore の名前。すべて小文字にする必要があります。
`storeType`	固定値： `rds-vpc`
`region`	データベースが所在するリージョン。 RDS MySQL データベースの場合、region を RDS インスタンスが配置されているリージョンに設定します。 ADB MySQL データベースの場合、region を ADB インスタンスが配置されているリージョンに設定します。データベースが Virtual Private Cloud (VPC) 内の ECS インスタンス上のセルフマネージド MySQL データベースである場合、region を ECS インスタンスが配置されているリージョンに設定します。重要データベースインスタンス (RDS、ADB、または ECS) は、SLS プロジェクトと同じリージョンにある必要があります。
`vpc-id`	データベースの VPC ID。 VPC 内の RDS MySQL データベースの場合、vpc-id を VPC の ID に設定します。 VPC 内の ADB MySQL データベースの場合、vpc-id を VPC の ID に設定します。 VPC 内の ECS インスタンス上のセルフマネージド MySQL データベースの場合、vpc-id を VPC の ID に設定します。
`host`	データベースが VPC 内にある場合、インスタンスの IP アドレスが変更されると (移行後など)、外部テーブルへのアクセスが失敗することがあります。バックエンドは作成時にホストを IP アドレスに解決し、自動的に更新しません。この問題が発生した場合は、外部テーブルを更新または再作成してください。 VPC 内の RDS MySQL データベースの場合、host を RDS インスタンスの内部エンドポイント (内部ドメイン名またはプライベート IP アドレス) に設定します。 VPC 内の ADB MySQL データベースの場合、host を ADB インスタンスの内部エンドポイント (内部ドメイン名またはプライベート IP アドレス) に設定します。 VPC 内の ECS インスタンスでホストされているセルフマネージド MySQL データベースの場合、host を ECS インスタンスのプライベート IP アドレスに設定します。データベースがインターネット経由でアクセス可能な場合は、このパラメーターをそのパブリックエンドポイントまたはパブリック IP アドレスに設定します。
`port`	データベースのポート番号。 RDS MySQL データベースの場合、port を RDS インスタンスのポート番号に設定します。 ADB MySQL データベースの場合、port を ADB インスタンスのポート番号に設定します。 ECS インスタンス上のセルフマネージド MySQL データベースの場合、port を ECS インスタンス上の MySQL データベースのサービスポートに設定します。
`username`	データベースアクセス用のユーザー名。
`password`	指定されたユーザー名のパスワード。
`db`	データベース名。
`table`	データベースのテーブル名。サポートされている形式： `table_name`。例： `test` `schema_name.table_name`。例： `public.test`

次のコマンドを実行して ExternalStore を作成します。<project-name> をお使いのプロジェクト名に置き換えてください。
```
aliyunlog log create_external_store --project_name=<project-name> --config="file:///home/shell/config.json" 
```

JOIN 構文を使用して連合クエリを実行します。
1. Log Serviceコンソールにログインします。
2. [プロジェクト] セクションで、管理するプロジェクトをクリックします。
3. [ログストレージ] > [ログストア] タブで、管理するログストアをクリックします。
4. クエリおよび分析ステートメントを実行します。
  ログの userid フィールドとデータベースの uid フィールドをキーに、Logstore と MySQL データベースを結合します。
  - アクティブユーザーの性別分布を分析します。
```
* | 
select 
  case gender when 1 then 'Male' else 'Female' end as gender, 
  count(1) as pv 
from log l join sls_join_meta_store u on l.userid = u.uid 
group by gender 
order by pv desc
```
  - リージョン別のユーザーアクティビティを分析します。
```
* | 
select region , count(1) as pv 
from log l join sls_join_meta_store u on l.userid = u.uid 
group by region 
order by pv desc
```
  - 性別ごとの支出パターンを分析します。
```
* | 
select 
  case gender when 1 then 'Male' else 'Female' end as gender, 
  sum(money) as money 
from log l join sls_join_meta_store u on l.userid = u.uid 
group by gender 
order by money desc
```

クエリ結果を MySQL データベースに保存します。

MySQL データベースに、5分単位のページビュー (PV) 数を格納する report という名前のテーブルを作成します。
```
CREATE TABLE `report` ( 
  `minute` bigint(20) DEFAULT NULL, 
  `pv` bigint(20) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
手順 3 の説明に従って、report テーブル用の ExternalStore を作成します。

Logstore のクエリ・分析ページで、次のクエリを実行して分析結果を report テーブルに保存します。このクエリでは、sls_report_store は report テーブルの ExternalStore の名前です。

* | insert into sls_report_store select __time__- __time__ % 300 as min, count(1) as pv group by min

その後、MySQL データベースで結果を確認できます。

mysql> select * from report;
+------------+-------+
| minute     | pv    |
+------------+-------+
| 1526448600 |  3000 |
| 1526448540 |  9900 |
| 1526448780 |  3100 |
| 1526448480 |  5400 |
| 1526448720 |  3000 |
| 1526448960 |  3000 |
| 1526448900 |  3000 |
| 1526449080 |  3000 |
| 1526449140 |  3000 |
| 1526448660 |  2900 |
| 1526449260 |  3000 |
+------------+-------+

前提条件

シナリオ

操作手順

RDS for MySQL データベース

ECS インスタンス上の自己管理 MySQL データベース

AnalyticDB for MySQL データベース