Apache Phoenix Driver and Date Parameter SQL

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 :slight_smile:

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

After spending lot of time, i assume that the Metabase query processor uses CAST extensively and nothing wrong with “defmethod sql.qp/date [:phoenix :day] [_ _ expr] (hx/->date expr))” and I should keep as it :slight_smile: ?

And the issue may be related to underlying jdbc driver (phoenix thin jdbc driver),

  1. Following query is from Metabase dash board to show records older than given date (THROWS ERROR)
SELECT "SAMPLES"."WEB_STAT"."ACTIVE_VISITOR" AS "ACTIVE_VISITOR", "SAMPLES"."WEB_STAT"."CORE" AS "CORE", "SAMPLES"."WEB_STAT"."DATE" AS "DATE", "SAMPLES"."WEB_STAT"."DB" AS "DB", "SAMPLES"."WEB_STAT"."DOMAIN" AS "DOMAIN", "SAMPLES"."WEB_STAT"."FEATURE" AS "FEATURE", "SAMPLES"."WEB_STAT"."HOST" AS "HOST" FROM "SAMPLES"."WEB_STAT" WHERE CAST("SAMPLES"."WEB_STAT"."DATE" AS date) < CAST(? AS date) LIMIT 2000
  1. Same query is from a standalone java client (THROWS ERROR)
         PreparedStatement statement = connection.prepareStatement("SELECT SAMPLES.WEB_STAT.ACTIVE_VISITOR AS ACTIVE_VISITOR, SAMPLES.WEB_STAT.CORE AS CORE, SAMPLES.WEB_STAT.DATE AS DATE, SAMPLES.WEB_STAT.DB AS DB, SAMPLES.WEB_STAT.DOMAIN AS DOMAIN, SAMPLES.WEB_STAT.FEATURE AS FEATURE, SAMPLES.WEB_STAT.HOST AS HOST FROM SAMPLES.WEB_STAT WHERE SAMPLES.WEB_STAT.DATE < CAST(? as date) LIMIT 2000");

        statement.setDate(1,new Date(System.currentTimeMillis()));
        ResultSet resultSet = statement.executeQuery();
  1. Same query altered to remove CAST for input parameter, from a standalone java client (SUCCESS)
        PreparedStatement statement = connection.prepareStatement("SELECT SAMPLES.WEB_STAT.ACTIVE_VISITOR AS ACTIVE_VISITOR, SAMPLES.WEB_STAT.CORE AS CORE, SAMPLES.WEB_STAT.DATE AS DATE, SAMPLES.WEB_STAT.DB AS DB, SAMPLES.WEB_STAT.DOMAIN AS DOMAIN, SAMPLES.WEB_STAT.FEATURE AS FEATURE, SAMPLES.WEB_STAT.HOST AS HOST FROM SAMPLES.WEB_STAT WHERE SAMPLES.WEB_STAT.DATE < ? LIMIT 2000");

        statement.setDate(1,new Date(System.currentTimeMillis()));
        ResultSet resultSet = statement.executeQuery();

After looking at the output of 2) 3), I think I need to find why it fails with explicit CAST

Exception in thread “main” org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: SELECT SAMPLES.WEB_STAT.ACTIVE_VISITOR AS ACTIVE_VISITOR, SAMPLES.WEB_STAT.CORE AS CORE, SAMPLES.WEB_STAT.DATE AS DATE, SAMPLES.WEB_STAT.DB AS DB, SAMPLES.WEB_STAT.DOMAIN AS DOMAIN, SAMPLES.WEB_STAT.FEATURE AS FEATURE, SAMPLES.WEB_STAT.HOST AS HOST FROM SAMPLES.WEB_STAT WHERE SAMPLES.WEB_STAT.DATE < CAST(? as date) LIMIT 2000
at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:54)
at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:332)
at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:168)
at PhoenixSqlClient.main(PhoenixSqlClient.java:15)
java.lang.NullPointerException
at org.apache.phoenix.jdbc.PhoenixParameterMetaData.isSigned(PhoenixParameterMetaData.java:138)
at org.apache.calcite.avatica.jdbc.JdbcMeta.parameters(JdbcMeta.java:276)
at org.apache.calcite.avatica.jdbc.JdbcMeta.signature(JdbcMeta.java:288)
at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:721)
at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:195)
at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1215)
at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1186)
at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)
at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)

Finally I was able get the driver working after spending couple of days on this :slight_smile: , every database has it’s own way of handling date/timestamp and sql functions, also use of casting string as date/timestamp and passing query parameter fields in other drivers don’t make them as a good example to writer a new driver.

Were you able to get the driver working? If so, I would like to use it.

Yes I was, and planning to submit a pull request and at the end i was caught up with an issue how apache phoenix sql date time functions work with a time zone.

Apache Phoenix has this HBase property to configure the timezone

  <property>
    <name>phoenix.query.dateFormatTimeZone</name>
    <value>GMT+5:30</value>
  </property>

It seems TRUNC function always uses UTC to truncate date/timestamp to ceiling hour/day/… values
But where as DAYOFMONTH,DAYOFWEEK,DAYOFYEAR … functions uses the given timezone

Finally i ended up writing a user defined function TRUNCATEDATE to support time zone.

Now I am wondering what to do next :slight_smile: If I submit the driver it will not work for others