SQL queries and tables

carla_813

Hello all,

I am begginer using sql.

I have a database from which I have created two different queries with the information that I need to join them after.

But after that, every code I try to run gives me this Error message:

Table "accounts" must be qualified with a dataset (e.g. dataset.table)

Does anyone knows what could be happening here? Do I need to adjust somenthing in my queries before start coding?

Thank you in advance,

Sarina

Hi @carla_813
,

Thank you for your query! It can be helpful in a case like this to provide a screenshot of your query and error, so that we know exactly what type of query you are running and where in DSS.

Based on what you've described, my understanding is that you have a BigQuery dataset, and are running a SQL query based off of your BigQuery dataset. It is the case that when querying from BigQuery, you must specify the BigQuery dataset that houses your BigQuery table (similar to how you might need to specify a database or schema for other database types).

The BigQuery "dataset" setting will be configured either at the BigQuery connection level, or at the DSS dataset level under Settings > BigQuery dataset:

Or under Administration > Connections > BigQuery for managed datasets specfically:

Assuming that the BigQuery table exists and has data in it, the issue you may be encountering is attempting to access this BigQuery table without specifying the corresponding dataset in your query. So for example, if I attempt to run a SQL query off of my BigQuery DSS dataset, I can reproduce the error you get if I omit the BigQuery dataset and select directly from the tablename instead:

This is because, I am attempting to access a table without the corresponding BigQuery dataset. The easiest way to include the dataset in your query is by simply clicking on the "insert" button when hovering over your DSS dataset name:

This will properly fill in the BigQuery dataset and table name in the query in the format <bigquery_dataset>.<tablename>:

Hopefully that helps resolve the error for you. If it doesn't, please attach some screenshots of the error you are seeing and the dataset settings you using as your input. This will help troubleshoot the issue.

Thanks,
Sarina

carla_813

Hello Sarina,

Thak you so much for your explanations.

I have tryed to follow your steps but I am not able to find those connection actions that you show me.

Probably the isue is related to what you show in your screeshots.

I have attached an screechot of the error that I receive when I try to merge both tables.

Does it make sense to you?

Thank you in advance,

Carla

Sarina

Hi @carla_813
,

Ah, I see you are in GCP itself So in this case, your dataset is "case_study_IL" (as you can see in your left-hand "Explorer" navigation. It also lives in the project "analytics-case-study". The syntax that BigQuery expects here is:

`<project>.<dataset>.<table>`

Or in your case:

`analytics-case-study.case_study_IL.payments`

So you'll want to update your FROM clause to select FROM `analytics-case-study.case_study_IL.payments`. In BigQuery itself, I would suggest relying on the auto-suggest feature. If you start typing in payments and click on the autocomplete option, it will fill in the full project and dataset path to the table as well, for example:

I hope that information is helpful, and good luck! 

Thanks,
Sarina