株式会社カケハシの技術ブログ

全381件

2023/04/26

Redash(Presto)とDarabricksのDMLの構文違いについてまとめてみた

こんにちは、カケハシのデータ基盤チームで開発ディレクターをしている松田です。最近、歳のせいか疲れが溜まりやすくなっており、毎週サウナに通っています。カケハシでは今までRedashを利用して全社にデータ提供をしていましたが、去年の7月からDatabricksを利用することになりました。そのため、今までRedashで使っていたクエリやダッシュボードをDatabricksへ移行する必要がありました。その時に、Redash（Presto）とDatabricksのデータベース操作言語（DML）違いで少しハマり、みなさんにも同じ轍を踏んで欲しくないため、本記事では変更点や対応方法をまとめたいと思います。是非、チートシート的な使い方をしてくれると泣いて喜びます！ Redash時代の構成とPrestoについて Databricks導入以前のアーキテクチャイメージは以下になっていました。データ利活用観点だと、RedashからAthenaでS3を参照して分析したり、そのクエリ結果から CTAS で中間データをを作成していました。Athenaでは、DMLとしてPrestoというオープンソースを利用しているため、基本的にはPrestoのドキュメントを参照しています。 Presto は、大量なデータに対してインタラクティブな分析クエリを実行するための高性能分散型SQLクエリエンジンです。 Facebookが自社の大規模なデータセットに対して、インタラクティブに結果を返すことを目的として2012年に開発され、2013年にオープンソースとなったものです。主に「一秒〜数分程度で終わる集計」や「コンパクトな処理を素早く実行したい場合」などに利用されるアーキテクチャとなります。 Databricks時代の構成とDatabricks SQLについて現在のDatabricks導入後のアーキテクチャイメージは以下になります。 Databricksは、Redashとほぼ同じ様な見た目や使い勝手になっており、SQLクエリ、ビジュアライズ、ダッシュボードなどが利用できます。 Databricks SQLでDelta Lakeを参照してデータ分析したり、中間データを作成したりしています。 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" まとめ RedashからDatabricksへ移行した際にDMLでハマったポイントとその解決方法をご紹介しました。まだ、文法の違いで詰まることはあるとは思いますが定期的に情報をアップデートしていき、社内や社外の人に知見を共有できたらとと考えています。もし少しでも興味を持った方がいらっしゃいましたら、データエンジニアとしてカケハシで一緒に働きませんか？　一緒に有数のデータカンパニーを目指せていければと思い、仲間を募集しております。

株式会社カケハシ の技術ブログ

コンテンツ

株式会社カケハシの技術ブログ