BigQuery error using SQL Variables: "missing dataset while no default dataset is set in the request"

image

My query:

select * from engineers where {{id}}

Metabase: v0.34.3
Similar issue: No default dataset is set in the request

My dataset is in the ‘US’ location in BigQuery so it seems to be a different problem than the issue above from another poster.

WARN middleware.process-userland-query :: Query failure {:status :failed,
 :class clojure.lang.ExceptionInfo,
 :error "Table name \"engineers\" missing dataset while no default dataset is set in the request.",
 :stacktrace
 ("--> driver.google$execute_no_auto_retry.invokeStatic(google.clj:39)"
  "driver.google$execute_no_auto_retry.invoke(google.clj:32)"
  "driver.google$execute.invokeStatic(google.clj:53)"
  "driver.google$execute.invoke(google.clj:43)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:202)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:191)"
  "driver.bigquery$execute_bigquery.invokeStatic(bigquery.clj:193)"
  "driver.bigquery$execute_bigquery.invoke(bigquery.clj:191)"
  "driver.bigquery$process_native_STAR_$thunk__1362.invoke(bigquery.clj:209)"
  "driver.bigquery$process_native_STAR_.invokeStatic(bigquery.clj:214)"
  "driver.bigquery$process_native_STAR_.invoke(bigquery.clj:204)"
  "driver.bigquery$eval1366$fn__1368.invoke(bigquery.clj:229)"
  "query_processor$fn__46335$execute_query__46340$fn__46341.invoke(query_processor.clj:69)"
  "query_processor$fn__46335$execute_query__46340.invoke(query_processor.clj:64)"
  "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__37455.invoke(mbql_to_native.clj:40)"
  "query_processor.middleware.annotate$result_rows_maps__GT_vectors$fn__40562.invoke(annotate.clj:541)"
  "query_processor.middleware.annotate$add_column_info$fn__40468.invoke(annotate.clj:485)"
  "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__41503.invoke(cumulative_aggregations.clj:57)"
  "query_processor.middleware.resolve_joins$resolve_joins$fn__43430.invoke(resolve_joins.clj:184)"
  "query_processor.middleware.limit$limit$fn__42138.invoke(limit.clj:19)"
  "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__46195.invoke(results_metadata.clj:87)"
  "query_processor.middleware.format_rows$format_rows$fn__42126.invoke(format_rows.clj:76)"
  "query_processor.middleware.add_dimension_projections$add_remapping$fn__38215.invoke(add_dimension_projections.clj:232)"
  "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__38866.invoke(add_source_metadata.clj:107)"
  "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__43480.invoke(resolve_source_table.clj:46)"
  "query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__38703.invoke(add_row_count_and_status.clj:16)"
  "query_processor.middleware.driver_specific$process_query_in_context$fn__41578.invoke(driver_specific.clj:12)"
  "query_processor.middleware.resolve_driver$resolve_driver$fn__43094.invoke(resolve_driver.clj:22)"
  "query_processor.middleware.store$initialize_store$fn__46220$fn__46221.invoke(store.clj:11)"
  "query_processor.store$do_with_store.invokeStatic(store.clj:46)"
  "query_processor.store$do_with_store.invoke(store.clj:40)"
  "query_processor.middleware.store$initialize_store$fn__46220.invoke(store.clj:10)"
  "query_processor.middleware.async$async__GT_sync$fn__37366.invoke(async.clj:23)"
  "query_processor.middleware.async_wait$runnable$fn__40619.invoke(async_wait.clj:89)"),
 :query
 {:type "native",
  :native
  {:query "select * from engineers where {{id}}", :template-tags {:id {:id "8d8d1522-ed97-543c-42e7-d5a93a3bb17b", :name "id", :display_name "Id", :type "number", :required true, :default "3"}}},
  :parameters [{:type "category", :target ["variable" ["template-tag" "id"]], :value "3"}],
  :async? true,
  :middleware {:add-default-userland-constraints? true, :userland-query? true},
  :info
  {:executed-by 1,
   :context :ad-hoc,
   :card-id nil,
   :nested? false,
   :query-hash [-119, 1, 40, -70, 112, -40, 31, -50, 122, -65, -39, 122, -119, 103, 33, -127, -18, 89, -62, 115, 93, 26, -69, 14, -37, -72, 118, 116, -112, -29, -114, -98]},
  :constraints {:max-results 10000, :max-results-bare-rows 2000}},
 :ex-data
 {"code" 400,
  "errors" [{"domain" "global", "message" "Table name \"engineers\" missing dataset while no default dataset is set in the request.", "reason" "invalid"}],
  "message" "Table name \"engineers\" missing dataset while no default dataset is set in the request.",
  "status" "INVALID_ARGUMENT"}}

Hi @troyharvey
Make sure you’re including the dataset name with the table, and enclosing them with ` as noted in the documentation:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type