How to pass query statement to bigquery in node.js environment

Question

During the big query, the parameters of the function in the SQL statement I want to update the result of a sql statement by inserting it as @ variable name. However, there is no method to support node.js.

For python, there are methods like the following example. You can use the function's parameters as @ variable names.

query = "" "
SELECT word, word_count
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @ corpus
AND word_count> = @min_word_count
ORDER BY word_count DESC;"" "
query_params = [
bigquery.ScalarQueryParameter ('corpus', 'STRING', 'romeoandjuliet'),
bigquery.ScalarQueryParameter ('min_word_count', 'INT64', 250)]
job_config = bigquery.QueryJobConfig ()
job_config.query_parameters = query_params

related document: https://cloud.google.com/bigquery/docs/parameterized-queries#bigquery-query-params-python

I would like to ask for advice.

The fact there is no javascript example in the link provided doesn't means the @ is not supported via javascript client lib in node.js. The @ is a property of the sql process on bigquery side. Please try this using javascript sql client and if it doesn't work post the code and the error message you get... Good luck. — Tamir Klein
Use this [link] (cloud.google.com/bigquery/docs/reference/rest/v2/…) which explain how to set job.query with a parameter object — Tamir Klein
One option is to use API to connect & query through node js cloud.google.com/bigquery/docs/… (you can try it in the API explorer) — Logan

Steffany Steffany · Accepted Answer · 2019-03-17T15:07:50

BigQuery node.js client supports parameterized queries when you pass them with the params key in options. Just updated the docs to show this. Hope this helps!

Example:

const sqlQuery = `SELECT word, word_count
      FROM \`bigquery-public-data.samples.shakespeare\`
      WHERE corpus = @corpus
      AND word_count >= @min_word_count
      ORDER BY word_count DESC`;

const options = {
  query: sqlQuery,
  // Location must match that of the dataset(s) referenced in the query.
  location: 'US',
  params: {corpus: 'romeoandjuliet', min_word_count: 250},
};

// Run the query
const [rows] = await bigquery.query(options);

How to pass query statement to bigquery in node.js environment

1 Answers