Logstore と MySQL データベースの関連付けによるクエリと分析 - SLS

本トピックでは、Logstore を MySQL データベースと関連付けてクエリおよび分析を実行する方法について説明します。例として、あるゲーム会社のログを用います。

前提条件

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

背景情報

A 社はゲーム会社であり、ユーザーゲームログおよびユーザーメタデータという 2 種類のデータを保有しています。Simple Log Service (SLS) では、ユーザーゲームログをリアルタイムで収集できます。ユーザーゲームログには、操作、対象、ヒットポイント（HP）、マジックポイント（MP）、ネットワーク、支払方法、クリック位置、状態コード、ユーザー ID などのイベント情報が含まれます。ユーザーメタデータには、性別、登録日時、リージョンなどのユーザー情報が含まれます。通常、ユーザーメタデータはログに表示できないため、データベースに格納されます。A 社では、ユーザーゲームログとユーザーメタデータを関連付けてアソシエーション分析を行い、最適な運用計画を策定したいと考えています。

SLS のクエリおよび分析エンジンでは、Logstore を外部ストアと関連付けてクエリおよび分析を実行できます。外部ストアには、MySQL データベースおよび Object Storage Service (OSS) バケットが含まれます。ユーザー属性に関連するメトリックを分析するには、SQL JOIN 構文を用いてユーザーゲームログとユーザーメタデータを関連付けます。また、分析結果を外部ストアに書き込んで処理することも可能です。背景信息

操作手順

MySQL データベースにユーザー属性テーブルを作成します。

ユーザー ID、ユーザー名、性別、年齢、残高、登録日時、登録リージョンを格納するデータテーブル join_meta を作成します。

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

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

外部ストアを作成します。

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

リージョンの詳細は以下のとおりです：

ApsaraDB RDS for MySQL データベースを使用する場合、region パラメーターを RDS インスタンスのリージョンに設定します。
ADB for MySQL データベースを使用する場合、region パラメーターを ADB インスタンスのリージョンに設定します。
VPC 内の ECS インスタンス上で動作する自己管理 MySQL データベースを使用する場合、region パラメーターを ECS インスタンスのリージョンに設定します。

重要

RDS インスタンス、ADB インスタンス、または ECS インスタンスは、Simple Log Service プロジェクトと同じリージョンに配置されている必要があります。

vpc-id

VPC の ID です。

VPC 内の ApsaraDB RDS for PostgreSQL インスタンスを使用する場合、vpc-id を VPC ID に設定します。
VPC 内の ADB for PostgreSQL データベースを使用する場合、vpc-id を ADB インスタンスを含む VPC の ID に設定します。
VPC 内の Alibaba Cloud Hologres データベースを使用する場合、vpc-id を VPC ID に設定します。

host

データベースのアドレスです。

VPC 内では、外部テーブルを作成後にデータベースインスタンスの IP アドレスが変更された場合（例：データベースインスタンスの移行時など）、外部テーブルへのアクセスに影響が出る可能性があります。これは、構成で内部エンドポイントを使用している場合でも同様です。理由は、外部テーブル作成時にバックエンドがドメイン名を自動的に IP アドレスに解決し、その IP アドレスをバックエンド構成に保存するためです。ドメイン名に対応する IP アドレスは自動的にリフレッシュされません。この場合、外部テーブルを更新または再作成する必要があります。

VPC 内の ApsaraDB RDS for MySQL データベースを使用する場合、host パラメーターを RDS インスタンスの内部アドレス（内部エンドポイントまたはプライベート IP アドレス）に設定します。
VPC 内の ADB for MySQL データベースを使用する場合、host パラメーターを ADB インスタンスの内部アドレス（内部エンドポイントまたはプライベート IP アドレス）に設定します。
VPC 内の ECS インスタンス上で動作する自己管理 MySQL データベースを使用する場合、host パラメーターを ECS インスタンスのプライベート IP アドレスに設定します。
データベースがパブリックネットワーク経由でアクセス可能な場合、このパラメーターをパブリックエンドポイントまたはパブリック IP アドレスに設定します。

port

ポート番号の詳細は以下のとおりです：

ApsaraDB RDS for MySQL データベースを使用する場合、port パラメーターを RDS インスタンスのポート番号に設定します。
ADB for MySQL データベースを使用する場合、port パラメーターを ADB インスタンスのポート番号に設定します。
ECS インスタンス上で動作する自己管理 MySQL データベースを使用する場合、port パラメーターを ECS インスタンス上の MySQL のサービスポートに設定します。

username

データベースアカウントのユーザー名です。

password

データベースアカウントのパスワードです。

db

データベースの名称です。

table

データベーステーブルの名称です。以下のフォーマットがサポートされています：

table_name（例：test）
schema_name.table_name（例：public.test）

SQL JOIN 構文を用いて関連クエリおよび分析を実行します。
1. Simple Log Service コンソールにログインします。
2. プロジェクト一覧から、対象のプロジェクトをクリックします。
3. ログストレージ > Logstore タブで、対象の Logstore をクリックします。
4. クエリ文を実行します。
  クエリ文内で、ログの userid フィールドとデータベーステーブルの uid フィールドを指定します。
  - 性別ごとのアクティブユーザーのディストリビューションを分析します。
```
* | 
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 データベースに保存します。
1. MySQL データベースに、1 分あたりのページビュー（PV）数を格納する report データテーブルを作成します。
```
CREATE TABLE `report` ( 
  `minute` bigint(20) DEFAULT NULL, 
  `pv` bigint(20) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
2. report テーブル用の外部ストアを作成します。詳細については、「ステップ 3」をご参照ください。
3. Logstore のクエリおよび分析ページで、以下のクエリ文を実行して結果を report テーブルに保存します。sls_report_store は外部ストアの名称です。
```
* | insert into sls_report_store select __time__- __time__ % 300 as min, count(1) as pv group by min
```
  結果が保存された後、MySQL データベースで結果を確認できます。

前提条件

背景情報

操作手順

RDS for MySQL データベース

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

ADB for MySQL データベース

例