TiDB Cloudを使ってGitHubイベントを10分で解析 #SQL

TiDBは、水平方向のスケーラビリティ、高可用性、強力な一貫性を備えたオープンソースの分散型NewSQLデータベースです。また、HTAP（Hybrid Transactional and Analytical）機能により、OLTPとOLAPが混在したワークロードを同時に処理することが可能です。
TiDB Cloudは、フルマネージドDatabase-as-a-Service（DBaaS）です。TiDBの優れた点をすべてクラウドに取り込み、データベースの煩雑さではなく、アプリケーションに集中できるようにします。

このチュートリアルでは、 2022年1月1日に発生したすべてのGitHubイベントに基づいたサンプルデータを提供し、TiDB Cloudを使って10分以内にこのデータを分析する方法について説明します。

TiDB Cloudアカウントの登録（無料）

TiDB Cloudのアカウント登録（無料）はこちらです。
アカウントにログインしてください。

TiDB Developer Tierのクラスタを作成する（無料）

アカウントを登録すると、TiDB Developer Tierのクラスタを無料で作成することができます。

INFO：
クラスタとは、データを保存するためのデータベースのことです。

Get Started for Free をクリックして、無料クラスタの作成を開始します。
Create a Clusterページで、クラスタ名とルートパスワードを設定します。
クラウドプロバイダーがAWSであることを確認し、クラスタを作成するためにUS-West-2（Oregon）リージョンを選択する必要があります（これは必須です）。
クラスタの階層は、デフォルトではS1.devです。
Submitをクリックします。TiDB Cloud クラスタは 1〜 3 分で作成されます。

NOTE：Developer Tierは1年間無料です。

TiDB Cloudクラスタにデータをインポートする

データをインポートする
クラスタの準備ができたら、サンプルデータをクラスタにインポートする作業を開始します。

INFO：
SQLファイル内のcreate database文やcreate table文をマージしていますので、ご自身でデータベースやテーブルを作成する必要はありません。テーブルスキーマを知りたい場合は、次のステップの後で、desc gharchive_devを確認することができます。

1.Active Clusterページで自分のクラスタ名をクリックし、クラスタの詳細ページに移動します。
2.Active ClustersページのImportボタンをクリックし、Data Import Taskページに移動します。

3.以下の値をコピーし、データインポートタスクページのBucket URLとRole-ARNの空欄にそれぞれペーストしてください。

Bucket URL：

s3://tidbcloud-samples/gharchive/

Role-ARN：

arn:aws:iam::385595570414:role/import-sample-access

4.Bucket regionにUS West （Oregon）を選択します。
5.Data Formatは、[TiDB Dumpling] にチェックを入れます。
6.Target DatabaseセクションのPassword欄に、クラスタのパスワードを入力します。

7.Data Import Taskページですべての項目に入力したら、このページの下にある [Import] ボタンをクリックし、システムがデータのインポートを完了するまでしばらくお待ちください。

データの準備ができたかどうかを確認するためにウェブシェルを使用

TiDB Cloudでは、データベースをオンライン接続するためのウェブシェルが用意されています。

1.クラスタへのデータインポートに成功したら、[Exit] ボタンをクリックします。
2.Active Clusterページでクラスタ名をクリックし、クラスタの詳細ページを表示します。
3.[Connect] ボタンをクリックすると、Connect to TiDBパネルがポップアップ表示されます。
4.Web SQL Shell --> Open SQL Shellを選択します。
5.次に、以下の画像のようにクラスタのパスワードを入力します。

カラムストレージレプリカを設定します。TiFlash （オプションですが、SQLを高速化できます！）

TiFlashは、TiDB / TiDB CloudをHTAPデータベースとし、OLTPとOLAPワークロードを同時に処理することができる重要なコンポーネントです。
ここでは、TiDB Cloud上で以下のSQLコマンドを試し、リアルタイム分析を簡単に体験することができます。
1.以下のSQLステートメントを実行します。

use gharchive_dev;
ALTER TABLE github_events SET TIFLASH REPLICA 1;

2.TiFlashレプリカの設定には時間がかかりますので、以下のSQLステートメントを使って、手順が完了したかどうかを確認することができます。

SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'gharchive_dev' and TABLE_NAME = 'github_events';

3.以下のような結果が得られたら、手順は完了したことになります。

mysql> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'gharchive_dev' and TABLE_NAME = 'github_events';
+---------------+---------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA  | TABLE_NAME    | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+---------------+---------------+----------+---------------+-----------------+-----------+----------+
| gharchive_dev | github_events |       68 |             1 |                 |         1 |        1 |
+---------------+---------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.27 sec)

mysql>

ヒント：
テーブルスキーマを知りたい場合は、show create table tbl_nameを使ってその情報を取得することができます。

2022年の最初の1時間（2022-01-01 00:00:00 から 2022-01-01 00:59:59 まで）に発生したすべてのGitHubイベントというサンプルデータをインポートしたので、そのデータをもとに SQL コマンドで任意のクエリを作成することができるようになりました。

合計でいくつのイベントが発生しましたか？

次のSQLステートメントを実行して、イベントの総数を検索します。

SELECT count(*) FROM github_events;

どのリポジトリが最もスターを獲得しましたか？

次のステートメントを実行して、最もスターが多いリポジトリを検索クエリします。

  SELECT repo_name, count(*) AS events_count
    FROM github_events
   WHERE type = 'WatchEvent' /* Yes, `WatchEvent` means star */
GROUP BY 1
ORDER BY 2 DESC
   LIMIT 20;

小テスト

TiDB Cloudを使って分析を行う方法を練習するための小テストを用意しました。

Q：2022年の最初の1時間にロボットアカウント以外で最もアクティブなコントリビューターは誰でしょうか？

クリックすると回答が表示されます↓

ここをクリック

  SELECT actor_login, 
         count(*) AS events_count
    FROM github_events
   WHERE actor_login NOT LIKE '%bot%'
GROUP BY 1
ORDER BY 2 DESC 
   LIMIT 20

詳細については、以下の動画をご覧ください。

INFO：
OSS Insightの仕組みの詳細
OSS InsightをサポートするためにTiDBを選んだ理由をご覧ください。
Data Preparation for Analyticsでも、GitHubの膨大なデータをどのように扱っているかがわかります。

PingCAPでは、定期的にウェビナーを開催しています。最新のトピックはこちらからご覧ください。
また、2022年7月7日にユーザー企業様の体験を起点としたリアルとオンラインのハイブリッドイベント「TiDB User Day」を開催します。大手ユーザー企業の採用事例やパネルディスカッションを予定しておりますので、ぜひお気軽にお申し込みください。なお、イベントに登録すると先着200名様にAmazonギフト券1,000円分をプレゼントします。