BigQuery初心者必見！使い方を実例を元に丁寧に解説！

2024.03.22

BigQueryGoogle Cloud技術ブログ

データ分析は、ビジネスの成長や意思決定に役立つ重要なスキルです。しかし、データ分析をしたことがない多くの人にとって、データ分析は難しく、複雑な作業に思えるかもしれません。

そこで、本記事ではデータ初心者向けに、Google Cloud が提供しているデータ分析サービスである BigQuery の

アカウント設定
プロジェクト作成
基本的な SQL クエリ
データの読み込み
実践的な分析例

まで、一つひとつ丁寧に解説します。さらに、コストを抑えつつセキュリティを確保する方法も紹介します。BigQuery 初心者でも、理解を深め、実践に役立てられる内容となっています。ぜひ最後までご覧ください。

1 BigQuery の基本概要と操作前の準備方法
2 BigQuery を操作する上でのクエリの基本
3 BigQuery の使い方を実践的なデータ分析で理解する
4 BigQuery の利用料金
5 BigQuery のコスト管理とセキュリティ
6 コストを抑えるための BigQuery のベストプラクティス
7 BigQuery のセキュリティ設定とアクセス管理
8 BigQuery の監査ログとモニタリング
9 エラーハンドリングとトラブルシューティング
10 まとめ

BigQuery の基本概要と操作前の準備方法

BigQuery は、データ分析をより高速かつ簡単に実行できる、革新的なツールです。しかし、その真価を理解し、効果的に活用するには、まず基本をしっかりと習得することが重要です。

ここでは、初めてのプロジェクトの立ち上げ方、そして BigQuery 環境でのナビゲーション方法について、解説します。

アカウント設定と最初のプロジェクト作成

BigQuery を使用するには、Google Cloud アカウントが必要です。Google Cloud アカウントを作成したら、以下の手順でプロジェクトを作成します。

Google Cloud コンソールにアクセスします。
左側のメニューから「 BigQuery 」を選択します。
画面左上の「プロジェクトを作成」をクリックします。
プロジェクト名と所在地を入力します。
「作成」をクリックします。

プロジェクト作成後、BigQuery API を有効にする必要があります。

Google Cloud コンソールで、左側のメニューから「 API とサービス」を選択します。
画面上部の検索バーに「 BigQuery 」と入力します。
検索結果から「 BigQuery API 」を選択します。
「有効にする」をクリックします。

コンソールの使い方：主要機能の紹介

BigQuery の Web UI は直感的な操作が可能で、以下の主要な機能に簡単にアクセスできます。

機能	説明
SQL クエリの実行	テキストエディタに SQL クエリを入力して実行できます。
ジョブの管理	実行したジョブの一覧を表示、キャンセル、再実行できます。
データセットの閲覧	作成したデータセットとその中のテーブルを閲覧できます。
データエクスプローラー	ビジュアルな操作でデータセットを探索できます。

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

BigQuery でのデータ分析を始めるためには、まず「データセット」というコンテナを作成し、その中に分析対象のデータを格納する「テーブル」を作成する必要があります。ここでは、それぞれの作成方法と、なぜそれらが必要なのかについて解説します。

データセットの作成

データセットは、関連するテーブルやビューをまとめて保管するためのコンテナです。プロジェクト内でデータを論理的に整理し、アクセス権を管理する際に重要な役割を果たします。例えば、販売データや顧客情報を別々のデータセットに分けて管理することで、データの整理やアクセス制御が容易になります。

データセットの作成手順は以下の通りです。

BigQuery コンソールで、プロジェクトから「データセットを作成」をクリックします。
データセット名とロケーションを入力します。
「データセットを作成」をクリックします。

テーブルの作成

テーブルは、データセット内に作成され、分析対象となるデータを実際に保持します。テーブルを作成することで、生のデータを構造化し、BigQuery で効率的に分析できるようになります。

テーブルの作成手順は以下の通りです。

データセットから「テーブルを作成」をクリックします。
テーブル名、スキーマを入力します。
「テーブルを作成」をクリックします。

データのインポート

CSV、JSON など、さまざまな形式のデータをインポートできます。
Web UI、コマンドラインツール、BigQuery API を使用してインポートできます。

BigQuery を操作する上でのクエリの基本

BigQuery では、データの探索、分析、そして洞察の獲得に SQL クエリを使用します。ここでは、SQL クエリの基本文法から始めて、実践的なデータ分析シナリオでのクエリ例、クエリジョブの管理、そしてクエリパフォーマンスの最適化について解説します。

SQL クエリの基本文法

BigQuery で使用される SQL は、標準的な SQL 文法に基づいていますが、ビッグデータを効率的に扱うための独自の機能も備えています。基本的な SELECT 文から始めて、データのフィルタリング、集計、そして結合まで、データ分析に必要な操作を網羅的にカバーします。

SQL 文例
SELECT name, age
　FROM `project.dataset.table`
　WHERE age >= 18
ORDER BY age DESC
　LIMIT 10;

このクエリでは、特定のテーブルから名前と年齢を選択し、18歳以上のレコードのみをフィルタリングして、年齢の降順で上位10件を表示します。

その他の重要な文法:

WHERE : 条件に基づいてレコードをフィルタリング
GROUP BY : 特定の列に基づいてデータをグループ化
ORDER BY : 結果を特定の列に基づいてソート
JOIN : 複数のテーブルからデータを結合

データ分析のためのクエリ例

シナリオ：オンラインストアの販売データから、最も人気の商品カテゴリを特定する。

SQL 文例

SELECT product_category, COUNT(order_id) AS order_count
　FROM `project.dataset.sales`
　GROUP BY product_category
　ORDER BY order_count DESC
　LIMIT 5;

このクエリでは、商品カテゴリごとに注文数を集計し、最も注文数の多い上位5つのカテゴリを特定します。

その他の分析シナリオ:

特定の期間における顧客の購買傾向を分析
製品レビューから顧客の満足度を分析
異なるマーケティングキャンペーンの効果を比較

以上のようなシナリオでも、クエリを使うと簡単に実現できます。

ジョブとクエリの実行管理

BigQuery では、クエリの実行は「ジョブ」として管理されます。Web UI やbq コマンドラインツールを使用して、実行中のジョブの状態を確認したり、過去のクエリ実行履歴を閲覧することができます。これにより、データ分析作業の進捗管理や、課題が発生した際のトラブルシューティングが容易になります。

ジョブ管理の機能:

ジョブの状態確認 (実行中、完了、失敗など)
過去のクエリ履歴の閲覧
ジョブのキャンセル
クエリ結果の保存

クエリパフォーマンスの最適化
BigQuery のクエリコストは処理するデータ量によって決まります。そのため、不必要なデータを読み込まないようにクエリを最適化することが重要です。
具体的な方法
SELECT * の使用を避け、必要な列のみを指定する
不要な列を読み込むと、処理時間が長くなり、コストも増加します。
適切なパーティショニングやクラスタリングを使用する
データを分割することで、特定のデータへのアクセス速度が向上します。
WHERE 句で条件を絞り込む
不要なデータを読み込まないように、条件を指定してデータ量を削減します。
サブクエリではなく JOIN を使用する
サブクエリは処理速度が遅くなる場合があるため、JOIN を使用することで効率化できます。
不要な ORDER BY や GROUP BY を避ける
不要な処理はコストを増加させるため、本当に必要な場合のみ使用します。
クエリの結果をキャッシュする
繰り返し実行されるクエリは、結果をキャッシュすることで処理速度を向上できます。
これらの基本的なガイドラインに従うことで、BigQuery でのデータ分析をより効率的かつコスト効果的に行うことができます。

BigQuery の使い方を実践的なデータ分析で理解する

データ分析は、過去のデータから学び、未来への洞察を得るための強力なツールです。ここでは、1912年に起きたタイタニック号の沈没事故という歴史的な事件を題材に、乗客の生存率に影響を与えた要因を探ります。

公開データセットを用いた生存者分析を通して、BigQuery のデータ処理能力を体感し、実践的なデータ分析スキルを習得しましょう。分析プロセスを段階的に解説することで、BigQuery の操作方法だけでなく、データ分析の考え方も理解できます。

データ分析の目的

この分析では、タイタニック号の乗客の生存に影響を与えた可能性のある要因を探ります。性別、年齢、乗船クラスなどの情報を基に、どのような乗客が生存する可能性が高かったのかを分析します。

データセットの準備

分析を始める前に、適切なデータを準備し、分析ツールに取り込む必要があります。この準備は、分析の質と結果の信頼性に大きく影響します。

ここでは、タイタニック号の乗客データを例に、データセットの準備から BigQuery へのデータロードまでの手順を詳しく説明します。

データソースの準備

今回は、Kaggle からダウンロードした CSV ファイルを使用します。

Kaggle から「Titanic: Machine Learning from Disaster」データセットを CSV 形式でダウンロードします。
ダウンロードしたファイルは、後ほど BigQuery に読み込みます。

データセットの作成

BigQuery にデータをロードする前に、データセットを作成する必要があります。

BigQuery コンソールにアクセスします。
左側のナビゲーションメニューからデータセットをクリックします。
データセットを作成をクリックします。
以下の項目を入力します。
データセット名: titanic_survival
場所: [リージョンを選択]
データセットの所有者: [ユーザー名]
データセットの説明: タイタニック号乗客データ
作成をクリックします。

データの読み込み方法

BigQuery は、CSV ファイルだけでなく、多様なデータソースからデータを柔軟に取り込むことができるデータ分析プラットフォームです。

ここでは、CSV ファイルに加えて、Google Cloud Storage と Google スプレッドシートの3つのデータソースを例に、それぞれの読み込み方法を詳しく解説します。

CSV ファイルからのデータソースの読み込み

CSV ファイルは、データ分析で最も一般的に使用されるデータ形式の一つです。カンマで区切られたシンプルな構造のため、多くのツールで簡単に扱えます。

BigQuery では、以下の方法で CSV ファイルを簡単に読み込むことが可能です。

左側のナビゲーションメニューからデータセットをクリックします。
作成したデータセット titanic_survival をクリックします。
テーブルを作成をクリックします。
以下の項目を入力します。

テーブル名: titanic_passengers
テーブルの作成元: アップロード
ファイル形式: CSV
ファイルの選択: ダウンロードした CSV ファイル
スキーマ: 自動検出

「テーブルを作成」をクリックします。

Google Cloud Storage からのデータソースの読み込み

Google Cloud Storage は、Google Cloud が提供するオブジェクトストレージサービスです。大量のデータを安全かつスケーラブルに保存することができ、BigQuery とシームレスに連携できます。

Google Cloud Storage から読み込むには、以下の手順に従います。

Google Cloud Storage バケットの作成
CSV ファイルのアップロード
BigQuery でのテーブル作成

Google Cloud Storage バケットの作成

Google Cloud Console にアクセスします。
左側のナビゲーションメニューからストレージをクリックします。
バケットを作成をクリックします。
以下の項目を入力します。

バケット名: titanic_survival
場所: [リージョンを選択]
ストレージクラス:Standard

作成をクリックします。

CSV ファイルのアップロード

作成したバケットをクリックします。
ファイルをアップロードをクリックします。
読み込みたい CSV ファイルを選択します。
アップロードをクリックします。

BigQuery でのテーブル作成

BigQuery コンソールに移動します。
左側のナビゲーションメニューからデータセットをクリックします。
データセットを選択してテーブルを作成をクリックします。
以下の項目を入力します。

テーブルの作成元: Google Cloud Storage
バケット: アップロードしたファイルが保存されているバケット
データセット: アップロードしたファイルが保存されているバケット
テーブル名: titanic_passengers_gcs
スキーマ: 自動検出を選択

5．テーブルを作成をクリックします。

Google スプレッドシートからのデータソースの読み込み

Google スプレッドシートは、Google が提供するオンラインの表計算ソフトです。ブラウザ上で簡単に編集、共有できるため、チームでのデータ分析に適しています。

Google スプレッドシートを BigQuery に読み込むには、以下の手順に従います。

Google スプレッドシートの作成
スプレッドシートの URL の取得
BigQuery でのテーブル作成

Google スプレッドシートの作成

Google ドライブにアクセスします。
新規 > Google スプレッドシートをクリックします。
ファイル > インポート > アップロードをクリックします。
インポートしたい CSV ファイルを選択します。
開くをクリックします。
CSV ファイルの内容がスプレッドシートにインポートされます。

スプレッドシートの URL の取得

作成したスプレッドシートを開きます。
アドレスバーから URL をコピーします。

BigQuery でのテーブル作成

BigQuery コンソールにアクセスします。
左側のナビゲーションメニューからデータセットをクリックします。
データセット[titanic_survival]を選択して、テーブルを作成をクリックします。
以下の項目を入力します。

テーブル名: titanic_passengers_spread
ソース: ドライブを選択します。
ファイル形式: Google スプレッドシート
URL : コピーしたスプレッドシートの URL を入力します。

テーブルを作成をクリックします。

上記のように、BigQuery は多様なデータソースに対応しており、ニーズに合わせて最適な方法を選択できます。

BigQuery でのクエリの実行

乗船クラス、性別、年齢別の生存率を調べるためのクエリを実行します。例えば、次のようなクエリです。

SELECT Pclass, Sex, AVG(Survived) as SurvivalRate
　FROM `titanic_survival.titanic_passengers
GROUP BY Pclass, Sex
ORDER BY Pclass, Sex;

このクエリは、乗船クラスと性別による生存率の平均を計算します。

その他にも、以下のようなクエリを実行できます。

年齢別の生存率
乗船クラスと年齢別の生存率
性別と年齢別の生存率

クエリの実行結果の視覚化

BigQuery のクエリ結果をグラフ化することで、どのグループの生存率が高かったのかを直感的に理解できます。

先ほどのクエリ結果からグラフを作ってみましょう。

乗船クラス別の生存率の棒グラフ

棒グラフで、乗船クラスごとの生存率を比較しています。

x軸：Pclass【乗船クラス (1等、2等、3等)】

y軸：SurvivalRate【生存率】

1等クラスの生存率が最も高く、3等クラスの生存率が最も低いことがわかります。

性別別の生存率の棒グラフ

棒グラフで、性別ごとの生存率を比較しています。

x軸：Sex【性別 (男性、女性)】

y軸：SurvivalRate【生存率】

女性の生存率が男性よりも高かったことがわかります。

より高度な視覚化には、Looker Studio などのデータ視覚化ツールを使用することをお勧めします。

クエリの実行後の分析結果の解釈

分析結果から、特定の乗船クラス、性別が生存率に大きく影響を与えていることが明らかになりました。

具体的な結果例：

女性は男性よりも生存率が高かった。
上位クラスの乗客は下位クラスの乗客よりも生存率が高かった。

これらの結果から、以下のような考察が導かれます。

女性や子供は、男性や大人よりも避難の優先度が高かった可能性があります。
上位クラスの乗客は、救命ボートへのアクセスなど、生存に有利な条件を享受していた可能性があります。
当時の社会における性別や階級による格差が、生存率にも影響を与えていた可能性があります。

注意事項:

この分析はあくまでも一例であり、より詳細な分析には追加的なデータや分析手法が必要となります。

BigQuery の利用料金

BigQuery の料金は主に以下の要素に基づいています。

ストレージ使用量
クエリによるデータ処理量
無料枠

以下で、詳しく解説します。

ストレージ使用量

保存されているデータの量に基づいて課金されますが、データ圧縮やデータフォーマットの最適化を行うことで、ストレージ使用量を削減することが可能です。

クエリによるデータ処理量

クエリ実行時に処理されるデータ量に基づいて課金されますが、上記で述べたベストプラクティスを実践することにより、データ処理量を削減することができます。

無料枠

BigQueryには無料枠が用意されており、毎月一定量までのデータ処理とストレージが無料で利用できます。詳細は、Google Cloud の公式サイトでご確認ください。

BigQuery のコスト管理とセキュリティ

BigQuery のようなパワフルなデータ分析ツールを使用する際、コスト管理とセキュリティは非常に重要な要素となります。ここでは、BigQuery を使用する上でのコストを効率的に管理する方法と、データとアクセスのセキュリティを保つためのベストプラクティスについて解説します。

コストを抑えるための BigQuery のベストプラクティス

BigQuery はクエリ実行にかかるデータ処理量に基づいて課金されます。そのため、以下の方法でコストを効果的に管理することができます。

データの分割とパーティショニング

テーブルを論理的に分割することで不要なデータの読み込みを避け、コストを削減できます。さらに、時間や地域など分析に役立つ属性に基づいてパーティショニングを行うことで、クエリのパフォーマンスを向上させ、コストを削減することが可能です。

必要なデータのみを対象にクエリを実行する

WHERE 句や LIMIT 句を活用して、必要なデータのみを抽出することで、データ処理量を削減できます。

クエリのパフォーマンスを最適化する

不要な SELECT * を避け、必要な列のみを指定することでデータ処理量を削減することができます。また、集計操作の前に行単位でのフィルタリングを行うことにより、より効率的なデータ処理を実現できます。

BigQuery のセキュリティ設定とアクセス管理

BigQuery では、以下の機能を使用してデータとアクセスのセキュリティを保つことができます。

IAM (Identity and Access Management):

ユーザーやグループに対して、データセットやテーブルへのアクセス権を細かく制御できます。
最小権限の原則に基づいて、必要なアクセス権のみを付与することが重要です。

データ暗号化:

保存時と転送時にデータを自動的に暗号化することで、データの機密性を保護できます。

アクセス監査ログ:

ユーザーのアクセス履歴を記録することで、不正アクセスを検出できます。

BigQuery の監査ログとモニタリング

BigQuery は操作の監査ログを自動的に記録します。監査ログを活用することで、以下のことが可能になります。

不正アクセスの検出
分析の実行履歴の追跡
セキュリティ問題の調査

また、モニタリングを使用して、BigQuery のジョブのパフォーマンスやリソース使用量を監視することができます。

エラーハンドリングとトラブルシューティング

BigQuery クエリ実行中にエラーが発生するのは避けられません。しかし、エラーメッセージを理解し、適切なトラブルシューティングを行うことで、問題を迅速に解決することができます。

エラーメッセージの理解

BigQuery は、エラー発生時に詳細なエラーメッセージを表示します。エラーメッセージには、エラーの種類、原因、および解決策に関する情報が含まれています。

エラーメッセージの例:

Not found: Table helloworld-bigquery-416414:titanic_survival.titanic_passengers_ggg was not found in location US

上記の例では、次の情報が得られます:

項目	内容
エラーの種類	Not found
原因	titanic_survival.titanic_passengers_ggg が存在しない
解決策	テーブル名を確認し、正しい名前に修正するテーブルが存在しない場合は、作成する