代码之家 › 专栏 › 技术社区 › MatiasG

使用node js更新BigQuery中的数据

sql-update google-bigquery node.js sql javascript

MatiasG · 技术社区 · 6 年前

是否可以使用一条SQL指令更新BigQuery中的数据?

数据在一个数组中,每个元素必须更新BigQuery中的一行。

是否可以一次性发送所有信息,或者我必须逐个更新行?

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

在我的代码中有一个数组:

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

所以我需要用用户的新级别数据更新用户01和02。

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
}

0 回复 | 直到 6 年前

Mikhail Berlyant 6 年前

有可能一次性发送所有信息吗

是的,你应该使用 UPDATE statement -请参阅简化示例,让您开始玩游戏

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;

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

MatiasG 6 年前

根据Mikhail的回答,下面是我在nodejs中的代码。这是可行的!

    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);
}