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

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

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

### TiDB Cloudアカウントの登録（無料）
TiDB Cloudのアカウント登録（無料）は[こちら](https://tidbcloud.com/signup?utm_source=ossinsight&utm_medium=referral)です。
アカウントに[ログイン](https://tidbcloud.com/?utm_source=ossinsight&utm_medium=referral)してください。
### TiDB Developer Tierのクラスタを作成する（無料）
アカウントを登録すると、TiDB Developer Tierのクラスタを無料で作成することができます。

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

1. Get Started for Free をクリックして、無料クラスタの作成を開始します。
![dev-tier-5c6c7947591b242e9633e7383ee0dea4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1487691/3dbb8ba7-31c7-572d-be29-6c780347b3e2.png)

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

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

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

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

1.Active Clusterページで自分のクラスタ名をクリックし、クラスタの詳細ページに移動します。
2.Active ClustersページのImportボタンをクリックし、Data Import Taskページに移動します。
![import-e518a06648e00950b7016c1e110d2fec.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1487691/f884d2ca-8cf2-00de-4024-b6ae560401f8.png)


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

Bucket URL：
```SQL
s3://tidbcloud-samples/gharchive/
```
Role-ARN：
```SQL
arn:aws:iam::385595570414:role/import-sample-access
```

4.Bucket regionにUS West （Oregon）を選択します。
5.Data Formatは、[TiDB Dumpling] にチェックを入れます。
6.Target DatabaseセクションのPassword欄に、クラスタのパスワードを入力します。
![fill-54fa3814373b30203c4bfff48c6bd2c6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1487691/6d96dc83-549c-d15f-37dd-4de78febdfa5.png)

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.次に、以下の画像のようにクラスタのパスワードを入力します。
![web-shell-367d4333b6b67f27dba4b1c2097a80a6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1487691/854e9118-6aed-952f-ef9f-eeb94f719457.png)

### カラムストレージレプリカを設定します。TiFlash （オプションですが、SQLを高速化できます！）
[TiFlash](https://docs.pingcap.com/tidb/stable/tiflash-overview?utm_source=ossinsight&utm_medium=referral)は、TiDB / TiDB CloudをHTAPデータベースとし、OLTPとOLAPワークロードを同時に処理することができる重要なコンポーネントです。
ここでは、TiDB Cloud上で以下のSQLコマンドを試し、リアルタイム分析を簡単に体験することができます。
1.以下のSQLステートメントを実行します。
```SQL
use gharchive_dev;
ALTER TABLE github_events SET TIFLASH REPLICA 1;
```
2.TiFlashレプリカの設定には時間がかかりますので、以下のSQLステートメントを使って、手順が完了したかどうかを確認することができます。
```SQL
SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'gharchive_dev' and TABLE_NAME = 'github_events';
```
3.以下のような結果が得られたら、手順は完了したことになります。
```SQL
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>
```
:::note warn
ヒント：
テーブルスキーマを知りたい場合は、show create table tbl_nameを使ってその情報を取得することができます。
:::

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

#### 合計でいくつのイベントが発生しましたか？
次のSQLステートメントを実行して、イベントの総数を検索します。
```SQL
SELECT count(*) FROM github_events;
```
#### どのリポジトリが最もスターを獲得しましたか？
次のステートメントを実行して、最もスターが多いリポジトリを検索クエリします。
```SQL
  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時間にロボットアカウント以外で最もアクティブなコントリビューターは誰でしょうか？
クリックすると回答が表示されます↓
<details><summary>ここをクリック</summary>

```SQL
  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
```
</details>

### 詳細については、以下の動画をご覧ください。
https://youtu.be/9h-p9eqsJzs


:::note info
INFO：
OSS Insightの仕組みの詳細
[OSS InsightをサポートするためにTiDBを選んだ理由](https://pingcap.co.jp/why-we-choose-tidb-to-support-oss-insight/?utm_source=qiita&utm_medium=qiita_blog)をご覧ください。
[Data Preparation for Analytics](https://ossinsight.io/blog/how-it-works)でも、GitHubの膨大なデータをどのように扱っているかがわかります。
:::

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

https://youtu.be/Ia66MxXuGaI