Metadata providers demo

demo.clj

(ns demo
  (:require
   [metabase.driver :as driver]
   [metabase.driver.sql.query-processor :as sql.qp]
   [metabase.lib.core :as lib]
   [metabase.lib.metadata :as lib.metadata]
   [metabase.lib.metadata.jvm :as lib.metadata.jvm]
   [metabase.lib.test-metadata :as meta]
   [metabase.lib.test-util :as lib.tu]
   [metabase.query-processor.compile :as qp.compile]
   [metabase.query-processor.preprocess :as qp.preprocess]
   [metabase.query-processor.store :as qp.store]
   [metabase.test :as mt]
   [metabase.util.honey-sql-2 :as h2x]
   [toucan2.core :as t2]))

;;; Background: old QP store, MLv2 metadata provider, QP x MetadataProviders

;;; Demo the static (test) metadata provider. Completely decoupled from the app DB, neat!

(defn query []
  (lib/query meta/metadata-provider (meta/table-metadata :venues)))

(defn compiled []
  (qp.compile/compile (query)))

(defn print-query [query]
  (->> query
       :query
       (driver/prettify-native-form :h2)
       println))

(defn print-compiled []
  (print-query (compiled)))

(defn how-many-db-calls-did-this-make? []
  (t2/with-call-count [call-count]
    (compiled)
    (call-count)))

;;; Something more interesting.: let's use try changing out some properties of a Field that affects query compilation.

;;; A query with temporal bucketing. What does the query look like after preprocessing?
(defn query-2-preprocessed []
  (-> (lib/query meta/metadata-provider (meta/table-metadata :checkins))
      (lib/breakout (-> (meta/field-metadata :checkins :date)
                        (lib/with-temporal-bucket :month)))
      qp.preprocess/preprocess))

;;; This is actually how BigQuery works -- different functions for different types
(defmethod sql.qp/date [:h2 :month]
  [_driver _unit honeysql-expr]
  (let [f (condp = (h2x/database-type honeysql-expr)
            "date" :date_trunc
            "time" :time_trunc
            :timestamp_trunc)]
    [f (h2x/literal :month) honeysql-expr]))

(defn query-2 []
  (-> (lib/query meta/metadata-provider (meta/table-metadata :checkins))
      (lib/breakout (-> (meta/field-metadata :checkins :date)
                        (lib/with-temporal-bucket :month)))
      qp.compile/compile
      print-query))

;;; ok, let's try changing checkins.date from a DATE to a TIMESTAMP... does it compile differently?
(defn query-3 []
  (let [metadata-provider (lib.tu/merged-mock-metadata-provider
                           meta/metadata-provider
                           {:fields [{:id             (meta/id :checkins :date)
                                      :base-type      :type/DateTimeWithLocalTZ
                                      :effective-type :type/DateTimeWithLocalTZ
                                      :database-type  "timestamp"}]})]
    (-> (lib/query metadata-provider (lib.metadata/table metadata-provider (meta/id :checkins)))
        (lib/breakout (-> (lib.metadata/field metadata-provider (meta/id :checkins :date))
                          (lib/with-temporal-bucket :month)))
        qp.compile/compile
        print-query)))

;;; You can even fake columns!
(defn query-4 []
  (let [metadata-provider (lib.tu/merged-mock-metadata-provider
                           meta/metadata-provider
                           {:fields [{:id             100
                                      :table-id       (meta/id :checkins)
                                      :name           "my_amazing_column"
                                      :base-type      :type/DateTimeWithLocalTZ
                                      :effective-type :type/DateTimeWithLocalTZ
                                      :database-type  "timestamp"}]})]
    (-> (lib/query metadata-provider (lib.metadata/table metadata-provider (meta/id :checkins)))
        (lib/breakout (-> (lib.metadata/field metadata-provider 100)
                          (lib/with-temporal-bucket :month)))
        qp.compile/compile
        print-query)))

;;; remamp metadata provider, application database metadata provider

;;; without remapping
(defn query-5 []
  (let [metadata-provider (lib.metadata.jvm/application-database-metadata-provider (mt/id))
        venues            (lib.metadata/table metadata-provider (mt/id :venues))
        category-id       (lib.metadata/field metadata-provider (mt/id :venues :category_id))
        query             (-> (lib/query metadata-provider venues)
                              (lib/aggregate (lib/count))
                              (lib/breakout category-id))]
    (print-query (qp.compile/compile query))))

;;; with remapping
(defn query-6 []
  (let [metadata-provider (-> meta/metadata-provider
                              (lib.tu/remap-metadata-provider (meta/id :venues :category-id)
                                                              (meta/id :categories :name)))
        venues            (lib.metadata/table metadata-provider (meta/id :venues))
        category-id       (lib.metadata/field metadata-provider (meta/id :venues :category-id))
        query             (-> (lib/query metadata-provider venues)
                              (lib/aggregate (lib/count))
                              (lib/breakout category-id))]
    (print-query (qp.compile/compile query))))

;;; using the QP store binding stuff -- for use with legacy queries (where metadata provider is not attached, or if it's
;;; not passed in as an arg)
(defn query-7 []
  (qp.store/with-metadata-provider (-> (lib.metadata.jvm/application-database-metadata-provider (mt/id))
                                       (lib.tu/remap-metadata-provider (mt/id :venues :category_id)
                                                                       (mt/id :categories :name)))
    (-> (mt/mbql-query venues
          {:aggregate [[:count]]
           :breakout [$category_id]})
        qp.compile/compile
        print-query)))