Accessing Big Query Table from VM throwing Invalid Table Name error

[suddeb]

I have created a bigquery dataset and table like my-test-project-388514.babanames.all-baby-names. Added the Service Account With BigQuery Admin Role both in Dataset Level and Table Level. After that I have created one VM with that service account selected. Also the service account is having IAM BigQuery Admin Role in the IAM section.

With all these, when I am issuing the below command from VM SSH window, it is throwing me the error.

Command: 

bq query 'SELECT * FROM my-test-project-388514.babanames.all-baby-names LIMIT 1000'

Error:

There is no apilog flag so non-critical logging is disabled.
BigQuery error in query operation: Error processing job 'my-test-
project-388514:bqjob_r22055e354882c867_00000188a14dffc8_1': Invalid table name: `my-test-
project-388514.babanames.all-baby-names`
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)].

[ms4446]

In BigQuery, table names must follow certain rules:

1. A table name must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_)
2. The first character of a table name must be a letter
3. Table names are case-insensitive
4. Table names must be unique per dataset

Based on these rules, your table name my-test-project-388514.babanames.all-baby-names is invalid because it contains hyphens (-), which are not allowed.

To resolve this issue, you should rename your table using only valid characters. You can do this either through the BigQuery UI or using the bq command-line tool.

Here's how to do it using the bq command-line tool:

bq mk --table new_table_name my-test-project-388514:babanames.all_baby_names

Replace new_table_name with your chosen name that follows the rules above. After running this command, your table will be renamed and you should be able to query it with the new name.

In your command, you should replace my-test-project-388514.babanames.all-baby-names with my-test-project-388514:babanames.all_baby_names.

So, the correct command would be:

bq query 'SELECT * FROM `my-test-project-388514.babanames.all_baby_names` LIMIT 1000'

[suddeb]

I made the changes as per your suggestion. The name of the table is: 

all_baby_names. (See screenshot). But getting the same error (See screenshotError MessageTable Name

[suddeb]

I was able to solve this by adding the parameter bq query --nouse_legacy_sql. Basically using standard SQL, the problem is solved. But not sure why I need to use the standard SQL? Any idea?

[ms4446 · Accepted solution]

Yes I was going to suggest using --use_legacy_sql=false flag

In BigQuery, you can use either Legacy SQL or Standard SQL. The format for referencing tables differs between the two.

1. Legacy SQL: In Legacy SQL, you use a colon to separate the project ID and dataset ID, and a period to separate the dataset ID and table ID. So, it would look like this: [project_id:dataset_id.table_id] or project_id:dataset_id.table_id.

2. Standard SQL: In Standard SQL, you use periods to separate the project ID, dataset ID, and table ID, and you must enclose the entire table reference in backticks. So, it would look like this: `project_id.dataset_id.table_id`.

The --use_legacy_sql=false flag tells BigQuery to interpret the query as Standard SQL.

[suddeb]

Thank you for your detail answer. It is super helpful.