logica で readable, testable な SQL の夢を見る

こんにちは。河内です。

Logica はデータ操作のための論理プログラミング言語で、Google のオープンソースプロダクトです。今年の4月に Google Open Source Blog でも紹介されています。

opensource.googleblog.com

Logica のターゲットユーザとして次の3つの人物像が挙げられています。

論理プログラミングをすでにやっていて、より計算力が欲しい人
SQLを使っていて、リーダビリティに満足していない人
論理プログラミングを学びたいと思っていて、ビッグデータにそれを適用したい人

この分類でいうと私は2番目の「SQLに満足していない人」です。長いこと付き合ってきていますが、CTEを多数含むある程度長いクエリになると、どうしても読み解くのに時間がかかるんですよね。 logica では readability が上がるということで期待が高まります！

TL;DR;

logica はシンプルな記法を持つ SQL トランスパイラとして使え、次のような特徴があります。

再利用性の高いサブクエリが書ける
サブクエリごとにゴールデンテストができる
生成されるSQLの制御は難しい

チュートリアル

Logica は Google Colab 上で動作するチュートリアルがついています。このチュートリアルが実質上リファレンスにもなるので、logica を書く際には何度も参照することになるでしょう。

colab.research.google.com

インストール

logica は python 製であり、 pip でインストールできます。

$ pip install logica

インストールすると logica コマンドが実行可能になります。主な使い方は次の通り。

logica <file> print <predicate> で SQL の出力
logica <file> run <predicate> で bq コマンドを介した BigQuery 上での SQL 実行

偶数を求める例

チュートリアルの先頭にもある偶数を求める例を実行してみましょう。

次の内容を even.l として保存します。

# 0~19 までの整数
# (本来自然数を定義したいが logica では無理なので範囲を区切る)
Z(x) :- x in Range(20);

# xが整数で x/2も整数なら、xは偶数
Even(x) :- Z(x), Z(x/2);

まず Z の SQL を生成してみましょう。

$ logica even.l print Z
SELECT
  x_1 AS col0
FROM
  UNNEST(GENERATE_ARRAY(0, 20 - 1)) as x_1;

生成できてそうです。次に Even のSQLが生成できるか試してみましょう。

$ logica even.l print Even
SELECT
  x_4 AS col0
FROM
  UNNEST(GENERATE_ARRAY(0, 20 - 1)) as x_4, UNNEST(GENERATE_ARRAY(0, 20 - 1)) as x_6
WHERE
  (x_6 = ((x_4) / (2)));

なんだかよくわかりませんがそれっぽいSQLが生成されました。

ここで注目したいのは、Even の SQL も生成できるし、Even の定義で利用している Z の SQL も生成できるということです。一連の定義を書いて、そのうちの任意の定義をSQL化することができます。

ではEven を BigQuery上で実行してみましょう。

$ logica even.l run Even
Waiting on bqjob_r1b7635488d5ba31e_00000179a13760d7_1 ... (0s) Current status: DONE
+------+
| col0 |
+------+
|    0 |
|    2 |
|    4 |
|    6 |
|    8 |
|   10 |
|   12 |
|   14 |
|   16 |
|   18 |
+------+

ちゃんと偶数を求めることができました。

Cohortの例

では次にもっと複雑な例を見ていきましょう。

Quick Guide: Calculate Cohort Retention Analysis with SQL で紹介されている Cohort を求めるクエリにチャレンジしたいと思います。

この例ではいくつかの CTE (Common Table Expression; WITH 句のこと) を使っています。そのうちの一つ cohort_items を logica に翻訳してみます。

元の SQL は次の通り。

with cohort_items as (
  select
    date_trunc('month', U.timestamp)::date as cohort_month,
    id as user_id
  from users U
  order by 1, 2
)

logica で書くとこんな感じになります。

Month() = SqlExpr("MONTH", {});

@OrderBy(CohortItems, "1", "2");
CohortItems(cohort_month:, user_id:) :-
  Users(user_id:, date:),
  cohort_month == DateTrunc(date, Month());

DateTrunc() の引数の MONTH は logica に組み込みの表現が無いので、SQL の任意の式を表現する SqlExpr() を使っています。

logica print で SQL を生成すると次のとおり。

SELECT
  DATE_TRUNC(Users.date, MONTH) AS cohort_month,
  Users.user_id AS user_id
FROM
  Users ORDER BY 1, 2;

ほぼ同じSQLが得られています。

同じ用にサブクエリ単位で翻訳していきました。元のクエリと最終的に翻訳し終わったものを比べてみましょう。

元のSQL: cohort.sql · GitHub
logica への翻訳: logica-exercise/cohort.l at cohort-blog · tkawachi/logica-exercise · GitHub

行数は、元のSQLがコメント行を除いて約40行、logica 版が約25行です。記法も私の目には logica のほうがシンプルに見えます。

サブクエリ単位のゴールデンテスト

ここからが面白いところです！

チュートリアルや README では触れられていないのですが、logica にはゴールデンテストをサポートする仕組みが用意されており、サブクエリ単位でテストを実施できます。

ゴールデンテストとは、プログラムの実行結果を保存しておき、再度実行したときに結果が変わっていないことを確認するテストです。

本家の test runner を真似して、私の場合は、こんな感じに test runner (run_test.py) を作りました。

#!/usr/bin/env python

import sys
from logica.common import logica_test

def run_test(name, src=None, golden=None, predicate=None,
            user_flags=None):
    """Run one test from this folder with TestManager."""
    src = src or (name + ".l")
    golden = golden or (name + ".txt")
    predicate = predicate or "Test"
    logica_test.TestManager.RunTest(
      name,
      src="test/" + src,
      golden="test/" + golden,
      predicate=predicate,
      user_flags=user_flags)


def run_all():
  """Running all tests."""
  run_test("cohort_items")
  run_test("user_activities")
  run_test("cohort_size")
  run_test("cohort")

if __name__ == '__main__':
  if 'golden_run' in sys.argv:
    logica_test.TestManager.SetGoldenRun(True)

  if 'announce_tests' in sys.argv:
    logica_test.TestManager.SetAnnounceTests(True)

  for a in sys.argv:
    if a.startswith('test_only='):
      logica_test.TestManager.SetRunOnlyTests(a.split('=')[1].split(','))

  logica_test.PrintHeader()
  run_all()

そしてテストクエリ用に test/cohort_items.l を作ります。

import cohort.CohortItems;

TestUsers(user_id: 1, date: Date(2000, 1, 1));
TestUsers(user_id: 2, date: Date(2000, 2, 1));

Test := CohortItems(Users: TestUsers);

ここで Test の定義は上述したCohortItems の定義から、Users を TestUsers に入れ替えたものになります。TestUsers はここで定義している通り、テスト用のサンプルデータです。

Test の SQL を生成してみると、サンプルデータを使ってクエリするようになっていることが見て取れます。

$ logica test/cohort_items.l print Test
WITH t_0_TestUsers AS (SELECT * FROM (

    SELECT
      1 AS user_id,
      DATE(2000, 1, 1) AS date
   UNION ALL

    SELECT
      2 AS user_id,
      DATE(2000, 2, 1) AS date

) AS UNUSED_TABLE_NAME  )
SELECT
  DATE_TRUNC(TestUsers.date, MONTH) AS cohort_month,
  TestUsers.user_id AS user_id
FROM
  t_0_TestUsers AS TestUsers ORDER BY 1, 2;

./run_test.py golden_run を実行すると test/cohort_items.txt にクエリ結果が保存されます。

$ cat test/cohort_items.txt
+--------------+---------+
| cohort_month | user_id |
+--------------+---------+
|   2000-01-01 |       1 |
|   2000-02-01 |       2 |
+--------------+---------+

test/cohort_items.l で指定したサンプルデータに対して正しい結果が得られているので、大丈夫そうです。

次回以降は ./run_test.py を実行することで、実行結果が test/cohort_items.txt と同じかをチェックできます（ゴールデンテスト）。サブクエリ単位のリグレッションテストとして活用できるので、リファクタリング時も安心できます。

テストの例はこちらを参照ください。