Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Cannot visualize chart when using metabase variables #4

Open
ZZZeno opened this issue Jul 20, 2023 · 3 comments
Open

Cannot visualize chart when using metabase variables #4

ZZZeno opened this issue Jul 20, 2023 · 3 comments

Comments

@ZZZeno
Copy link

ZZZeno commented Jul 20, 2023

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
Copy link
Collaborator

hantmac commented Jul 21, 2023

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.

@hantmac hantmac added the wontfix This will not be worked on label Oct 14, 2023
@smartguo
Copy link

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

@hantmac
Copy link
Collaborator

hantmac commented Jun 2, 2024

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.

@hantmac hantmac removed the wontfix This will not be worked on label Jun 2, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@hantmac @ZZZeno @smartguo