UPDATE data in BigQuery using node js

Question

It is possible to update data in BigQuery with one SQL instruction?

Data are in an array and each element must to update one row in BigQuery.

It is possible send all the information in one shot or I have to update rows one by one?

In BigQuery I have this table:

| userID | Level |
|--------|-------|
| 01     | 2     |
| 02     | 2     |
| 03     | 3     |
| 04     | 3     |

and in my code I have this array:

[{
userID: 01,
newLevel: 5
},
{
userID: 02,
newLevel: 8
}
]

So I need to UPDATE users 01 and 02 with their new levels data.

here is my code for executing queries to BigQuery using nodejs:

const customQuery = async (query) => {
    // Imports the Google Cloud client library

    /**
     * TODO(developer): Uncomment the following line before running the sample.
     */
    // const projectId = "your-project-id";
    //console.log("query " + query)
    // Modify this query however you need
    const sqlQuery = query;

    // Creates a client
    const bigquery = new BigQuery();

    // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
    const options = {
        query: sqlQuery,
        timeoutMs: 100000, // Time out after 100 seconds.
        useLegacySql: false, // Use standard SQL syntax for queries.
    };

    // Runs the query
    try {
        var r = await bigquery.query(options);
        //console.log("r: ", r)
    } catch (err) {
        console.log(err)
    }

    return r
}

user_5221944 · Answer

is it possible send all the information in one shot

Yes, you should use UPDATE statement - see simplified example for you to play and start with

CREATE TEMP TABLE mytable  AS (
  SELECT '01' userID, 2 Level UNION ALL
  SELECT '02', 2 UNION ALL
  SELECT '03', 3 UNION ALL
  SELECT '04', 3 
);

UPDATE mytable t 
SET Level = newLevel
FROM (
  SELECT '01' userID, 5 newLevel UNION ALL
  SELECT '02', 8 
) u
WHERE t.userID = u.userID;

SELECT * FROM mytable;

the output is

Row userID  Level    
1   02      8    
2   01      5    
3   03      3    
4   04      3

user_10542487 · Answer

Based on Mikhail answer, here is how my code in nodejs looks like. And it works!

    const updateRows = async (users) => {
    var query
    query = "UPDATE `mytable` t "
    query = query + "SET Level = newLevel "
    query = query + "FROM ( "
    users.forEach((u, index, array) => {
        if (index !== array.length - 1) {
            query = query + " SELECT '" + u.userID + "' userID, " + u.newLevel + " newLevel UNION ALL"
        } else {
            query = query + " SELECT '" + u.userID + "' userID, " + u.newLevel + " newLevel "
        }
    })
    query = query + ") u "
    query = query + "WHERE t.userID = u.userID "

    console.log(query)

    var updateResult = await bigquery.customQuery(query)
    console.log('updateResult :', updateResult);
}

UPDATE data in BigQuery using node js

2 Answers