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

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

BigQueryGoogle Cloud技術ブログ

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

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

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

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

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

目次

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

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

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

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

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

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

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

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

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

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

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

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

データセットの作成

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

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

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

テーブルの作成

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

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

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

データのインポート

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

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 でのデータ分析をより効率的かつコスト効果的に行うことができます。

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

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

データ分析の目的

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

データセットの準備

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

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

データソースの準備

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

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

データセットの作成

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

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

データの読み込み方法

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

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

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

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

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

  1. 左側のナビゲーションメニューから データセット をクリックします。
  2. 作成したデータセット titanic_survival をクリックします。
  3. テーブルを作成 をクリックします。
  4. 以下の項目を入力します。
  • テーブル名: titanic_passengers
  • テーブルの作成元: アップロード
  • ファイル形式: CSV
  • ファイルの選択: ダウンロードした CSV ファイル
  • スキーマ: 自動検出
  1. 「テーブルを作成」 をクリックします。

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

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

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

  1. Google Cloud Storage バケットの作成
  2. CSV ファイルのアップロード
  3. BigQuery でのテーブル作成
Google Cloud Storage バケットの作成
  1. Google Cloud Console にアクセスします。
  2. 左側のナビゲーションメニューから ストレージ をクリックします。
  3. バケットを作成 をクリックします。
  4. 以下の項目を入力します。
  • バケット名: titanic_survival 
  • 場所: [リージョンを選択]
  • ストレージクラス:Standard
  1. 作成 をクリックします。

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

BigQuery でのテーブル作成
  1. BigQuery コンソールに移動します。
  2. 左側のナビゲーションメニューから データセット をクリックします。
  3. データセットを選択して テーブルを作成 をクリックします。
  4. 以下の項目を入力します。
  • テーブルの作成元: Google Cloud Storage
  • バケット: アップロードしたファイルが保存されているバケット
  • データセット: アップロードしたファイルが保存されているバケット
  • テーブル名: titanic_passengers_gcs
  • スキーマ: 自動検出 を選択

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

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

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

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

  1. Google スプレッドシートの作成
  2. スプレッドシートの URL の取得
  3. BigQuery でのテーブル作成
Google スプレッドシートの作成
  1. Google ドライブ にアクセスします。
  2. 新規 > Google スプレッドシート をクリックします。
  3. ファイル > インポート > アップロード をクリックします。
  4. インポートしたい CSV ファイル を選択します。
  5. 開く をクリックします。
  6. CSV ファイルの内容がスプレッドシートにインポートされます。
スプレッドシートの URL の取得
  1. 作成した スプレッドシート を開きます。
  2. アドレスバーから URL をコピーします。
BigQuery でのテーブル作成
  1. BigQuery コンソール にアクセスします。
  2. 左側のナビゲーションメニューから データセット をクリックします。
  3. データセット[titanic_survival]を選択して、テーブルを作成 をクリックします。
  4. 以下の項目を入力します。
  • テーブル名: titanic_passengers_spread
  • ソース: ドライブ を選択します。
  • ファイル形式: Google スプレッドシート
  • URL : コピーしたスプレッドシートの URL を入力します。
  1. テーブルを作成 をクリックします。

上記のように、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 はクエリ実行にかかるデータ処理量に基づいて課金されます。そのため、以下の方法でコストを効果的に管理することができます。

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

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

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

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

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

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

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

IAM (Identity and Access Management):

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

データ暗号化:

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

アクセス監査ログ:

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

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 が存在しない
解決策テーブル名を確認し、正しい名前に修正する
テーブルが存在しない場合は、作成する

BigQuery ドキュメント

BigQuery ドキュメントには、エラーメッセージとその意味、およびトラブルシューティングの手順に関する情報が掲載されています。

Google Cloud コミュニティ

Google Cloud コミュニティは、BigQuery ユーザー同士の情報交換の場です。フォーラムやチャットで質問を投稿することで、他のユーザーからアドバイスを得ることができます。

これらの情報源とポイントを組み合わせることで、BigQuery で発生する可能性のあるエラーや問題に対処するためのより深い理解と対応策を得ることができます。

Google Cloud のコンサルティング会社の活用

Google Cloud に精通している会社にコンサルティングやアドバイスをもらうこともトラブル解決にはおすすめです。Google Cloud に精通しているかどうかは、Google Cloud のパートナー認定を受けているかどうかで判定すると良いでしょう。

ちなみに、Google Cloud のパートナーとは、Google Cloud のサービスやテクノロジーを顧客に提供、導入、またはカスタマイズするためにGoogleと協力関係にある企業や組織のことです。

これらのパートナーは、

  • クラウドインフラの構築
  • データ分析
  • 機械学習
  • アプリケーション開発

など、さまざまな分野での専門知識を提供し、顧客がGoogle Cloudのポテンシャルを最大限に活用できるよう支援します。

パートナー企業はGoogleからの認定を受け、定期的なトレーニングやサポートを通じて最新のGoogle Cloudの技術やサービスに精通しています。

もちろん、当社センティリオンシステム 大阪事業所も認定パートナーですので、お困りごとがございましたら、お気軽にご相談ください

本記事では、BigQuery の基本から始めて、具体的なデータ分析の実例を通じて、BigQuery の使い方を解説しました。

当社センティリオンシステム 大阪事業所では、BigQuery の運用やデータ基盤の構築にとどまらず、ランニングコストの最適化や Google Cloud のサポート、コンサルティングまで幅広くサービスを提供しています。

以下のような課題をお持ちの方は、ぜひお気軽にご相談ください。

  • データ基盤を構築したいが社内に知見がない
  • BigQuery を活用したい
  • 信頼性の高いシステム構築や具体的な実装方法
  • クラウド活用を推進するための開発体制作りが進まない
  • 既存資産をどのようにクラウド移行するか検討する知見が不足している
  • 内製化するためのクラウド開発スキルを持った人材が不足している
  • コスト削減の実現方法に悩んでいる
  • SRE を実現したい

貴社の状況に合わせて、体制づくり支援や開発計画支援、クラウド開発スキルアップ支援など、様々な支援メニューを提供しています。無料相談も可能なため、まずは問い合わせフォームからご連絡いただければと思います。

本記事を参考にして、 Google Cloud の導入および BigQuery の活用を検討してみてはいかがでしょうか?