Programming Room.

Google Cloud Tips.

BigQuery.

基本的な知識と使い方.2023/04/29

　BigQueryの基本的な知識と使い方についての説明です。
　私の調査・経験不足のため、およびこのサイトをメンテする余裕の不足のため、ちょっとしか説明できていません。

　BigQueryは機能が非常に多いので、以下は対象外とします。

BigQuery ML
BigQuery Omni
レガシーSQL
従来のストリーミング

おしながき

BigQueryについて.
DHWについて.
BigQueryのデータ構造.
使い方.

BigQueryについて.

概要.

　検索対象が大量でも検索が高速ということで人気のGCPのサービスです。あまりの人気のためかAWS(Amazon Web Services)やMicrosoft AzureにもBigQuery OmniとしてGoogleがサービス提供しています。おもな特徴は以下のとおり。

検索対象が多くても検索が速い。
検索対象のデータ量に応じて課金されるので、調子に乗って使っていると、いつの間にか利用料金が莫大になることがある。
各種BI(Business Intelligence)ツールのデータストアとして利用可能。
サーバレスでメンテナンスフリー。

歴史.

　BigQueryはGAE/GCSとともにGCPの初期からあるサービスで、すでにリリースされてから10年以上経っています。ずっと使い続けている人も少ないと思うので、改めて調べて(私の主観で)主だったものだけ列挙してみました。

2011/10 たぶん初版のリリース。
2013/09 ストリーミングの追加。
2016/09 標準SQL/DMLのサポートでデータの編集が可能に。
2018/04 東京リージョンで提供開始。
2019/04 大阪リージョンでも提供開始。
2020/02 BI Engineが一般提供(Generally Available)に。
2020/03 Storage APIが一般提供に。
2020/04 Reservationsが一般提供に。
2020/06 Flex Slotsが一般提供に。
2021/12 BI Engine SQL Interfaceが一般提供に。

　リリースノートを見ると分かりますが非常に更新が多く、ずっと進化を続けていることがわかります。GCPの中でこんなに更新が多いのは他にはGCEくらいでしょうか。

使ってた感想.

　実際に使ってみた感想ですが、使い方によります。

単純にデータを一括して読み込んで、単純に検索して、終わったら捨てるだけなら簡単。
ストリーミングで特定のイベントログを書き込んで、特定のものを検索するなら、多少は技術が必要だがまだ簡単。
ある程度以上の規模でBIツールのDWHとして使用するなら、多くの制限を回避しながら注意深く設計・実装が必要になり、難易度が高くなる。

　次の「DHWについて.」で説明している使い方の前提を間違えなければ、そんなに難しいものではありません。しかしその前提を無視するなら、BigQueryの多くの制限の回避が困難になり、いろいろと出来ないことや制限が発生することになります。つまり前提に従って使用することが重要です。

DHWについて.

　データウェアハウス(Data WareHouse)を直訳してしまうと「データ倉庫」になります。しかしこれではデータベースやデータレイクなどとの区別がつきません。
　データウェアハウスという言葉は、アメリカの科学者William H. Inmonさんの1990年の著書の中で提唱されたもののようです。この中で『意志決定のため、主題別に編成・統合され、時系列で、削除や更新しないデータの集合体』と定義されています。ちゃんと定義が存在するところは、定義の曖昧なデータレイクなどと対照的です。

意志決定のため ‐ BIツールは組織の意思決定を助けるためにデータを可視化するものなので、BIツールとペアで使うのに適しています。
主題別に編成・統合 ‐ 目的に応じて構成したスキーマを持つテーブルと解釈すればいいと思います。
時系列で、削除や更新しない ‐ これがデータベースとのいちばん大きな違いで、基本的に追記のみ行います。

　つまりデータウェアハウスは、データベースとは別物です。
　上記の定義の中で、特に誤解が多いのは『時系列で、削除や更新しない』のところです。データベースがデータの永続化を目的としており自由にデータを書き換えられるのに対し、データウェアハウスでは基本的に追記のみです。データウェアハウスに記録するのはデータではなく、イベントのログと考えれば解りやすいと思います。

　もちろんBigQueryもDHWの1つですから、この定義にしたがって設計されています。BigQueryでは標準クエリを使えばある程度は更新・削除が可能になりましたが、書き換えには制限があり積極的に使うようには作られていません。そもそも数年前はデータの更新・削除は出来ず、「追記していって、いらなくなったらテーブルごと削除」という使い方のみ可能でした。BigQueryはそのころから基本的な設計は変化していないため、データの更新・削除は限定的にしかできないままとなっているものと思われます。
　またDWHに記録するのはイベントのログですから、これを更新・削除するということは歴史を改ざんするのと同じであり、基本的には悪事を行った痕跡を消す時くらいしか積極的に使う理由がないはずです。

BigQueryのデータ構造.

構成の概要.

　BigQueryの構造は以下のような階層構造になっています。

データセット :複数のテーブルを格納できる入れ物。テーブルのグルーピングやアクセス制御に使用します。
テーブル : 主題ごとにまとめられ、行で構成されるデータの集合。リレーショナルデータベースと同様の表です。
行 : リレーショナルデータベースと同様で、複数のフィールドから構成されるデータのアクセス単位です。
フィールド : データの最小の単位で、名前・型・属性で構成され、行ことに異なる値を保持します。

データセット.

　単にテーブルを格納する入れ物として使ってもいいですし、テーブルを機能ごとに分類したり、アクセス制御の単位ごとに分けたりなどに使用します。プロジェクトあたりのデータセット数には制限がありません。

　データセットにアクセス権を設定すると、そのデータセット内のテーブルすべてに同じようにアクセス権を継承できます。そのためアクセス制御単位にデータセットを分けるのも便利です。

　残念なことにデータセットは階層構造に出来ません。プロジェクト直下に1階層のみフラットに作れます。

テーブル.

　テーブルはいずれかのデータセットの中に作ります。プロジェクト直下に作ることは出来ませんし、複数のデータセットに属することもできません。データセットあたりのテーブル数には制限がありません。

　テーブルはリレーショナルデータベースと同様に、各行が同じスキーマを持ち、複数の行で構成可能な表の形をしています。

　テーブルにもデータセットと同様にアクセス権の設定ができます。

　論理ビューやマテリアライズドビューも作ることができ、検索においてはテーブルと同様に扱えます。

行.

　データの最小のアクセス単位であり、複数のフィールドで構成されます。リレーショナルデータベースの行と同じです。テーブルは複数の行の並びで構成されます。テーブル内の行はすべて同じスキーマを待ちます。

　行に対してもアクセス権を設定することができます。

フィールド.

　構造体で言えばメンバあるいはフィールド、リレーショナルデータベースのテーブルの列(カラム)と同じです。主に列名、データ型、モードで構成されます。

　データ型は格納するデータの型です。データベースなどと同様に、多くの型に対応しています。

　モードは以下の3つの選択肢があります。

モードの値	説明
Required	値としてnullが許されない。作成済みのテーブルにフィールドを追加する際は使用不可。
Nullable	nullを使用可能。これがデフォルト。
Repeated	配列。配列は型ではなく、モードで実装されている。要素数0とnullは区別できない。

スキーマ.

　行内のすべてのフィールドの定義をまとめて、スキーマとして扱います。
　BigQueryは書き込み時にスキーマを決めるスキーマ・オン・ライトですので、基本的にテーブル作成時に同時にスキーマも指定します。テーブル作成後でもフィールドの追加・削除は出来ます。フィールド名の変更やデータ型の変更は、制限があり自由には出来ません。注意が必要です。

使い方.

　BigQueryはデフォルトで有効化されているようなので、特にAPI/サービスの有効化は必要ないようです。もし無効になっていたら対象のプロジェクトでGCPコンソールのAPIとサービスを表示し、リストから「BigQuery」を選んで有効化する必要があります。

　あとは以下のとおり順に行うだけです。

データセット・テーブルの作成.

　データセット → テーブルの順で作ります。作成方法はGCPコンソール、bqコマンド、APIどれでも作れます。最低名前を決めるだけで作れますが、ロケーションには注意してください。指定しないとusマルチリージョンになります。名前・ロケーションは後から変更できません。

スキーマの指定.

　テーブルの作成では基本的にスキーマの指定が必要になります。空のテーブルであれば、最初のデータ読み込み時にスキーマを指定することもできます。ただしこの場合はモードが指定できないと思われます。
　読み込み時にスキーマの自動検出も可能ですが、JSON/CSVではデータ型が曖昧で思いどおりのデータ型になってくれない可能性があります。データ型を思いどおりに決めたい場合は、自動検出はあてにしない方がいいでしょう。また自動検出に使われるのはデータの先頭500行のみという制限があります。未確認ですが、もし先頭500行がすべてnullの列があった場合、スキーマを決められなくてエラーになると思われます。

テーブルにデータを読み込む.

　テーブルにデータを読み込む方法は、以下に説明する3種類が用意されています。Data Transfer Serviceなどの機能も、Googleがこっそり裏口を用意していない限り、これら3つのいずれかを使用しているだけです。

読み込みジョブによるバッチ読み込み.

　あらかじめ用意されたデータを一括して読み込む方法です。検索対象のデータをあらかじめ用意できるなら、この方法が最も簡単です。データ量にもよりますがそれなりに時間がかかるので、ジョブとして実行されます。読み込み開始時にテーブルを空にしてから読み込むか、既存の内容に追記するかを選択できます。

　GCPコンソールで手作業でも読み込みが可能ですが、テーブル作成と同時にしか読み込みできないようです。もしくはAPIで実行します。SQLでは実行できないはず。

　最大6時間までという制限があります。

INSERTステートメントで読み込み.

　SQLに慣れた人ならこれを使いたいところでしょうが、大量に読み込んだり、頻繁に読み込むには向きません。『単一行を更新または挿入する DML ステートメント』には以下の記載があります。基本的には読み込みジョブで読み込むべきです。テスト用など少量なら問題ありません。

INSERT DML ステートメントは、控え目に使用するように意図されています。
挿入では、読み込みジョブと同じ変更割り当てが消費されます。
ユースケースで単一行の挿入が頻繁に行われる場合は、代わりにデータのストリーミングを検討してください。

　SQLの実行は、実行ごとにクエリジョブとして実行されます。これはSELECTによる検索であろうが、INSERT/UPDATE/DELETE/MERGEなどを用いたデータの変更であろうが、同じです。そしてジョブによるテーブルのデータの変更は1日あたり1500回までの制限があります。手作業でクエリするなら問題になりませんが、プログラムでこまめにクエリを実行していたら、あっという間に超えかねません。規模と設計・実装によってはクリティカルな制限になるので、注意が必要です。

　クエリジョブも最大6時間までの制限があります。

注意事項.

　クエリジョブのジョブ数のカウントは、ステートメントごとではありません。1回の実行に複数のステートメントが含まれていても、ジョブは1回とカウントされます。

ストリーミング.

　逐次発生するデータを都度書き込みたい場合は、ストリーミングを使います。アクセスログなどのいつ発生するかわからないイベントの記録に適しています。当然、追記しかできません。

　ストリーミングはジョブではないので、ジョブに関する制限に干渉しません。

　ストリーミングは別料金です。BigQueryの利用料金は基本的に保存しているデータ量と、クエリの対象になったデータ量で決まりますが、それらに追加で利用料がかかります。ただしStorage APIでは書き込みに関しては無料枠が2TB/月もあるのが幸いです。

データの検索・参照.

　SELECTクエリを実行して検索結果を得ます。あるいはBIツールなどを接続して、BIツールからアクセスします。

データの整理.

　DWHでは基本的に追記のみ行うものなので整理があるのは変なのですが、BigQueryでは標準SQLである程度のデータの変更が行えます。ただしデータの変更は頻繁に行うものではなく、最小限になるように注意が必要です。理由は『INSERTステートメントで読み込み.』にも書いたとおりですが、ジョブによるテーブルのデータの変更は1日あたり1500回までの制限があるためです。手作業なら問題になることはまずないと思いますが、プログラムで行うなら状況によっては簡単に超えかねません。

　データの整理は必要に応じて、UPDATE/DELETE/MERGEなどのクエリを駆使して、テーブルに記録したデータを修正・削除します。これらのクエリも検索と同様に課金対象です。

　テーブルのデータをすべて消したいならDELETEではなく、TRUNCATE TABLEを使用します。このステートメントはスキーマなどのテーブルのメタデータは削除しません。テーブルの行をすべて消すだけです。DELETEは削除対象のデータ量に応じて時間と料金がかかりますが、TRUNCATE TABLEなら一瞬で消えますし料金も発生しない点で異なります。

後片付け.

　テーブルやデータセットが不要になったら、削除します。作成と違って簡単に、しかもすぐに消えます。これらの操作は料金が発生しません。テーブルはGCPコンソール、bqコマンド、API、標準SQLのいずれでも削除が可能ですが、データセットはSQLでは操作できません。