BigQueryの2020年アップデートを（だいたい）全部振り返る

本記事はBigQueryアドベントカレンダーの4日目です。

BigQuery Advent Calendar 2020 - Qiita

みなさんこんにちは。年末ですね。年末やることといえば、振り返り、総集編ですよね。

BigQueryアドベントカレンダーができたのをみて、「そういえば2019年にやったBigQueryのリリースまとめ的なの、2020年やってないな・・・？」と思い立ち2020年のBigQueryアップデートをまとめました。リリースノート開いたらスクロールバーがすごく小さくなり心が折れかけましたが、なんとか気持ちを奮い立たせました。もしもこんな長い記事を最後までスクロールしてくれる方がいたら、感謝の意を表明しますので、Twitterで @yutah_3 まで教えていただけると、泣いて喜びます。

以下、カテゴリに分けてアップデート紹介します。

1. SQL関連
2. UI関連
3. ワークロードマネジメント関連と課金関連
4. 性能最適化関連
5. ML関連
6. Data Transfer関連
7. セキュリティ関連
8. BI Engine関連
9. その他 (Firebase関連など)

SQL 関連

DDLの拡張（特に外部テーブル関連、エクスポートなどは便利だと思いました。DDLによる外部テーブル作成・変更ができることで、データアナリストがCloud Storageに置かれたデータの分析が楽になります。）

• CREATE EXTERNAL TABLE : Cloud StorageやCloud SQL、Bigtableなどのデータを利用した外部テーブルを作成する
• DROP EXTERNAL TABLE : 外部テーブルをDROPする
• ALTER TABLE ADD COLUMN : TABLEのカラム追加
• EXPORT DATA : BigQueryからCloud StorageにデータをエクスポートするジョブをSQLから実行できるようになった（従来はAPI or GUI）
• TRUNCATE TABLE : DELETE DMLと異なりオンデマンド課金では無料で実行できる
• DDL におけるユニコード（含む日本語）サポート

スクリプト、ストアドプロシージャーのサポート

• スクリプトを用いて一回のリクエストで複数ステートメントの実行、変数の利用、IFなどの制御ステートメント利用がサポート
• スクリプトの中で地味に便利なのが CREATE TEMP TABLE ステートメントがサポートされたことです。これはスクリプト内部からだけ呼び出せる一時テーブルで、1.キャッシュと同じ仕組みなので読み出しが早い 2.一時テーブル扱いの場合、永続テーブルに有効期限を設定するのに比べてコストがかからない などのメリットがあります。
• ストアドプロシージャを作成しクエリから呼び出せるようになった
• 動的 SQL ステートメントのサポートで EXECUTE IMMEDIATE を利用してプレースホルダを使った動的なSQLが実行可能に
• SQLを用いた実行中ジョブのキャンセルができるようになった。システム プロシージャで SQL から CALL BQ.JOBS.CANCEL を呼び出すと実行中ジョブがキャンセルできる。地味に便利。

個人的に便利だと思った新しい関数、算術演算子

• LAST_DAY : 指定した日付の月の最終日を返してくれる（従来UDFで作ってた方も多いのではないでしょうか。僕もです。）
• 日付算術演算子のサポート : ‘2020–01–01’ + 1 のような演算ができるようになった（従来は DATE_SUB , DATE_ADD などを利用してできた）
• 永続化しない SELECT 文の結果に対する列名の重複のサポート

永続化しないSELECT文の結果に対する列名の重複のサポートの解説

• 認可済みUDF : データセットにアクセスを許可しないでも、ユーザーやグループに対し、ユーザー定義関数を通してならテーブルにアクセスができるようになった
• JSON_EXTRACT_ARRAY の追加による JSON 形式の文字列から配列の抽出（ユーザー定義関数でやってた方は多いかと）
• ORDER BY による NULLS FIRST、NULLS LASTのサポート
• デバッグ ステートメントの ASSERT によるSQLを用いたデータのテスト
• 連結演算子 || のサポート（従来はCONCATだったけど短く書けるように）

その他関数サポート（元々別の関数で同じことはできていたが、互換性を高めるために追加された関数が多かった印象）

• SUBSTRING、ASCII、CHR、INITCAP、INSTR、LEFT、OCTET_LENGTH、REGEXP_EXTRACT、REGEXP_INSTR、REGEXP_SUBSTR、RIGHT、SOUNDEX、TRANSLATE、UNICODE

クエリのスケジューリングがGA

• DDL, DML などのクエリを定期的に実行できる（実体化したデータマートの更新などに便利）
• ユーザーアカウントでしか登録した定期実行するクエリを実行できなかったのが、システムが利用することを想定したサービスアカウントを用いて設定できるようになった

DMLのクオータが無制限になった

• 詳細ブログでBigQueryにおけるDMLの仕組み、トランザクションの仕組みが解説された

UI関連

BigQuery Cloud ConsoleでのData Catalogの検索とオートコンプリート

• メタデータ管理サービスであるData Catalogに入れたメタデータをBigQueryのリソースパネルから検索し、データを検索できるように
• あわせて、クエリエディタでオートコンプリートされる

オートコンプリートを使っている人の様子 — Twitter@hiro_koba_jp さんより引用

課金関連とワークロードマネジメント関連

SLAが 99.99% に改定

• SLA規約が改定されが月間 SLA 99.9% から月間 SLA 99.99% に（ブログ）
• 従来通りメンテナンスウィンドウの除外なく利用ができる（そもそもBigQueryのメンテナンスが透過的だからですが）

BigQuery Sandbox がGAに

• クレジットカード不要でクエリ データ処理を毎月 1 TB まで、ストレージを同 10 GB まで無料で、BigQueryを利用できる仕組み（ブログ）
• BigQuery ML、GISも利用できる

サンドボックスで気軽に遊んでみた方の記事 — Twitter @hctaq_srp さんより引用

BigQuery Reservations が東京、大阪で利用可能に

• BigQuery Reservations はBigQueryを従来のオンデマンド課金（スキャン量に対する課金）から、コンピュートリソースであるスロットを予約（リザーブ）する課金モデルに切り替えることができる課金体型を提供する仕組み（解説スライド）

BigQuery Reservations の説明 — Cloud On Airより引用

• BigQuery ML の Matrix Factorization（行列分解）が使える他、BigQuery MLの機能が全て定額で利用できる
• スロットは 100 スロットから購入できるように（ブログ）
• また、組織内のバッチやアドホックなどの用途ごとに、パフォーマンスを確保するワークロードマネジメントの機能が提供された
• BigQuery Reservations のスロット購入が年、月単位に加えて、最小60秒の秒単位課金をサポート （ブログ）
• 同じ組織内部でプロジェクトによりReservationsによる定額課金モデルと、オンデマンドのクエリ従量課金を使い分け可能になった
• BigQueryのクエリ実行プランの詳細ドキュメントが公開された

大規模にBigQuery Reservations を利用するユーザーのブログも出てきました

6,000スロットを使うBigQueryのリソース配分最適化への挑戦

BigQueryのジョブやテーブル、BigQuery Reservations の状態、など様々な情報をクエリで取れるINFORMATION_SCHEMAの追加

• データセット、ジョブ、ジョブのタイムライン、BigQuery Reservations 関連（予約スロット、割り当て状態など）、ストリーミング、ルーティン（永続UDF、ストアドプロシージャ）、テーブル、ビュー などの情報をクエリから取得できる

INFORMATION_SCHEMAの様々な情報を整理してみれるBigQuery Admin Panelのプレビュー（ビデオ）

Admin Panel UI からクエリやスロットなどのモニタリングができる — Google Cloud Next OnAir ’20 BigQuery 最新情報より引用

性能最適化関連

• パーティション分割で従来の日付に加え、時間、月、年がサポートされた
• パーティション分割でレンジバケット（整数型）がサポートされた
• マテリアライズド ビューがサポートされた （Google Cloud ブログ、na0さんによる解説ブログ）
• クラスタ化テーブルが単独で利用可能になった（従来はパーティション分割との併用が必須だった）

ML関連

BigQueryには、 BigQuery 内部で SQL で ML を行う BigQuery ML 機能があります。SQL でモデルを作成し、予測も SQL で実行できるため、 BI ツールの埋め込みなどにも最適です。ここにも多くのリリースがありました。

機能追加

• Matrix Factorization (行列分解)のサポート （BigQuery Reservationsのみで利用できます）
• BigQuery ML で作成したモデルのエクスポート（Tensorflow Saved Model形式、ブースター形式）
• ARIMA時系列モデルのサポート

AI Platform とのインテグレーション

• BigQuery ML でCREATE MODELを行う際にAutoML Tables（BigQuery MLはモデルは自分で指定するのに対し、AutoML TablesはBigQueryのデータを用いてモデルも自動で選択・アンサンブルなどしてくれる）を利用できるように
• BigQuery ML でCREATE MODELを行う際に XGBoost や DNN モデル利用することが可能に。（実際は AI Platform上で学習させ、BigQuery MLで利用する）

BigQuery MLにAutoML Tables、XGBoost、DNN、ARIMAが来たのでおさらい - Qiita

Data Transfer関連

BigQuery Data Transfer Service は DWH の移行やオブジェクトストレージからのデータ取り込み、各種 Google サービスやサードパーティーソースからのデータ取り込みをサポートするサービスです。

• 従来の東京リージョンに加え大阪リージョンをサポートした (日本国内でクロスリージョンでのデータセットコピーが可能になった、DRに使える)
• Cloud Storage からの転送が上書き、差分追加に対応した
• Amazon S3 からの転送がGAに
• Amazon Redshiftデータウェアハウス移行がGAになるとともに、VPC構成のRedshiftからの転送をサポート

フューチャーアーキテクトさんによるわかりやすい Data Transfer の解説 : GCP連載#7 GCPのData Transfer Serviceを使って簡単にS3からBigQueryにデータ転送をしてみる

セキュリティ関連

データ持ち出し防止やIP制限、専用線接続をサポートするための機構である VPC Service Controls に加え、 BigQuery 自体が備えるセキュリティの機能にも強化がありました。

• テーブルへのアクセス制御（ACL）が一般提供開始（ブログ）
• 列レベルセキュリティが一般提供開始（ブログ）
• 列レベルセキュリティはより組織内で統制を効かせることができるようにメタデータ管理サービスのData Catalogと連携。タグ付けすると一括でアクセス制御ができる
• DLPで検出したマイナンバーカードや日本の銀行口座番号、運転免許証番号のカラムなどを自動的に制御することも可能

Google Cloud のデータ ガバナンス - データへのアクセスとデータ検出を保護する新たな方法 | Google Cloud Blog

データレイク関連 / データ取り込み関連

BigQuery から Cloud Storage のデータをクエリできる外部テーブル機能の拡張

• 外部テーブル機能は データレイクとしての Cloud Storage のデータをBigQuery に読み込まずにクエリする、データレイクハウスを実現するための機能です（ 2015年からあります）
• 従来のAvro、CSV、JSON、Datastore/Firestoreエクスポートに加え、 ORC, Parquetフォーマットをサポート
• Avro、ORC、JSON、CSVのHiveパーティションがサポートされより効率的にスキャンが可能になった
• DataprocでHadoop/Sparkジョブを実行して様々なETL処理を行っている場合に、データ置き場はCloud Storageとしつつ、BigQueryをクエリエンジンとしてだけで用いることができる

BigQuery から Cloud Storage のデータ読み込み（ロード）のサポート拡張

• Avro、CSV、JSONに加え、ORC、Parquetのデータ読み込みをサポート
• 外部テーブル同様にHiveパーティションをサポートした
• AmazonS3 BigQuery Data Transfer Service でも同様のフォーマットがサポート

BigQuery にリアルタイムでデータを取り込むストリーミング挿入のデフォルト取り込み可能な秒間あたりの行数の増加

• ストリーミング挿入が次世代になり、デフォルトのソフトクオータの上限が10倍になった（ブログ）
• デフォルトのソフトクオータはさらに追加申請可能

Cloud SQLへの連携クエリ機能のサポート

• Cloud SQL （MySQL / PostgreSQL） に対してBigQueryからクエリを実行できるようになった（外部テーブル）

BigQuery Storage API が東京、大阪含むリージョンで利用可能に

• BigQuery Storage API を使うとクエリの結果やテーブルの内容をgRPCを利用した並列読み出しが行えるため、高速になるため、BigQueryのデータを外部で処理したい際に有用
• Jupyter Notebookからはpandas_gbqあるいはgoogle-cloud-bigqueryでStorage APIを介して高速にBigQueryから結果をDataFrame形式で取り出しできる（これ自体は2019年から存在）
• Hadoop/Spark ではBigQueryコネクタでオプションを指定することでDataFrame形式で読み出しできるようになり、一部ジョブをSparkで実行するのが楽に、高速になる
• ODBC/JDBCドライバにも対応している
• Storage APIでキャッシュが利用可能になった

Storage API の性能を確認したブログ : BigQuery Storage APIの性能を試すため １億件回してみました

ストリーミング・リアルタイム関連

Dataflow SQL が GAに

• BigQuery から SQL を実行し、PubSubのストリーミングデータとBigQueryのマスタデータをJOINなどをリアルタイムでできるDataflow SQL がGAしました
• 実行結果はBigQueryのViewとしてBigQuery上から確認でき、その際の最新のウィンドウ内での集計が確認できます

GCSに置かれたCSVをマイクロバッチ的に読み込む、Dataflow Templateにデッドレターキューが実装された

• Dataflow Template はGUIだけで簡単に使えるサーバーレスのデータ処理サービスです
• BigQueryの無料でつかえるロードジョブは、バルクでの読み込みに最適化されています。そのため、短い間隔でデータを入れるには、ストリーミングインサートを使うと、すぐにデータが反映できるようになっています
• これを簡単に使えるのがText Files on Cloud Storage to BigQuery (Stream) テンプレートです。GCSに配置されたファイルを検知して、BigQueryのテーブルにマイクロバッチ的にストリーミングインサートを用いて反映してくれるようになります
• 今回はこのテンプレートに、デッドレターキュー（形式エラーなどで該当テーブルに挿入できなかった値をいれておく場所）をBigQuery上にスキーマ設定なしに入れてくれるようになりました
• エラーで挿入できなかったレコードもBigQueryのテーブルにはいるので、リカバリがUDFとSQLだけですぐに行えます（データフォーマットを直してINSERT文を使えばOK）

Cloud Dataflow がテンプレートにより気軽に使えるサーバーレスのサービスに進化した話

BI Engine 関連

BigQuery BI Engine （解説動画）はBigQuery内のインメモリの分析エンジンです。BIツールから利用することで高速なドリルダウンを可能にします。2020年2月にGAになりました。

• データポータルからのカスタムクエリサポートの追加

GIS関連

機能改善

• ST_GEOGFROMTEXT が planarパラメーターをサポート
• ST_GEOGFROMGEOJSON と ST_GEOGFROMTEXT が make_validパラメータによるポリゴンの自動修正をサポート

新規追加

• ST_Simplify
• ST_CONVEXHULL
• ST_DUMP

その他

Google Workspace — コネクテッド シートのGA

• コネクテッド シートがGAに
• Google SpreadsheetからBigQuery上のデータをみながら、スプレッドシート上の操作をBigQueryのクエリに変換し分析ができる
• 数TBのデータでもスプレッドシートのUIでピボットテーブルを用いたクロス集計などができる（ブログ、事例ブログ）

Google Analytics 4プロパティでBigQueryへのエクスポートがサポート

• 新しいGoogle Analytics 4プロパティが出て、BigQueryへのストリーミングエクスポートが使えるように
• 無料で使えるBigQuery サンドボックスとの併用が可能

BigQueryで作成したセグメントを、Firebase へインポートが可能に

• Firebase はいままで Google Analytics for Firebase やPerformance Monitoring、Predictions など多くのサービスで BigQuery へのデータエクスポートをサポートしてきましたが、今回はインポートです
• BigQuery でつくったユーザーセグメントを Firebase に取り込み、 RemoteConfig や In-AppMessaging 、Cloud Messaging などでターゲットに利用できるようになりました
• BigQuery で Web や CRM などのデータ、アクティビティログと組み合わせてターゲットを分析、セグメントを作成して、 Cloud Messaging でプッシュ通知を行い、アップリフト施策を行ったり、A/B テストのセグメント作成の精度を上げたりできます

Firebase 公式ドキュメントより利用イメージの引用

Firebase Crashlytics がストリーミングエクスポートのサポート

• Firebase のネイティブアプリのクラッシュ分析ツールである Firebase Crashlytics が BigQuery へのバルクエクスポートに加え、ストリーミングエクスポートをサポート
• Firebase Crashlyticsで収集したアプリケーションのクラッシュ情報をBigQueryでリアルタイムで分析できる
• 新しいアプリバージョンをローンチした際に便利

BigQuery/Dremel の新しい論文が出た（論文リンク）

• BigQuery (Dremel) の新しい論文がオンライン開催だったPVLDBでひっそり公開
• 今まで公開されていなかったGoogle社内ユースケース、外部テーブルサポートの経緯（Lakehouse的な考え方）や分散インメモリシャッフルの実装の経緯と仕組みについて触れられている
• BigQueryのクエリプランの特徴である投機的実行などの仕組みも解説

まとめ：2021年は・・・？

オンラインで開催されたイベントGoogle Cloud Next OnAir ’20では多数のプレビューが発表をされています。発表された機能ですでにリリースされているものもあれば、プレビュー中のものもあります。期待して待っておきましょう！

BigQuery最新情報の日本語セッション

個人的には以下のプレビューが気になっています :

• マルチタブエディタ

マルチタブエディタに対応 — Next OnAir ‘20より引用

• BI Engine の BigQuery コンソールからの有効化とTableau、Looker対応
• BigQuery Slots Autoscaling のプレビュー（スライド , ビデオ）

Slot Autoscaling がBigQuery Reservationsに対応。Reservationsの欠点だった、スロットがバーストしないという部分を補うモデルで予算は最大を管理しつつ、すぐにスケールアウトできる。

• 適切なBigQueryのスロットを提案するSlot Recommenderのプレビュー発表（ビデオ）
• BigQuery Omni （マルチクラウドのBigQuery）
• Data QnA （自然言語クエリによる問合せ）

2021年も期待してBigQueryを使っていきましょう。それでは、良いお年を！

Disclaimer : 本内容は個人の意見であり、所属する組織を代表するものではありません。