how to update the date in big query table has already records.

Google Cloud Data Analytics
1

Hi All,
I am using the node API client Library https://github.com/googleapis/nodejs-bigquery

how to update the data into the table. already existing data

1 Like
2

In BigQuery, you can perform updates to the data in a table using the query method of the BigQuery client in the Node.js client library. You will use SQL UPDATE statement to modify the existing data.

Here is a simple example:

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

async function updateData() {
const query = UPDATE dataset_id.table_id SET column_to_update = 'new_value' WHERE condition_column = 'condition_value';

// For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/Job
const options = {
query: query,
// Location must match that of the dataset(s) referenced in the query.
location: ‘US’,
};

// Run the query as a job
const [job] = await bigquery.createQueryJob(options);

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

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

updateData().catch(console.error);

In the query string, you would replace dataset_id, table_id, column_to_update, new_value, condition_column, and condition_value with your specific values.

1 Like
3

@ms4446 Thanks lot for your answer this really helpfully. does any

can any feature in big query allowed insert or update data.

1 Like
4

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.

3 Likes