Redashのパラメーター機能を用いて、PrestoにSQLを実行する時のTips

株式会社アイスタイル アドベントカレンダー 1発目記事!

概要

弊社ではマイクロサービス化や国内・海外の会社のJOINもあり、SQL Server、MySQL、BigQuery、Hadoop、PostgreSQL、Elasticsearchなど、サービスごとに様々なDBが使用されています。
ですが、分析業務、データ抽出業務、分析基盤の運営をする身からすると大変ですよね...

そんなわけで弊社ではRedashのデータソースにPrestoを追加し、
異機種のDBをまたがるデータ分析・抽出業務に活用しています。
今回はRedashのパラメーター機能を使用して、PrestoへSQLを実行する際のちょっとしたTipsをいくつかご紹介します。

prestoなんぞや? redashなんぞや? という方は下記リンクから

• 『Prestoとは何か，Prestoで何ができるか』 - Treasure Data
• ダッシュボードツール「Re:dash」とは？インストール方法から使い方まで解説！【初級編】 | SELECK

RedashからDate型のパラメーターをPrestoに送る場合

SQL Serverなどであれば、RedashのGUIでパラメーターの型をDate型に設定し、
このようなSQLを実行すると、正常に結果を取得できます。

redash_parameter_to_sqlserver.sql

   USE hoge

SELECT
       *
  FROM 
       guns g
 WHERE 
       1=1
   AND g.created_at <= {{作成日が何日以下か入力}}

しかし、このようなSQLでPrestoに対し実行すると、以下のエラーが返ってきます。

error_redash_parameter_to_presto.sql

SELECT
       *
  FROM 
       catalog.weapon.guns g
 WHERE 
       1=1
   AND g.created_at <= {{作成日が何日以下か入力}}

error

com.facebook.presto.sql.analyzer.SemanticException: line 3:20: '<=' cannot be applied to timestamp,integer

GUIでパラメーターの型をDate型と設定したにも関わらず、integerとして受け取られています。
ちなみに、Presto上では以下のSQLが実行されています。

error_on_presto.sql

SELECT 
       *
  FROM 
       catalog.weapon.guns g
 WHERE 
       1=1
   AND g.created_at <= 2017-11-29

解決方法として、Date型にCASTしてあげましょう
ただinteger型から直接Date型にCASTできないので、
パラメーターをシングルクォートで囲み、StringにしてからCASTします。
下記のようなSQLになります。

redash_parameter_to_presto.sql

SELECT 
       *
  FROM 
       catalog.weapon.guns g
 WHERE 
       1=1
   AND g.created_at <= CAST('{{作成日が何日以下か入力}}' AS DATE)

これでRedashのパラメーターとDate型のカラムの比較が行われ、結果が返ってきます。

IN句の中にパラメーターを渡し、Prestoに送る場合

通常であれば下記のSQLのように、IN句の中身をTEXT型のパラメータに置き換えて、
実行するだけなのですが、これだと意図した結果が返ってきません。

html_escape.sql

SELECT 
       *
  FROM 
       catalog.weapon.guns g
 WHERE 
       1=1
   AND g.id IN ({{指定の番号をカンマ区切りで入力}})

HTMLエスケープを無効にする必要があり、波括弧2つ{{ }}ではなく、波括弧3つ{{{ }}}で囲む必要があります。
以下のようなSQLになります。

non_html_escape.sql

SELECT 
       *
  FROM 
       catalog.weapon.guns g
 WHERE 
       1=1
   AND g.id IN ({{{指定の番号をカンマ区切りで入力}}})

GithubのRedashの下記issueで紹介されています。
公式のHELPにも書いていないので、解決に結構苦労しました…。
Quotation Mark is Not Replaced Correctly When Inserted Using Query Parameters · Issue #1211 · getredash/redash · GitHub

まとめ

Redashは本当便利ですが、様々なデータソースを繋げれるPrestoと使うと、
Pythonデータソースをつかわなくても、SQLのみで異機種のDBの結合を行える ので、とても便利です。
ただ、少し一筋縄では行かないことがあるのでそんな時にこの記事を参考にしていただければと思います。

2日目はkubotakさんの「勉強会駆動開発のすゝめ」です！