BigQueryで LIMIT を使いこなす

[takegue]

SQLにおける limit 節は、データの出力行数を制御するためのパラメータであり
カラムナ型データベースであるBigQueryにおいては 一見役割が薄く見える節でありますが

BigQueryのSQLエンジンにおいては、このLIMIT節について高度な最適化がされており
これらを使いこなすことでいくつかの恩恵を受けることができます。
ここではこれらのtipsについていくつかまとめていきます。

ORDER BY と LIMIT の組み合わせによる最適化

ORDER BY節はテーブルの行数が多い場合に特にコストが高くなる計算のひとつにあげられますが、LIMIT節と組み合わせることで恩恵を受けることができます。
LIMIT節があることでLIMIT操作を割り当てられたslotが十分な結果を保持した段階もしくは
計算処理打ち切りが条件が明確な場合において、前段のslot消費を止めることができるためです。

これは BigQueryのクエリ最適化のプラクティスにも挙げられている手法のひとつです

-- 最適化されたSQLの例: 次から引用
-- https://cloud.google.com/bigquery/docs/best-practices-performance-filter-order#use_limit_with_an_order_by_clause
SELECT  id,  reputation,  creation_date,  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rankFROM bigquery-public-data.stackoverflow.users  
ORDER BY user_rank ASCLIMIT 10;

実際のBigQueryのエンジンがどのようにslotを配置するかを視覚的に理解するには次の動画が参考になります

BigQuery 管理者リファレンス ガイド: クエリの最適化

LIMIT 0: 一見無用に見える便利なテクニック

LIMIT 0は行を出力しない構文で、一見意味のない構文に見えるかもしれませんが
LIMIT 0に施された最適化はリソース消費をゼロもしくは極限に抑えることができます
これを使うことで次のようなことができます

• 出力をともわないクエリのDRY RUN: ViewやTVFに破壊的変更がないか確認することができます
• 空テーブルの構築: SQLのスキーマを確認する際に便利です
• 複数のSQLのスキーマ整合性の確認

このようなSQLがあったとしましょう。

with complex_1 as (
  -- ...
)
, complex_2 as (
  -- ...
  -- from complext_1
)
, complex_3 as (
  -- ...
  -- from complex_2
)

select * from complex_3

スキーマの確認すらも容易ではないですが、次のようなSQLを書くことで
簡単にスキーマ出力を得ることができます！

create temp table `check_schema`
as
with complex_1 as (
  -- ...
)
, complex_2 as (
  -- ...
  -- from complext_1
)
, complex_3 as (
  -- ...
  -- from complex_2
)

select * from complex_3
limit 0

require_partition_filter オプションの検証をバイパスし、スキーマチェックする

require_partition_filter はパーティション構造をもつテーブルに対して
パーティションを指定しないクエリの記述を禁止するオプションである.

このテーブルに対して limit 0 を使うと パーティションに関するフィルタを指定しない状態においてもクエリのチェックが可能になる。
これによって巨大なパーティションテーブルとのスキーマの整合性チェックなどが容易にできる

次のクエリは実行可能な有効なクエリである.

create or replace temp table `takegue.sandbox.sample_partition_table`
partition by date_jst
options(
  require_partition_filter  = true
)
as select * from `takegue.sandbox.sample_partition_table` limit 0

;

with _source as (
  SELECT * FROM `takegue.sandbox.sample_partition_table`
)
, _query as (
  SELECT date(null) as date_jst
)

select * from _source
union all
select * from _query
limit 0

LIMIT 節の制限: 定数値の使用のみが許される

ここまで紹介してきたようにLIMIT節は有用な用途が存在する一方でいくつかの制限があります

• BigQuery Scriptの変数を使うことはできません

• クエリパラメータは使うことができます @limit_num

• array_agg などの 配列集計で動的な制限をかけたい場合には where節で条件指定をすることで、limitと同等の出力を得ることができます。

declare limit_num int64;
set limit_num = 3;

select 
  [
    select
      u
    from
	  unnest(generate_array(1, 10)) as u
	-- Error!: limit limit_num
	-- Passed: limit @limit_num
	limit 3
  ]

この制限は次のような書き換えを行うことで回避することができます。

declare limit_num int64;
set limit_num = 3;

select 
  [
    select
      u
    from
	  unnest(generate_array(1, 10)) as u 
	    with offset ix
	where
	   ix < 3
	-- Passed: ix < limit_num
	-- Passed: ix < @limit_num
  ]

まとめ

LIMIT 節にまつわるいくつかのtipsを紹介しました。
ここで紹介したtipsがみなさんの役に立てれば幸いです！