BigQueryを参照するAPIを速くしたい話

はじめに

タイトルの通り、BigQueryを参照するAPIを速くするために試したことを紹介します。

以下の ruby スクリプトでクエリの結果を取得するまでの時間を測定します。

query.sql 内のクエリを書き換えて、実行時間を調べます。

main.rb

require 'google/cloud/bigquery'
require 'benchmark'

def query(client, sql, params: nil)
  client.query(sql,
    dataset: 'dataset',
    cache: false,
    params: params
  )
end

# クエリ読み込み
file = File.open("./query.sql")
sql = file.read
file.close

# BigQueryのクライアント作成
client = Google::Cloud::Bigquery.new(
  project_id: 'your-project-id',
  credentials: './credentials.json'
)

# 試行回数
trial_count = 10

times = (1..trial_count).map do |id|

  time = Benchmark.realtime do
    query(client, sql)
  end

  puts "trial #{ id }: #{ time.round(3) } sec"
  time
end

puts "average: #{(times.inject(:+) / trial_count).round(3)} sec"

BigQueryとのやりとりの回数を最小限にする

とても簡単な以下のクエリを実行してみます。

query.sql

SELECT 1;

$ ruby main.rb    
trial 1: 1.891 sec
trial 2: 1.643 sec
trial 3: 1.509 sec
trial 4: 1.608 sec
trial 5: 1.687 sec
trial 6: 1.798 sec
trial 7: 2.045 sec
trial 8: 1.505 sec
trial 9: 1.771 sec
trial 10: 1.746 sec
average: 1.72 sec

簡単なクエリでも1.5〜2秒ほどかかってしまうので、apiのレスポンスタイム短縮のためにはBigqueryとの往復回数を小さくする必要がありそうです。

動的SQLをやめる

さきほどのクエリを動的SQLに変えて実行してみます。

query.sql

EXECUTE IMMEDIATE '''
  SELECT 1
'''
;

$ ruby main.rb
trial 1: 2.444 sec
trial 2: 2.021 sec
trial 3: 2.284 sec
trial 4: 2.253 sec
trial 5: 2.148 sec
trial 6: 2.106 sec
trial 7: 2.121 sec
trial 8: 2.023 sec
trial 9: 2.055 sec
trial 10: 1.843 sec
average: 2.13 sec

静的SQLのときよりも0.4秒ほど時間がかかってしまうので、なるべく静的SQLでクエリを実行したいです。以下の対応ができそうです。

• 参照するテーブルの接尾辞が動的に変わる場合は、ワイルドカードテーブルと_TABLE_SUFFIXを用いることで静的SQLにすることができます。ただしワイルドカードテーブルには制限事項があるので、この制限があっても問題ないことを先に確認したほうがいいかもしれません。

• SQLの中で文字列操作をしてクエリを動的に作るのではなく、rubyで静的SQLのクエリを作ってクエリを実行することを検討する。例えば、取得するカラムを動的に変更できるクエリは、以下のように書き換えられる。

  before.sql

  EXECUTE IMMEDIATE '''
    SELECT
  ''' || @column || '''
    FROM
      table_name
  '''
  

  before.rb

  # クエリ読み込み
  file = File.open("./before.sql")
  sql = file.read
  file.close
  
  query(client, sql, params: {
    column: 'column_name'
  })
  

  after.sql

  SELECT
    %<column>s
  FROM
    table_name
  ;
  

  after.rb

  # クエリ読み込み
  file = File.open("./after.sql")
  base_sql = file.read
  file.close
  
  sql = format(base_sql, column: 'column_name')
  query(client, sql)
  

無闇に変数を定義しない

変数を定義すると、0.5秒ほど遅くなります。

query.sql

DECLARE x INT64 DEFAULT 1;

SELECT x;

$ ruby main.rb
trial 1: 2.346 sec
trial 2: 2.073 sec
trial 3: 2.047 sec
trial 4: 2.264 sec
trial 5: 2.072 sec
trial 6: 2.509 sec
trial 7: 2.271 sec
trial 8: 2.148 sec
trial 9: 2.518 sec
trial 10: 2.089 sec
average: 2.234 sec

ただ、定義すればするほど遅くなるというわけではなく、複数のステートメントを実行すること自体が遅くさせるみたいです。動的SQLの件と合わせて、スクリプトが遅い......？

query.sql

DECLARE x1 INT64 DEFAULT 1;
DECLARE x2 INT64 DEFAULT 1;
DECLARE x3 INT64 DEFAULT x1 + x2;
DECLARE x4 INT64 DEFAULT x2 + x3;
DECLARE x5 INT64 DEFAULT x3 + x4;
DECLARE x6 INT64 DEFAULT x4 + x5;
DECLARE x7 INT64 DEFAULT x5 + x6;
DECLARE x8 INT64 DEFAULT x6 + x7;
DECLARE x9 INT64 DEFAULT x7 + x8;

SELECT x9;

$ ruby main.rb
trial 1: 2.693 sec
trial 2: 2.266 sec
trial 3: 2.318 sec
trial 4: 2.029 sec
trial 5: 2.311 sec
trial 6: 2.045 sec
trial 7: 2.459 sec
trial 8: 2.354 sec
trial 9: 2.196 sec
trial 10: 2.021 sec
average: 2.269 sec

• rubyで計算できる値はrubyで計算してパラメータで渡す
• bigqueryの関数を使って計算したい場合は動的SQLと同じように文字列を埋め込む
• bigqueryのデータを使って計算したい場合は仕方ない？（1文で書けないか検討する余地はある）

WITH句は実体化されず、参照するたびに実行される

公式の説明にもあるように、WITH句のクエリは参照するたびに実行されます。なので、レコード全件とその件数を取得する次のクエリは結構時間がかかります。

query.sql

WITH t_table AS (
  SELECT
    *
  FROM
    bigquery-public-data.samples.gsod
)
SELECT
  t1.*
  ,t2.total_count
FROM
  t_table AS t1
  RIGHT OUTER JOIN (
    SELECT
      COUNT(*) AS total_count
    FROM
      t_table
  ) AS t2 ON true
;

$ ruby main.rb
trial 1: 212.409 sec
trial 2: 216.808 sec
trial 3: 213.515 sec
trial 4: 194.175 sec
trial 5: 207.469 sec
trial 6: 195.35 sec
trial 7: 214.256 sec
trial 8: 196.199 sec
trial 9: 190.32 sec
trial 10: 211.981 sec
average: 205.248 sec

かわりにウィンドウ関数で件数を取得することで、gsodテーブルへの参照を1回にすることができます。（テーブルが空の時の挙動が変わることに注意）

query.sql

SELECT
  *
  ,COUNT(*) OVER() AS total_count
FROM
  bigquery-public-data.samples.gsod
;

$ ruby main.rb
trial 1: 61.471 sec
trial 2: 65.139 sec
trial 3: 53.197 sec
trial 4: 57.477 sec
trial 5: 62.907 sec
trial 6: 50.188 sec
trial 7: 55.619 sec
trial 8: 56.373 sec
trial 9: 55.552 sec
trial 10: 56.606 sec
average: 57.453 sec

おわりに

動的SQLと変数の定義をやめるだけで少し速くなるのは意外でした。クエリの中身の改善では限界があるときには試してみるとよさそうです。