FLINTERS Engineer's Blog

FLINTERSのエンジニアが綴る技術ブログ

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);

まず ZSQL を生成してみましょう。

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

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

$ 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が生成されました。

ここで注目したいのは、EvenSQL も生成できるし、Even の定義で利用している ZSQL も生成できるということです。 一連の定義を書いて、そのうちの任意の定義を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がコメント行を除いて約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 の定義から、UsersTestUsers に入れ替えたものになります。TestUsers はここで定義している通り、テスト用のサンプルデータです。

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

$ 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 と同じかをチェックできます(ゴールデンテスト)。 サブクエリ単位のリグレッションテストとして活用できるので、リファクタリング時も安心できます。

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

感想など

Cohortの例で紹介したように、いくつかのSQLを手習いとして logica に書き換えてみました。 大半はスムーズに進みましたが、一部実行性能が悪いクエリが生成されてしまい制御が難しいと感じることがありました。 ただし、これは私が logica に慣れていないことによるものの可能性があります。

出力されるクエリを制御しづらいとなると、性能が要求されるクエリには向きません。 サブクエリ単位でテストができるのは利点だと感じますが、テストを書きたいほど長いクエリに関しては性能も求めたいことが多いと思うので、現時点ではメリットが活かしづらい感覚を持っています。

今回はとりあげませんでしたが、Google Colab 上でスムーズに扱うことができるため、BigQuery を Google Colab 上でアドホックに分析するといったユースケースが現時点ではぴったりハマりそうです。

質問などは GitHub の discussion で行われており、私もいくつか質問しましたが、Auhtor から素早い返信があり活発に活動している感触を受けました。 Logica 自体まだまだ若い言語でこれから発展すると思いますので、今後に期待したいと思います!