通过应用程序脚本从Google BigQuery获取数据 [英] Get data from Google BigQuery by app script

问题描述

我对Google BigQuery进行了查询，并且它在控制台中运行，但是当我在Google应用程序脚本中尝试使用相同的代码时，代码失败：在控制台运行中，

正常：

  SELECT title FROM [bigquery-public-data：samples.wikipedia]其中title包含'olimpic'LIMIT 100 
  

在应用程序脚本中运行时出现错误：

遇到 - - 第1行第27列。期望：EOF

  function runQuery（）{
 var projectId ='cool-reality-177704'; 
 var request = {
 query：'SELECT title FROM bigquery-public-data：samples.wikipedia where title containsolimpicLIMIT 100'
}; 
 var queryResults = BigQuery.Jobs.query（request，projectId）; 
 var jobId = queryResults.jobReference.jobId; 
 Logger.log（queryResults）
} 
  

我做错了什么？

解决方案

您错过了项目的方括号：dataset.table 名称。它应该是：

'SELECT title FROM [bigquery-public-data：samples.wikipedia]其中title包含olimpicLIMIT 100' / code>

注意：尽量避免使用旧版SQL。我会这样做（使用标准的SQL）：

  var configuration = {
query：{
useQueryCache：false，
useLegacySql：false，
query：'SELECT title FROM`bigquery-public-data.samples.wikipedia'其中标题如％olimpic％ LIMIT 100'
}}; 
  

I make query to Google BigQuery, and it works in console, but fail when I try the same in google app script here is the code:

in console runs normal:

SELECT title FROM [bigquery-public-data:samples.wikipedia] where title contains 'olimpic' LIMIT 100

in app script it runs with error:

Encountered " "-" "- "" at line 1, column 27. Was expecting: EOF

function runQuery() {
  var projectId = 'cool-reality-177704';
  var request = {
    query: 'SELECT title FROM bigquery-public-data:samples.wikipedia where title contains "olimpic" LIMIT 100'
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;
  Logger.log(queryResults)
}

What I do wrong?

解决方案

You're missing the square brackets around the project:dataset.table name. It should be:

'SELECT title FROM [bigquery-public-data:samples.wikipedia] where title contains "olimpic" LIMIT 100'

Note: try to avoid using legacy SQL. I'd do this instead (use standard SQL):

var configuration = {
    "query": {
    "useQueryCache": false,
    "useLegacySql": false,
    "query": 'SELECT title FROM `bigquery-public-data.samples.wikipedia` where title like "%olimpic%" LIMIT 100'
    }   };

这篇关于通过应用程序脚本从Google BigQuery获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！