node.js - 使用选项 ALLOW_FIELD_ADDITION 插入缺少字段的 BigQuery（来自 nodeJS）

Question

我正在尝试从 nodeJS 应用程序在 BigQuery 中插入一行。当由于缺少字段而无法插入行时，我想追加一个新列并插入。如此处所示：https ://github.com/googleapis/nodejs-bigquery/blob/master/samples/addColumnLoadAppend.js

我设法检测到丢失的字段并创建了一个新模式。但是当我尝试使用选项加载时，如示例所示，我得到了同样的错误说：'没有这样的字段'并且 BigQuery 中的架构仍然相同

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

const projectId = "XXXXX";
const datasetId = "XXXXX";
const tableId = "XXXXX";

const bigquery = new BigQuery({
    projectId: projectId
});

const table = bigquery.dataset(datasetId).table(tableId);

const rows = [
    {
        playerId: '48374',
        object: 'gemss',
        value: '764',
        gameTime: '0',
        date: '2019-0-3',
        other: 'other',
        bubu: 'banane'
    }
]

exports.main = (event, context) => {
    bigquery
        .dataset(datasetId)
        .table(tableId)
        .insert(rows)
        .then((foundErrors) => {
            rows.forEach((row) => console.log('Inserted: ', row));

            if (foundErrors && foundErrors.insertErrors != undefined) {
                foundErrors.forEach((err) => {
                    console.log('Error: ', err);
                })
            }
        })
        .catch((err) => {
            //We go through the list of insertions errors
            err.errors.forEach((error) => {
                //We get the list of errors on each insert
                const insertErrors = error.errors
                //We get the row in error
                const rowInError = error.row
                //We search in the list of errors for an error linked to a missing field in the database
                insertErrors.forEach((insertErr) => {
                    if (insertErr.message == 'no such field.') {
                        console.log("insert error, we need to update the database")
                        //We get the BQ schema
                        getTableMetadata().then((metadata) => {
                            //We get the misisng fields
                            const missingfield = getMissingFields(
                                Object.keys(rowInError),
                                metadata.schema.fields
                            )

                            //Add the misssing fields to the new schema
                            metadata.schema.fields.push({
                                name: missingfield,
                                type: "STRING"
                            })
                            //const schema = 'playerId:STRING, object:STRING, value:STRING, gameTime:STRING, date:STRING, other:STRING, bubu:STRING'

                            const destinationTableRef = table.metadata.tableReference;
                            //Set insert job options
                            const options = {
                                schema: metadata.schema,
                                schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
                                writeDisposition: 'WRITE_APPEND',
                                destinationTable: destinationTableRef,
                            };
                            console.log(options)
                            //Insert data with the new schema
                            bigquery
                                .dataset(datasetId)
                                .table(tableId)
                                .load(rows, options)
                                .catch((err) => {
                                    // HERE I GOT THE SAME NO SUCH FIELD ERROR
                                    console.log(err.errors)
                                })
                        })
                    } else {
                        console.error("Unhandled insert error:", insertErr)
                    }
                })
            });
        });
};

const getMissingFields = (a1, a2) => {
    const missingFields = []

    a1.forEach((field) => {
        let isInArray = false
        a2.map((elmt) => {
            if (elmt.name === field)
                isInArray = true
        })
        if (!isInArray) {
            console.log("missing filed:", field)
            missingFields.push(field)
        }
    })
    return missingFields[0]
}

const getTableMetadata = async () => {
    const [metadata] = await table.getMetadata();
    return metadata
}

this.main()

标签： node.jsgoogle-cloud-platformgoogle-bigquery