SQLの書き換えによるSQL生成システム

[takegue]

SQL が持つ開発体験上の問題点

プログラミングであるような ライブラリへの切り出しやコンポーネント化が難しい

• 仕様上テーブルの識別子が埋め込みであるため集計などの操作のSQLの再利用できないため、CTEの再利用もできない
• 複雑化したSQLでは多段のCTE/サブクエリが発生するが、これらを検証する術が乏しく負債化しやすい。

従来型のテンプレートエンジンシステム

これらの問題を解決するデータモデリング用のフレームワークでは
SQLのテンプレートを利用したSQL生成方法が一般的である。

SQLテンプレート型エンジンの開発体験上の問題点

テンプレートエンジンはSQLが持つ表現能力の問題点の多くを解決するが、同時に次のような問題を発生させる。

• SQLテンプレート自身が有効なSQLではない
  • SQLコードに対するフォーマッターやSQLエンジン、コード解析ツールといったエコシステムのサポートの負担
  • 生成されるSQLが実行しないと評価できないことも多く、コード規模が大規模化にするにつれ急激に開発体験が悪化する
• テンプレートエンジン と SQLエンジンの組み合わせ的複雑化
  • SQL自身は複雑な言語であるが、この上さらにテンプレートやマクロによる生成規則の複雑な情報が重なることから煩雑になる.
  • テンプレートによるSQLの生成は解析や型制約を伴わないためバグの温床になる. このためSQLテンプレートをコードベースとして扱うときに中長期にテンプレートやマクロが負債になりやすい

提案コンセプト: 有効なSQLの有効なリライトのみによるSQL生成

SQL上安全な書き換えを提供し、それによるSQL生成アプローチをとることで
テンプレートエンジンの問題点を解決する

• クエリの実装はあくまでSQLなので、SQLに対するエコシステムの恩恵が受けられる
• 書き換えを前提とするため、すべての定義はひとつのSQLで表現する必要がる
• この操作はすべてBigQuery上で操作可能であるため、外部依存を減らせる

安全な書き換え例1: Final 節の置き換え

with A as (
  select ix, ix * ix as ix2  from unnest(generated_array(1, 100, 1)) as ix
)
, B as (
 select avg(ix2) from A
)
, C as (
 select count(1) from A
)

select * from A
-- 以下のfinal節に置き換えて利用したい
-- select * from B
-- select * from C

安全な書き換え2: テーブル参照子の置き換え

同一スキーマであれば、そのテーブルの参照子は書き換え可能である

with 
super_large_A as (
  select ix, ix * ix as ix2  from unnest(generated_array(1, 100, 1)) as ix
)
, A as (
  -- デバッグ利用のみで利用
  select ix, ix * ix as ix2  from unnest(generated_array(1, 100000000, 1)) as ix
)
, __constrains_A_table as (
  -- Aとsuper_large_Aは同一スキーマである必要がある.
  select * from A
  union all
  select * from super_large_A
)
, B as (
 select avg(ix2) from A
 -- 本番時は以下に書きかえて集計したい
 -- select avg(ix2) from super_large_A
)
, C as (
 select count(1) from A
 -- 本番時は以下に書きかえて集計したい
 -- select avg(ix2) from A
)

select * from C

実行例

一般公開データセット bqmake.bqtest にデモを用意した。

以下の例では、SQLの安全な書き換えを行う bqmake.bqtest.zbqt_gensql__remake_view UDFを用意し
bqmake.bqtest.demo_sample_view に対し書き換えを実施する。
対象ViewはBigQueryによって有効なSQLであることが担保されるため、安全な書き換え操作により
有効なSQLが生成できる

-- bqmake.bqtest.demo_sample_view
with datasource as (
  select * from `bigquery-public-data.google_trends.top_terms`
)
, datasource_sampled as (
  select * from `bigquery-public-data.google_trends.top_terms`
  TABLESAMPLE SYSTEM (5 percent)
)
, __test_count as (
  select count(1) from datasource
)

select * from datasource

execute immediate `bqtest.zbqt_gensql__remake_view`(
   'demo_sample_view', '__test_count', [('datasource', 'datasource_sampled')]
);

これは次のSQLが実行される

-- bqmake.bqtest.demo_sample_view
with datasource as (
  select * from `bigquery-public-data.google_trends.top_terms`
)
, datasource_sampled as (
  select * from `bigquery-public-data.google_trends.top_terms`
  TABLESAMPLE SYSTEM (5 percent)
)
, __test_count as (
  select count(1) from datasource_sampled 
)

select * from __test_count

bqmake.bqtest.zbqt_gensql__remake_view は データセットプライベートなUDFである。
UDFであることでプロシージャなどと違いUDFで完結するため、セキュリティ安全性が保ちやすい.
他データセットのviewを利用するためには UDFを適切に複製する必要がある。
この機能はは bqmake.bqtest.init__bqtest_dataset により提供される。

どのような場合に役立つか？

SQLに対するテストコード/データの作成

SQL単体では最終出力のみでしか検証できないが、この書き換え操作が実現できると
複数のCTEが存在する場合において、例えば、ユニークが保たれているかなど
途中の任意のCTEに対しデータ検証を行うSQLを書くことができる。

識別子の書き換えにより、ローカル開発時にはモック用データを利用といった操作もできる。

同スキーマテーブルに対する操作の一元化

データアプリケーション開発などで発生するのが、スキーマが同一でデータ生成方法が異なるテーブルを生成するケースである。
この時に、ユニークネスやデータの新鮮さといった共通のデータバリデーションを行う。
この操作をViewとして定義し、上記の書き換えによるSQLを行うことで共通化されたバリデーションが行える。