Missing Metabase features

[slvrtrn]

During the last year of releases, we were mainly focused on addressing the breaking changes affecting the driver and fixing bugs.

However, we were not as focused on implementing the newly introduced Metabase driver features until now. I'd like to address this, starting from #179, which is included in the latest 1.2.0 release that is intended to be used with Metabase 0.47.x.

If you have a particular feature that you are missing in the driver, please don't hesitate to react and leave a comment here, so this issue can be used as a source for ideas on what should be implemented next.

EDIT: to clarify, by the driver features I mean this:

(def driver-features
  "Set of all features a driver can support."
  #{
    ;; Does this database support foreign key relationships?
    :foreign-keys

    ;; Does this database support nested fields for any and every field except primary key (e.g. Mongo)?
    :nested-fields

    ;; Does this database support nested fields but only for certain field types (e.g. Postgres and JSON / JSONB columns)?
    :nested-field-columns

    ;; Does this driver support setting a timezone for the query?
    :set-timezone

    ;; Does the driver support *basic* aggregations like `:count` and `:sum`? (Currently, everything besides standard
    ;; deviation is considered \"basic\"; only GA doesn't support this).
    ;;
    ;; DEFAULTS TO TRUE.
    :basic-aggregations

    ;; Does this driver support standard deviation and variance aggregations? Note that if variance is not supported
    ;; directly, you can calculate it manually by taking the square of the standard deviation. See the MongoDB driver
    ;; for example.
    :standard-deviation-aggregations

    ;; Does this driver support expressions (e.g. adding the values of 2 columns together)?
    :expressions

    ;; Does this driver support parameter substitution in native queries, where parameter expressions are replaced
    ;; with a single value? e.g.
    ;;
    ;;    SELECT * FROM table WHERE field = {{param}}
    ;;    ->
    ;;    SELECT * FROM table WHERE field = 1
    :native-parameters

    ;; Does the driver support using expressions inside aggregations? e.g. something like \"sum(x) + count(y)\" or
    ;; \"avg(x + y)\"
    :expression-aggregations

    ;; Does the driver support using a query as the `:source-query` of another MBQL query? Examples are CTEs or
    ;; subselects in SQL queries.
    :nested-queries

    ;; Does the driver support persisting models
    :persist-models
    ;; Is persisting enabled?
    :persist-models-enabled

    ;; Does the driver support binning as specified by the `binning-strategy` clause?
    :binning

    ;; Does this driver not let you specify whether or not our string search filter clauses (`:contains`,
    ;; `:starts-with`, and `:ends-with`, collectively the equivalent of SQL `LIKE`) are case-senstive or not? This
    ;; informs whether we should present you with the 'Case Sensitive' checkbox in the UI. At the time of this writing
    ;; SQLite, SQLServer, and MySQL do not support this -- `LIKE` clauses are always case-insensitive.
    ;;
    ;; DEFAULTS TO TRUE.
    :case-sensitivity-string-filter-options

    :left-join
    :right-join
    :inner-join
    :full-join

    :regex

    ;; Does the driver support advanced math expressions such as log, power, ...
    :advanced-math-expressions

    ;; Does the driver support percentile calculations (including median)
    :percentile-aggregations

    ;; Does the driver support date extraction functions? (i.e get year component of a datetime column)
    ;; DEFAULTS TO TRUE
    :temporal-extract

    ;; Does the driver support doing math with datetime? (i.e Adding 1 year to a datetime column)
    ;; DEFAULTS TO TRUE
    :date-arithmetics

    ;; Does the driver support the :now function
    :now

    ;; Does the driver support converting timezone?
    ;; DEFAULTS TO FALSE
    :convert-timezone

    ;; Does the driver support :datetime-diff functions
    :datetime-diff

    ;; Does the driver support experimental "writeback" actions like "delete this row" or "insert a new row" from 44+?
    :actions

    ;; Does the driver support uploading files
    :uploads

    ;; Does the driver support schemas (aka namespaces) for tables
    ;; DEFAULTS TO TRUE
    :schemas

    ;; Does the driver support custom writeback actions. Drivers that support this must
    ;; implement [[execute-write-query!]]
    :actions/custom

    ;; Does changing the JVM timezone allow producing correct results? (See #27876 for details.)
    :test/jvm-timezone-setting

    ;; Does the driver support connection impersonation (i.e. overriding the role used for individual queries)?
    :connection-impersonation})

or missing multimethod implementations.

[brendavarguez]

Hello @slvrtrn. Something I recently discovered is that for date columns of specific type Nullable(Date) I can easily perform aggregations (count, sum, avg, ...) by date truncated by day, week, month, etc. However, for date columns of specific type Nullable(DateTime('America/Mexico_City')) I'm not having the same options. When building queries from the UI, I can only make aggregations by date truncated by day (seems like it's the default).

My company is migrating from Postgres --> ClickHouse and having the timezone + making aggregations it's important to us since many reports are build using Metabase.

Not sure it it's related to #182

[slvrtrn]

@brendavarguez, this should be fixed as of 1.2.0 with Metabase 0.47. It was related to #178.

[brendavarguez]

@slvrtrn latest seems to be 0.46.6.4

[slvrtrn]

@brendavarguez, yes, 1.2.0 is for the upcoming 0.47, which is almost there.

You could try to use 0.47-RC2 until the stable release is out.

[alekseyp]

We've tried to switch from PostgreSQL to ClickHouse and found couple issues so far:

• nested-field-columns: we use a lot of jsonb which was converted to string in ClickHouse, but so far we haven't figured out how can we use our json data the same way it's working between Metabase and pgsql
• convert-timezone: this one is still WIP, but as far as I know we store everything in UTC and some reports are in diff timezone, those work with pgsql, but I see reports that we have hard time make them work same way with ClickHouse.