Qlik View BigQuery connector and Standard SQL

[#1]

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.

Steve

[Darren_Ball]

Hi,

Could you please explain what you mean about legacy v's standard SQL views?

[#3]

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.

https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql

Cheers,

Steve

Steve Hill

Supply Chain Data Manager

Group Supply Chain - Management Information Services

Travis Perkins plc

M: (44) 7730 815560

www.travisperkinsplc.co.uk

<http://www.travisperkinsplc.co.uk/>

Follow us on the Group LinkedIn page:www.linkedin.com/company/travis-perkins

Lodge Way House,Lodge Way, Harlestone Road, Northampton, NN5 7UG

[Darren_Ball]

Hi,

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:

https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql

Let me know how you get on or if I can help any further.

[Darren_Ball]

Hi again,

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

[#6]

Thanks I will give this a go

[#7]

Hi

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 ...

D_Items:

LOAD * FROM [lib://D_Items]

(qvx);

so there is no opportunity to include the #standardSQ.

And my connection string contains ....

connectorID=GoogleBigQueryConnector&table=SynchronousQuery&projectId=myprojectname&query=SELECT+*+FROM+%5bmyprojectname%3aMIS_SC_Qlik.D_Items%5d&appID=

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.

[Darren_Ball]

Hi,

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.

[petter]

Well a regular newline is not enough

So you will have to write:

#standardSQL\n

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

[petter]

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)

- \n

- \r

But avoid changing these to %0a or %0d