search

databendcloud / metabase-databend-driver

Databend database driver for the Metabase business intelligence front-end
Apache License 2.0
4 stars 2 forks source link

Cannot visualize chart when using metabase variables #4

Open ZZZeno opened 1 year ago

ZZZeno commented 1 year ago

Got an error when using metabase visualization + variables.

SQL is

select created_at,
    platform,
    sum(unique_device_id_count) as dau
from daily_active_devices_temp
where platform is not null
[[and created_at > {{date}}]]
[[and platform = {{platform}} ]]
group by created_at, platform

Raw error log as below


2023-07-20 12:09:46   --> SQL:7:16
2023-07-20 12:09:46   |
2023-07-20 12:09:46 1 | -- Metabase:: userID: 1 queryType: native queryHash: 8552154856d6292fb35901ec67aabaab4e9205596612e5fb462f202474234804
2023-07-20 12:09:46 2 | select created_at,
2023-07-20 12:09:46   | ------ while parsing `SELECT ...`
2023-07-20 12:09:46 3 |     platform,
2023-07-20 12:09:46 4 |     sum(unique_device_id_count) as dau
2023-07-20 12:09:46 5 | from daily_active_devices_temp
2023-07-20 12:09:46 6 | where platform is not null
2023-07-20 12:09:46   |       -------- while parsing expression
2023-07-20 12:09:46 7 | and created_at > ?
2023-07-20 12:09:46   |                ^ expecting more subsequent tokens
2023-07-20 12:09:46 
2023-07-20 12:09:46 
2023-07-20 12:09:46 {:database_id 2,
2023-07-20 12:09:46  :started_at #t "2023-07-20T04:09:44.219160Z[GMT]",
2023-07-20 12:09:46  :via
2023-07-20 12:09:46  [{:status :failed,
2023-07-20 12:09:46    :class clojure.lang.ExceptionInfo,
2023-07-20 12:09:46    :error
2023-07-20 12:09:46    "Error executing query: Query failed (#): error: \n  --> SQL:7:16\n  |\n1 | -- Metabase:: userID: 1 queryType: native queryHash: 8552154856d6292fb35901ec67aabaab4e9205596612e5fb462f202474234804\n2 | select created_at,\n  | ------ while parsing `SELECT ...`\n3 |     platform,\n4 |     sum(unique_device_id_count) as dau\n5 | from daily_active_devices_temp\n6 | where platform is not null\n  |       -------- while parsing expression\n7 | and created_at > ?\n  |                ^ expecting more subsequent tokens\n\n",
2023-07-20 12:09:46    :stacktrace
2023-07-20 12:09:46    ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__71859.invoke(execute.clj:505)"
2023-07-20 12:09:46     "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:502)"
2023-07-20 12:09:46     "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
2023-07-20 12:09:46     "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:497)"
2023-07-20 12:09:46     "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
2023-07-20 12:09:46     "driver.sql_jdbc$fn__105189.invokeStatic(sql_jdbc.clj:63)"
2023-07-20 12:09:46     "driver.sql_jdbc$fn__105189.invoke(sql_jdbc.clj:61)"
2023-07-20 12:09:46     "query_processor.context$executef.invokeStatic(context.clj:60)"
2023-07-20 12:09:46     "query_processor.context$executef.invoke(context.clj:49)"
2023-07-20 12:09:46     "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
2023-07-20 12:09:46     "query_processor.context.default$default_runf.invoke(default.clj:66)"
2023-07-20 12:09:46     "query_processor.context$runf.invokeStatic(context.clj:46)"
2023-07-20 12:09:46     "query_processor.context$runf.invoke(context.clj:40)"
2023-07-20 12:09:46     "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
2023-07-20 12:09:46     "query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
2023-07-20 12:09:46     "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69243.invoke(cache.clj:224)"
2023-07-20 12:09:46     "query_processor.middleware.permissions$check_query_permissions$fn__64715.invoke(permissions.clj:126)"
2023-07-20 12:09:46     "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__68134.invoke(mbql_to_native.clj:24)"
2023-07-20 12:09:46     "query_processor$fn__70762$combined_post_process__70767$combined_post_process_STAR___70768.invoke(query_processor.clj:243)"
2023-07-20 12:09:46     "query_processor$fn__70762$combined_pre_process__70763$combined_pre_process_STAR___70764.invoke(query_processor.clj:240)"
2023-07-20 12:09:46     "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69154$fn__69159.invoke(resolve_database_and_driver.clj:36)"
2023-07-20 12:09:46     "driver$do_with_driver.invokeStatic(driver.clj:90)"
2023-07-20 12:09:46     "driver$do_with_driver.invoke(driver.clj:86)"
2023-07-20 12:09:46     "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69154.invoke(resolve_database_and_driver.clj:35)"
2023-07-20 12:09:46     "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__65025.invoke(fetch_source_query.clj:316)"
2023-07-20 12:09:46     "query_processor.middleware.store$initialize_store$fn__65203$fn__65204.invoke(store.clj:12)"
2023-07-20 12:09:46     "query_processor.store$do_with_store.invokeStatic(store.clj:47)"
2023-07-20 12:09:46     "query_processor.store$do_with_store.invoke(store.clj:41)"
2023-07-20 12:09:46     "query_processor.middleware.store$initialize_store$fn__65203.invoke(store.clj:11)"
2023-07-20 12:09:46     "query_processor.middleware.normalize_query$normalize$fn__69443.invoke(normalize_query.clj:25)"
2023-07-20 12:09:46     "query_processor.middleware.constraints$add_default_userland_constraints$fn__66381.invoke(constraints.clj:54)"
2023-07-20 12:09:46     "query_processor.middleware.process_userland_query$process_userland_query$fn__69379.invoke(process_userland_query.clj:151)"
2023-07-20 12:09:46     "query_processor.middleware.catch_exceptions$catch_exceptions$fn__69756.invoke(catch_exceptions.clj:171)"
2023-07-20 12:09:46     "query_processor.reducible$async_qp$qp_STAR___59524$thunk__59526.invoke(reducible.clj:103)"
2023-07-20 12:09:46     "query_processor.reducible$async_qp$qp_STAR___59524.invoke(reducible.clj:109)"
2023-07-20 12:09:46     "query_processor.reducible$async_qp$qp_STAR___59524.invoke(reducible.clj:94)"
2023-07-20 12:09:46     "query_processor.reducible$sync_qp$qp_STAR___59536.doInvoke(reducible.clj:129)"
2023-07-20 12:09:46     "query_processor$process_userland_query.invokeStatic(query_processor.clj:366)"
2023-07-20 12:09:46     "query_processor$process_userland_query.doInvoke(query_processor.clj:362)"
2023-07-20 12:09:46     "query_processor$fn__70811$process_query_and_save_execution_BANG___70820$fn__70823.invoke(query_processor.clj:377)"
2023-07-20 12:09:46     "query_processor$fn__70811$process_query_and_save_execution_BANG___70820.invoke(query_processor.clj:370)"
2023-07-20 12:09:46     "query_processor$fn__70856$process_query_and_save_with_max_results_constraints_BANG___70865$fn__70868.invoke(query_processor.clj:389)"
2023-07-20 12:09:46     "query_processor$fn__70856$process_query_and_save_with_max_results_constraints_BANG___70865.invoke(query_processor.clj:382)"
2023-07-20 12:09:46     "api.dataset$run_query_async$fn__86655.invoke(dataset.clj:73)"
2023-07-20 12:09:46     "query_processor.streaming$streaming_response_STAR_$fn__54387$fn__54388.invoke(streaming.clj:166)"
2023-07-20 12:09:46     "query_processor.streaming$streaming_response_STAR_$fn__54387.invoke(streaming.clj:165)"
2023-07-20 12:09:46     "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
2023-07-20 12:09:46     "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
2023-07-20 12:09:46     "async.streaming_response$do_f_async$task__36975.invoke(streaming_response.clj:88)"],
2023-07-20 12:09:46    :error_type :invalid-query,
2023-07-20 12:09:46    :ex-data
2023-07-20 12:09:46    {:driver :databend,
2023-07-20 12:09:46     :sql
2023-07-20 12:09:46     ["-- Metabase:: userID: 1 queryType: native queryHash: 8552154856d6292fb35901ec67aabaab4e9205596612e5fb462f202474234804"
2023-07-20 12:09:46      "select"
2023-07-20 12:09:46      "  created_at,"
2023-07-20 12:09:46      "  platform,"
2023-07-20 12:09:46      "  sum(unique_device_id_count) as dau"
2023-07-20 12:09:46      "from"
2023-07-20 12:09:46      "  daily_active_devices_temp"
2023-07-20 12:09:46      "where"
2023-07-20 12:09:46      "  platform is not null"
2023-07-20 12:09:46      "  and created_at > ?"
2023-07-20 12:09:46      "  and platform = ?"
2023-07-20 12:09:46      "group by"
2023-07-20 12:09:46      "  created_at,"
2023-07-20 12:09:46      "  platform"],
2023-07-20 12:09:46     :params [#t "2023-07-12" "iOS"],
2023-07-20 12:09:46     :type :invalid-query}}],
2023-07-20 12:09:46  :state "1005",
2023-07-20 12:09:46  :error_type :invalid-query,
2023-07-20 12:09:46  :json_query
2023-07-20 12:09:46  {:type "native",
2023-07-20 12:09:46   :native
2023-07-20 12:09:46   {:query
2023-07-20 12:09:46    "select created_at,\n    platform,\n    sum(unique_device_id_count) as dau\nfrom daily_active_devices_temp\nwhere platform is not null\n[[and created_at > {{date}}]]\n[[and platform = {{platform}} ]]\ngroup by created_at, platform\n\n",
2023-07-20 12:09:46    :template-tags
2023-07-20 12:09:46    {:date {:id "aa6a27a1-5bf8-29d7-f51b-49f282f94232", :name "date", :display-name "Date", :type "date"},
2023-07-20 12:09:46     :platform {:id "22e67e1f-ace8-d2d7-419c-3d8e5d0d99b0", :name "platform", :display-name "Platform", :type "text"}}},
2023-07-20 12:09:46   :database 2,
2023-07-20 12:09:46   :parameters
2023-07-20 12:09:46   [{:id "aa6a27a1-5bf8-29d7-f51b-49f282f94232",
2023-07-20 12:09:46     :type "date/single",
2023-07-20 12:09:46     :value "2023-07-12",
2023-07-20 12:09:46     :target ["variable" ["template-tag" "date"]]}
2023-07-20 12:09:46    {:id "22e67e1f-ace8-d2d7-419c-3d8e5d0d99b0",
2023-07-20 12:09:46     :type "category",
2023-07-20 12:09:46     :value "iOS",
2023-07-20 12:09:46     :target ["variable" ["template-tag" "platform"]]}],
2023-07-20 12:09:46   :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
2023-07-20 12:09:46  :status :failed,
2023-07-20 12:09:46  :class java.sql.SQLException,
2023-07-20 12:09:46  :stacktrace
2023-07-20 12:09:46  ["com.databend.jdbc.AbstractDatabendResultSet.resultsException(AbstractDatabendResultSet.java:159)"
2023-07-20 12:09:46   "com.databend.jdbc.DatabendStatement.internalExecute(DatabendStatement.java:215)"
2023-07-20 12:09:46   "com.databend.jdbc.DatabendStatement.execute(DatabendStatement.java:172)"
2023-07-20 12:09:46   "com.databend.jdbc.DatabendPreparedStatement.executeBatchByCopyInto(DatabendPreparedStatement.java:270)"
2023-07-20 12:09:46   "com.databend.jdbc.DatabendPreparedStatement.executeBatch(DatabendPreparedStatement.java:297)"
2023-07-20 12:09:46   "com.databend.jdbc.DatabendPreparedStatement.executeQuery(DatabendPreparedStatement.java:303)"
2023-07-20 12:09:46   "com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1471)"
2023-07-20 12:09:46   "--> driver.sql_jdbc.execute$fn__71782.invokeStatic(execute.clj:380)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$fn__71782.invoke(execute.clj:378)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:393)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:389)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$execute_reducible_query$fn__71859.invoke(execute.clj:503)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:502)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:497)"
2023-07-20 12:09:46   "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:489)"
2023-07-20 12:09:46   "driver.sql_jdbc$fn__105189.invokeStatic(sql_jdbc.clj:63)"
2023-07-20 12:09:46   "driver.sql_jdbc$fn__105189.invoke(sql_jdbc.clj:61)"
2023-07-20 12:09:46   "query_processor.context$executef.invokeStatic(context.clj:60)"
2023-07-20 12:09:46   "query_processor.context$executef.invoke(context.clj:49)"
2023-07-20 12:09:46   "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
2023-07-20 12:09:46   "query_processor.context.default$default_runf.invoke(default.clj:66)"
2023-07-20 12:09:46   "query_processor.context$runf.invokeStatic(context.clj:46)"
2023-07-20 12:09:46   "query_processor.context$runf.invoke(context.clj:40)"
2023-07-20 12:09:46   "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
2023-07-20 12:09:46   "query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
2023-07-20 12:09:46   "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69243.invoke(cache.clj:224)"
2023-07-20 12:09:46   "query_processor.middleware.permissions$check_query_permissions$fn__64715.invoke(permissions.clj:126)"
2023-07-20 12:09:46   "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__68134.invoke(mbql_to_native.clj:24)"
2023-07-20 12:09:46   "query_processor$fn__70762$combined_post_process__70767$combined_post_process_STAR___70768.invoke(query_processor.clj:243)"
2023-07-20 12:09:46   "query_processor$fn__70762$combined_pre_process__70763$combined_pre_process_STAR___70764.invoke(query_processor.clj:240)"
2023-07-20 12:09:46   "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69154$fn__69159.invoke(resolve_database_and_driver.clj:36)"
2023-07-20 12:09:46   "driver$do_with_driver.invokeStatic(driver.clj:90)"
2023-07-20 12:09:46   "driver$do_with_driver.invoke(driver.clj:86)"
2023-07-20 12:09:46   "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__69154.invoke(resolve_database_and_driver.clj:35)"
2023-07-20 12:09:46   "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__65025.invoke(fetch_source_query.clj:316)"
2023-07-20 12:09:46   "query_processor.middleware.store$initialize_store$fn__65203$fn__65204.invoke(store.clj:12)"
2023-07-20 12:09:46   "query_processor.store$do_with_store.invokeStatic(store.clj:47)"
2023-07-20 12:09:46   "query_processor.store$do_with_store.invoke(store.clj:41)"
2023-07-20 12:09:46   "query_processor.middleware.store$initialize_store$fn__65203.invoke(store.clj:11)"
2023-07-20 12:09:46   "query_processor.middleware.normalize_query$normalize$fn__69443.invoke(normalize_query.clj:25)"
2023-07-20 12:09:46   "query_processor.middleware.constraints$add_default_userland_constraints$fn__66381.invoke(constraints.clj:54)"
2023-07-20 12:09:46   "query_processor.middleware.process_userland_query$process_userland_query$fn__69379.invoke(process_userland_query.clj:151)"
2023-07-20 12:09:46   "query_processor.middleware.catch_exceptions$catch_exceptions$fn__69756.invoke(catch_exceptions.clj:171)"
2023-07-20 12:09:46   "query_processor.reducible$async_qp$qp_STAR___59524$thunk__59526.invoke(reducible.clj:103)"
2023-07-20 12:09:46   "query_processor.reducible$async_qp$qp_STAR___59524.invoke(reducible.clj:109)"
2023-07-20 12:09:46   "query_processor.reducible$async_qp$qp_STAR___59524.invoke(reducible.clj:94)"
2023-07-20 12:09:46   "query_processor.reducible$sync_qp$qp_STAR___59536.doInvoke(reducible.clj:129)"
2023-07-20 12:09:46   "query_processor$process_userland_query.invokeStatic(query_processor.clj:366)"
2023-07-20 12:09:46   "query_processor$process_userland_query.doInvoke(query_processor.clj:362)"
2023-07-20 12:09:46   "query_processor$fn__70811$process_query_and_save_execution_BANG___70820$fn__70823.invoke(query_processor.clj:377)"
2023-07-20 12:09:46   "query_processor$fn__70811$process_query_and_save_execution_BANG___70820.invoke(query_processor.clj:370)"
2023-07-20 12:09:46   "query_processor$fn__70856$process_query_and_save_with_max_results_constraints_BANG___70865$fn__70868.invoke(query_processor.clj:389)"
2023-07-20 12:09:46   "query_processor$fn__70856$process_query_and_save_with_max_results_constraints_BANG___70865.invoke(query_processor.clj:382)"
2023-07-20 12:09:46   "api.dataset$run_query_async$fn__86655.invoke(dataset.clj:73)"
2023-07-20 12:09:46   "query_processor.streaming$streaming_response_STAR_$fn__54387$fn__54388.invoke(streaming.clj:166)"
2023-07-20 12:09:46   "query_processor.streaming$streaming_response_STAR_$fn__54387.invoke(streaming.clj:165)"
2023-07-20 12:09:46   "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
2023-07-20 12:09:46   "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
2023-07-20 12:09:46   "async.streaming_response$do_f_async$task__36975.invoke(streaming_response.clj:88)"],
2023-07-20 12:09:46  :card_id nil,
2023-07-20 12:09:46  :context :ad-hoc,
2023-07-20 12:09:46  :error
2023-07-20 12:09:46  "Query failed (#): error: \n  --> SQL:7:16\n  |\n1 | -- Metabase:: userID: 1 queryType: native queryHash: 8552154856d6292fb35901ec67aabaab4e9205596612e5fb462f202474234804\n2 | select created_at,\n  | ------ while parsing `SELECT ...`\n3 |     platform,\n4 |     sum(unique_device_id_count) as dau\n5 | from daily_active_devices_temp\n6 | where platform is not null\n  |       -------- while parsing expression\n7 | and created_at > ?\n  |                ^ expecting more subsequent tokens\n\n",
2023-07-20 12:09:46  :row_count 0,
2023-07-20 12:09:46  :running_time 0,
2023-07-20 12:09:46  :data {:rows [], :cols []}}
2023-07-20 12:09:46 
2023-07-20 12:09:46 2023-07-20 04:09:46,078 DEBUG middleware.log :: POST /api/dataset 202 [ASYNC: completed] 1.9 s (4 DB calls) App DB connections: 0/7 Jetty threads: 3/50 (6 idle, 0 queued) (70 total active threads) Queries in flight: 0 (0 queued); databend DB 2 connections: 0/2 (0 threads blocked)```
hantmac commented 1 year ago

To enable filter queries and other interactive features, Metabase needs to have a dedicated driver implemented specifically for the target database. Some driver is not natively supported as a first-party driver in Metabase, meaning it doesn't come bundled with Metabase by default. So this driver do not support filter temporarily. Maybe wait this issue response, metabase would give some suggestions.

smartguo commented 2 months ago

I have same problems, so how to solve this issue? What if I report an issue to metabase?

hantmac commented 2 months ago

I have same problems, so how to solve this issue? What if I report an issue to metabase?

I will check this issue again, as soon as possible.