BigQuery で Wikipedia データセットに対してクエリを実行する

1. 概要

BigQuery は、Google が提供する低コストで NoOps のフルマネージド分析データベースです。BigQuery では、インフラストラクチャを所有して管理したり、データベース管理者を配置したりすることなく、テラバイト単位の大規模なデータでクエリを実行できます。また、SQL が採用されており、従量課金制というメリットもあります。このような特徴を活かし、お客様は有用な情報を得るためのデータ分析に専念できます。

この Codelab では、BigQuery を使用して Wikipedia データセットを探索します。

学習内容

BigQuery の使用方法
実際のデータセットを BigQuery に読み込む方法
大規模なデータセットから分析情報を得るためのクエリを作成する方法

必要なもの

Google Cloud プロジェクト
ブラウザ（Google Chrome、Firefox など）

アンケート

このチュートリアルの利用方法をお選びください。

通読のみ

通読して演習を行う

Google Cloud のご利用経験について、いずれに該当されますか？

初心者

中級者

上級者

2. 設定と要件

BigQuery を有効にする

Google アカウントをまだお持ちでない場合は、アカウントを作成する必要があります。

Google Cloud コンソールにログインし、BigQuery に移動します。ブラウザに次の URL を入力して、BigQuery ウェブ UI を直接開くこともできます。

https://console.cloud.google.com/bigquery

利用規約に同意します。
BigQuery を使用する前に、プロジェクトを作成する必要があります。プロンプトに従って新しいプロジェクトを作成します。

プロジェクト名を選択し、プロジェクト ID をメモします。

プロジェクト ID は、すべての Google Cloud プロジェクトで一意の名前です。以降、このコードラボでは PROJECT_ID と呼びます。

この Codelab では、BigQuery サンドボックスの制限内の BigQuery リソースを使用します。請求先アカウントは必要ありません。後でサンドボックスの上限を削除する場合は、Google Cloud の無料トライアルに登録して請求先アカウントを追加できます。

次のセクションでは、Wikipedia データセットを読み込みます。

3. データセットを作成する

まず、プロジェクトに新しいデータセットを作成します。データセットは複数のテーブルで構成されます。

データセットを作成するには、[リソース] ペインでプロジェクト名をクリックし、[データセットを作成] をクリックします。

データセット ID として「lab」と入力します。

[データセットを作成] をクリックして、空のデータセットを作成します。

4. bq コマンドラインプログラムでデータを読み込む

Cloud Shell をアクティブにする

Cloud Console で、[Cloud Shell をアクティブにする] をクリックします。

Cloud Shell を初めて起動した場合は、その内容を説明する画面が（スクロールしなければ見えない位置に）表示されます。その場合は、[続行] をクリックしてください（以後表示されなくなります）。この中間画面は次のようになります。

すぐにプロビジョニングが実行され、Cloud Shell に接続されます。

この仮想マシンには、必要な開発ツールがすべて用意されています。仮想マシンは Google Cloud で稼働し、永続的なホームディレクトリが 5 GB 用意されているため、ネットワークのパフォーマンスと認証が大幅に向上しています。このコードラボでの作業のほとんどは、ブラウザまたは Chromebook から実行できます。

Cloud Shell に接続すると、すでに認証は完了しており、プロジェクトに各自のプロジェクト ID が設定されていることがわかります。

Cloud Shell で次のコマンドを実行して、認証されたことを確認します。

gcloud auth list

コマンド出力

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Cloud Shell で次のコマンドを実行して、gcloud コマンドがプロジェクトを認識していることを確認します。

gcloud config list project

コマンド出力

[core]
project = <PROJECT_ID>

上記のようになっていない場合は、次のコマンドで設定できます。

gcloud config set project <PROJECT_ID>

コマンド出力

Updated property [core/project].

BigQuery へのデータの読み込み

便宜上、Wikimedia のページビューデータセットの 2019 年 4 月 10 日のデータの一部が、Google Cloud Storage の gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz で利用できます。データファイルは GZip 形式の CSV ファイルです。このファイルは、bq コマンドラインユーティリティを使用して直接読み込むことができます。読み込みコマンドの一部として、ファイルのスキーマも記述します。

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

いくつかの高度なオプションを使用して、ページビューファイルを読み込みました。

--source_format CSV を設定して、ファイルを CSV ファイルとして解析する必要があることを示します。CSV はデフォルトの形式であるため、この手順は省略可能です。
--field_delimiter " " を設定して、フィールドの区切りに単一のスペースが使用されることを示します。
--allow_jagged_rows を設定して、列数が少ない行を含め、CSV ファイルの読み込み中にエラーを無視します。
文字列が引用符で囲まれていないことを示すには、--quote "" を設定します。
--max_bad_records 3 を設定して、CSV ファイルの解析中に最大 3 つのエラーを無視します。

bq コマンドラインの詳細については、ドキュメントをご覧ください。

5. データセットをプレビューする

BigQuery コンソールで、読み込んだテーブルのいずれかを開きます。

プロジェクトを開きます。
データセットを開きます。
テーブルを選択します。

テーブルのスキーマは [スキーマ] タブで確認できます。4. [詳細] タブに移動して、テーブル内のデータ量を確認します。

[プレビュー] タブを開くと、テーブルから選択された行が表示されます。

6. クエリを作成する

[クエリを新規作成] をクリックします。

クエリエディタが開きます。

2019 年 4 月 10 日の午後 2 時から午後 3 時までの Wikimedia の合計ビュー数を取得するには、次のクエリを作成します。

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

[実行] をクリックします。

数秒後に、結果が下部に表示され、処理されたデータ量も表示されます。

テーブルのサイズは 691.4 MB ですが、このクエリで処理されたデータ量は 123.9 MB です。BigQuery は、クエリで使用される列のバイト数のみを処理するため、処理されるデータの合計量はテーブルサイズよりも大幅に少なくなる可能性があります。クラスタリングとパーティショニングを使用すると、処理されるデータ量をさらに削減できます。

7. より高度なクエリ

Wikipedia のページビュー数を調べる

Wikimedia データセットには、すべての Wikimedia プロジェクト（Wikipedia、Wiktionary、Wikibooks、Wikiquotes など）のページビューが含まれています。WHERE ステートメントを追加して、クエリを英語版 Wikipedia ページのみに絞り込みます。

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

追加の列 wiki をクエリすることで、処理されるデータ量が 124 MB から 204 MB に増加していることに注目してください。

BigQuery は、CONTAINS、GROUP BY,、ORDER BY などの一般的な SQL 句と、多くの集計関数をサポートしています。また、正規表現を使用してテキストフィールドをクエリすることもできます。以下をお試しください。

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

複数のテーブルにまたがるクエリ

ワイルドカードテーブルを使用して、ユニオンを形成するテーブルの範囲を選択できます。

まず、次の 1 時間のページビューを新しいテーブルに読み込んで、クエリする 2 つ目のテーブルを作成します。

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

クエリエディタで、接頭辞として「pageviews_2019」が付いたテーブルをクエリして、読み込んだ両方のテーブルをクエリします。

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

_TABLE_SUFFIX 疑似列を使用して、テーブルをより選択的にフィルタリングできます。このクエリは、4 月 10 日に対応するテーブルに制限します。

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. クリーンアップ

必要に応じて、bq rm コマンドを使用して作成したデータセットを削除します。-r フラグを使用して、含まれているテーブルを削除します。

bq rm -r lab

9. 完了

BigQuery と SQL を使用して、実際の Wikipedia のページビューデータセットに対してクエリを実行しました。ペタバイト規模のデータセットをクエリできます。

その他の情報

BigQuery のサブレディットで、他の方が今どのように BigQuery を使用しているかがわかります。
BigQuery で利用可能な一般公開データセットを見つける。
データを BigQuery に読み込む方法を確認する。

BigQuery で Wikipedia データセットに対してクエリを実行する

1. 概要

学習内容

必要なもの

アンケート

このチュートリアルの利用方法をお選びください。

Google Cloud のご利用経験について、いずれに該当されますか？

2. 設定と要件

BigQuery を有効にする

3. データセットを作成する

4. bq コマンドライン プログラムでデータを読み込む

Cloud Shell をアクティブにする

BigQuery へのデータの読み込み

5. データセットをプレビューする

6. クエリを作成する

7. より高度なクエリ

Wikipedia のページビュー数を調べる

複数のテーブルにまたがるクエリ

8. クリーンアップ

9. 完了

その他の情報

4. bq コマンドラインプログラムでデータを読み込む