1. 概要
BigQuery は、Google が提供する低コスト、NoOps のフルマネージド分析データベースです。BigQuery では、インフラストラクチャを所有して管理することなく、テラバイト単位の大規模なデータでクエリを実行できます。また、データベース管理者も不要です。BigQuery では使い慣れた SQL が採用されており、従量課金制モデルでご利用いただけます。このような特徴を活かし、お客様は有用な情報を得るためのデータ分析に専念できます。
この Codelab では、BigQuery を使用して Wikipedia データセットを調べます。
学習内容
- BigQuery の使用方法
- 実際のデータセットを BigQuery に読み込む方法
- 大規模なデータセットの分析情報を取得するためのクエリの作成方法
必要なもの
- Google Cloud プロジェクト
- ブラウザ（Google Chrome、Firefox など）
アンケート
このチュートリアルの利用方法をお選びください。
<ph type="x-smartling-placeholder">Google Cloud の使用経験をどのように評価されますか。
<ph type="x-smartling-placeholder">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 コマンドライン ユーティリティを使用して直接読み込むことができます。load コマンドの一部として、ファイルのスキーマも記述します。
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
高度なオプションをいくつか使用して、ページビュー ファイルを読み込みました。
- ファイルを CSV ファイルとして解析する必要があることを示すには、
--source_format CSVを設定します。CSV がデフォルトの形式であるため、このステップは省略可能です。 --field_delimiter " "を設定すると、フィールドを 1 つのスペースで区切ることができます。--allow_jagged_rowsを設定して、列数が少ない行が含まれ、CSV ファイルの読み込み中のエラーを無視します。--quote ""を設定して、文字列が引用符で囲まれていないことを示します。- CSV ファイルの解析中に最大 3 つのエラーを無視するように、
--max_bad_records 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 にデータを読み込む方法を確認する。