How to set up Google BigQuery access with Direct Discovery

mountaindude — Wed, 05 Feb 2014 14:20:35 GMT

After struggling a fair amount to get this working, I figured it might be helpful for others trying to do the same thing.

Background

Using QV desktop 11.20 SR5.
The Google BigQuery connector available on the QV Marketplace is installed.
The BigQuery extension also installed. It is however not used in the context of this how-to, so strictly speaking it is not really needed.

Goal

To be able to query Google Analytics data (or other data sets available to BigQuery) from QV using Google BigQuery.

Problem

After reading through the BigQuery export page in general, and the page on how to access the sample data set in particular, I was left with an empty BigQuery project called "API Project", and two Google defined projects called "google.com:analytics-bigquery" and "public data:samples".

The BigQuery dashboard (or whatever it is called..) is available here.

The other important page is the Google Developer Console. For the newly created project ("API Project" in my case), you need to enable the BigQuery API. Go into the project, select "APIs & auth", then "APIs". Enable BigQuery API.

Next, create login credentials on the "Credentials" tab (right under the "APIs" tab used above).

Click the red "Create new client ID" button, select "Installed application" as application type, and "Other" for installed application type. Click the blue "Create Client ID" button. You will now get a new client ID/client secret pair, which is needed later on when connecting to BigQuery from QV.

So in theory this should be all there is to it... So let's try creating a new QV application and using the BigQueryConnector.exe connector from the load script, then entering the ID/secret pair above, getting a response token back from Google (via a browser window opened by the connector, paste that token into the connector dialog, click ok.

Fine - the "API Project" shows up in the list of available projects. But when clicking on it I get an unhanded expression:

System.NullReferenceException: Object reference not set to an instance of an object.

at BigQueryConnector.Authenticate.lstProjects_SelectedIndexChanged(Object sender, EventArgs e)

at System.Windows.Forms.ListBox.OnSelectedIndexChanged(EventArgs e)

etc....

Ouch....

Solution

The solution turns out to be pretty simple.

The BigQuery connector does not gracefully handle empty BigQuery projects. Probably a trivial fix for the developers of the connection - but as of right now it is a bug.

An obvious solution would be to copy the data from the google.com:analytics-bigquery project into our own "API project". But however many times I tried this, it didn't work. Turns out reading the documentation helps... You need to enable billing for the BigQuery project in order to be able to copy data into it. After doing that the google.com:analytics-bigquery data (it's only ca 70 kbyte, so it's quite suitable for test purposes) can be copied into your own project. Also, Google only start charging you once you hit a certain query or storage volume, so these small first tests should not cost anything (but I of course take no liability for this - whether or not to enable billing is your decision).

And voila - when doing the same exercise again in QV, the BigQuery works flawlessly, and we can run a query like this:

CUSTOM CONNECT TO "Provider=BigQueryConnector.exe;clientId=<my-client-id>;clientSecret=<my-secret>;refreshToken=1/........;";

SQL

SELECT SUM(totals.pageviews) as TotalPageviews FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910];

DISCONNECT;

(My own authentication credentials are removed from the connect line above)

The result is simply 249 - which is the same result you get when executing that query directly in Google's BigQuery site.

Mission accomplished - next steps will be to link the QV internal data model with suitable result sets from BigQuery.

topic How to set up Google BigQuery access with Direct Discovery in QlikView

How to set up Google BigQuery access with Direct Discovery