Yes, BigQuery supports INSERT and MERGE operations for adding new data or updating existing data.
For an INSERT operation, you can use the same query method of the BigQuery client in the Node.js client library. Here is an example of how to insert new rows into a table:
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function insertData() {
const query = `
INSERT dataset_id.table_id (column1, column2)
VALUES ('value1', 'value2')
`;
const options = {
query: query,
location: 'US',
};
const [job] = await bigquery.createQueryJob(options);
const [rows] = await job.getQueryResults();
console.log('Rows:');
rows.forEach(row => console.log(row));
}
insertData().catch(console.error);
For a MERGE operation, which allows you to insert new rows or update existing rows based on whether a certain condition is met, you can use something like this:
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function mergeData() {
const query = `
MERGE dataset_id.table_id T
USING (SELECT 'value1' as column1, 'value2' as column2) S
ON T.column1 = S.column1
WHEN MATCHED THEN
UPDATE SET T.column2 = S.column2
WHEN NOT MATCHED THEN
INSERT (column1, column2) VALUES(S.column1, S.column2)
`;
const options = {
query: query,
location: 'US',
};
const [job] = await bigquery.createQueryJob(options);
const [rows] = await job.getQueryResults();
console.log('Rows:');
rows.forEach(row => console.log(row));
}
mergeData().catch(console.error);
Please note that BigQuery is designed to be a data warehouse and its data manipulation operations are not as efficient as in a transactional database. Therefore, it is recommended to minimize the use of UPDATE, INSERT and MERGE operations in favor of loading and appending data in bulk where possible.
LinkedIn
Twitter
