topic Re: Qlik View BigQuery connector and Standard SQL in Connectivity & Data Prep

Qlik View BigQuery connector and Standard SQL

Fri, 02 Apr 2021 07:52:36 GMT

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

Re: Qlik View BigQuery connector and Standard SQL

Darren_Ball — Tue, 27 Dec 2016 09:23:14 GMT

Hi,

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

Re: Qlik View BigQuery connector and Standard SQL

Tue, 03 Jan 2017 09:15:25 GMT

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

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

Re: Qlik View BigQuery connector and Standard SQL

Darren_Ball — Mon, 09 Jan 2017 08:46:50 GMT

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.

Re: Qlik View BigQuery connector and Standard SQL

Darren_Ball — Mon, 09 Jan 2017 09:17:35 GMT

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

Re: Qlik View BigQuery connector and Standard SQL

Mon, 09 Jan 2017 14:26:17 GMT

Thanks I will give this a go

Re: Qlik View BigQuery connector and Standard SQL

Wed, 25 Jan 2017 09:22:35 GMT

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.

Re: Qlik View BigQuery connector and Standard SQL

Darren_Ball — Mon, 13 Feb 2017 09:18:45 GMT

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.

Re: Qlik View BigQuery connector and Standard SQL

petter — Mon, 13 Feb 2017 14:46:57 GMT

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

Re: Qlik View BigQuery connector and Standard SQL

petter — Wed, 15 Feb 2017 13:17:42 GMT

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