BigQuery FAQ

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

最終更新



Google Cloud Platform 最強のサービスと言っても過言ではないデータウェアハウスエンジン BigQuery に関するよくある質問をまとめました。

目次

BigQuery の基礎

BigQuery とは何か?

BigQuery とは、Google が提供するデータベース・データウェアハウスサービスで、Google のクラウドサービス Google Cloud Platform (GCP) の一部です。

BigQuery で何ができる?

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

データベースなのでおおむねどのようなデータでも格納できますが、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 は管理の手間が少ないというのは大きなメリットです。

BigQuery は高速ってこと?

状況によります。1レコードしかないテーブルから SELECT でデータを取り出す場合、おおよそ 2~5秒程度かかります。これは Oracle・MySQL・PostgreSQL などと比較すると非常に遅いといえるでしょう。

また、Redshift・SQL Data Warehouse などのクラウドサービスでのデータウェアハウスと比べても、遅いです。

しかしながら、BigQuery は大量データに強いという特徴があります。数百万レコード・数千万レコードあたりから BigQuery が有利になり、数億・数十億レコードともなると BigQuery が圧倒的に有利です。

BigQuery は管理の手間が少ない、とはどういうこと?

当ページ管理人が考える BigQuery 管理の楽な点は下記です。

  • インスタンスの概念がないので、インスタンスを起動したり停止したりということを考えなくてよい。
  • インスタンスの概念がないので、インスタンスサイズ (CPU・メモリ等) を考えなくてよい。
  • 容量の上限が事実上ないので、容量監視やディスク追加を考えなくてよい。
  • インデックスがなく常にフルスキャンであるため、インデックスについて考えなくてよい。
  • 圧縮は自動的に行われるため、圧縮タイプなどを考えなくてよい。
  • ソートキーを考えなくてよい。

プロジェクト・データセット・テーブル

データセットとは?

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

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

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

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

データセットのロケーションとは?

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

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

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

ロケーションは下記のような選択肢があります。

  • マルチリージョン
    • US
    • EU
  • シングルリージョン
    • asia-northeast1 (東京)
    • asia-northeast2 (大阪)
    • asia-east1 (台湾)
    • など

ロケーションにより料金が異なります。下記は一例ですが、最安は US・EU です。日本からの利用であれば、基本的には価格が安い US をおすすめします。

ロケーションクエリコスト (1TBあたり)ストレージコスト(1GBあたり)
US$5.00$0.020 / $0.010
EU$5.00$0.020 / $0.010
us-west2
(ロサンゼルス)
$10.35$0.023 / $0.016
us-east4
(北バージニア)
$6.45$0.023 / $0.016
northamerica-northeast1
(モントリオール)
$7.60$0.023 / $0.016
asia-northeast-1
(東京)
$8.55$0.023 / $0.016
europe-west6
(チューリッヒ)
$9.20$0.025 / $0.017

データセットをリネーム・コピーするには? ロケーションを変更するには?

2019/9 より、ベータですがデータセットのコピーができるようになりました。

下記の作業が楽に行えます。

  • 同一ロケーションで、 データセットのコピー・バックアップを取得する
  • 同一ロケーションで、データセットのリネームをする (コピー後にコピー元データセットを自分で削除)
  • 別ロケーションにデータをコピー・移動する。

参考:https://cloud.google.com/bigquery/docs/copying-datasets?en=ja

テーブルとは?

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)
GEOGRAPHY地理情報 (緯度経度)
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 (受信時のデータ)
  • JSON 文字列をそのままマッピングする
    • {“foo”: {“bar”: “hoge”, “baz”: [“data1”, “data2”]}} といった JSON データをマッピングする際、下記のようにする。
      • foo RECORD
        • bar STRING
        • baz STRING REPEATED
  • レイヤ分けをする。例えば
    • 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 はありません。あったらいいなと思うことはありますが、分散環境であるため一意であることをチェックするコストがあまりに高いということでしょう (ちなみに一意制約・UNIQUE KEY は、AWS の Redshift、Azure の SQL Data Warehouse にもありません)。

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

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

  • とりあえず重複してもよいので、データを一時的なテーブルに突っ込む (BigQuery の機能としての一時テーブルではなく、tmp_table_XXXX などの作業用テーブルという意味)。
  • 一時的なテーブルにて PARTITION BY 等を駆使し、重複除去を行う。
  • その結果を、結果テーブルに突っ込む。

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

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

NOT NULL 制約は?

NOT NULL 制約は存在します。下記スキーマ定義の col1 は nullable なので NULL を許容します。col2 は required なので NULL 不可 (=NOT NULL) です。

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

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

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

外部キー制約 (参照整合性制約) は?

2019/09 現在、BigQuery に外部キー制約 (参照整合性制約) はありません。

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

Web UI でカラムを指定して、STRING を INTEGER に変更する、というやり方はできません。

しかしながら下記のように DDL を使うことで比較的簡単に変更が可能です。

-- まずはテーブルを作成
CREATE TABLE `mydataset.mytable` (col1 INT64, col2 STRING)
  AS SELECT 123, 'def'

-- 同じテーブル名で、カラム col1 を CAST し、自分自身を置き換え
CREATE OR REPLACE TABLE `mydataset.mytable`
  AS SELECT CAST(col1 as STRING) as col1, col2 FROM `mydataset.mytable`

カラムの NOT NULL の変更はできる?

Web UI では、required (NOT NULL) を nullable (NULL) に変更することはできます。その逆はできません。

しかしながら下記のように DDL を使うことで比較的簡単に変更が可能です。

-- まずはテーブルを作成
CREATE OR REPLACE TABLE `mydataset.mytable` (col1 INT64, col2 STRING)
  AS SELECT 123, 'def'

-- 同じテーブル名で、カラム col1 を CAST し、自分自身を置き換え
CREATE OR REPLACE TABLE `mydataset.mytable`
  (col1 INT64 NOT NULL, col2 STRING NOT NULL)
  AS SELECT col1, col2 FROM `mydataset.mytable`

カラムの追加はできる?

可能です。すでにレコードが存在する場合、追加したカラムは全レコード NULL になります。カラムは先頭・中間・末尾のどこにでも追加できます (要確認)

カラムの削除はできる?

Web UI からカラムを選択して削除、ということはできません。

しかしながら下記のように DDL を使うことで比較的簡単に可能です。

-- まずはテーブルを作成
CREATE TABLE `mydataset.mytable` (col1 STRING, col2 STRING)
  AS SELECT 'abc', 'def'

-- 同じテーブル名で、カラム col2 を対象にしない形で、自分自身を置き換え
CREATE OR REPLACE TABLE `mydataset.mytable`
  AS SELECT col1 FROM `mydataset.mytable`

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

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

SELECT * FROM `bigquery-public-data.__TABlES__`
ORDER BY table__id

また、INFORMATION_SCHEMA もあります。

一時テーブルについて

一時テーブルとは?

BigQuery において、SELECT の結果は必ずテーブルに格納されます。宛先テーブルとしてテーブルを明示的に指定した場合はそのテーブルに、そうでない場合は「一時テーブル」(Temporary Table) に格納されます。

例えば下記のような SELECT 結果も、一時テーブルに格納されます。

SELECT * FROM mydataset.mytable WHERE col1=1

下記のような参照先テーブルがまったくない SQL も同じです。一時テーブルができます。

SELECT 1

上記 SELECT 1 の「ジョブ状況」タブを確認すると、下記のように「宛先テーブル: 一時テーブル」となっています。

一時テーブルをクリックすると、下記のように具体的なデータセット名・テーブル名を確認することができます。一時テーブルはごく普通のテーブルなので、再度 SELECT したりすることもできます。

なお一時テーブルのストレージ料金は課金されません。また、24時間で削除されます (作成時に 24時間後の expire 設定がなされている模様)。 一時テーブルからの SELECT は (ドキュメントには明記されていませんが) 無料のようです。

一時テーブルの便利な使い方

普通は一時テーブルを意識する必要はないのですが、便利な使い方があるので紹介します。Web アプリやバッチなどから BigQuery に対して SELECT をする場合、(宛先テーブルを指定していなければ) その結果は一時テーブルに格納されます。

トラブル発生時など、一時テーブルが作成されてから 24時間以内であれば一時テーブルの内容を確認することで、問題の切り分けが簡単に行えます。

具体的には、下記のように BigQuery Web UI の左メニュー「クエリ履歴」 → 右画面の「プロジェクト履歴」をクリックすると、そのプロジェクト内で発行されたクエリ一覧が表示されます。

日付で絞ったりSQL 文で絞ったりして SQL を特定し、一時テーブルを参照することができます。

なお、自分以外のアカウントから作成された一時テーブルの中身を見るには、bigquery/admin ロールが必要です。

SQLについて

StandardSQL・LegacySQL とは?

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

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

SELECT col1, col2 FROM [myproject:mydatasert.mytable]

SQLパラメータとは?

SQL インジェクション・セキュリティ対策として、SQL パラメータという仕組みがあります。ただし 2019年9月現在、 Web UI 上では 使用できません。

公式ドキュメントはこちら

SELECT 文は?

使えます。下記のように一般的な SQL が使えます。WHERE・GROUP BY・HAVING・ORDER BY・LIIMT なども使えます。

-- データセット名とテーブル名を指定
SELECT * FROM mydataset.mytable
-- プロジェクト名とデータセット名とテーブル名を指定
SELECT col1, col2 FROM myproject.mydataset.mytable

SELECT に限ったことではありませんが、BigQuery ではテーブル名だけを書くとエラーになります。少なくとも「データセット名.テーブル名」は必須です。

INSERT 文は?

当初は未実装でしたが 2017年あたりから使えるようになりました。 MySQL や PostgreSQL のように複数レコードの一括投入も可能です。

-- カラム指定
INSERT INTO mydataset.mytable (col1, col2, col3) VALUES ('1', 2, 'HOGE');
-- カラム省略
INSERT INTO mydataset.mytable VALUES ('1', 2, 'HOGE');
-- 複数レコード一括投入
INSERT INTO mydataset.mytable VALUES
   ('1', 2, 'HOGE'),
   ('2', 3, 'HOGE'),
   ('3', 4, 'HOGE');

UPDATE 文は?

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

なお、ストリーミングインサート直後でバッファ内にデータがあるときは UPDATE・DELETE 文は実行できません。

MERGE 文は?

使えます。

DELETE 文は?

使えます。

BigQuery の DELETE 文には WHERE 句が必須です。もし全レコードの削除をしたい場合、WHERE true や WHERE 1=1 などをつけましょう。

なお、ストリーミングインサート直後でバッファ内にデータがあるときは UPDATE・DELETE 文は実行できません。

TRUNCATE TABLE は?

2019年8月現在、TRUNCATE TABLE は使えません。

DELETE FROM mydataset.mytable WHERE true で全レコードを削除するか、bq query –destination_table=mydataset.mytable “select 1 where 1=0” などとしましょう。

BigQuery にはロールバックできるようなトランザクション機能はなく、ローロバックセグメントのようなものもないので、DELETE による全行削除は TRUNCATE TABLE と同じと考えてください。

トランザクション

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

よって、複数テーブルに整合性が取れた形でレコードを追加・更新することはできません。そういう用途のデータベースエンジンではない、とあきらめましょう。

ただし、単体の SQL についてはアトミックであることが保証されており、「100万行突っ込むはずが途中でエラーが発生したため 10万行だけ成功してしまった」といったことは起こりません。エラー発生時は元の状態に戻ります。

ジョブ

ジョブって何?

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

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

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

クエリジョブ

SELECT・INSERT・UPDATE・DELETE・MERGE を発行すると、クエリジョブが生成されます。bq コマンドであれば bq query 実行時に生成されます。

「クエリ (query)」とは「質問」「問い合わせ」という意味の英単語なので、本来は “SELECT” だけが対象であるべきだと当ページ管理人は考えます。BigQuery リリース直後は “SELECT” しか存在しなかったのですが、その後 INSERT・UPDATE・DELETE・MERGE が追加される際に「クエリのひとつ」として定義されたので、少しおかしな感じがします。

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

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

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

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

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

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

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

Web UI の左側に下記のような「クエリ履歴」「ジョブ履歴」がありますので、そこから確認できます。 「クエリ履歴」からはクエリジョブ、「ジョブ 履歴」からは「読み込み・コピー・エクスポートジョブ」が確認できます。

そもそもジョブの種類は下記の4つで、それぞれ並列関係なのでクエリジョブだけ特別扱いするのも変な話なのですが、使用頻度を考慮してこういう UI になっているそうです。

  • クエリジョブ
  • 読み込みジョブ
  • コピージョブ
  • エクスポートジョブ

また、コマンドラインから bq ls -j でジョブ一覧を表示することができます。bq ls -j -a で、自分以外が発行したジョブも確認できます。

分割テーブル・取り込み時間分割テーブル ・日付別テーブル・

分割テーブル的なもののまとめ

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

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

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

分割テーブルとは?

分割テーブルは世の中の RDBMS 言うところのパーティションテーブルと言ってよいでしょう。

分割テーブルは特定カラムをキーとして、内部的にテーブルを分割しておく仕組みです。

一般的な RDBMS ではパーティションのキーには任意のカラムを指定できます (例えば売上日とか、法人コードなど)。一方、BigQuery の分割テーブルのキーは TIMESTAMP または DATE のカラムのみです (2019年8月、Integer の Range パーティションが alpha とのことです)。

取り込み時間分割テーブルとは?

取り込み時間分割テーブルとは、分割テーブルの前に登場したもので、パーティションのキーが「取り込み時間」の固定となっている分割テーブルです。

分割テーブルは、利用者が「このカラムをパーティションキーとせよ」と指定できますが、 取り込み時間分割テーブル は「パーティションキーは _PARTITIONTIME である」と決められています。

日付別テーブル

一般的な 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 の権限はどういうもの?

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 など他言語が使えたりするとここに追加される)

Persistent UDF (永続 UDF) とは?

2019年4月の Google Next にて発表されたもので、2019年8月現在アルファです。

Scripting とは?

端的にいうと、PL/SQL です。2019年4月に Google Next にて発表されたもので、2019年8月現在アルファです。

ビュー

ビューとは?

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

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

BigQuery コンソールから作成

bq mk –view コマンドで作成

API で作成

ビューの権限は?

ビューにパラメータを渡せる?

下記のようなにビューにパラメーったを渡すことができるかという意味であれば、誠に残念ながら 2019年9月現在、できません。

-- このようなビューは作成できません
CREATE VIEW mydataset.myview(mycol INT64) AS
  SELECT * FROM myproject.mydataset.mytable
   WHERE col = @mycol

ただ、BigQuery の特性上、仮に上記のようなことができたとしても、クエリコスト削減にはつながらないと思われます。

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

パラメータは渡せませんが、ビューに _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)

スケジュールクエリを使うと、クエリを定期的に実行することができます。

  • どのような SQL を実行するか
  • どのテーブルに結果を格納するか
  • テーブルに追記するか、上書きするか
  • 実行タイミング (毎日N時など)
  • 開始日時~終了日時
  • 通知結果をメールで送信するか

クラスタ

バックアップ・リストア

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

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 export で GCS にファイルとして保存するのがよいでしょう。コピー・エクスポートは無料ですが、コピー先のテーブルのデータや GCS に保存したファイルについてはストレージ料金がかかります。

BigQuery BI Engine とは?

まず、BigQuery にデータを保存し、Tableau や Google Data Studio などの BI ツールでグラフや表形式で閲覧するというのは一般的な使い方です。ただ、BigQuery はそもそも単純な SQL でも数秒かかるため、GUI での操作には若干のもたつきがありました。

BigQuery BI Engine は、BigQuery 側にキャッシュなどの機構を追加し、BI ツールからアクセスする際のクエリを高速化する機能です。キャッシュ領域は 1GB 単位で確保することができ、料金は 月額 $30/GB 程度です。

注意点は下記です。

  • BI Engine は、BI ツールから使う際に高速化するための仕組みです。BI ツールを使っていない場合は効果がありません。
  • 2019/9 現在、対応している BI ツールは Data Studio のみです。Tableau 等は未対応です。
  • BigQuery のテーブルへのアクセスは高速化しますが、2019年9月現在、ビューに対しては高速化しません。

有効期限・自動削除

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

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 Data Transfer Service

料金

BigQuery の料金の概要

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

クエリ料金とは?

クエリ料金は SELECT 文を実行したときにかかる料金です。

具体的な金額はロケーションによって異なり、下記のようにデータ処理量にて決まります。

  • 東京: $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日更新しなかったテーブルは、「長期保存」と呼ばれる状態になり、ストレージ料金が 50~70% になります (率はロケーションにより異なる)。これはお得な仕組みですのでぜひ活用しましょう。

アクティブストレージと長期保存について詳しく教えて

新規で作成したテーブルは必ず「アクティブストレージ」です。

テーブル内容に 90日間変更を加えなかった場合、「長期保存」に変わり、それ以降のストレージ料金が安くなります。もしテーブル内容を加えた場合、また「アクティブストレージ」に戻ります。その後も、90日間変更を加えなかった場合、再度「長期保存」に変わります。

長期保存にするためには?

下記をしないようにします。

  • 読み込みジョブの実行 (bq load など。追記も含む)
  • コピージョブの実行 (bq cp など。追記も含む)
  • INSERT・UPDATE・DELETE・MERGE などの DML の実行
  • CREATE OR REPLACE TABLE によるテーブルの置き換え
  • ストリーミングインサートによるレコード追加

アクティブストレージか長期保存か判断する方法は?

データ投入の料金は?

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

memo: dataset copy

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

SNSでもご購読できます。

Leave a Reply

*

CAPTCHA