'Location myLocation does not support this operation.' When trying to get a specific row in BigQuery using node client

Question

I have a cloud function that uses bigquery client to make operations. Ultimately I'm looking to check if a row with an specific id exists so I built the following function (for now I'm just looking to make the request work and log the results):

const {BigQuery} = require('@google-cloud/bigquery');

const bigquery = new BigQuery({
  projectId: 'myProjectID',
  keyFilename: 'mykey.json'
});

 await idExist('MyID--Ak2aRpL0','myProjectID.myDatasetID.myTableName')

  async function idExist (id,table){
    console.log('Checking if it exist')

    const query = `SELECT * FROM \`${table}\` WHERE id='${id}'`;
    const options = {
      query: query,
      location: table
    }

    const [job] = await bigquery.createQueryJob(options);
    console.log(`Job '${job.id}' started.`);

    const [rows] = await job.getQueryResults();

    console.log('Resulted Rows:');
    rows.forEach(row => console.log(row));
  }

When I run this I get Error: Location projectID.datasetId.tableName does not support this operation. at new ApiError (/node_modules/@google-cloud/common/build/src/util.js:75:15)

Things I tried:

• Making sure that the service account that I'm using have "Big Query Data Editor" and "Big Query User" roles. Both should give me access to create jobs and manage tables.
• Different combinations of locations 'datasetID.tableName' and 'tableName' only. All with the same error result.
• Running the SAME query on the SQL workspace in browser (with an account with data editor + query user roles). This worked fine and was able to retrieve the row perfectly.
• Re-checked :) that the SA contains 'Data Editor' and 'User' roles
• Edit: As suggested I also tried running SELECT 1 AS TEST and SELECT count(1) FROM myProjectID.myDatasetID.myTableName with the same result.

Notes:

• The function is a firebase function, and the service account is the firebase-adminsdk, I added the big query roles to that one. -Inserting a row using the client works fine:

await bigquery.dataset('datasetID').table('tableName').insert(myObject);

• Querying using query() function works. So maybe an issue with creating jobs? I would prefer to use jobs for this to prevent having the function running too much time.

const result = await bigquery.query(query);

I would really appreciate some help/guidance on this issue. Let me know If I'm missing any helpful information.

Thanks!!

Answer 1

I followed the wrong tutorial and made a simple mistake. In the query options location means the fisical location of the dataset not the location of the table, mine was US. So the following options fixed the issue:

const options = {
  query: query,
  location: 'US'
}