Hi all,
I am trying to implement a new Metabase driver for Apache Phoenix, so far I was able to run simple queries and get the result.
Now I am stuck with implementing a query with date ranges. The problem is, Phoenix doesn’t support CAST(? as DATE), where as we need to convert to TO_DATE(?, ‘yyyy-MM-dd’),
This is the actual result in the console error log
{:query
“SELECT count(*) AS “count” FROM “SAMPLES”.“WEB_STAT” WHERE (CAST(“SAMPLES”.“WEB_STAT”.“DATE” AS date) >= CAST(? AS date) AND CAST(“SAMPLES”.“WEB_STAT”.“DATE” AS date) < CAST(? AS date))”,
:params (#inst “2019-03-15T07:14:00.280000000-00:00” #inst “2019-04-13T07:14:00.281000000-00:00”)}}
This is the expected query
{:query
“SELECT count(*) AS “count” FROM “SAMPLES”.“WEB_STAT” WHERE (CAST(“SAMPLES”.“WEB_STAT”.“DATE” AS date) >= TO_DATE(?, ‘yyyy-MM-dd’) AND CAST(“SAMPLES”.“WEB_STAT”.“DATE” AS date) < TO_DATE(?, ‘yyyy-MM-dd’))”,
:params (#inst “2019-03-15T07:14:00.280000000-00:00” #inst “2019-04-13T07:14:00.281000000-00:00”)}}
(As you can see in below code snippet, Phoenix doesn’t support BETWEEN clause, so I have to combine with two and conditions)
I also tried to apply the date conversion using functions, but Metabase is changing the condition to (to_timestamp(to_char(“SAMPLES”.“WEB_STAT”.“DATE”, ‘yyyy-MM-dd HH:mm:ss.SSS zzz’), ‘yyyy-MM-dd HH:mm:ss.SSS zzz’) >= to_timestamp(to_char(?, ‘yyyy-MM-dd HH:mm:ss.SSS zzz’), ‘yyyy-MM-dd HH:mm:ss.SSS zzz’) which i cant understand.
Any help appreciated, hope I can open a pull request after fixing major issues
Here is the driver code
(ns metabase.driver.phoenix
(:require [clojure.java.jdbc :as jdbc]
[clojure.string :as str]
[honeysql.core :as hsql]
[metabase.driver :as driver]
[metabase.driver.sql-jdbc
[common :as sql-jdbc.common]
[connection :as sql-jdbc.conn]
[execute :as sql-jdbc.execute]
[sync :as sql-jdbc.sync]]
[metabase.driver.sql.query-processor :as sql.qp]
[metabase.driver.sql.util.unprepare :as unprepare]
[metabase.util
[date :as du]
[honeysql-extensions :as hx]
[i18n :refer [trs]]])
(:import java.sql.Time
java.util.Date))
(driver/register! :phoenix, :parent :sql-jdbc)
(defmethod sql-jdbc.sync/database-type->base-type :phoenix [_ database-type]
({:BOOLEAN :type/Boolean
:INTEGER :type/Integer
:BIGINT :type/BigInteger
:VARBINARY :type/*
:BINARY :type/*
:CHAR :type/Text
:VARCHAR :type/Text
:DECIMAL :type/Decimal
:DOUBLE :type/Double
:FLOAT :type/Float
:DATE :type/Date
:TIMESTAMP :type/DateTime
:ARRAY :type/Array} database-type))
(defmethod sql-jdbc.conn/connection-details->spec :phoenix [_ {:keys [host port db jdbc-flags]
:or {host "localhost", port 8765, db "", jdbc-flags ""}
:as details}]
(-> (merge {:classname "org.apache.phoenix.queryserver.client.Driver"
:subprotocol "phoenix:thin"
:subname (str "url=http://" host ":" port ";serialization=PROTOBUF" jdbc-flags)}
(dissoc details :host :port :db))
(sql-jdbc.common/handle-additional-options details)))
(defmethod driver/supports? [:phoenix :set-timezone] [_ _] true)
(defmethod driver/supports? [:phoenix :basic-aggregations] [_ _] true)
(defmethod driver/supports? [:phoenix :standard-deviation-aggregations] [_ _] true)
(defmethod driver/supports? [:phoenix :expressions] [_ _] true)
(defmethod driver/supports? [:phoenix :native-parameters] [_ _] true)
(defmethod driver/supports? [:phoenix :expression-aggregations] [_ _] true)
(defmethod driver/supports? [:phoenix :binning] [_ _] true)
(defmethod sql.qp/unix-timestamp->timestamp [:phoenix :seconds] [_ _ expr]
(hx/cast :timestamp expr))
(defmethod sql.qp/date [:phoenix :day] [_ _ expr] (hx/->date expr))
;TODO: fix all commented functions below
;(defmethod sql.qp/date [:phoenix :minute] [_ _ expr] (hsql/call :trunc expr :minute))
;(defmethod sql.qp/date [:phoenix :minute-of-hour] [_ _ expr] (hsql/call :minute expr))
;(defmethod sql.qp/date [:phoenix :hour] [_ _ expr] (hsql/call :trunc expr :hour))
;(defmethod sql.qp/date [:phoenix :hour-of-day] [_ _ expr] (hsql/call :hour expr))
;(defmethod sql.qp/date [:phoenix :day-of-week] [_ _ expr] (hsql/call :dayofweek expr))
;(defmethod sql.qp/date [:phoenix :day-of-month] [_ _ expr] (hsql/call :dayofmonth expr))
;(defmethod sql.qp/date [:phoenix :day-of-year] [_ _ expr] (hsql/call :dayofyear expr))
;(defmethod sql.qp/date [:phoenix :week] [_ _ expr] (hsql/call :trunc expr :week)) ; Y = week year; w = week in year
;(defmethod sql.qp/date [:phoenix :week-of-year] [_ _ expr] (hsql/call :week expr))
;(defmethod sql.qp/date [:phoenix :month] [_ _ expr] (hsql/call :trunc expr :month))
;(defmethod sql.qp/date [:phoenix :month-of-year] [_ _ expr] (hsql/call :month expr))
;(defmethod sql.qp/date [:phoenix :quarter-of-year] [_ _ expr] (hx/quarter expr))
;(defmethod sql.qp/date [:phoenix :year] [_ _ expr] (hsql/call :year expr))
(defmethod unprepare/unprepare-value [:phoenix Date] [_ value]
(hsql/call :to_timestamp
(hx/literal (du/date->iso-8601 value))
(hx/literal "yyyy-MM-dd\\\\'T\\\\'HH:mm:ss.SSS\\\\'Z\\\\'")))
(prefer-method unprepare/unprepare-value [:sql Time] [:phoenix Date])
(defmethod unprepare/unprepare-value [:phoenix String] [_ value]
(str \' (str/replace value "'" "\\\\'") \'))
(defmethod sql.qp/->honeysql [:phoenix :between] [driver [_ field min-val max-val]]
[:and [:>= (sql.qp/->honeysql driver field) (sql.qp/->honeysql driver min-val)] [:< (sql.qp/->honeysql driver field) (sql.qp/->honeysql driver max-val)]])
;TODO: Phoenix set time zone is done using connection url param, this is a dummy call to satisfy metabase driver interface
(defmethod sql-jdbc.execute/set-timezone-sql :phoenix [_] "UPSERT INTO SAMPLES.TIMEZONE_CONFIG VALUES('dummy')")
thanks,
-Jestan Nirojan