Does anyone know if there any way to get the BigQuery connector to work with Standard SQL views rather than the older Legacy SQL?
Apologies this is in the Web connector section, its the closest place I could find.
Previously, BigQuery executed queries using a non-standard SQL dialect
known as BigQuery SQL <https://cloud.google.com/bigquery/query-reference>.
With the launch of BigQuery 2.0, BigQuery released support for standard SQL
<https://cloud.google.com/bigquery/sql-reference/index>, and renamed
BigQuery SQL to legacy SQL. Standard SQL is the preferred SQL dialect for
querying data stored in BigQuery.
Supply Chain Data Manager
Group Supply Chain - Management Information Services
Travis Perkins plc
M: (44) 7730 815560
Follow us on the Group LinkedIn page:www.linkedin.com/company/travis-perkins
Lodge Way House,Lodge Way, Harlestone Road, Northampton, NN5 7UG
Thanks for clarifying with the extra notes.
With the connector you define the SQL, which is interpreted by Google BigQuery, so I believe that if you state #standardSQL at the start of your query, you should be able to use Standard SQL in your statements.
There's more information on this here:
Let me know how you get on or if I can help any further.
Just to be clear, all my comments relate to the Qlik Web Connector's Google BigQuery connector, not the Qlik BigQuery connector - appreciate that this is confusing
Sorry but my licence for the Web connector BigQuery connector has expired and I am arranging for a new one. In the meantime, could you confirm where I should be entering this, in my load script I am simply stating this ...
LOAD * FROM [lib://D_Items]
so there is no opportunity to include the #standardSQ.
And my connection string contains ....
Are you simply suggesting I need to insert #standardSQ before the select, but then unsure how I then follow that with the select, e.g.
connectorID=GoogleBigQueryConnector&table=SynchronousQuery&projectId=myprojectname&query= #standardSQ SELECT+*+FROM+%5bmyprojectname%3aMIS_SC_Qlik.D_Items%5d&appID=
The problem is with standard SQL the way the project and dataset is named is different as well as the actual query
Will re-test again as soon as I can.
As for Qlik View I cannot find any way to connect.
Sorry for the late reply.
In the Qlik Google BigQuery Connector, you can enter it in the Query parameter of the SynchronosQuery table - there's an icon to the top right of this field input, which allows you to enter multi-line text, so you can just add #standardSQL at the start of your statement.
Well a regular newline is not enough
So you will have to write:
Notice the \n that tells BigQuery that a newline should come after the standardSQL directive.
I get errormessage if in 2.1 of Qlik Web Connector unless I use the \n
Actually I discovered that the \n is not necessary if you are careful to not replace a regular Chr(10) or Chr(13) into the % encoded values %0a or %0d. They will not be considered as an end of the single line comment (a directive in this context).
So the string with the SQL that needs to be sent to BigQuery has to contain either:
- the actual ASCII value 10 which is Chr(10)
- the actual ASCII value 13 which is Chr(13)
But avoid changing these to %0a or %0d