イクジニアブログ

育児 + エンジニア = イクジニア

BigQuery 2021/7月のupdate情報まとめ

6月のまとめに引き続き7月のまとめもいってみましょう!

nii-yan.hatenablog.com

ODBC/JDBC driverのバージョンアップ

6月に発表された型に桁数を持たせられる機能の他bugfixなどが含まれています。

マテリアライズドビューが集約無しクエリに対応

今まではSUMやAVGなどの集約関数を使ったクエリでしかマテビューが作れなかったが、集約関数を使わなくてもマテビューが作成可能になりました。
クラスタ化や、フィルタリングの検証に使えます。
クラスタ化されたマテビューは、indexのように機能し、元テーブルに対してのクエリもマテビューからのデータを参照するようになります。

BigQueryではoracleのクエリリライトと同じような機能があり、a というテーブルを元に、 mv_a というマテビューを作った場合、 a に対するクエリでも mv_a を見たほうがクエリコストが低いとクエリオプティマイザが判断した場合、内部的に mv_a を参照される場合があります。

マテリアライズド ビューの概要

BigQuery は、事前に計算されたマテリアライズド ビューの結果を利用し、可能な場合にはベーステーブルからの差分のみを読み取って最新の結果を計算します。マテリアライズド ビューに直接クエリを発行できるのはもちろん、ベーステーブルに対するクエリを処理するために BigQuery オプティマイザーでマテリアライズド ビューを使用することもできます。

公式docでは以下のような例が記載されています

データの再クラスタ化

ベーステーブルと異なるクラスタリング スキームを使用したクエリの多くを発行する場合、マテリアライズド ビューを使用するとクエリのパフォーマンスが向上する可能性があります。

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY y
AS SELECT * FROM dataset.base_table;

データの事前フィルタリング

テーブルの特定のサブセットのみを読み取るクエリを頻繁に実行する場合は、マテリアライズド ビューを使用してクエリのパフォーマンスを向上させることができます。

CREATE MATERIALIZED VIEW dataset.mv
CLUSTER BY x
AS SELECT * FROM dataset.base_table
WHERE y < 1000;

データの事前計算

コンピューティング コストが高い関数を頻繁に使用する場合や、大きな列から少量のデータを抽出する場合は、マテリアライズド ビューを使用するとクエリのパフォーマンスが向上します。

CREATE MATERIALIZED VIEW dataset.mv
AS SELECT x, JSON_EXTRACT(string_field, "$.subfield1.subfield2") subfield2
FROM dataset.base_table;

発行したクエリがマテビューを参照したかどうかはクエリプランのデータスキャン量などを見ると確認することが出来ます。

マテリアライズドビューが内部結合クエリに対応

今までは結合を使えなかったが、内部結合(inner join)を使ったクエリでもマテビューが作成可能になりました。
クロス結合、完全結合、左結合、右結合は非対応
ファクトテーブルとディメンションテーブルのように、頻繁にjoinされるテーブルのクエリはマテビューを作っておくことでパフォーマンス改善が可能に
(テーブルの参照順はviewとクエリで同一でなければいけない)

CONTAINS_SUBSTR関数

式(文字列や数値、配列など)に値(stirngで指定)が存在するかどうかを確認出来ます。(正規化して比較)
式が文字列の場合大文字小文字の区別をしません。

CONTAINS_SUBSTR関数はWhere句での条件にも指定ができ、テーブル全体から特定の文字列が入っている行を検索など全文検索ぽいことも出来る非常に便利な関数です。

使用例

■戻り値がTrueとなるパターン
SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result;
SELECT CONTAINS_SUBSTR((23, 35, 41), '35') AS result;
-- '\u2168'は正規化するとⅨのため trueとなる
SELECT CONTAINS_SUBSTR('\u2168', 'IX') AS result; 

■Where句でも使用可能
-- Recipes テーブルのすべての列から値 toast を検索し、この値を含む行を返します。
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR(Recipes, 'toast');
-- Recipe テーブルの Lunch 列と Dinner 列で値 potato を検索し、いずれかの列にこの値が含まれている場合にその行を返します。
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch, Dinner), 'potato');

PIVOT関数

エクセルのピボットテーブルと同じような操作が関数で可能に。
(以前もPreviewで使用出来ましたが7月にGAになりました。)

SQLからreservations(slot)の管理が可能に

slotの購入、reservationの作成、reservationへのアサインなどreservationsに関する操作がSQLで実行可能になりました。
スケジュールドクエリと組み合わせることで、特定の時間のみslotを増やすなどの設定が容易に行えます。

-- slotの購入
CREATE CAPACITY
-- reservationsの作成
CREATE RESERVATION
-- reservationsへのアサイン
CREATE ASSIGNMENT
-- slotの開放
DROP CAPACITY
-- reservationsの削除
DROP RESERVATION
-- アサインからdrop
DROP ASSIGNMENT

6月には権限設定がSQLから出来るようになったりと、BQではAPIだけではなくSQLからも実行出来ることが増えて来てますね。

INFORMATION_SCHEMAでDMLの結果が取得可能に

INFORMATION_SCHEMA.JOBS_BY_(PROJECT / USER)にdml_statisticsカラムが追加
・INSERT and MERGEによって追加された行数
(loadは含まない)
・UPDATE and MERGEによって更新された行数
・DELETE, MERGE and TRUNCATEによって削除された行数
が確認出来ます

SELECT
  SUM(dml_statistics.inserted_row_count),
  SUM(dml_statistics.updated_row_count),
  SUM(dml_statistics.deleted_row_count),
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY)
  AND TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = "QUERY"
  AND statement_type != "SCRIPT";

INTERVAL型

時間間隔を保持出来るINTERVAL型を使えるようになりました。
(フォーマット:Y-M D H:M:S[.F])

使用例

CREATE TABLE
  yu_work.interval_test1 ( aa INTERVAL);

-- データ登録例
INSERT INTO  yu_work.interval_test1 VALUES (INTERVAL 1 DAY);
INSERT INTO yu_work.interval_test1 VALUES (CAST('2-4 26 22:00:00' AS INTERVAL));

SELECT
  *
FROM
  yu_work.interval_test1 ;

f:id:nii_yan:20210809070934p:plain

INTERVAL型の使い所について考察しているブログもありました!

【BigQuery】2021.7.27にPreview公開されたINTERVAL型を試してみる - とりゅふの森

7月のアップデートは以上となります。

INFORMATION_SCHEMAはどんどん便利になっていきますね。今後load数や、データ量なども取れるようになっていくといいな〜と思っています。