唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryでアドホックなクエリを書くときに気をつけようかなと思うこと

最近Webのコンソールでクエリを書くときに気をつけようかなと思ったことをメモしておく

クエリにStandard SQLを使っているかLegacy SQLを使っているかを明記する

クエリの1行目に#standardSQLや#legacySQLなどと書くことでどちらの種類のSQLを使うか指定できる
これを指定しておかないとコピペして実行するときなどに、違う方で動いてエラーになったりするのでできるだけ書いておく

パラメータの定数はできるだけわかりやすくまとめておく

クエリ中に含まれる条件などの定数は後から変えたくなったりすることも多い。普通にクエリ中に書いているとどれを変えればよいかわからなくなる
また同じ定数が複数回クエリに登場するときにそれぞれをリテラルとして書いておくと一括して修正するのが大変だったり漏れが発生したりしてしまいます

以下の記事でも書いたがUDFの関数として定数を定義してしまうとわかりやすい

雑な例を書くと以下のようにパラメータの定数をUDFとして定義してコメントを書いておくとあとから見てもわかりやすいしどこを変えればよいかは一目瞭然だと思う

#standardSQL

#集計の開始日
CREATE TEMPORARY FUNCTION START_DATE() AS ('20170701');
#集計の終了日
CREATE TEMPORARY FUNCTION END_DATE() AS ('20170731');

SELECT
  *
FROM `foo.bar*`
WHERE _TABLE_SUFFIX BETWEEN START_DATE() AND END_DATE()

何をするクエリか最初にコメントを書いておく

1行目にStandard SQLかLegacy SQLを書いた次の行にクエリの説明を書いておくとよいと思います

説明を書いておくとクエリの履歴の画面を見たときにわかりやすくなります
f:id:sucrose:20170730205528p:plain
また検索でも探しやすくなります。たとえば日付を書いておけば日付で検索したりもできます
後から必要なクエリは名前をつけて保存しておけばよい話ですが、使いまわす予定のないクエリを後から使いたくなったりもするので検索しやすくなっていると便利です
f:id:sucrose:20170730205715p:plain

WITH句を使ってできるだけ共通部分をまとめておく

共通の処理がクエリ中の複数箇所にコピペされていると修正するときに大変になるのでできるだけWITH句を使ってまとめる

BigQueryのクエリで定数を定義して複数箇所で使いたい

BigQuery

BigQueryでクエリを書くときに、同じ日付や倍率、円周率などの定数をクエリの複数箇所で使いたい時があります
単純に複数箇所に書くと修正するときに大変なので、共通のものを複数箇所で使いまわす方法を調べてみました

UDF(ユーザー定義関数)による方法

定数を返す関数を定義して使う方法が一番簡単です

Standard SQLのUDFは以下のような形式で書けます
CREATE TEMPORARY FUNCTION 関数名(引数名 型, ...) AS (使いたいSQL);
これを使うと以下のように定数として使うことができて、修正したいときは関数の定義だけを変えればよくなります

#StandardSQL
CREATE TEMPORARY FUNCTION RATE1() AS (0.01);
CREATE TEMPORARY FUNCTION PI() AS (ACOS(-1));

SELECT
  i, i * RATE1(), PI()
FROM UNNEST(GENERATE_ARRAY(1, 100)) AS i

WITHで適当なテーブルを作ってCROSS JOINする方法

こっちの方法だとCROSS JOINをしないといけないので少しコードがわかりづらくなります

#StandardSQL
WITH const AS (
  SELECT
    0.01 AS RATE1
    , ACOS(-1) AS PI
  )

SELECT
  i, i * RATE1, PI
FROM const, UNNEST(GENERATE_ARRAY(1, 100)) AS i