Enable complex queries for Google Big Query

[_hans1]

Google Big Query can contain so-called complex queries, this means that a table "cell" can host an array. These arrays are currently not supported in the SIMBA ODBC driver for Google Big Query (GBQ), ie. you can't download the full table with arrays.

All Google Analytics Premium exports to GBQ contain these arrays, so as an example, please have a look at the dataset provided via this link: 

https://support.google.com/analytics/answer/3416091?hl=en

The way to be able to query these tables is via a specific GBQ SQL query, for instance:

Select * From flatten(flatten(flatten(testD113.ga_sessions,customDimensions),hits.customVariables),hits.customDimensions).

This is not currently supported from within the Input tool with the Simba ODBC driver for GBQ, which I would like to suggest as a product idea.

Thanks,

Hans

[_hans1]

With more working experience in Google Big Query (GBQ) I wish I could rename the title into "Enable queries for Google Big Query".

It is just impossible to send (simple) queries directly to GBQ via the Alteryx ODBC driver. This means that you always need to create some kind of staging table in GBQ if you would like to work with GBQ data in Alteryx. In plain Dutch I would say that the ODBC driver provided by Simba is just plain useless .

So I hope the current GBQ driver will be replaced soon :-)

Thanks, 

Hans

[PEliason]

I'm having trouble with the table_date_range() function in BigQuery, as well. Probably makes sense to lump this function request in with the above.

[DevinHarrington]

It seems there is not an ability to run queries as "pass thru" in Alteryx.  The Simba driver works fine in other tools for submitting a SQL and paging thru the BQ data to return it, but we seem to currently be limited to having to "disable native query" as suggested here:

https://help.alteryx.com/10.6/index.htm#DataSources/GoogleBQ.htm?Highlight=odbc error no columns returned

[Pete]

 Adding my voice to the above.

Until Alteryx can allow us to read the BigQuery tables exported by Google Analytics Premium, then we don't have much use for it.

[RobWalker]

Experienced the same issues using the SIMBA driver and also tried the Grazitti Big Query connector.  Both had the same issue with the record structure of the GA session tables and were not able to read them.

As a workaround we were originally using Python scripts to create a flattened user defined table, but an easier method we found was to create a view in Big Query and have the SIMBA driver access that instead.  The view flattened the structure and as long as you name the columns e.g. hits.sourcePropertyInfo.sourcePropertyTrackingId as Source_Property_Tracking_Id it appears to work fine, although performance could be improved.

[monica_ams]

Hi,

I am wondering if someone could finally unflatten custom dimensions from GA (from BigQuery) through simba connector.

Thanks,

[_hans1]

Hi Monica, a possible solution could be using the CLI of GBQ (https://cloud.google.com/bigquery/quickstart-command-line). This can also be used in Alteryx using the run command tool (https://help.alteryx.com/11.5/RunCommand.htm).

It is a bit more complex than a OOTB solution but as long as there is no alternative...

Hope this helps!

Kind regards,

Hans

[ARich]

Hi All,

Thanks for the feedback- please keep starring this idea if you're interested!

Best,

Alex

[Jerod]

The ODBC Driver for Google BigQuery by CData Software (my employer) fully supports query-passthrough, regardless of Alteryx's support. Simply set the QueryPassThrough property to "TRUE" (and for this specific query set UseLegacySQL to "TRUE") when you configure the DSN.

Configuring the DSN

From there, I connected an Input Data tool to the Configured DSN and set the query to the one described above

SELECT * FROM flatten(flatten(flatten([ga_sessions_20130910],customDimensions),hits.customVariables),hits.customDimensions)

and pushed the results to an Output Data tool (CSV).

Simple flow in Alteryx Designer

Just to ensure accuracy, I compared the CSV output by Alteryx to the exported results direct from Google BigQuery. The only discrepancy I noticed was the way that "false" was outputted ("False" vs "false"). For a more in-depth walkthrough of using the ODBC driver in Alteryx, refer to our article: https://www.cdata.com/kb/tech/bigquery-odbc-alteryx.rst

[ARich]

Hi All,

First, we are planning on rewriting our connectors for Google BQ and hope to release those in Q2. This will include write functionality and should resolve most of the issues mentioned above.

Second, in the meantime, I do have the latest BQ drivers available to beta. The latest drivers might resolve several of the issues mentioned above. We haven't fully tested, so we're not publicly distributing yet, but if you're a current customer, please DM me and I'll get the latest drivers to you.

Best,

Alex