BigQueryの使い方解説！利用手順から料金体系までをご紹介

BigQueryを使ってデータ分析をしたいけれど、どうやって使えば良いのかわからない
お金はかかるのか気になる

こういった疑問をお持ちの方に、BigQueryの使い方と料金を中心に解説していきます。

「BigQueryって何？」という方は始めからご一読ください！

BigQueryとは

BigQueryとは、Google Cloud Platformのデータ分析サービスの一つです。

データの保存・SQLクエリの実行環境を備えています。

例えば、Google AnalyticsのデータをBigQueryに入れ、ページ毎のPV・UU数をクエリ実行して確認することができます。

TwitterやTOYOTAなど大手企業も使っているサービスで、大量のデータを高速に処理することが可能です。

もちろん個人でも利用できます。Google AnalyticsのデータをBigQueryで分析したいと思った場合、ボタン一つで連携が可能です。

実際の使い方を次でご紹介します。

BigQueryの使い方

Google Cloud コンソールへアクセス

こちらからGoogle Cloud Platform(GCP)へアクセスします。

Googleアカウントでログインします。下記の画面で利用規約にチェックし、同意して続行を押します。

3ヶ月間利用できる$300分のクレジットを申し込みます。ここでクレジットカードを入力しますが、無料トライヤルのままであれば課金されることはありません。

GCPのコンソール画面に移ります。

BigQueryへアクセス

①ハンバーガーメニューからBigQueryを選択 or プロダクトとリソースの検索で「BigQuery」と入力して選択

②一般公開データセットを表示する

GCPで一般提供されているデータセットを利用してBigQueryを利用してみます。

三点リーダー>一般公開データセットを調べるを選択します
データセットが表示されるので、広告からGoogle Analytics Sampleを選択します
データセットを表示します

そうするとBigQueryのコンソール上にGoogle Analyticsのサンプルデータが表示されます。

③クエリを実行する

🔍テーブルをクエリを押して、クエリを書いて実行ボタンを押します。

以下のクエリはvisitorIdごとにPV数の多い順で表示するクエリとなります。

ちなみに、展開>クエリを書式設定を押すと、自動で書式を整えてくれます。

SELECT 
  visitId,
  sum(totals.pageviews) AS pv
FROM 
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY 
  visitId
ORDER BY 
  pv DESC

④自分でデータを入れる場合

自分のプロジェクトの三点リーダーからデータセットを作成を選択します
データセットIDを入力し、データセットを作成を選択します
作成したデータセットを開き、テーブルを作成ボタンを押します
テーブルの作成元をアップロードに変更し、アップロードするデータを選択します
テーブル名を入力し、スキーマと入力パラメータにチェックを入れ、テーブルを作成を押します

そうするとデータがBigQueryに入ります。

あとは先ほどのようにクエリ書いてデータを取得します。

BigQueryの料金体系

料金体系は2種類で、従量課金のオンデマンド課金と、定額料金のBigQuery Reservationsがあります。

オンデマンド課金

BigQueryは分析料金とストレージ料金がかかります。

分析料金はクエリ処理にかかる費用で、毎月1TBまで無料です。それを超えると1TBにつき$5.00の費用がかかります。

ストレージ料金はデータ保存にかかる費用で、毎月10GBまで無料です。それを超えると1GBあたり$0.020の費用がかかります。

つまり、個人で利用する分には十分無料で利用できるレベルです。

初めてGCPを利用すると$300分のクレジットが付与されるので、無料枠を超えても$300分は使い放題です。

なので実費がかかることはほとんどないと思います。

とはいえ無料トライヤルからアップグレードし、$300を使い切るとそれ以上は課金される可能性があります。

絶対に課金したくない場合は予算アラートの設定をしておきましょう。

コンソールのハンバーガーメニューから、お支払い>予算とアラートへ遷移します。
予算を作成を押し、範囲>金額>操作の順で設定し、終了を押します。

名前をつけます。その他はデフォルトのままで構いません。

予算タイプを「指定額」にして、上限金額を指定します。

アラートメールのトリガーを設定し、アラートメールにチェックを入れます。

これで設定は完了です。

BigQuery Reservations

BigQuery Reservationsは、クエリ処理容量を購入することで定額料金の利用を可能にします。

オンデマンド課金ではクエリのデータスキャン量で料金が都度変わるため、コスト管理はしにくくなります。

一方、BigQuery Reservationsでは購入した分の料金なので、コスト管理がしやすいです。

個人で利用する場合はオンデマンド課金で十分だと思います。企業で利用していてコスト管理を厳密にしたい場合や、単価の高いクエリが多い場合には、BigQuery Reservationsの利用を検討すると良いかもしれません。

クエリの最適化

BigQueryはの最大のメリットの一つは、大容量のデータに対して、低コストかつ数秒で処理を実行できることです。

このメリットを最大限にするテクニックは以下となります。

SELECT * を避け、必要なカラムのみ選択する

BigQueryはカラム指向のデータベースです。選択したカラムの全データを取り出します。

WHEREやLIMIT条件で行数を減らしても、内部では全データを取り出してから条件を適用させるため、スキャンするデータ量は変わりません。

クエリを最適化する場合は、SELECT * で全カラムを取り出すのではなく、必要なカラムのみを選択するようにします。

パーティション分割・クラスタ化機能を活用する

パーティション分割・クラスタ化機能を利用すると、行数を絞り込むことでスキャン量を減らすことができます。

パーティション分割とは、指定した一つの時間単位か整数のカラム、もしくはデータの取り込み時間でテーブルのパーティション化をする機能です。

下記の画像は、パーティション分割されていないテーブルとしているテーブルのスキャン量の違いを表しています。

このように、スキャン量の削減がクエリ実行前に厳密に分かります。

クラスタ化は、指定したカラムを基準にデータを並べ替えてくれる機能で、WHERE句を利用するクエリや集計クエリを効率的にしてくれます。

パーティション分割と違い、利用するカラムは複数可能で、型の指定もありません。また、パーティション分割では分割数に4000（取り込み時間では5000）と制限が設けられていますが、クラスタ化には制限がありません。

クラスタ化の場合はスキャン量の削減がクエリ実行前に分かりません。ただ、表示されたスキャン量よりは少なくなります。

パーティション分割とクラスタ化の併用も可能です。クエリの用途に合わせて選択することが肝となります。

マテリアライズドビュー

マテリアライズドビューは、集計関数を含むクエリのパフォーマンスの向上ができます。

以下のDDLでマテリアライズドビューが作成できる…はずなのですが、CREATE MATERIALIZED VIEW columns must be named, but column 2 has no name at [3:1] というエラーが出てしまいました。

公式ドキュメントの通りに書けているように思うのですが…こちらは解決でき次第、検証したいと思います。

CREATE MATERIALIZED VIEW  project-id.dataset.view_name
AS SELECT date, SUM(pv) 
FROM  project-id.dataset.table
GROUP BY 1

まとめ

BigQueryは手軽に利用できる分析サービスです。

公開されているデータや、お手元のデータをアップロードしてぜひ試してみてください。

GA4+BigQuery+データポータルでアクセス解析するやり方もご紹介しています。ご興味ある方はご覧ください！

BigQueryの使い方解説！利用手順から料金体系までをご紹介

BigQueryとは