こんにちは、カケハシのデータ基盤チームで開発ディレクターをしている松田です。最近、歳のせいか疲れが溜まりやすくなっており、毎週サウナに通っています。

カケハシでは今までRedashを利用して全社にデータ提供をしていましたが、去年の7月からDatabricksを利用することになりました。そのため、今までRedashで使っていたクエリやダッシュボードをDatabricksへ移行する必要がありました。

その時に、Redash（Presto）とDatabricksのデータベース操作言語（DML）違いで少しハマり、みなさんにも同じ轍を踏んで欲しくないため、本記事では変更点や対応方法をまとめたいと思います。是非、チートシート的な使い方をしてくれると泣いて喜びます！

Redash時代の構成とPrestoについて

Databricks導入以前のアーキテクチャイメージは以下になっていました。データ利活用観点だと、RedashからAthenaでS3を参照して分析したり、そのクエリ結果からCTASで中間データをを作成していました。Athenaでは、DMLとしてPrestoというオープンソースを利用しているため、基本的にはPrestoのドキュメントを参照しています。

Redashのアーキテクチャ

Prestoは、大量なデータに対してインタラクティブな分析クエリを実行するための高性能分散型SQLクエリエンジンです。 Facebookが自社の大規模なデータセットに対して、インタラクティブに結果を返すことを目的として2012年に開発され、2013年にオープンソースとなったものです。主に「一秒〜数分程度で終わる集計」や「コンパクトな処理を素早く実行したい場合」などに利用されるアーキテクチャとなります。

Databricks時代の構成とDatabricks SQLについて

現在のDatabricks導入後のアーキテクチャイメージは以下になります。 Databricksは、Redashとほぼ同じ様な見た目や使い勝手になっており、SQLクエリ、ビジュアライズ、ダッシュボードなどが利用できます。 Databricks SQLでDelta Lakeを参照してデータ分析したり、中間データを作成したりしています。

Redashのアーキテクチャ

Databricks SQLは、Databricks Lakehouse Platformに組み込まれたエンタープライズデータウェアハウスとしてなります。 Databricks SQLのコア機能は、SQLウェアハウスと呼ばれる最適化されたコンピューティングです。

Databricksは、Hadoopのオルタナティブとなるプロダクトを開発するためにカリフォルニア大学バークレイ校の研究室仲間で2013年に立ち上がりました。そのHadoopのオルタナティブとなるプロダクトとして開発されたのがSparkで、そのSparkを使ったクラウドサービスが「Databricks Cloud」となっています。そのため、Databricksのドキュメントでは多くの場合はSpark SQLを使用してSQLクエリと機能を説明しています。そして、Spark SQLはDatabricks SQLを使用して作成されたクエリをサポートします。

つまり、Redash側はPresto、Databricks側はDatabricks SQL(≒ Spark SQL)で、Spark SQL自体はHiveQLと似ておりHiveの関数と互換性があります。それらの構文の違いでハマったポイントを説明します。

DMLの違いによりハマったところ

基礎関数(配列、結合、変換)

ARRAYの記述が異なる

配列を作りたい時に使用する。 Databricks SQLでは()を使い、Prestoでは[]を使う。

-- Databricks(Databricks SQL)
SELECT ARRAY("a", "b"); -- 結果: ["a", "b"]

-- Redash(Presto)
SELECT ARRAY["a", "b"]; -- 結果: ["a", "b"]

LATERAL VIEW explode / CROSS JOIN UNNEST

Array型のカラムに保存されている値を行へ展開するする時に使います。例では、患者さん1レコードに対して複数の疾患をArray型で持っている場合、疾患毎にレコードが展開されます。

-- Databricks(Databricks SQL)
SELECT
    patient.name,
    disease
FROM patients
LATERAL VIEW explode(diseases) AS disease;

-- Redash(Presto)
SELECT
    patient.name,
    disease
FROM patients
CROSS JOIN UNNEST(diseases) AS t(disease);

バッククォート / ダブルクォート

AS句で日本語を利用する時は、Databricks SQLではバッククォートで囲み、Prestoではダブルクォートで囲む必要があります。

-- Databricks(Databricks SQL)
SELECT '日本語' AS `日本語`;

-- Redash(Presto)
SELECT '日本語' AS "日本語";

Hash関数の型が異なる

SHA256でHash化する時に使います。 Prestoの方はSHA256関数の引数と返り値がbinaryのため、型変換の関数が必要になる。

-- Databricks(Databricks SQL)
SELECT SHA2('patient_id', 256)

-- Redash(Presto)
SELECT TO_HEX(SHA256(TO_UTF8('patient_id')))

varcharをcastする時のLength Parameterの有無

型変換のcast関数を使う時にDatabricks SQLはLength Parameterを指定する必要がある。

-- Databricks(Databricks SQL)
cast(created_at as varchar(10))

-- Redash(Presto)
cast(created_at as varchar)

try_cast / try

無効なCASTが発生した際に、クエリを正常終了してNULLを返して欲しい時に使用する。 Databricks SQLでは型に厳しくないため利用の機会が少ないが、Prestoでは利用するシーンはよくあります。 Prestoのtryでは、CAST以外にもゼロ除算や数値範囲外も検知できる。

-- Databricks(Databricks SQL)
SELECT try_cast('10' AS INT); -- 結果: 10
SELECT try_cast('a' AS INT); -- 結果: NULL

-- Redash(Presto)
SELECT try('10' AS INT); -- 結果: 10
SELECT try('a' AS INT); -- 結果: NULL

日付関数

datediff / date_diff

2つの日付の期間を計算したい時に使います。 Prestoでは引数にday以外にもmonthなどを選択できますが、Databricks SQLでは選択できないため日数の差分しか取得できません。また、引数の順番が異なるためご注意ください。

-- Databricks(Databricks SQL)
SELECT datediff('2023-02-09', '2023-02-07') --結果: 2

-- Redash(Presto)
SELECT date_diff('day', CAST('2023-02-07' AS DATE), CAST('2023-02-09' AS DATE)) --結果: 2

TIMESTAMPDIFF / TIMESTAMP_DIFF

2つのタイムスタンプの期間を計算したい時に使います。引数の順番が異なるためご注意ください。

-- Databricks(Databricks SQL)
TIMESTAMPDIFF(MINUTE, c.time_created, d.time_created) --結果: 0,

-- Redash(Presto)
TIMESTAMP_DIFF(d.time_created, c.time_created, MINUTE) -- 結果: 0,

date_addの引数指定が異なる

日付の足し引き計算をしたい時に使います。 Prestoでは引数にday以外にもmonthなどを選択できますが、Databricks SQLでは選択できません。そのため、月の足し引き計算はadd_monthsで代替します。

-- Databricks(Databricks SQL)
date_add('2023-02-09', -1) -- 結果: '2023-02-08'

-- Redash(Presto)
date_add('day', -1, CAST('2023-02-09' AS DATE)) -- 結果: '2023-02-08'

DATE_FORMATの時刻形式が異なる

タイムスタンプ型の日時を日付文字列へ変換する時に使います。時刻形式のフォーマットに差異があります。

-- Databricks(Databricks SQL)
DATE_FORMAT('2023-02-09 11:22:33', 'yyyy-MM-dd') -- 結果: '2023-02-09'

-- Redash(Presto)
DATE_FORMAT('2023-02-09 11:22:33', '%Y-%m-%d') -- 結果: '2023-02-09'

to_date / from_iso8601_date

文字列から日付へ変換する時に使います。

-- Databricks(Databricks SQL)
to_date('2023-02-09') -- 結果: '2023-02-09'

-- Redash(Presto)
from_iso8601_date('2023-02-09') -- 結果: '2023-02-09'

date_trunc / TIMESTANP_TRUC

タイムスタンプを指定した日付や時刻のところで切り捨てる時に使います。引数の順番が異なるためご注意ください。

-- Databricks(Databricks SQL)
date_trunc('day', '2023-02-09 11:22:33') -- 結果: '2023-02-09'

-- Redash(Presto)
TIMESTAMP_TRUNC('2023-02-09 11:22:33', DAY) -- 結果: '2023-02-09'

集計関数

percentile / percentile_cont

INT型のグループ内で中央値、第一四分位数などを取得する時に使用する。例では、0.5に設定することで中央値を取得している。

-- Databricks(Databricks SQL)
SELECT percentile(col, 0.5) FROM VALUES (0), (10) AS tab(col); -- 結果: 5

-- Redash(Presto)
SELECT percentile_cont(col, 0.5) FROM (VALUES(0),(10)) t(col); -- 結果:5

row_numberでorder byを指定する必要がある

行に番号をふる時に使用する。 Databricks SQLではORDER BYを指定して上げる必要がある。

-- Databricks(Databricks SQL)
ROW_NUMBER() over(PARTITION BY count ORDER BY count),

-- Redash(Presto)
ROW_NUMBER() over(PARTITION BY count),

max_by, min_byの複数返却指定ができないため、指定した要素の値を取得する際にはarray_aggとfilterを利用する必要がある

group byを使用した時に、第2引数で指定したカラムの最大・最小の第2引数を取得したい時に使用する。 Prestoでは第3引数に返却個数を指定して配列で取得できるが、Databricks SQLでは値しか取得できない。そのため、Databricks SQLを利用する場合は、group byで1番目の値ではなく、2番目の値を取得するには少し書き換えが必要になります。

-- group by でまとめられる対象のレコード
-- date, name, rank
-- "2023-01-01","A",1
-- "2023-01-01","B",2

-- Databricks(Databricks SQL)
SELECT min_by(name, rank) as first_rank_name -- 結果: "A"
SELECT array_agg(name) filter(where rank = 2)[0] as second_rank_name -- 結果: "B"

-- Redash(Presto)
SELECT min_by(name, rank) as first_rank_name -- 結果: "A"
SELECT element_at(min_by(name, rank, 2), 2) as second_rank_name -- 結果: "B"