BigQuery FAQ

  • このエントリーをはてなブックマークに追加

最終更新

目次

BigQuery の基礎

BigQuery とは何か?

BigQuery とは、Google が提供するデータウェアハウスサービスです。

BigQuery で何ができる?

データの分析やデータ保管庫として活用するのが一般的です。

特に、何十億・何百億レコードをそこそこ高速に処理できます。

競合サービスは? メリット・デメリットは?

クラウド界隈の競合サービスは下記です。

  • AWS の Redshift
  • Azure の SQL Data Warehouse
  • Oracle の Oracle Autonomous Data Warehouse

一般的な RDBMS、Oracle・MySQL・PostgreSQL と比べて何が違うの? メリット・デメリットは?

BigQuery は大規模データに特化したつくりになっています。大量のデータを扱えますが、リアルタイム性には劣ります。また、主キー・CHECK 制約・参照整合性制約など使い勝手のよい機能がありません。

よって、 EC サイトなど、多数の一般利用者が使うシステムのデータベースとしては適していません。EC サイトそのものは Oracle・MySQL・PostgreSQL を使い、その結果を BigQuery に流し込んで、分析・保存するという使い方をおすすめします。

データセット・テーブル

データセットとは?

BigQuery では、他の RDBMS と同様にテーブル単位でデータを保持しますが、テーブルは必ずデータセットという箱の中に存在します。

イメージとしては下記のようなものです。

  • データセットA
    • テーブルX
    • テーブルY
  • データセットB
    • テーブルX
    • テーブルZ

データセットの下に置かないテーブルというものは存在しません。

データセットのリネームは可能?

データセットのリネームはできません。新しいデータセットを作りなおし、テーブルを新データセットにコピーする必要があります。

データセットのロケーションとは? ロケーションを変更するには?

BigQuery におけるロケーションはとても重要です。なぜなら1つのSQLで、ロケーションをまたぐことはできないため。例えば購入テーブルが Tokyo にあり、マスタテーブルが US にある場合、それを1つのSQLで join することはできません。

また、一度テーブルに格納したデータはロケーションの変更ができません。ロケーションをまたがるコピー (bq cp) もできません。一度 GCS に export して、新ロケーションに load する必要があります。

ロケーションはどこにするべき?

ロケーションは、US・EU や、Tokyo などがあります。

テーブルとは?

BigQuery のテーブルは、一般的な RDBMS のテーブルと同じです。テーブル名を持ち、複数のカラムを持ち、カラムには文字列型・数値型などのデータ型があります。

データ型・制約

BigQuery のデータ型は?

BigQuery の主なデータ型は下記のとおりです。

型名
STRING文字列
INTEGER/INT64整数 (64bit符号付き)
FLOAT/FLOAT64浮動小数点
TIMESTAMP時刻。0001-01-01 00:00:00~9999-12-31 23:59:59.999999 UTC。タイムゾーンあり
DATE日付。 0001-01-01 00:00:00 ~ 9999-12-31 。タイムゾーンなし。
DATETIME日時。0001-01-01 00:00:00 ~ 9999-12-31 23:59:59.999999 。イムゾーンなし。
BOOLEAN/BOOL真偽値 (TRUE または FALSE)
BYTESバイト列
RECORD/STRUCT後述

CHAR(10) や VARCHAR(10) は?

BigQuery のデータ型には、CHAR(10) や VARCHAR(10) はありません。STRING を使いましょう。

文字列型の長さ制限はありませんが、1レコードの最大サイズは 100MB と決まっていますので、その枠内なら 1MB でも 10MB でも可能です。

RECORD とは?

RECORD とはデータ型のひとつであり、構造体のようなもので、foo.bar のようにドットでつなぐことで表現できます。下記の JSON 形式のスキーマ型において、col2 が RECORD 型です。

[
   {"name": "col1", "type": "STRING", "mode": "NULLABLE"},
   {"name": "col2", "type": "RECORD", "mode": "NULLABLE", "fields": [
      {"name": "subcol1", "type": "STRING", "mode": "NULLABLE"},
      {"name": "subcol2", "type": "STRING", "mode": "NULLABLE"}
    ]}
}

SQLは下記になります。

-- 全カラムを select
SELECT * FROM mytable
-- col2 のみ select
SELECT col2 FROM mytable
-- col2.subcol1 のみ select
SELECT col2.subcol1 FROM mytable
-- col2 の subcol1 と subcol2 を select
SELECT col2.subcol1, col2.subcol2 FROM mytable

RECORD 型の使い所・メリットは?

  • 例えば売上レコードに購入した店舗情報を含める際、shop と shop.geo をそれぞれ RECORD とし、下記のようにする。
    • shop.shop_id (店舗コード)
    • shop.shop_name (店舗名)
    • shop.geo.prefecture_name (都道府県名)
    • shop.geo.city_name (市区町村名)
    • shop.geo.latitude (緯度)
    • shop.geo.longitude (経度)
  • タイミングで分ける。例えば IoT 機器が送信したデータと、中継サーバのデータと、受信時のデータを分けるなど。
    • sourceinfo (IoT 機器の送信データ)
    • relayinfo (中継サーバの付加データ)
    • receiveinfo (受信時のデータ)
  • レイヤ分けをする。例えば
    • commnication
      • protocol: “TCP” など
      • version: “v4” など
      • data
        • request
          • method: “GET” など
          • header: “User-Agent” など
          • body
        • response
          • header: “Content-Type” など
          • body

REPEATED とは?

型ではないのですが、REPEATED という配列・繰り返しを表すモードがあります。下記のようなことを実現できます。

  • STRING の repeated で文字列の配列
  • INTEGER の repeated で数値の配列
  • RECORD の repeated で、レコードの配列

スキーマ定義は下記のようになります。type ではなく mode に “REPEATED” と記述すると繰り返し項目ですよという意味になります。

[
   {"name": "col1", "type": "STRING", "mode": "REPEATED"},
   {"name": "col2", "type": "RECORD", "mode": "REPEATED", "fields": [
      {"name": "subcol1", "type": "STRING", "mode": "NULLABLE"},
      {"name": "subcol2", "type": "STRING", "mode": "REPEATED"}
    ]}
}

上記は col1 STRING だけではなく、col2 RECORD も REPEATED にしています。さらに RECORD 内の col2.subcol1 も REPEATED にしました。

RECORD A 内に RECORD Asub を作った場合、以下のいずれも可能です。

  • RECORD A・RECORD Asub いずれも REPEATED にしない。
  • RECORD A を REPEATED、RECORD Asub は REPEATED にしない。
  • RECORD A を REPEATED にせず、RECORD Asub は REPEATED にする。
  • RECORD A・RECORD Asub いずれも REPEATED にする。

一意制約・UNIQUE KEY は?

BigQuery には一意制約・UNIQUE KEY はありません。あったらいいなと思うことはありますが、分散環境であるため一意であることをチェックするコストがあまりに高いということでしょう (ちなみに AWS の Redshift、Azure の SQL Data Warehouse にもありません)。

ではどうやって重複データを除去すればいいの?

これは結構難しい話題です。今取り込もうとしているデータについての重複除去であれば下記でよいでしょう。

  • とりあえず重複してもよいので、データを一時テーブルに突っ込む。
  • 一時テーブルにて ORDER BY や TOP 等を駆使し、重複除去を行う。
  • その結果を、結果テーブルに突っ込む。

日次バッチで、昨日到着したデータが、本日も再度現れている可能性があります。例えば IoT 機器からのデータであれば、一時的に通信状態が悪く、リトライでそのようなことが起こるケースは結構あります。そのときは…

そもそもデータ生成側で UUID などで一意なデータ作成を行うことをおすすめし…

NOT NULL 制約

NOT NULL 制約は存在します。下記 col1 は nullable なので NULL を許容します。col2 は required なので NULL 不可です。

[
   {"name": "col1", "type": "STRING", "mode": "nullable"},
   {"name": "col2", "type": "STRING", "mode": "required"}
]

ただし DWH という性格上、NOT NULL とすべきかはよく考えたほうがよいでしょう。すでに他の DB で生成されたデータであることも多いでしょうから、その場合は不正データ投入防止という観点ではあまり役に立ちません (すでに他の DB には NULL が入ってしまっているため)。

NOT NULL ではじいた場合どうするのか? レコード全体を投入不可としてよいのか? むしろ NULL を許容して、不正データとしてレポートしたほうがよいのではないか? というのはよく考えたほうがよいかと思います。

カラムの型の変更はできる?

できません。型を変更した新テーブルを作成し、レコードを内容を移行する必要があります。

カラムの追加はできる?

可能です。

カラムの削除はできる?

できません。不要なカラムを削除した新テーブルを作成し、コピーする必要があります。

データセット・テーブルのメタ情報について

データセットが特定できているとき、テーブルの一覧や各テーブルの行数・サイズを確認するには、[データセット名].__TABLES__ を使います。

また、INFORMATION_SCHEMA もあります。

SQLについて

StandardSQL・LegacySQL とは?

BigQuery のリリース時点で使えた SQL はクセがあり、評判がよいとは言えませんでした。2016年に “StandardSQL” と呼ばれる新しい SQL が発表され、それに伴い従来の SQL は “LegacySQL” と命名されました。

2019年8月現在、LegacySQL は使えますし、いつ使えなくなるとアナウンスされているわけでもありませんが、StandardSQL のみで使用可能な機能が増えてきていますので、StandardSQL を使うことを強くおすすめします。

SQLパラメータ

SELECT 文は?

使えます。

UPDATE 文は?

当初は未実装でしたが 2017年あたりから使えるようになりました。

DELETE 文は?

使えます。ただし WHERE句は必須です。TRUNCATE TABLE 相当のことをしたい場合、WHERE 1=1 をつけましょう。

TRANCATE TABLE は?

2019年8月現在、使えません。DELETE FROM mydataset.mytable WHERE 1=1 で全レコードを削除するか、bq qeury –destination_table=mydataset.mytable “select 1 where 1=0” などとしましょう。

トランザクション

BigQuery には一般的な RDBMS にあるトランザクションは実装されていません。具体的には、BEGIN でトランザクションを開始し、複数の SELECT や INSERT・UPDATE 等を実行し、COMMIT で反映、ROLLBACK で戻す、ということは BigQuery ではできません。

ただし、単体の SQL についてはアトミックであることが保証されており、「100万行突っ込むはずが 10万行だけ成功してしまった」といったことは起こりません。

ジョブ

ジョブって何?

BigQuery についての操作は、すべて API を経由して行います。そのうち、実行に長時間かかる可能性のあるものをジョブと定義しています (多分)。

クエリジョブ・読み込みジョブ (load ジョブ)・コピージョブ・エクスポートジョブの 4種類があります。

わかりづらいのですが、BigQuery の制限を把握する上で重要なので、しっかりと理解しておくことをおすすめします。

クエリジョブ

平たく言うと、SELECT 文です。

クエリジョブは、bq コマンドであれば bq query 実行時に生成されます。

読み込みジョブ (load ジョブ)

与えられたデータを BigQuery に読み込むジョブです。これは INSERT INTO SELECT のような複雑な形式ではなく、投入すべきデータを CSV や JSON 形式でファイルとして用意しておき、それを指定したテーブルに投入するだけ、というものです。

読み込みジョブは、bq コマンドであれば bq load 実行時に生成されます。

ジョブではない BigQuery 操作とは?

データセットやテーブルの作成・一覧取得・情報取得・情報更新はジョブではありません。また、クエリジョブの結果を取得する動作も、ジョブではありません。

要は、すぐに結果が返ってくることが期待できる操作はジョブではありません。

ジョブの実行状況を確認するには?

bq ls -j とします。bq ls -j -a で、自分以外のジョブも確認できます。

日付別テーブル・分割テーブル

日付別テーブル・分割テーブルのまとめ

Google の名前付けが下手くそすぎるので大変わかりづらいのですが、BigQuery における分割テーブル的なしくみは下記3つあります。

  • A. 日付別テーブル
  • 分割テーブル
    • B. 取り込み時間分割テーブル
    • C. 分割テーブル

A がもっとも古い機能で、C がもっとも新しい機能です。おすすめは C で、B は使う必要なし。A は状況によっては使ってもいいかもしれない、と当ページ管理人は考えます。

日付別テーブル

一般的な RDBMS において、データを 2019年のテーブル・2020年のテーブルと分割したり、月単位・日単位に分割する「水平分割」は、行わないほうがよいと言われています。

一方、BigQuery では下記のように日付別にテーブルを作成するという方法が一般的です。

  • sales20190801
  • sales20190802
  • sales20190803

各日付のスキーマ定義は同じにします (カラム名や型は同じにするということ)。

その上で下記のようにテーブル名部分にワイルドカードを指定することで、sales201908 から始まるテーブルすべてを対象にクエリを実行します。

SELECT * FROM `mydataset.sales201908*`

日付別テーブルの実体

日付別テーブルは、ワイルドカードで検索するときに便利なようにしたものであって、末尾を YYYYMMDD 形式とすることが重要なわけではありません。YYYYMM 形式や YYYY 形式にしてもよいですし、sales_2019_1Q などとしても別に構いません。普通はしませんが、sales_a・sales_b・sales_c としても構いません。

分割テーブルとは?

もともとの BigQuery には日付別テーブルしか存在しなかったのですが、その後分割テーブルが追加されました。分割テーブルは世の中で言うところのパーティションテーブルと言ってよいでしょう。

分割テーブルは特定カラムをキーとして、内部的にテーブルを分割しておく仕組みです。一般的な RDBMS ではパーティションのキーには任意のカラムを指定できます (例えば売上日とか、法人コードなど)。

BigQuery の分割テーブルのキーは TIMESTAMP または DATE のカラムのみです。

大変ややこしいのですが、分割テーブルには、取り込み時間分割テーブルと、分割テーブルがあります。

権限

BigQuery の権限はどういうもの?

2段階の構成となっています。まず、GCP の IAM で下記を指定できます。

  • 誰が (Google アカウント・Google グループ・サービスアカウント)
  • どのような操作を可能とするか

例えばデータセットやテーブルの作成はできるが、削除はできない、ということはここで設定します。

さらに、下記を指定します。

  • どのデータセットに対するどのような権限があるか

承認済みビューとは、「ビューに権限を付け、ビューが参照しているデータセットには権限を付けない」とすることで、指定したテーブル・カラム・レコードのみを見せることができるものです。

UDF

UDFとは?

UDF とは User Definition Function の略で、Oracle・MySQL・PostgreSQL などでいうところのストアドファンクションに相当します。

UDF は SQL UDF と、Javascript の UDF があります。

より正確に書くと、下記の通りなのですが、2019年8月現在では外部UDFで使える言語は Javascript のみですので、SQL UDF か、Javascript UDF のいずれかと認識しておけば問題ありません。

  • SQL UDF
  • 外部 UDF
    • Javascript
    • (そのうち Python など他言語が使えたりするとここに追加される)

ビュー

ビューとは?

Oracle・MySQL・PostgreSQL などで一般的な、アレです。CREATE VIEW で作成します。

ビューを作成・更新するには?

BigQuery コンソールから作成

bq mk –view コマンドで作成

API で作成

ビューの権限は?

ビューを使って、外部から _TABLE_SUFFIX 等を指定するには?

下記を参考にしてください。

承認済みビューとは?

BigQuery の承認済みビュー (Authroized Views) とは、ビューに参照権限を付与し、ビューの参照先テーブルには権限を付与しないことで、特定のレコードやカラムのみを参照させるというやり方です。

「承認済みビュー」という機能があるわけではなく、上記のように設定することでデータセットよりも細かな単位で権限コントロールができる、という設計パターンのひとつと考えたほうがよいでしょう。

Materialized View (マテリアライズド・ビュー) は?

2019年8月現在、BigQuery には Materialized View (マテリアライズド・ビュー) はありません。

よって、下記のように手動なり定期的なりでテーブルを生成する必要があります。

CREATE TABLE mydataset.materialized_view AS
SELECT *
FROM mydataset.view;

外部データソース

Bigtable・Cloud Storage (GCS)・

Cloud SQL federated queries

監査ログ

BigQuery に関するすべてのクエリジョブは、ログに記録されます。

Stackdriver Logging の BigQuery への連携

BigQuery は、GCP サービスのログ保管場所としても活用できます。

Scheduled Query

バックアップ・リストア

バックアップ・スナップショットはとれる?

BigQuery ではデータ更新が自動的に記録されているため、下記のように “@ミリ数” というスナップショットデコレータという機能を使うことで、任意時点のデータを取り出すことができます。

#legacySQL
-- 1時間前の mytable を取得
SELECT * FROM [myprojectid:mydataset:mytable@-3600000] 

ただし、参照可能なデータは 7日以内、削除したテーブルは 2日以内という制限があります。2019年8月現在、スナップショットデコレータを使えるのは LegacySQL のみです。StandardSQL は対応予定ありとのこと。

一方、mysqldump 的な、pg_dump 的なデータベース全体のバックアップ・スナップショットをお手軽にとりたいという意味であれば、取れません。また、1日1回などの定期的なタイミングで、バックアップ・スナップショットを自動で取得し、1ヶ月間は保存しておく、という機能も BigQuery にはありません。

どうしてもバックアップを取りたい場合、テーブル単体で bq cp などでコピーしまくるのがよいでしょう。bq cp のコストはかかりませんが、テーブルのデータについてはストレージ料金がかかります。

BigQuery BI Engine とは?

BigQuery BI Engine とは、

有効期限・自動削除

テーブルの自動削除をするには?

BigQuery ではテーブルを自動的に削除する機能が備わっています。ただ、細かなところで挙動が異なるので注意が必要です。

まず、テーブルには有効期限を設定することができます。指定した時刻を過ぎるとテーブルは削除されます。下記は 3600秒後に削除という設定を行います。

bq mk --expiration=3600 mydataset.mytable

一度設定した有効期限設定を変更・削除するには

bq update にて更新できます。

bq update --expiration=7200 mydataset.mytable

有効期限設定を削除する (自動削除されないようにする) には、0 を設定します。

bq update --expiration=0 mydataset.mytable

デフォルトのテーブルの有効期限とは

データセットの自動削除機能はありません。

下記のように一見それっぽい設定があるのですが、これは「新しいテーブルを作成する際、そのテーブルに自動的に設定する有効期限」です。

あくまでデフォルトですので、テーブル作成時に異なる有効期限を指定することもできます。

つまりこういうことです。

  • デフォルトのテーブルの有効期限が 10日であるデータセットの下に、2019/08/01 に新規テーブルを作成した → そのテーブルの有効期限は 2019/08/11 となる。
  • 同様に、2019/08/21 に新規テーブルを作成した → 有効期限は 8/31 となる。
  • 同様に、2019/08/21 に新規テーブルを作成したが、有効期限は 3日と指定した → 有効期限は 8/24 となる。

テーブルではなく、レコードの自動削除はできないの?

下記のようなものをお探しであるなら、そのようなことを自動で行う機能はありません。

DELETE FROM mytable WHERE insert_date < xxxx

ただし、上記 SQL をスケジュールクエリにて定期実行するという案はあると思います。

日付別テーブルの有効期限

日付別テーブル (hoge_YYYYMMDD) について、「デフォルトのテーブル有効期限」を使って削除する場合の注意点です。

hoge_YYYYMMDD テーブルが作成されたときに、「そのN日後に削除される」という設定が自動的に行われるだけで、「テーブル名末尾のYYYYMMDD が何日前か」を判断してくれているわけではない、ということに注意しましょう。

例えば初期データ投入のために1000テーブルを一括作成したりすると、N日後1000テーブルが一度に消えます。

分割テーブルの有効期限

トリガについて

Oracle・MySQL・PostgreSQL いずれも、CREATE TRIGGER 文でトリガを作成できますが、2019年8月現在、BigQuery にはトリガはありません。

トリガとは、テーブルAにデータが INSERT・UPDATE・DELETE されたら、トリガにより何らかの SQL を発行するものです。よく使われる例としては下記です。

  • 集計結果を更新する (売上テーブルに INSERT したら、月別売上テーブルにも加算するなど)。
  • ログを記録する。
  • 履歴テーブルに変更前の行を追加する。

BigQuery においては上記はトリガで実現することはできませんので、アプリケーション側で複数回 SQL を実行するなどの対応を取りましょう。

そもそも BigQuery は 1レコード単位で何度も同じテーブルを更新することは、不得意ですので、1時間に一回集計や日次集計にしたりするなどの工夫をしたほうがよいと思います。

料金

BigQuery の料金の概要

いろいろな料金の種類がありますが、主なものはクエリ料金・ストレージ料金です。

クエリ料金とは?

ロケーションによって異なりますが、下記のとおりです。

  • 東京: $8.55 / 1TB
  • US/EU: $5 / 1TB

データ処理量とは、

SELECT col FROM mytable 

という SQL があるとして、

  • mytable の col 列が例えば INTEGER であるとすると、データサイズは 8バイト。
  • それが 1000万レコードあったとすると、この SQL のデータ処理量は 76MB (8バイト×1000万レコード)。
  • SQL ごとのデータ処理量を積算し、1TB に達するごとに $8.55 請求。

ということです。なお、1TB 未満であれば請求なしというわけではなく、例えばデータ処理量が 76MB の場合、約 0.068円となります。このような SQL を 100回実行すると、約 6.8円というわけです。

ストレージ料金

テーブルにデータを保存したままにしておくと、その容量に応じてストレージ料金がかかります。

  • 東京: $0.023/GB (≒2,590円/TB)
  • US/EU: $0.02/GB (≒2,252円/TB)

90日更新しなかったテーブルは、ストレージ料金が半額になります。

データ投入の料金は?

bq load などによる読み込みジョブは無料です。

bq –destination_table=’mydataset.mytable’ ‘SELECT * FROM xxxx’ はクエリジョブなので SELECT 部分についてはクエリコストがかかりますが、mydataset.mytable へのデータ投入は無料です。

bq cp などによるテーブルコピーは無料です。

カスタムコスト管理

定額

サンドボックス

制限・制約

PaaS サービスなので、さまざまな制限・制約があります。Oracle・MySQL・PostgreSQL と同様に考えていると痛い目にあいます。特にテーブル更新などの頻度についてよく検討することをおすすめします。

ノウハウ:BigQuery にデータを投入する方法一覧

bq load コマンド

もっともシンプルで簡単なのは bq load コマンドを使うことだと思います。

Pub/Sub → ストリーミングインサート

BigQuery のストリーミングインサートを使うことで、ほぼリアルタイムに BigQuery のテーブルにデータを投入できます。

なお、GCP 無料枠ではストリーミングインサートは使えません。

Dataflow → BigQuery

DataProc → BigQuery

Cloud Storage → BigQuery

S3 → Storage Transfer Service → GCS → BigQuery

Cloud Data Fusion → BigQuery

ノウハウ:BigQuery からデータを取得する方法一覧

bq query コマンド

bq export コマンド

BigQuery Storage API

  • このエントリーをはてなブックマークに追加

SNSでもご購読できます。

Leave a Reply

*

CAPTCHA