我只是在寻找一种方法来添加大量的数据到BigQuery表最近使用自定义函数。因此,我像这样尝试了推荐的方法:
#standardSQL
INSERT INTO `project.dataset.Quincy` (id, col)
WITH array_to_loop_through AS (
SELECT id
FROM UNNEST(GENERATE_ARRAY(1, 1000, 1)) id
)
SELECT id, CONCAT('Rank: ', CAST(id AS STRING))
FROM array_to_loop_through这花了8秒钟将100万个值添加到表中。因此,我在我的UDF上应用了以下方法:
CREATE TEMPORARY FUNCTION myFunc()
RETURNS array<string>
LANGUAGE js AS
"""
a=[""];
for(i=0;i<=50;i++){
a.push(randomString(12));
}
return a;
"""
OPTIONS (
library="gs://kaneki110299/tester.js"
);
#standardSQL
INSERT INTO `Lambert.fortune` (password)
WITH array_to_loop_through AS (
SELECT *
FROM UNNEST(myFunc()) id
)
SELECT CONCAT(CAST(id AS STRING))
FROM array_to_loop_through当我在BigQuery上运行这个查询时,它运行了5分钟,然后遇到一个只有50个值的UDF超时。当我将循环放在tester.js中时,同样的错误也发生了。所以,我尝试了一种不同的方式:
CREATE TEMPORARY FUNCTION myFunc()
RETURNS string
LANGUAGE js AS
"""
return randomString(12);
"""
OPTIONS (
library="gs://kaneki110299/tester.js"
);
#standardSQL
INSERT INTO `Lambert.fortune` (password)
Values (myFunc()),(myFunc()),(myFunc())...//1000 times与前一个查询不同,此查询只需30秒即可将1000个值从我的UDF结果添加到表中。看起来这个循环在BigQuery上运行得不好或者不快。
当运行用户定义的函数向其数据集插入大量数据时,是否有可能使用并行或BigQuery支持来优化其CPU性能?我试图在表中添加10亿个值,因此我使用的最后一个方法似乎不实用。
发布于 2019-04-09 19:28:16
使用googleApi npm,您可以编写一个JS程序,该程序将并行运行多个插入。
这是一个完整的mocha测试,介绍如何使用API进行一次性调用。您可以用自己的for loop包装内部调用,以并行地完成插入。
if (!global._babelPolyfill) {
var a = require("babel-polyfill")
}
import {google} from 'googleapis'
let bigQuery = google.bigquery("v2")
describe('Run query with API', async () => {
it('Run a query', async () => {
let result = await test('panada')
})
async function test(p1) {
try {
let query = `INSERT INTO \`project.dataset.Quincy\` (id, col)
WITH array_to_loop_through AS (
SELECT id
FROM UNNEST(GENERATE_ARRAY(1, 10, 1)) id
)
SELECT id, CONCAT('Rank: ', CAST(id AS STRING))
FROM array_to_loop_through`
let auth = getBasicAuthObj()
auth.setCredentials({
access_token: "myAccessToken",
refresh_token: "myRefreshToken"
})
let request = {
"projectId": "myProject",
auth,
"resource": {
"projectId": "myProject",
"configuration": {
"query": {
query,
"useLegacySql": false
},
"dryRun": false
}
}
}
console.log(`query is: ${query}`)
let result = await callBQ(request)
// debugger
console.log(`Status is: ${result.data.status.state}`)
} catch (err) {
console.log("err", err)
}
}
/**
* Call BigQuery jobs.insert
* @param request
* @returns {Promise<*>}
*/
async function callBQ(request) {
debugger
// console.log("request", request)
try {
let result = await bigQuery.jobs.insert(request, request)
console.log(`All good.....`)
return result
} catch (e) {
console.log(`Failed to run query: ${e}`)
}
}
/**
* Create oAuth object
* @returns {OAuth2Client}
*/
function getBasicAuthObj() {
let clientId = 'myclientId'
let clientSecret = 'mySecret'
let redirectUrl = 'URL'
return new google.auth.OAuth2(
clientId,
clientSecret,
redirectUrl
)
}
})注意: Bigquery在并行执行insert和运行查询时存在限制,有关更多详细信息,请参阅此link
https://stackoverflow.com/questions/55584300
复制相似问题