2

I know this topic has come up many times but still here I am. Data processing location seems consistent (dataset, US; query: US) and I am using backticks & long format in the FROM clause

Below are two sequences of code. The first one works perfectly:

SELECT station_id
FROM `bigquery-public-data.austin_bikeshare.bikeshare_stations`

Whereas the following returns an error message:

SELECT bikeshare_stations.station_id
FROM `bigquery-public-data.austin_bikeshare`

Not found: Dataset glassy-droplet-347618:bigquery-public-data was not found in location US

My question, thus, is why do the first lines of text work while the second doesn't?

enter image description here

enter image description here

enter image description here

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
Markku P.
  • 133
  • 1
  • 4

1 Answers1

2

You need to understand the different parts of the backticks:

  1. bigquery-public-data is the name of the project;
  2. austin_bikeshare is the name of the schema (aka dataset in BQ); and
  3. bikeshare_stations is the name of the table/view.

Therefore, the shorter format you are looking for is: austin_bikeshare.bikeshare_stations (instead of bigquery-public-data.austin_bikeshare).

Using bigquery-public-data.austin_bikeshare means that you have a schema called bigquery-public-data that contains a table called austin_bikeshare , when this is not true.

Aleix CC
  • 1,601
  • 1
  • 7
  • 18
  • Thanks for answering, with you reply and some more research I came to the following conclusions, please correct me if I am wrong. 1) Last part of a FROM-clause must always refer to a specific table (i.e. that it can not refer to a dataset)? 2) FROM-clause requires full reference to function without errors (project-dataset-table) – Markku P. May 18 '22 at 10:42
  • 1) Exactly, if the last part of the FROM clause refers to a dataset, BigQuery will treat it as if it was a table and won't probably find it. 2) If I am not mistaken, you can select, somewhere in the BQ UI, a project. If, for instance, you have the 'bigquery-public-data' project selected, you wouldn't need to state it in the FROM clause and just use `austin_bikeshare.bikeshare_stations`. – Aleix CC May 18 '22 at 11:08
  • Thanks, problem solved! I will check your suggestion for 2) – Markku P. May 18 '22 at 11:18
  • No worries! Glad I could help :) – Aleix CC May 18 '22 at 13:29