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?
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.
useLegacySql : false instead of useLegacySql = false but many thanks it saved me a lot of timesVery Minor correction in the accepted answer. (Semicolon needed, not equals symbol)
It should be
useLegacySql : false
And not
useLegacySql = false
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
Make sure that your first line of your query is
#StandardSQL
select 1;
