BigQuery: Table name missing dataset while no default dataset is set in the request

Question

Here is the issue with a BigQuery query. I know this query is missing a dataset name, so the error "Table name "my_table" missing dataset while no default dataset is set in the request."

select * from my_table;

Changing 'my_tabale' to 'my_dataset.my_table' will fix the issue. But can somebody help me with setting a default dataset. The error message clearly giving an indication that BigQuery has such an option.

Answer 1

Depending on which API you are using, you can specify the defaultDataset parameter when running your BigQuery job. More information for the jobs.query api can be found here https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query.

For example, using the NodeJS API for createQueryJob https://googleapis.dev/nodejs/bigquery/latest/BigQuery.html#createQueryJob, you can do something similar to this:

const options = {
  keyFilename: process.env.GOOGLE_APPLICATION_CREDENTIALS,
  projectId: process.env.GOOGLE_APPLICATION_PROJECT_ID,
  defaultDataset: {
    datasetId: process.env.BIGQUERY_DATASET_ID,
    projectId: process.env.GOOGLE_APPLICATION_PROJECT_ID
  },
  query: `select * from my_table;`
}

const [job] = await bigquery.createQueryJob(options);
let [rows] = await job.getQueryResults();

Answer 2

The short answer is: SET @@dataset_id = 'whatever';

But, this is a scripting command, so you will have to enclose your SQL query in a BEGIN ... END block. Note the semicolon. Scripting gives you some added flexibility. On the other hand, it prevents the console from doing that fun thing where it estimates the processing cost.