Can't get rows from BigQuery

I have connected Metabase to BigQuery, but error occured when I started New quwstion.

I couldn't resolve this error because error log is few.

Error message: Assert failed: (valid-bigquery-identifier? %)

How do I get other error information?

Thanks

Hi @systriver-koyama
Is the name of dataset long (like 32 characters)?
Can you copy the stacktrace from the logs? It should also be in the API response in the network tab on your browser Inspector.

Hi @flamber. Thanks for your reply.

name of dataset isn't long(10 characters).

stacktrace of Setting > Logs is as follows:
(I’m not sure how I check API response in the network tab on your browser Inspector.)

("--> driver.bigquery$dataset_name_for_current_query.invokeStatic(bigquery.clj:61)" "driver.bigquery$dataset_name_for_current_query.invoke(bigquery.clj:61)" "driver.bigquery.BigQueryIdentifier.to_sql(bigquery.clj:339)" "driver.generic_sql$honeysql_form__GT_sql_PLUS_args$fn__33417.invoke(generic_sql.clj:225)" "driver.generic_sql$honeysql_form__GT_sql_PLUS_args.invokeStatic(generic_sql.clj:224)" "driver.generic_sql$honeysql_form__GT_sql_PLUS_args.invoke(generic_sql.clj:219)" "driver.bigquery$honeysql_form__GT_sql.invokeStatic(bigquery.clj:349)" "driver.bigquery$honeysql_form__GT_sql.invoke(bigquery.clj:347)" "driver.bigquery$mbql__GT_native.invokeStatic(bigquery.clj:509)" "driver.bigquery$mbql__GT_native.invoke(bigquery.clj:493)" "driver$fn__29826$G__29732__29833.invoke(driver.clj:106)" "query_processor.middleware.mbql_to_native$query__GT_native_form$fn__32470.invoke(mbql_to_native.clj:16)" "query_processor.middleware.mbql_to_native$query__GT_native_form.invokeStatic(mbql_to_native.clj:16)" "query_processor.middleware.mbql_to_native$query__GT_native_form.invoke(mbql_to_native.clj:11)" "query_processor.middleware.mbql_to_native$mbql__GT_native$fn__32477.invoke(mbql_to_native.clj:32)" "query_processor.middleware.annotate$add_column_info$fn__31198.invoke(annotate.clj:300)" "query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__31939.invoke(cumulative_aggregations.clj:57)" "query_processor.middleware.limit$limit$fn__32430.invoke(limit.clj:17)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__37552.invoke(results_metadata.clj:87)" "query_processor.middleware.format_rows$format_rows$fn__32420.invoke(format_rows.clj:26)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__30542.invoke(add_dimension_projections.clj:236)" "query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__30830.invoke(add_row_count_and_status.clj:15)" "query_processor.middleware.driver_specific$process_query_in_context$fn__32181.invoke(driver_specific.clj:12)" "query_processor.middleware.resolve_driver$resolve_driver$fn__35350.invoke(resolve_driver.clj:15)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__31376$fn__31377.invoke(bind_effective_timezone.clj:9)" "util.date$call_with_effective_timezone.invokeStatic(date.clj:88)" "util.date$call_with_effective_timezone.invoke(date.clj:77)" "query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__31376.invoke(bind_effective_timezone.clj:8)" "query_processor.middleware.store$initialize_store$fn__37567$fn__37568.invoke(store.clj:11)" "query_processor.store$do_with_new_store.invokeStatic(store.clj:34)" "query_processor.store$do_with_new_store.invoke(store.clj:30)" "query_processor.middleware.store$initialize_store$fn__37567.invoke(store.clj:10)" "query_processor.middleware.cache$maybe_return_cached_results$fn__31798.invoke(cache.clj:127)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__31846.invoke(catch_exceptions.clj:64)" "query_processor$process_query.invokeStatic(query_processor.clj:213)" "query_processor$process_query.invoke(query_processor.clj:209)" "query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:322)" "query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:316)" "query_processor$fn__37732$process_query_and_save_execution_BANG___37737$fn__37738.invoke(query_processor.clj:354)" "query_processor$fn__37732$process_query_and_save_execution_BANG___37737.invoke(query_processor.clj:340)" "query_processor$fn__37756$process_query_and_save_with_max_BANG___37761$fn__37762.invoke(query_processor.clj:373)" "query_processor$fn__37756$process_query_and_save_with_max_BANG___37761.invoke(query_processor.clj:369)" "api.dataset$fn__44259$fn__44262.invoke(dataset.clj:45)" "api.common$fn__23158$invoke_thunk_with_keepalive__23163$fn__23164$fn__23165.invoke(common.clj:435)"),

and sql log is as follows:

#Feb 18 00:17:29 ERROR metabase.driver.generic-sql :: Invalid HoneySQL form:
{:from ({:dataset-name nil, :table-name "orders", :field-name nil, :alias? nil}),
:select ([{:dataset-name nil, :table-name "orders", :field-name "id", :alias? nil} :id]
...

I think that dataset-name is incorrect(Example: Initial character is numeric).
I try to change dataset-name.

@systriver-koyama
Great, at least there are some logs to work with. Which browser are you using?
WIth Chrome/Firefox, you can press Cmd+Shift+i to open browser developer tools. Then click the Network-tab. Now when you click the “Refresh” button, it generates POST request, which is shown as a line in the Network-tab. Click on that line and it will show the details for that request. Inside the details-pane, there’s a Response-tab, which will show a lot of details about the Metabase query communication.
Please review entire response data before posting, so nothing sensitive is shown publicly, that you want to keep private. But please clearly mark places where you have sanitized instead of just deleting.
Like My password is: 123secret => My password is: #SANITIZED#

EDIT: Also, which version of Metabase are you using?


@camsaul I need a little help with a stacktrace. This report sounds similar to issue #9388, but I don’t think they’re related.

@flamber
Thanks for your reply.

I resolved this problem.
The cause of this is that initial character of dataset-name is numeric.
I succeeded after changing initial char of dataset-name to alphabetic char.

Thanks for teaching us how to get browser log. I use chrome.
(I understood that metabase can show from browser.)

Metabase version: 0.31.2.1

Log of POST request is as follows just in case:

{"database_id":5,"started_at":"2019-02-18T08:03:55.807Z","json_query":{"database":5,"type":"query","query":{"source-table":15},"parameters":,"constraints":{"max-results":10000,"max-results-bare-rows":2000}},"native":null,"status":"failed","stacktrace":["--> driver.bigquery$dataset_name_for_current_query.invokeStatic(bigquery.clj:61)","driver.bigquery$dataset_name_for_current_query.invoke(bigquery.clj:61)","driver.bigquery.BigQueryIdentifier.to_sql(bigquery.clj:339)","driver.generic_sql$honeysql_form__GT_sql_PLUS_args$fn__33417.invoke(generic_sql.clj:225)","driver.generic_sql$honeysql_form__GT_sql_PLUS_args.invokeStatic(generic_sql.clj:224)","driver.generic_sql$honeysql_form__GT_sql_PLUS_args.invoke(generic_sql.clj:219)","driver.bigquery$honeysql_form__GT_sql.invokeStatic(bigquery.clj:349)","driver.bigquery$honeysql_form__GT_sql.invoke(bigquery.clj:347)","driver.bigquery$mbql__GT_native.invokeStatic(bigquery.clj:509)","driver.bigquery$mbql__GT_native.invoke(bigquery.clj:493)","driver$fn__29826$G__29732__29833.invoke(driver.clj:106)","query_processor.middleware.mbql_to_native$query__GT_native_form$fn__32470.invoke(mbql_to_native.clj:16)","query_processor.middleware.mbql_to_native$query__GT_native_form.invokeStatic(mbql_to_native.clj:16)","query_processor.middleware.mbql_to_native$query__GT_native_form.invoke(mbql_to_native.clj:11)","query_processor.middleware.mbql_to_native$mbql__GT_native$fn__32477.invoke(mbql_to_native.clj:32)","query_processor.middleware.annotate$add_column_info$fn__31198.invoke(annotate.clj:300)","query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__31939.invoke(cumulative_aggregations.clj:57)","query_processor.middleware.limit$limit$fn__32430.invoke(limit.clj:17)","query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__37552.invoke(results_metadata.clj:87)","query_processor.middleware.format_rows$format_rows$fn__32420.invoke(format_rows.clj:26)","query_processor.middleware.add_dimension_projections$add_remapping$fn__30542.invoke(add_dimension_projections.clj:236)","query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__30830.invoke(add_row_count_and_status.clj:15)","query_processor.middleware.driver_specific$process_query_in_context$fn__32181.invoke(driver_specific.clj:12)","query_processor.middleware.resolve_driver$resolve_driver$fn__35350.invoke(resolve_driver.clj:15)","query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__31376$fn__31377.invoke(bind_effective_timezone.clj:9)","util.date$call_with_effective_timezone.invokeStatic(date.clj:88)","util.date$call_with_effective_timezone.invoke(date.clj:77)","query_processor.middleware.bind_effective_timezone$bind_effective_timezone$fn__31376.invoke(bind_effective_timezone.clj:8)","query_processor.middleware.store$initialize_store$fn__37567$fn__37568.invoke(store.clj:11)","query_processor.store$do_with_new_store.invokeStatic(store.clj:34)","query_processor.store$do_with_new_store.invoke(store.clj:30)","query_processor.middleware.store$initialize_store$fn__37567.invoke(store.clj:10)","query_processor.middleware.cache$maybe_return_cached_results$fn__31798.invoke(cache.clj:127)","query_processor.middleware.catch_exceptions$catch_exceptions$fn__31846.invoke(catch_exceptions.clj:64)","query_processor$process_query.invokeStatic(query_processor.clj:213)","query_processor$process_query.invoke(query_processor.clj:209)","query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:322)","query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:316)","query_processor$fn__37732$process_query_and_save_execution_BANG___37737$fn__37738.invoke(query_processor.clj:354)","query_processor$fn__37732$process_query_and_save_execution_BANG___37737.invoke(query_processor.clj:340)","query_processor$fn__37756$process_query_and_save_with_max_BANG___37761$fn__37762.invoke(query_processor.clj:373)","query_processor$fn__37756$process_query_and_save_with_max_BANG___37761.invoke(query_processor.clj:369)","api.dataset$fn__44259$fn__44262.invoke(dataset.clj:45)","api.common$fn__23158$invoke_thunk_with_keepalive__23163$fn__23164$fn__23165.invoke(common.clj:435)"],"context":"ad-hoc","error":"Assert failed: (valid-bigquery-identifier? %)","row_count":0,"running_time":401,"preprocessed":{"database":5,"type":"query","query":{"source-table":15,"fields":[["field-id",150],["field-id",142],["field-id",144],["field-id",183],["field-id",181],["field-id",171],["field-id",184],["field-id",180],["field-id",174],["field-id",159],["field-id",149],["field-id",177],["field-id",156],["field-id",164],["field-id",176],["field-id",155],["field-id",146],["field-id",160],["field-id",154],["field-id",169],["field-id",165],["field-id",166],["field-id",151],["field-id",182],["field-id",173],["field-id",148],["field-id",140],["field-id",157],["field-id",141],["field-id",147],["field-id",163],["field-id",158],["field-id",179],["field-id",175],["field-id",170],["field-id",168],["field-id",153],["field-id",185],["field-id",145],["field-id",143],["field-id",167],["field-id",178],["field-id",162],["field-id",161],["field-id",172],["field-id",152]],"limit":2000},"constraints":{"max-results":10000,"max-results-bare-rows":2000},"driver":{},"settings":{}},"data":{"rows":,"cols":,"columns":}}

Thank you very match.

1 Like

Google's documentation says that BigQuery identifiers must start with a letter or an underscore:

Identifiers

Identifiers are names that are associated with columns, tables, and other database objects.

Identifiers must begin with a letter or an underscore. Subsequent characters can be letters, numbers, or underscores. Quoted identifiers are identifiers enclosed by backtick (`) characters and can contain any character, such as spaces or symbols. However, quoted identifiers cannot be empty. Reserved Keywords can only be used as identifiers if enclosed by backticks.

I guess that must not apply to dataset names if you have one that starts with a number. Please open a GitHub issue and ping me and I will tweak the Metabase validation code to allow dataset identifiers that start with numbers

1 Like

@camsaul
Thanks for your reply.

This issue already exist. (#9388)

I added message this issue.
Thanks.

Hi, we use BigQuery and since we begin our datasets with numeric figure it makes it kind of impossible to use it with Metabase. I see this was discussed two years ago, do you still have plans to resolve this issue and adapt to bigquery standards?

@gaspers There was a PR that tried to fix this, but had some test failures - and now has a merge conflict:
https://github.com/metabase/metabase/pull/13111
There are more than 2000 issues open, so we try to prioritize based on upvotes (:+1: on the first post of an issue) and based on other internal parameters as well.