CODEWITHSUNDEEP
google-bigquery
goose
goose

Reputation: 2652

How to call Google Big Query non-legacy SQL using Google App Script?

I want to use Google App Scripts to trigger a Google Big Query query as per the docs here:

https://developers.google.com/apps-script/advanced/bigquery#reference

Since my query uses the new DML functionality it needs to use non-legacy SQL.

Is this possible using Google App Script? How do I specify non-legacy SQL?

Upvotes: 2

Views: 1443

Answers (4)

user9638534
user9638534

Reputation:

Very Minor correction in the accepted answer. (Semicolon needed, not equals symbol)

It should be

useLegacySql : false

And not

useLegacySql = false

Upvotes: 3

Elliott Brossard
Elliott Brossard

Reputation: 33745

I don't believe that you need to use #StandardSQL in the query text. Using the sample code on https://developers.google.com/apps-script/advanced/bigquery#reference as a reference, it should be sufficient to change:

var request = {
  query: 'SELECT TOP(word, 300) AS word, COUNT(*) AS word_count ' +
    'FROM publicdata:samples.shakespeare WHERE LENGTH(word) > 10;'
};

to:

var request = {
  query: 'SELECT APPROX_TOP_COUNT(word, 300) AS word, COUNT(*) AS word_count ' +
    'FROM `publicdata.samples.shakespeare` WHERE LENGTH(word) > 10;',
  useLegacySql = false
};

Specifically, you need to add the useLegacySql key to the request.

Upvotes: 3

Pentium10
Pentium10

Reputation: 207912

Make sure that your first line of your query is 

#StandardSQL
select 1;

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

It is possible to enforce Standard SQL without code change. Just make sure that the first line of your query is

#StandardSQL

and BigQuery will treat the rest of the query as Standard SQL

Upvotes: 2

Related Questions