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
– Tamir Klein, Commented Mar 12, 2019 at 6:37
Use this [link] (cloud.google.com/bigquery/docs/reference/rest/v2/…) which explain how to set job.query with a parameter object — Tamir Klein
– Tamir Klein, Commented Mar 12, 2019 at 6:43
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
– Logan, Commented Mar 12, 2019 at 6:48

Tim Swena · Accepted Answer · 2019-03-18 17:13:09Z

13

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);

edited Mar 18, 2019 at 17:13

Tim Swena

14.9k4 gold badges43 silver badges68 bronze badges

answered Mar 17, 2019 at 15:07

Steffany

1561 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

smoore4 · Accepted Answer · 2022-10-04 22:21:31Z

0

It is worth adding that you can create a stored procedure and pass parameters the same way as the accepted answer shows.

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


function testProc() {
  return new Promise((resolve) => {   

  const bigquery = new BigQuery();
  const sql = "CALL `my-project.my-dataset.getWeather`(@dt);";

  const options = {
  query: sql,
  params: {dt: '2022-09-01'},
  location: 'US'
  };

  // Run the query
  const result = bigquery.query(options);

  return result.then((rows) => {
    console.log(rows);
    resolve(rows);
  });
  
  });
} 

testProc().catch((err) => { console.error(JSON.stringify(helpers.getError(err.message))); });

answered Oct 4, 2022 at 22:21

smoore4

4,9263 gold badges43 silver badges64 bronze badges

Comments

Vishal Birari · Accepted Answer · 2022-04-06 19:19:26Z

-1

let ip_chunk = "'1.2.3.4', '2.3.4.5', '10.20.30.40'"
let query = `
  SELECT 
    ip_address.ip as ip,
    instance.zone as zone,
    instance.name as vmName,
    instance.p_name as projectName
  FROM 
    \`${projectId}.${datasetId}.${tableId}\` instance, 
    UNNEST(field_x.DATA.some_info) ip_address 
  WHERE ip_address.networkIP IN (${ip_chunk})`

**Use - WHERE ip_address.networkIP in (${ip_chunk})
instead of - WHERE ip in (${ip_chunk})**

answered Apr 6, 2022 at 19:19

Vishal Birari

1

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related