I have a view in bigquery which contains fields from different datasets and tables, Now I would like to query this view through my google script. What is correct way of doing that.
我在bigquery中有一个视图，其中包含来自不同数据集和表的字段，现在我想通过我的google脚本查询此视图。这样做的正确方法是什么。
Actually Currently I have created a separate table in bigquery and querying the table instead of view but I need view as view will get updated when the dependency tables will be updated.
实际上目前我已经在bigquery中创建了一个单独的表并查询了表而不是查看但我需要视图，因为视图将在更新依赖表时得到更新。
If I am using the table, It is working fine but in case of view I am getting below error:
如果我正在使用该表，它工作正常，但如果查看我得到以下错误：
Exception: Response Code: 404. Message: Not Found.
Bigquery api's to return the result of query.
Bigquery api用于返回查询结果。
try {
var job = BigQuery.newJob();
var config = BigQuery.newJobConfiguration();
var queryConfig = BigQuery.newJobConfigurationQuery();
queryConfig.setQuery(sql);
queryConfig.setMaximumBillingTier(5);
config.setQuery(queryConfig);
job.setConfiguration(config);
var jobid = BigQuery.Jobs.insert(job, projectNumber).jobReference;
queryResults = BigQuery.Jobs.getQueryResults(projectNumber, jobid.jobId);
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
// Check on status of the Query Job : MONTHLY
while (queryResults.getJobComplete() == false) {
try {
queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.jobId);
//queryResults = BigQuery.Jobs.getQueryResults(projectNumber, job.id);
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
}
return queryResults;
If I comment out my first try clause and use below one
如果我注释掉我的第一个尝试条款并使用下面的一个
try {
var queryRequest = BigQuery.newQueryRequest();
queryRequest.setQuery(sql).setTimeoutMs(100000);
queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
//Browser.msgBox(queryResults);
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
then It starts giving me
然后开始给我
Exception: Query exceeded resource limits for tier 1. Tier 3 or higher required.
1 个解决方案
#1
0
It looks like the 'configuration.query.maximumBillingTier' property isn't getting set when you are inserting the job. The method of using 'JobConfigurationQuery' and other classes seems to have been abandoned as there is no mention of them in the current docs, and I needed to resort to using the Wayback Machine to find them.
在插入作业时，看起来没有设置'configuration.query.maximumBillingTier'属性。使用'JobConfigurationQuery'和其他类的方法似乎已被放弃，因为在当前的文档中没有提及它们，我需要使用Wayback Machine来查找它们。
The most recently available document from 11/12/2013 only defines getters and setters for a few configuration properties, and 'maximumBillingTier' isn't one of them.
2013年12月11日的最新文档仅定义了一些配置属性的getter和setter，而'maximumBillingTier'不是其中之一。
I'd suggest manually setting the request properties as is done in the usage examples from the current documentation, rather than relying on the "old" object constructors, as they seem to only be left around for compatibility purposes and are incomplete.
我建议手动设置请求属性，就像在当前文档的使用示例中所做的那样，而不是依赖于“旧”对象构造函数，因为它们似乎只是出于兼容性目的而不完整。
The reason a view would require a higher billing tier versus a table, by the way, is because a view is only a logical table and the queries which define the view must be re-executed when the view itself is queried.
顺便说一下，视图需要比表更高的计费层的原因是因为视图只是一个逻辑表，并且在查询视图本身时必须重新执行定义视图的查询。
#1
0
It looks like the 'configuration.query.maximumBillingTier' property isn't getting set when you are inserting the job. The method of using 'JobConfigurationQuery' and other classes seems to have been abandoned as there is no mention of them in the current docs, and I needed to resort to using the Wayback Machine to find them.
在插入作业时，看起来没有设置'configuration.query.maximumBillingTier'属性。使用'JobConfigurationQuery'和其他类的方法似乎已被放弃，因为在当前的文档中没有提及它们，我需要使用Wayback Machine来查找它们。
The most recently available document from 11/12/2013 only defines getters and setters for a few configuration properties, and 'maximumBillingTier' isn't one of them.
2013年12月11日的最新文档仅定义了一些配置属性的getter和setter，而'maximumBillingTier'不是其中之一。
I'd suggest manually setting the request properties as is done in the usage examples from the current documentation, rather than relying on the "old" object constructors, as they seem to only be left around for compatibility purposes and are incomplete.
我建议手动设置请求属性，就像在当前文档的使用示例中所做的那样，而不是依赖于“旧”对象构造函数，因为它们似乎只是出于兼容性目的而不完整。
The reason a view would require a higher billing tier versus a table, by the way, is because a view is only a logical table and the queries which define the view must be re-executed when the view itself is queried.
顺便说一下，视图需要比表更高的计费层的原因是因为视图只是一个逻辑表，并且在查询视图本身时必须重新执行定义视图的查询。