検索

CLI で覚える Google BigQuery

t2sy

Tech

2020.1.30

Topics

こんにちは。データサイエンスチームの t2sy です。
Google BigQuery は、Google が提供する高スケーラビリティでコスト効率に優れたサーバーレス型のクラウド データウェアハウス (DWH) です。BigQuery 以外のクラウド DWH は AWS が提供する Amazon Redshift や Microsoft が提供する Azure Synapse Analytics などが挙げられます。

BigQuery を操作する方法は Cloud Console の Web UI、bq コマンドラインツール、REST API、クライアントライブラリの4つがあります。この記事では、bq コマンドラインツールで BigQuery を操作し、使い方を確認してみます。内容としては初学者向けです。

今回、使用する Google Cloud Platform（GCP）のサービスは Google Cloud Storage (GCS) と BigQuery です。

BigQuery はクエリとストレージに対して課金が発生しますが、特定の上限までの無料枠があります。詳しくは、BigQuery の料金をご覧下さい。

全体の流れは以下です。

  1. データの取得
  2. データセットとテーブルの作成
  3. データをテーブルに読み込み
  4. クエリの実行
  5. テーブルのエクスポート

データの取得

クエリの実行を素早く試したい方は、BigQuery の一般公開データセットを使うのが簡単です。
今回は、一般公開データセットは使用せず、MovieLens 20M Dataset を用います。
MovieLens 20M Dataset は、GroupLens が公開しているユーザの映画に対する評価を集めたデータセットです。138,493 人のユーザが計 27,278 本の映画に対して 5段階 (1-5) で評価付けした 20,000,263 件の評価が含まれています。期間は 1995-01-09 から 2015-03-31 です。

MovieLens 20M Dataset をダウンロードし、GCS を操作するためのコマンドラインツールである gsutil を使用し GCS にアップロードするところから開始します。

GCP のコンソールを開き、Cloud Shell を起動します。MovieLens 20M Dataset をダウンロードし、チェックサムの一致を確認、そして展開します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ wget http://files.grouplens.org/datasets/movielens/ml-20m.zip
 
$ md5sum ml-20m.zip
cd245b17a1ae2cc31bb14903e1204af3  ml-20m.zip
 
$ unzip ml-20m.zip
Archive:  ml-20m.zip
   creating: ml-20m/
  inflating: ml-20m/genome-scores.csv
  inflating: ml-20m/genome-tags.csv
  inflating: ml-20m/links.csv
  inflating: ml-20m/movies.csv
  inflating: ml-20m/ratings.csv
  inflating: ml-20m/README.txt
  inflating: ml-20m/tags.csv

gsutil mb コマンドで GCS にバケットを作成します。

1
2
3
4
5
6
7
8
9
$ GCS_BUCKET_NAME="techblog-bq-ml-20m"
$ gsutil version
gsutil version: 4.47
 
$ gsutil mb gs://$GCS_BUCKET_NAME/
Creating gs://techblog-bq-ml-20m/...
 
$ gsutil ls
gs://techblog-bq-ml-20m/

gsutil cp コマンドで、MovieLens 20M Dataset を作成したバケットにアップロードします。

1
2
3
4
5
6
7
8
9
10
$ gsutil cp -r ml-20m gs://$GCS_BUCKET_NAME/
Copying file://ml-20m/README.txt [Content-Type=text/plain]...
Copying file://ml-20m/ratings.csv [Content-Type=text/csv]...
Copying file://ml-20m/links.csv [Content-Type=text/csv]...
Copying file://ml-20m/tags.csv [Content-Type=text/csv]...
Copying file://ml-20m/genome-scores.csv [Content-Type=text/csv]...
Copying file://ml-20m/genome-tags.csv [Content-Type=text/csv]...
Copying file://ml-20m/movies.csv [Content-Type=text/csv]...
\ [7 files][835.0 MiB/835.0 MiB]    1.2 MiB/s
Operation completed over 7 objects/835.0 MiB.

データセットとテーブルの作成

データを BigQuery に読み込む前に、データセットとテーブルを作成する必要があります。

  • データセット: テーブルとビューへのアクセスを整理して制御するために使用される最上位のコンテナ。テーブルやビューはデータセットに属する。
  • テーブル: 個々のレコードは行の形式にまとめられ、各レコードは列 (フィールド) で構成される。すべてのテーブルは、列名、データ型、その他の情報を記述するスキーマによって定義される。

bq コマンドラインツールを使用し BigQuery にデータセットと2つのテーブルを作成します。

1
2
$ bq version
This is BigQuery CLI 2.0.52

最初に、bq mk コマンドで dataset フラグを指定し、データセットを作成します。データセット名は techblog_ml_20m としています。

1
2
3
4
$ bq mk --dataset \
    --default_table_expiration 36000 \
    --description "MovieLens 20M movie ratings." \
    techblog_ml_20m

次に、bq mk コマンドで table フラグを指定し、テーブルを作成します。今回は ratings と movies の2つのテーブルを作成してみます。

ratings テーブルを作成します。テーブルのスキーマは JSON 形式または以下のようにインラインで指定することができます。

1
2
3
4
5
6
7
$ bq mk \
    --table \
    --expiration 36000 \
    --description "This is ratings table." \
    --label organization:development \
    techblog_ml_20m.ratings \
    userId:INT64,movieId:INT64,rating:FLOAT,timestamp:INT64

同様に、movies テーブルを作成します。

1
2
3
4
5
6
7
$ bq mk \
    --table \
    --expiration 36000 \
    --description "This is movies table." \
    --label organization:development \
    techblog_ml_20m.movies \
    movieId:INT64,title:STRING,genres:STRING

bq show コマンドで、作成した2つのテーブルとそのスキーマを確認します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ bq show techblog_ml_20m.ratings
Table datascience-bigquery-exp:techblog_ml_20m.ratings
   Last modified            Schema            Total Rows   Total Bytes     Expiration      Time Partitioning   Clustered Fields            Labels
 ----------------- ------------------------- ------------ ------------- ----------------- ------------------- ------------------ --------------------------
  24 Jan 11:43:50   |- userId: integer        0            0             24 Jan 21:43:49                                          organization:development
                    |- movieId: integer
                    |- rating: float
                    |- timestamp: integer
 
$ bq show techblog_ml_20m.movies
Table datascience-bigquery-exp:techblog_ml_20m.movies
   Last modified          Schema          Total Rows   Total Bytes     Expiration      Time Partitioning   Clustered Fields            Labels
 ----------------- --------------------- ------------ ------------- ----------------- ------------------- ------------------ --------------------------
  24 Jan 11:44:18   |- movieId: integer   0            0             24 Jan 21:44:18                                          organization:development
                    |- title: string
                    |- genres: string

データをテーブルに読み込み

bq load コマンドを用いて GCS 上にアップロードした CSV データを BigQuery のテーブルに読み込みます。

ratings.csv を ratings テーブルに読み込みます。skip_leading_rows フラグに 1 を指定し CSV のヘッダをスキップします。また autodetect フラグを指定し、スキーマの自動検出を有効化します。

1
2
3
4
5
6
$ bq load \
    --source_format=CSV \
    --skip_leading_rows 1 \
    --autodetect \
    techblog_ml_20m.ratings \
    gs://$GCS_BUCKET_NAME/ml-20m/ratings.csv

同様に movies.csv を movies テーブルに読み込みます。

1
2
3
4
5
6
$ bq load \
    --source_format=CSV \
    --skip_leading_rows 1 \
    --autodetect \
    techblog_ml_20m.movies \
    gs://$GCS_BUCKET_NAME/ml-20m/movies.csv

クエリの実行

テーブルにデータが読み込まれたため、BigQuery でクエリを実行する準備が整いました。

今回は例として、多くのユーザが高い評価を与えた映画を調べてみます。ただし、100件を超える評価が付けられた映画に絞ります。
平均評価値の高い順に、映画のID、タイトル、平均評価値、評価件数の列を出力する SQL クエリが以下です。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
  ranking.movieId,
  title,
  avgRating,
  cntRating
FROM (
  SELECT
    movieId,
    AVG(rating) AS avgRating,
    COUNT(rating) AS cntRating
  FROM
    `techblog_ml_20m.ratings` AS ratings
  GROUP BY
    movieId
  HAVING
    cntRating > 100) AS ranking
INNER JOIN
  `techblog_ml_20m.movies` AS movies
ON
  ranking.movieId = movies.movieId
ORDER BY
  avgRating DESC;

bq query コマンドで SQL クエリを実行します。destination_table フラグで出力先のテーブルを query_result テーブルに指定します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$ bq query \
    --destination_table techblog_ml_20m.query_result \
    --use_legacy_sql=false \
'SELECT
  ranking.movieId,
  title,
  avgRating,
  cntRating
FROM (
  SELECT
    movieId,
    AVG(rating) AS avgRating,
    COUNT(rating) AS cntRating
  FROM
    `techblog_ml_20m.ratings` AS ratings
  GROUP BY
    movieId
  HAVING
    cntRating > 100) AS ranking
INNER JOIN
  `techblog_ml_20m.movies` AS movies
ON
  ranking.movieId = movies.movieId
ORDER BY
  avgRating DESC;'

実行後、数秒でクエリ結果が出力されました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Waiting on bqjob_r1842e41df047993b_0000016fd7b21af2_1 ... (2s) Current status: DONE  
+---------+------------------------------------------------------------------------------------------------------+--------------------+-----------+
| movieId |                                                title                                                 |     avgRating      | cntRating |
+---------+------------------------------------------------------------------------------------------------------+--------------------+-----------+
|     318 | Shawshank Redemption, The (1994)                                                                     |  4.446990499637026 |     63366 |
|     858 | Godfather, The (1972)                                                                                |    4.3647321968323 |     41355 |
|      50 | Usual Suspects, The (1995)                                                                           |  4.334372207803254 |     47006 |
|     527 | Schindler's List (1993)                                                                              |  4.310175010988125 |     50054 |
|    1221 | Godfather: Part II, The (1974)                                                                       |  4.275640557704946 |     27398 |
|    2019 | Seven Samurai (Shichinin no samurai) (1954)                                                          |  4.274179657221598 |     11611 |
|     904 | Rear Window (1954)                                                                                   | 4.2713336007794105 |     17449 |
|    7502 | Band of Brothers (2001)                                                                              |  4.263182346109178 |      4305 |
|     912 | Casablanca (1942)                                                                                    |  4.258326830670663 |     24349 |
|     922 | Sunset Blvd. (a.k.a. Sunset Boulevard) (1950)                                                        |  4.256934865900388 |      6525 |
...

1位の『The Shawshank Redemption』(邦題: 『ショーシャンクの空に』) は私も好きな映画のひとつです。

オンデマンドクエリは、読み取られたバイト数に基づいて課金されるため、大規模なテーブルに対してクエリを実行する場合は、事前に読み取られるバイト数を見積もっておくと安心です。読み取られるバイト数は、CLI では bq query コマンドの dry_run フラグ、Web UI ではクエリ構文を検証するクエリ検証ツールを使用して見積もることができます。この見積もりを使用して、Google Cloud 料金計算ツール で費用を計算できます。

テーブルデータのエクスポート

bq extract コマンドで、得られたクエリ結果のテーブルを GCS にエクスポートしてみます。destination_format フラグにはエクスポートするデータの形式を指定します。

1
2
3
4
bq extract \
    --destination_format CSV \
    'techblog_ml_20m.query_result' \
    gs://$GCS_BUCKET_NAME/ml_20m_query_result.csv

gsutil ls コマンドで GCS に保存されていることを確認します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ gsutil ls gs://$GCS_BUCKET_NAME/
gs://techblog-bq-ml-20m/ml_20m_query_result.csv
gs://techblog-bq-ml-20m/ml-20m/
 
$ gsutil cat gs://techblog-bq-ml-20m/ml_20m_query_result.csv | head -n 10
movieId,title,avgRating,cntRating
318,"Shawshank Redemption, The (1994)",4.4469904996370211,63366
858,"Godfather, The (1972)",4.3647321968323105,41355
50,"Usual Suspects, The (1995)",4.3343722078032583,47006
527,Schindler's List (1993),4.3101750109881323,50054
1221,"Godfather: Part II, The (1974)",4.2756405577049348,27398
2019,Seven Samurai (Shichinin no samurai) (1954),4.2741796572215947,11611
904,Rear Window (1954),4.2713336007794158,17449
7502,Band of Brothers (2001),4.26318234610917,4305
912,Casablanca (1942),4.2583268306706712,24349

おわりに

この記事では、Google が提供するサーバーレス型のクラウド データウェアハウス (DWH) である Google BigQuery の操作を bq コマンドラインツールを使って確認しました。また、例として MovieLens 20M Dataset を BigQuery のテーブルに読み込み、多くのユーザが高い評価を与えた映画を調べました。
ご紹介したように BigQuery は SQL クエリを実行できますが、BigQuery ML では線形回帰やロジスティック回帰、k-means、訓練済みの TensorFlow モデルのインポートをサポートしており、SQL を用いて機械学習の機能を利用することもできます。

参考文献

[1] bq コマンドライン ツール リファレンス

t2sy

2016年11月、データサイエンティストとして中途入社。時系列分析や異常検知、情報推薦に特に興味があります。クロスバイク、映画鑑賞、猫が好き。

Recommends

こちらもおすすめ

Special Topics

注目記事はこちら

【資料ダウンロード】教育・公共機関向け定額チケットプラン

【資料ダウンロード】教育・公共機関向け定額チケットプラン

2019.12.18

AWS

【導入事例】データ分析基盤をAWSへ移行し、運用費用を9割削減

【導入事例】データ分析基盤をAWSへ移行し、運用費用を9割削減（株式会社エディア様）

2019.12.15

Column

 
:)