search

AlexR2D2 / metabase_duckdb_driver

Metabase DuckDB Driver shipped as 3rd party plugin
Apache License 2.0
70 stars 21 forks source link

Issues with metabase variable #22

Closed sutao closed 7 months ago

sutao commented 7 months ago

Looks like when using variables in Metabase SQL statements, the driver returns an error:

Parser Error: syntax error at or near "{" LINE 3: WHERE jyr={{jyr}}; ^

My statement is:

SELECT * from read_parquet('/dwh/HourlyPowerUsageByOrg/**/*.parquet', hive_partitioning=true)
WHERE jyr={{jyr}};

In the dataset, jyr is a column (and hive partition) indicating the year. The following is the stack trace in metabase:

2023-11-29 13:38:58 2023-11-29 05:38:58,728 ERROR middleware.catch-exceptions :: Error processing query: Parser Error: syntax error at or near "{"
2023-11-29 13:38:58 LINE 3: WHERE jyr={{jyr}};
2023-11-29 13:38:58                    ^
2023-11-29 13:38:58 {:database_id 2,
2023-11-29 13:38:58  :started_at #t "2023-11-29T05:38:58.145442333Z[Etc/UTC]",
2023-11-29 13:38:58  :via
2023-11-29 13:38:58  [{:status :failed,
2023-11-29 13:38:58    :class java.sql.SQLException,
2023-11-29 13:38:58    :error
2023-11-29 13:38:58    "java.sql.SQLException: Parser Error: syntax error at or near \"{\"\nLINE 3: WHERE jyr={{jyr}};\n                   ^",
2023-11-29 13:38:58    :stacktrace
2023-11-29 13:38:58    ["org.duckdb.DuckDBPreparedStatement.prepare(DuckDBPreparedStatement.java:112)"
2023-11-29 13:38:58     "org.duckdb.DuckDBPreparedStatement.execute(DuckDBPreparedStatement.java:183)"
2023-11-29 13:38:58     "com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
2023-11-29 13:38:58     "--> driver.duckdb$fn__112151.invokeStatic(duckdb.clj:146)"
2023-11-29 13:38:58     "driver.duckdb$fn__112151.invoke(duckdb.clj:144)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:564)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:561)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query$fn__81031$fn__81032.invoke(execute.clj:679)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query$fn__81031.invoke(execute.clj:678)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80832$fn__80833.invoke(execute.clj:384)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80800$do_with_resolved_connection__80801.invoke(execute.clj:334)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80832.invokeStatic(execute.clj:378)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80832.invoke(execute.clj:376)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:672)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:669)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
2023-11-29 13:38:58     "driver.sql_jdbc$fn__115907.invokeStatic(sql_jdbc.clj:72)"
2023-11-29 13:38:58     "driver.sql_jdbc$fn__115907.invoke(sql_jdbc.clj:70)"
2023-11-29 13:38:58     "query_processor.context$executef.invokeStatic(context.clj:60)"
2023-11-29 13:38:58     "query_processor.context$executef.invoke(context.clj:49)"
2023-11-29 13:38:58     "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
2023-11-29 13:38:58     "query_processor.context.default$default_runf.invoke(default.clj:66)"
2023-11-29 13:38:58     "query_processor.context$runf.invokeStatic(context.clj:46)"
2023-11-29 13:38:58     "query_processor.context$runf.invoke(context.clj:40)"
2023-11-29 13:38:58     "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
2023-11-29 13:38:58     "query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
2023-11-29 13:38:58     "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___77064.invoke(cache.clj:224)"
2023-11-29 13:38:58     "query_processor.middleware.permissions$check_query_permissions$fn__72647.invoke(permissions.clj:126)"
2023-11-29 13:38:58     "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__76189.invoke(mbql_to_native.clj:24)"
2023-11-29 13:38:58     "query_processor$fn__78297$combined_post_process__78302$combined_post_process_STAR___78303.invoke(query_processor.clj:260)"
2023-11-29 13:38:58     "query_processor$fn__78297$combined_pre_process__78298$combined_pre_process_STAR___78299.invoke(query_processor.clj:257)"
2023-11-29 13:38:58     "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76975$fn__76980.invoke(resolve_database_and_driver.clj:36)"
2023-11-29 13:38:58     "driver$do_with_driver.invokeStatic(driver.clj:93)"
2023-11-29 13:38:58     "driver$do_with_driver.invoke(driver.clj:88)"
2023-11-29 13:38:58     "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76975.invoke(resolve_database_and_driver.clj:35)"
2023-11-29 13:38:58     "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__72957.invoke(fetch_source_query.clj:316)"
2023-11-29 13:38:58     "query_processor.middleware.store$initialize_store$fn__73138$fn__73139.invoke(store.clj:12)"
2023-11-29 13:38:58     "query_processor.store$do_with_store.invokeStatic(store.clj:56)"
2023-11-29 13:38:58     "query_processor.store$do_with_store.invoke(store.clj:50)"
2023-11-29 13:38:58     "query_processor.middleware.store$initialize_store$fn__73138.invoke(store.clj:11)"
2023-11-29 13:38:58     "query_processor.middleware.normalize_query$normalize$fn__77271.invoke(normalize_query.clj:36)"
2023-11-29 13:38:58     "query_processor.middleware.constraints$add_default_userland_constraints$fn__74665.invoke(constraints.clj:54)"
2023-11-29 13:38:58     "query_processor.middleware.process_userland_query$process_userland_query$fn__77200.invoke(process_userland_query.clj:151)"
2023-11-29 13:38:58     "query_processor.middleware.catch_exceptions$catch_exceptions$fn__77597.invoke(catch_exceptions.clj:171)"
2023-11-29 13:38:58     "query_processor.reducible$async_qp$qp_STAR___66814$thunk__66816.invoke(reducible.clj:103)"
2023-11-29 13:38:58     "query_processor.reducible$async_qp$qp_STAR___66814.invoke(reducible.clj:109)"
2023-11-29 13:38:58     "query_processor.reducible$async_qp$qp_STAR___66814.invoke(reducible.clj:94)"
2023-11-29 13:38:58     "query_processor.reducible$sync_qp$qp_STAR___66826.doInvoke(reducible.clj:129)"
2023-11-29 13:38:58     "query_processor$process_userland_query.invokeStatic(query_processor.clj:383)"
2023-11-29 13:38:58     "query_processor$process_userland_query.doInvoke(query_processor.clj:379)"
2023-11-29 13:38:58     "query_processor$fn__78346$process_query_and_save_execution_BANG___78355$fn__78358.invoke(query_processor.clj:394)"
2023-11-29 13:38:58     "query_processor$fn__78346$process_query_and_save_execution_BANG___78355.invoke(query_processor.clj:387)"
2023-11-29 13:38:58     "query_processor$fn__78391$process_query_and_save_with_max_results_constraints_BANG___78400$fn__78403.invoke(query_processor.clj:406)"
2023-11-29 13:38:58     "query_processor$fn__78391$process_query_and_save_with_max_results_constraints_BANG___78400.invoke(query_processor.clj:399)"
2023-11-29 13:38:58     "api.dataset$run_query_async$fn__100175.invoke(dataset.clj:74)"
2023-11-29 13:38:58     "query_processor.streaming$streaming_response_STAR_$fn__61404$fn__61405.invoke(streaming.clj:166)"
2023-11-29 13:38:58     "query_processor.streaming$streaming_response_STAR_$fn__61404.invoke(streaming.clj:165)"
2023-11-29 13:38:58     "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
2023-11-29 13:38:58     "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
2023-11-29 13:38:58     "async.streaming_response$do_f_async$task__39661.invoke(streaming_response.clj:88)"],
2023-11-29 13:38:58    :state nil}
2023-11-29 13:38:58   {:status :failed,
2023-11-29 13:38:58    :class clojure.lang.ExceptionInfo,
2023-11-29 13:38:58    :error
2023-11-29 13:38:58    "Error executing query: java.sql.SQLException: Parser Error: syntax error at or near \"{\"\nLINE 3: WHERE jyr={{jyr}};\n                   ^",
2023-11-29 13:38:58    :stacktrace
2023-11-29 13:38:58    ["--> driver.sql_jdbc.execute$execute_reducible_query$fn__81031$fn__81032.invoke(execute.clj:681)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query$fn__81031.invoke(execute.clj:678)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80832$fn__80833.invoke(execute.clj:384)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80800$do_with_resolved_connection__80801.invoke(execute.clj:334)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80832.invokeStatic(execute.clj:378)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$fn__80832.invoke(execute.clj:376)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:672)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:669)"
2023-11-29 13:38:58     "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
2023-11-29 13:38:58     "driver.sql_jdbc$fn__115907.invokeStatic(sql_jdbc.clj:72)"
2023-11-29 13:38:58     "driver.sql_jdbc$fn__115907.invoke(sql_jdbc.clj:70)"
2023-11-29 13:38:58     "query_processor.context$executef.invokeStatic(context.clj:60)"
2023-11-29 13:38:58     "query_processor.context$executef.invoke(context.clj:49)"
2023-11-29 13:38:58     "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
2023-11-29 13:38:58     "query_processor.context.default$default_runf.invoke(default.clj:66)"
2023-11-29 13:38:58     "query_processor.context$runf.invokeStatic(context.clj:46)"
2023-11-29 13:38:58     "query_processor.context$runf.invoke(context.clj:40)"
2023-11-29 13:38:58     "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
2023-11-29 13:38:58     "query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
2023-11-29 13:38:58     "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___77064.invoke(cache.clj:224)"
2023-11-29 13:38:58     "query_processor.middleware.permissions$check_query_permissions$fn__72647.invoke(permissions.clj:126)"
2023-11-29 13:38:58     "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__76189.invoke(mbql_to_native.clj:24)"
2023-11-29 13:38:58     "query_processor$fn__78297$combined_post_process__78302$combined_post_process_STAR___78303.invoke(query_processor.clj:260)"
2023-11-29 13:38:58     "query_processor$fn__78297$combined_pre_process__78298$combined_pre_process_STAR___78299.invoke(query_processor.clj:257)"
2023-11-29 13:38:58     "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76975$fn__76980.invoke(resolve_database_and_driver.clj:36)"
2023-11-29 13:38:58     "driver$do_with_driver.invokeStatic(driver.clj:93)"
2023-11-29 13:38:58     "driver$do_with_driver.invoke(driver.clj:88)"
2023-11-29 13:38:58     "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76975.invoke(resolve_database_and_driver.clj:35)"
2023-11-29 13:38:58     "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__72957.invoke(fetch_source_query.clj:316)"
2023-11-29 13:38:58     "query_processor.middleware.store$initialize_store$fn__73138$fn__73139.invoke(store.clj:12)"
2023-11-29 13:38:58     "query_processor.store$do_with_store.invokeStatic(store.clj:56)"
2023-11-29 13:38:58     "query_processor.store$do_with_store.invoke(store.clj:50)"
2023-11-29 13:38:58     "query_processor.middleware.store$initialize_store$fn__73138.invoke(store.clj:11)"
2023-11-29 13:38:58     "query_processor.middleware.normalize_query$normalize$fn__77271.invoke(normalize_query.clj:36)"
2023-11-29 13:38:58     "query_processor.middleware.constraints$add_default_userland_constraints$fn__74665.invoke(constraints.clj:54)"
2023-11-29 13:38:58     "query_processor.middleware.process_userland_query$process_userland_query$fn__77200.invoke(process_userland_query.clj:151)"
2023-11-29 13:38:58     "query_processor.middleware.catch_exceptions$catch_exceptions$fn__77597.invoke(catch_exceptions.clj:171)"
2023-11-29 13:38:58     "query_processor.reducible$async_qp$qp_STAR___66814$thunk__66816.invoke(reducible.clj:103)"
2023-11-29 13:38:58     "query_processor.reducible$async_qp$qp_STAR___66814.invoke(reducible.clj:109)"
2023-11-29 13:38:58     "query_processor.reducible$async_qp$qp_STAR___66814.invoke(reducible.clj:94)"
2023-11-29 13:38:58     "query_processor.reducible$sync_qp$qp_STAR___66826.doInvoke(reducible.clj:129)"
2023-11-29 13:38:58     "query_processor$process_userland_query.invokeStatic(query_processor.clj:383)"
2023-11-29 13:38:58     "query_processor$process_userland_query.doInvoke(query_processor.clj:379)"
2023-11-29 13:38:58     "query_processor$fn__78346$process_query_and_save_execution_BANG___78355$fn__78358.invoke(query_processor.clj:394)"
2023-11-29 13:38:58     "query_processor$fn__78346$process_query_and_save_execution_BANG___78355.invoke(query_processor.clj:387)"
2023-11-29 13:38:58     "query_processor$fn__78391$process_query_and_save_with_max_results_constraints_BANG___78400$fn__78403.invoke(query_processor.clj:406)"
2023-11-29 13:38:58     "query_processor$fn__78391$process_query_and_save_with_max_results_constraints_BANG___78400.invoke(query_processor.clj:399)"
2023-11-29 13:38:58     "api.dataset$run_query_async$fn__100175.invoke(dataset.clj:74)"
2023-11-29 13:38:58     "query_processor.streaming$streaming_response_STAR_$fn__61404$fn__61405.invoke(streaming.clj:166)"
2023-11-29 13:38:58     "query_processor.streaming$streaming_response_STAR_$fn__61404.invoke(streaming.clj:165)"
2023-11-29 13:38:58     "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
2023-11-29 13:38:58     "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
2023-11-29 13:38:58     "async.streaming_response$do_f_async$task__39661.invoke(streaming_response.clj:88)"],
2023-11-29 13:38:58    :error_type :invalid-query,
2023-11-29 13:38:58    :ex-data
2023-11-29 13:38:58    {:driver :duckdb,
2023-11-29 13:38:58     :sql
2023-11-29 13:38:58     ["-- Metabase:: userID: 1 queryType: native queryHash: 15b079b734a8269c4b9274b16a7b7e2e201fe0ae0f2542208f43f35e3961c803"
2023-11-29 13:38:58      "SELECT"
2023-11-29 13:38:58      "  *"
2023-11-29 13:38:58      "from"
2023-11-29 13:38:58      "  read_parquet("
2023-11-29 13:38:58      "    '/dwh/HourlyPowerUsageByOrg/**/*.parquet',"
2023-11-29 13:38:58      "    hive_partitioning = true"
2023-11-29 13:38:58      "  )"
2023-11-29 13:38:58      "WHERE"
2023-11-29 13:38:58      "  jyr = {{jyr}};"],
2023-11-29 13:38:58     :params nil,
2023-11-29 13:38:58     :type :invalid-query}}],
2023-11-29 13:38:58  :state nil,
2023-11-29 13:38:58  :error_type :invalid-query,
2023-11-29 13:38:58  :json_query
2023-11-29 13:38:58  {:database 2,
2023-11-29 13:38:58   :native
2023-11-29 13:38:58   {:template-tags
2023-11-29 13:38:58    {:jyr
2023-11-29 13:38:58     {:type "number", :name "jyr", :id "49a25182-c2e0-42d8-b4d0-aa1f9ff06dd9", :display-name "Jyr", :required true}},
2023-11-29 13:38:58    :query
2023-11-29 13:38:58    "SELECT * from read_parquet('/dwh/HourlyPowerUsageByOrg/**/*.parquet', hive_partitioning=true)\nWHERE jyr={{jyr}};"},
2023-11-29 13:38:58   :type "native",
2023-11-29 13:38:58   :parameters
2023-11-29 13:38:58   [{:id "49a25182-c2e0-42d8-b4d0-aa1f9ff06dd9",
2023-11-29 13:38:58     :type "number/=",
2023-11-29 13:38:58     :value ["2023"],
2023-11-29 13:38:58     :target ["variable" ["template-tag" "jyr"]]}],
2023-11-29 13:38:58   :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
2023-11-29 13:38:58  :status :failed,
2023-11-29 13:38:58  :class java.sql.SQLException,
2023-11-29 13:38:58  :stacktrace
2023-11-29 13:38:58  ["org.duckdb.DuckDBNative.duckdb_jdbc_prepare(Native Method)"
2023-11-29 13:38:58   "org.duckdb.DuckDBPreparedStatement.prepare(DuckDBPreparedStatement.java:106)"
2023-11-29 13:38:58   "org.duckdb.DuckDBPreparedStatement.execute(DuckDBPreparedStatement.java:183)"
2023-11-29 13:38:58   "com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)"
2023-11-29 13:38:58   "--> driver.duckdb$fn__112151.invokeStatic(duckdb.clj:146)"
2023-11-29 13:38:58   "driver.duckdb$fn__112151.invoke(duckdb.clj:144)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:564)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:561)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_reducible_query$fn__81031$fn__81032.invoke(execute.clj:679)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_reducible_query$fn__81031.invoke(execute.clj:678)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$fn__80832$fn__80833.invoke(execute.clj:384)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$fn__80800$do_with_resolved_connection__80801.invoke(execute.clj:334)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$fn__80832.invokeStatic(execute.clj:378)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$fn__80832.invoke(execute.clj:376)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:672)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:669)"
2023-11-29 13:38:58   "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:661)"
2023-11-29 13:38:58   "driver.sql_jdbc$fn__115907.invokeStatic(sql_jdbc.clj:72)"
2023-11-29 13:38:58   "driver.sql_jdbc$fn__115907.invoke(sql_jdbc.clj:70)"
2023-11-29 13:38:58   "query_processor.context$executef.invokeStatic(context.clj:60)"
2023-11-29 13:38:58   "query_processor.context$executef.invoke(context.clj:49)"
2023-11-29 13:38:58   "query_processor.context.default$default_runf.invokeStatic(default.clj:68)"
2023-11-29 13:38:58   "query_processor.context.default$default_runf.invoke(default.clj:66)"
2023-11-29 13:38:58   "query_processor.context$runf.invokeStatic(context.clj:46)"
2023-11-29 13:38:58   "query_processor.context$runf.invoke(context.clj:40)"
2023-11-29 13:38:58   "query_processor.reducible$identity_qp.invokeStatic(reducible.clj:15)"
2023-11-29 13:38:58   "query_processor.reducible$identity_qp.invoke(reducible.clj:12)"
2023-11-29 13:38:58   "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___77064.invoke(cache.clj:224)"
2023-11-29 13:38:58   "query_processor.middleware.permissions$check_query_permissions$fn__72647.invoke(permissions.clj:126)"
2023-11-29 13:38:58   "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__76189.invoke(mbql_to_native.clj:24)"
2023-11-29 13:38:58   "query_processor$fn__78297$combined_post_process__78302$combined_post_process_STAR___78303.invoke(query_processor.clj:260)"
2023-11-29 13:38:58   "query_processor$fn__78297$combined_pre_process__78298$combined_pre_process_STAR___78299.invoke(query_processor.clj:257)"
2023-11-29 13:38:58   "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76975$fn__76980.invoke(resolve_database_and_driver.clj:36)"
2023-11-29 13:38:58   "driver$do_with_driver.invokeStatic(driver.clj:93)"
2023-11-29 13:38:58   "driver$do_with_driver.invoke(driver.clj:88)"
2023-11-29 13:38:58   "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__76975.invoke(resolve_database_and_driver.clj:35)"
2023-11-29 13:38:58   "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__72957.invoke(fetch_source_query.clj:316)"
2023-11-29 13:38:58   "query_processor.middleware.store$initialize_store$fn__73138$fn__73139.invoke(store.clj:12)"
2023-11-29 13:38:58   "query_processor.store$do_with_store.invokeStatic(store.clj:56)"
2023-11-29 13:38:58   "query_processor.store$do_with_store.invoke(store.clj:50)"
2023-11-29 13:38:58   "query_processor.middleware.store$initialize_store$fn__73138.invoke(store.clj:11)"
2023-11-29 13:38:58   "query_processor.middleware.normalize_query$normalize$fn__77271.invoke(normalize_query.clj:36)"
2023-11-29 13:38:58   "query_processor.middleware.constraints$add_default_userland_constraints$fn__74665.invoke(constraints.clj:54)"
2023-11-29 13:38:58   "query_processor.middleware.process_userland_query$process_userland_query$fn__77200.invoke(process_userland_query.clj:151)"
2023-11-29 13:38:58   "query_processor.middleware.catch_exceptions$catch_exceptions$fn__77597.invoke(catch_exceptions.clj:171)"
2023-11-29 13:38:58   "query_processor.reducible$async_qp$qp_STAR___66814$thunk__66816.invoke(reducible.clj:103)"
2023-11-29 13:38:58   "query_processor.reducible$async_qp$qp_STAR___66814.invoke(reducible.clj:109)"
2023-11-29 13:38:58   "query_processor.reducible$async_qp$qp_STAR___66814.invoke(reducible.clj:94)"
2023-11-29 13:38:58   "query_processor.reducible$sync_qp$qp_STAR___66826.doInvoke(reducible.clj:129)"
2023-11-29 13:38:58   "query_processor$process_userland_query.invokeStatic(query_processor.clj:383)"
2023-11-29 13:38:58   "query_processor$process_userland_query.doInvoke(query_processor.clj:379)"
2023-11-29 13:38:58   "query_processor$fn__78346$process_query_and_save_execution_BANG___78355$fn__78358.invoke(query_processor.clj:394)"
2023-11-29 13:38:58   "query_processor$fn__78346$process_query_and_save_execution_BANG___78355.invoke(query_processor.clj:387)"
2023-11-29 13:38:58   "query_processor$fn__78391$process_query_and_save_with_max_results_constraints_BANG___78400$fn__78403.invoke(query_processor.clj:406)"
2023-11-29 13:38:58   "query_processor$fn__78391$process_query_and_save_with_max_results_constraints_BANG___78400.invoke(query_processor.clj:399)"
2023-11-29 13:38:58   "api.dataset$run_query_async$fn__100175.invoke(dataset.clj:74)"
2023-11-29 13:38:58   "query_processor.streaming$streaming_response_STAR_$fn__61404$fn__61405.invoke(streaming.clj:166)"
2023-11-29 13:38:58   "query_processor.streaming$streaming_response_STAR_$fn__61404.invoke(streaming.clj:165)"
2023-11-29 13:38:58   "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:69)"
2023-11-29 13:38:58   "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:67)"
2023-11-29 13:38:58   "async.streaming_response$do_f_async$task__39661.invoke(streaming_response.clj:88)"],
2023-11-29 13:38:58  :card_id nil,
2023-11-29 13:38:58  :context :ad-hoc,
2023-11-29 13:38:58  :error "Parser Error: syntax error at or near \"{\"\nLINE 3: WHERE jyr={{jyr}};\n                   ^",
2023-11-29 13:38:58  :row_count 0,
2023-11-29 13:38:58  :running_time 0,
2023-11-29 13:38:58  :data {:rows [], :cols []}}
AlexR2D2 commented 7 months ago

Hi, could you run the PRAGMA version; query in the Metabase query editor ?

I tried almost the same

Screenshot 2023-11-30 at 17 56 35
sutao commented 7 months ago

Thanks for the reply! I am running Metabase open source version v0.47.4 with DuckDB driver version 0.9.1. 1701351903120 1701351776444

Thanks!

sutao commented 7 months ago

Hey Alex,

I think I've located the problem. The error only occurs when I use a rglob pattern in the path. If I use a specific parquet file, the issue goes away. See attached screenshot.

Tao 1701352485207

AlexR2D2 commented 7 months ago

Hey ) but in my case it works...

Screenshot 2023-11-30 at 19 01 35

here is the db file structre

Screenshot 2023-11-30 at 19 03 45
AlexR2D2 commented 7 months ago

may be some special characters present in path

sutao commented 7 months ago

Hmmm, that's quite odd... I do not have any special characters in the path... though I do have hyphens in the UUIDs which are used as partition values... The entire stack is run in docker (with the data mapped to /dwh) 1701353364029

sutao commented 7 months ago

Also, if I run the query directly without any parameters like below, it works fine.

SELECT * from read_parquet('/dwh/HourlyPowerUsageByOrg/**/*.parquet', hive_partitioning=true)
WHERE jyr=2023;

Is there any oddity in the stack trace in the initial post? It appears the query is passed into the duckdb engine without filling in the variable?

AlexR2D2 commented 7 months ago

hmm... yes, indeed, looks like the query is passed into the duckdb engine without filling in the variable

here is the query i run directly in the native duckdb console app. The error is very similar to the error in the stack trace in the initial post

Screenshot 2023-11-30 at 19 19 39
AlexR2D2 commented 7 months ago

Here is the part of you stack trace

2023-11-29 13:38:58  {:database 2,
2023-11-29 13:38:58   :native
2023-11-29 13:38:58   {:template-tags
2023-11-29 13:38:58    {:jyr
2023-11-29 13:38:58     {:type "number", :name "jyr", :id "49a25182-c2e0-42d8-b4d0-aa1f9ff06dd9", :display-name "Jyr", :required true}},
2023-11-29 13:38:58    :query
2023-11-29 13:38:58    "SELECT * from read_parquet('/dwh/HourlyPowerUsageByOrg/**/*.parquet', hive_partitioning=true)\nWHERE jyr={{jyr}};"},
2023-11-29 13:38:58   :type "native",
2023-11-29 13:38:58   :parameters
2023-11-29 13:38:58   [{:id "49a25182-c2e0-42d8-b4d0-aa1f9ff06dd9",
2023-11-29 13:38:58     :type "number/=",
2023-11-29 13:38:58     :value ["2023"],
2023-11-29 13:38:58     :target ["variable" ["template-tag" "jyr"]]}],
2023-11-29 13:38:58   :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}},
2023-11-29 13:38:58  :status :failed,

looks like variable jyr have an internal id=49a25182-c2e0-42d8-b4d0-aa1f9ff06dd9 may be do you have the same UUID in the you data path?

i don't know how it is linked with each other but who knows :) also, it seems Metabase recognises the variable and value of variable, but in some reasons it passes the query as is into duckdb driver

sutao commented 7 months ago

I removed the org_id partitioning, and the directory structure is re-organized as follows: 1701355231548

However the error still persists. And It looks like Metabase does not even fill in the parameter jyr in Query Preview. You can see the first statement does not contain asterisks and the parameter was filled correctly (even though the parquet file does not actually exist). The second query does have asterisks and Metabase refused to fill in the parameter. 1701355172161

AlexR2D2 commented 7 months ago

can you check the error if you type exactly this path (query) select * from read_parquet('/my1/my2/**/*.parquet', hive_partitioning=true) where jyr={{jyr}};

sutao commented 7 months ago

The above query produced the same error. {{jyr}} is not filled. I think it might be an issue with Metabase. I tried the built-in Sample Database: *Whenever there is `/` appearing in the query, the variables of that statement do not get filled.**

1701355948715

AlexR2D2 commented 7 months ago

yes, looks like this is Metabase issue

I've got the error: IO Error: No files found that match the pattern "/my1/my2/**/*.parquet"

My Metabase: version v0.44.2, Built on 2022-08-31

sutao commented 7 months ago

Yeah, IO Error should be the correct error, because SQL is parsed but the path is not found. I will try 0.44.2 later on. It might be a new bug introduced by Metabase. In that case I will open an issue with Metabase. I will try it tomorrow and update this thread (and close it) as it becomes quite late in my timezone. :-D Thanks for your help!

sutao commented 7 months ago

Created a bug in Metabase and closing this issue. https://github.com/metabase/metabase/issues/36262

AlexR2D2 commented 7 months ago

@sutao thanks! subscribed to the issue