Simple Log Service の Logstore と MySQL データベースを関連付けてクエリと分析を実行する方法 - Simple Log Service

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

前提条件

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

背景情報

A社は、ユーザーゲームログとユーザーメタデータの2種類のデータを持つゲーム会社です。Simple Log Service (SLS) は、ユーザーゲームログをリアルタイムで収集できます。ユーザーゲームログには、操作、ターゲット、ヒットポイント (HP)、マジックポイント (MP)、ネットワーク、支払方法、クリック位置、ステータスコード、ユーザー ID などのイベント情報が含まれています。ユーザーメタデータには、性別、登録時間、リージョンなどのユーザー情報が含まれます。通常、メタデータはログに表示できないため、ユーザーメタデータはデータベースに保存されます。A社は、ユーザーゲームログとユーザーメタデータの関連分析を行い、最適な運用計画を立てたいと考えています。

SLS のクエリ・分析エンジンを使用すると、Logstore を外部ストアに関連付けてクエリと分析を実行できます。外部ストアには、MySQL データベースや Object Storage Service (OSS) バケットなどがあります。ユーザープロパティに関連するメトリックを分析するには、SQL JOIN 構文を使用してユーザーゲームログとユーザーメタデータを関連付けます。分析結果を外部ストアに書き込んで処理することもできます。背景信息

操作手順

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

MySQL データベースのホワイトリストを作成します。
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 をホワイトリストに追加します。詳細については、「ホワイトリストの設定」をご参照ください。

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

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 インスタンスのリージョンに設定します。
AnalyticDB for MySQL データベースを使用する場合、region パラメーターを AnalyticDB for MySQL インスタンスのリージョンに設定します。
ECS インスタンス上の自己管理型 MySQL データベースを使用する場合、region パラメーターを ECS インスタンスのリージョンに設定します。

重要

RDS インスタンス、AnalyticDB for MySQL インスタンス、または ECS インスタンスは、SLS プロジェクトと同じリージョンにある必要があります。

vpc-id

データベースインスタンスが存在する VPC の ID。詳細は次のとおりです：

RDS PostgreSQL インスタンスが Virtual Private Cloud (VPC) 内にある場合、vpc-id をその VPC の ID に設定します。
Virtual Private Cloud (VPC) 内の ADB PostgreSQL データベースの場合、vpc-id をその VPC の ID に設定します。
Alibaba Cloud Hologres データベースが Virtual Private Cloud (VPC) 内にある場合、vpc-id をその VPC の ID に設定します。

host

データベースのアドレス。詳細は次のとおりです：

VPC 内で、外部テーブルを作成した後にデータベースインスタンスの IP アドレスが変更されると、外部テーブルへのアクセスに影響が出ます。たとえば、データベースインスタンスが移行された場合にこれが発生する可能性があります。この問題は、設定で内部エンドポイントを使用していても発生します。外部テーブルが作成されると、バックエンドはドメイン名を IP アドレスに解決し、その IP アドレスをバックエンド設定に保存します。ドメイン名に対応する IP アドレスは自動的に更新されません。この場合、外部テーブルを更新または再作成する必要があります。

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

port

ポート番号。詳細は次のとおりです：

ApsaraDB RDS for MySQL データベースを使用する場合、port パラメーターを RDS インスタンスのポート番号に設定します。
AnalyticDB for MySQL データベースを使用する場合、port パラメーターを AnalyticDB for MySQL インstance のポート番号に設定します。
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 データベースに report という名前のデータテーブルを作成し、1分あたりのページビュー (PV) 数を保存します。
```
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 データベース

AnalyticDB for MySQL データベース

例