Can I pass parameters of a function as part of a query in bigquery?

Question

async function test(p1) {
  let [result] = await bigquery.query({
    query: [
      'SELECT url',
      'FROM `publicdata.samples.github_nested`',
      'WHERE repository.owner = @owner'

    ].join(' '),
    params: {
      owner: p1
    }
  });
  result.forEach(row => {
    console.log(url)
  }
}

error : (node:19492) UnhandledPromiseRejectionWarning: Error: Undeclared query parameters

To write a big query, pass the function's parameters as part of the query I used the query statement provided by bigquery. In the node.js environment. However, I can not save the @owner that stores p1.

The following documents are related to this. https://cloud.google.com/nodejs/docs/reference/bigquery/2.0.x/BigQuery

To send a function's parameters as part of a query in bigquery What should I do? The node.js environment.

Answer 1

Your problem was that you did console.log(url) instead of console.log(row.url)

This is the full code I used to verify this

if (!global._babelPolyfill) {
    var a = require("babel-polyfill")
}

import BigQuery from '@google-cloud/bigquery'

describe('Check BigQuery', async () => {

    it('Test param', async () => {
        let result = await test('panada')

    })

    async function test(p1) {
        try {
            const bigquery = new BigQuery({
                projectId: `myProject`,
                keyFilename: '/Users/tamirklein/ssh/9473b308ab0e.json'
            })

            let query = [
                'SELECT url',
                'FROM `publicdata.samples.github_nested`',
                'WHERE repository.owner = @owner'

            ].join(' ')

            console.log(`query is: ${query}`)
            let [result] = await bigquery.query({
                query,
                params: {
                    owner: p1
                }
            })

            result.forEach(row => {
                console.log(`row number ${index}, url is: ${row.url}`)
            })
        } catch (err) {
            console.log("err", err)
        }
    }
})

And this is the output:

query is: SELECT url FROM `publicdata.samples.github_nested` WHERE repository.owner = @owner
row number 0, url is: https://github.com/de3/Panada
row number 1, url is: https://github.com/schbern/samples
row number 2, url is: https://github.com/panada/Panada/compare/47a1801f13...9dedbc8ce6
row number 3, url is: https://github.com/panada/Panada/pull/38
row number 4, url is: https://github.com/panada/samples/compare/46934664ea...a7cae9f088
row number 5, url is: https://github.com/panada/Panada/compare/175c88e2cb...47a1801f13
row number 6, url is: https://github.com/panada/samples/compare/1a35a44548...46934664ea
row number 7, url is: https://github.com/panada/samples/compare/256c9b4ed3...1f293ca245
row number 8, url is: https://github.com/panada/documentation/compare/49c38b23e2...d948d2eb97
row number 9, url is: https://github.com/panada/Panada/pull/38
row number 10, url is: https://github.com/panada/documentation/compare/d948d2eb97...46b7bcde5f
row number 11, url is: https://github.com/panada/samples/compare/a7cae9f088...256c9b4ed3
row number 12, url is: https://github.com/panada/Panada/pull/36
row number 13, url is: https://github.com/panada/documentation/pull/1
row number 14, url is: https://github.com/panada/documentation/pull/1
row number 15, url is: https://github.com/de3/documentation
row number 16, url is: https://github.com/cakyus/Panada
row number 17, url is: https://github.com/panada/Panada/compare/9dedbc8ce6...4db3e50d80
row number 18, url is: https://github.com/panada/documentation/compare/46b7bcde5f...52e9ef5c67
row number 19, url is: https://github.com/panada/documentation/issues/1#issuecomment-4533276

Answer 2

If the query returns a result, your code throws the ReferenceError "url is not defined", because you are using "console.log(url)" instead of "console.log(row.url)".

The Error "Undeclared query parameters" is not related to that, though. It means that you are calling the function "test" without the necessary argument, like 'test()'. You have to pass a string to it, like 'test("UnionOfRAD")'.

A test code that worked for me:

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

async function test(p1) {
  let [result] = await bigquery.query({
    query: [
      'SELECT url',
      'FROM `publicdata.samples.github_nested`',
      'WHERE repository.owner = @owner'

    ].join(' '),
    params: {
      owner: p1
    }
  });
  result.forEach(row => {
    console.log(row.url)
  })
}
test("UnionOfRAD")