Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Enable complex queries for Google Big Query

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

 

 

15 Comments
_hans1
7 - Meteor

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

 

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

 

Thanks, 

 

Hans

 

PEliason
6 - Meteoroid

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
5 - Atom

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
5 - Atom

 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
6 - Meteoroid

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
5 - Atom

Hi,

 

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

 

Thanks,

_hans1
7 - Meteor

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
Alteryx Alumni (Retired)
Status changed to: Under Review

Hi All,

 

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

 

Best,

Alex

Jerod
7 - Meteor

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 DSNConfiguring 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 DesignerSimple 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
Alteryx Alumni (Retired)

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