NodeJS 使用其中某些值可能为空的对象数组更新BigQuery表

zqdjd7g9 于 2023-03-01 发布在 Node.js

关注(0)|答案(1)|浏览(124)

我需要使用自定义对象数组更新BigQuery中的现有表。我正在使用Node.js和以下客户端库：https://github.com/googleapis/nodejs-bigquery
假设我有一个包含两个“STRING”类型列的表，要更新它，我使用以下代码。

import BigQueryConnection from "./utilities/BigQuery.connection";

const data = [
  {
    id: "1",
    country: "Germany"
  },
    {
    id: "2",
    country: "France"
  },
]

  const tableId = 'test_dataset.test_table';
  const mergeQuery = `
  MERGE ${tableId} t
  USING UNNEST(@rows) s
  ON t.id = s.id
  WHEN MATCHED THEN
  UPDATE SET 
    id = s.id,
    country = s.country
  WHEN NOT MATCHED THEN
    INSERT (id, country) VALUES (s.id, s.country)`;

  const options = {
    query: mergeQuery,
    params: {
      rows: data
    },
    types: {
      id: 'STRING',
      country: 'STRING',
    },
    useLegacySql: false
  };

  const response = await BigQueryConnection.bigquery.createQueryJob(options);
  const job = response[0];

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

它运行良好，但不时我的数据来这样：

const data = [
  {
    id: "1",
    country: null
  },
  {
    id: "2",
    country: "France"
  },
]

Error: Parameter types must be provided for null values via the 'types' field in query options.即使提供了参数类型，错误仍然存在。是我遗漏了什么，还是这不适用于大查询？

node.js

来源：https://stackoverflow.com/questions/75524165/update-bigquery-table-with-an-array-of-objects-where-some-of-the-values-may-be-n

1条答案

按热度按时间

j13ufse21#

我用下面的结构创建了我这边的脚本：

query.js文件：

'use strict';

function main() {
    // [START bigquery_query]
    // [START bigquery_client_default_credentials]
    // Import the Google Cloud client library using default credentials
    const {BigQuery} = require('@google-cloud/bigquery');
    const bigquery = new BigQuery();

    const data = [
        {
            "featureName": "featureReal",
            "jobName": "jobReal",
            "pipelineStep": "pipelineReal",
            "inputElement": "inputElementReal",
            "exceptionType": "myExceptionType",
            "stackTrace": "stackTraceReal",
            "componentType": "componentTypeReal",
            "dagOperator": "dagOperatorReal",
            "additionalInfo": "info Real"
        },
        {
            "featureName": "featurePSG",
            "jobName": "jobPSG2",
            "pipelineStep": "pipelinePSG",
            "inputElement": "inputElementPSG",
            "exceptionType": "myExceptionType",
            "stackTrace": "stackTracePSG",
            "componentType": "componentTypePSG",
            "dagOperator": "dagOperatorPSG",
            "additionalInfo": "info PSG"
        }
    ]

    // [END bigquery_client_default_credentials]
    async function query() {
        const tableId = "`gb-poc-373711.monitoring.job_failure`"

        const mergeQuery = `
          MERGE ${tableId} t
          USING UNNEST(@rows) s
          ON t.featureName = s.featureName
          WHEN MATCHED THEN
          UPDATE SET 
            jobName = s.jobName,
            pipelineStep = s.pipelineStep
          WHEN NOT MATCHED THEN
            INSERT (
                featureName, 
                jobName,
                pipelineStep,
                inputElement,
                exceptionType,
                stackTrace,
                componentType,
                dagOperator,
                additionalInfo
            ) 
            VALUES (
                s.featureName, 
                s.jobName,
                s.pipelineStep,
                s.inputElement,
                s.exceptionType,
                s.stackTrace,
                s.componentType,
                s.dagOperator,
                s.additionalInfo
            )`;

        // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
        const options = {
            query: mergeQuery,
            // Location must match that of the dataset(s) referenced in the query.
            location: 'EU',
            params: {rows: data}
        };

        // Run the query as a job
        const [job] = await bigquery.createQueryJob(options);
        console.log(`Job ${job.id} started.`);

        // Wait for the query to finish
        const [rows] = await job.getQueryResults();

        // Print the results
        console.log('Rows:');
        rows.forEach(row => console.log(row));
    }

    // [END bigquery_query]
    query();
}

main(...process.argv.slice(2));

它在我这边工作正常。

rows参数被正确地传递给了我的查询
我还在null字段上测试了合并查询，它工作正常

package.json文件：

{
  "name": "bigquery-node-client-test",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "@google-cloud/bigquery": "^6.1.0"
  }
}

要获得选项的详细信息和参考文档，请查看link。

赞(0）回复(0）举报 2023-03-01

我来回答

NodeJS 使用其中某些值可能为空的对象数组更新BigQuery表

1条答案

相关问题

热门标签

最新问答