community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Google Big Query Tools

Alteryx
Alteryx
Created on

 

Google Big Query ToolsBQ.png

 

You can download the Big Query Connector Tools here

 

There are two ways to log into the Google BigQuery Connector.

  1. Service to Service:
    1. Service-to-service authentication requires a Google Service Account File, also known as a service account JSON key file. You can obtain a service account JSON key file from the Google Cloud Console or you can create a new key for an existing service account. More information about Google BigQuery can be found on the Google Big Query Documentation site under Creating and Managing Service Account Keys.
  2. End User:
    1. In Authentication mode, select End user.
    2. Enter your Client ID and Client Secret.
    3. Click Log in.
    4. In Sign in with Google, enter your Google email and password then click Allow to approve the connector's permission to access Google BigQuery on your behalf.
    5. Click Next.

After connecting, select the table you want to read from. You can also refresh the metadata by clicking on the refresh icon.

  

The next thing to look at is the 5 options:

  1. Limit the number of results returned
    • This is similar to the TOP or LIMIT clause used to limit how many records are returned
  2. Use a custom query
    • Offers more flexibility in selecting input data from Big Query, however using a custom query disables the ability to “infer max repeated record limit” and to “limit the number of results returned”
    • User can use either Standard or Legacy SQL dialect
    • User must also select a project to query
    • Only Repeated record limit and max field limit are allowed
  3. Infer max repeated record limit
    • This will bring in the nested and repeated fieldsPic1.pngInside BigQuery Table

      Pic2.pngReturned nested rows inside Alteryx

  1. Repeated record limit
    • Use this if you do not want the max repeated record limit
  2. Max field length
    • Use this to select the data size coming in

Google Big Query Output Tool

  1. Similarly to the Google Big Query Input tool, there are two ways to log in – Service to Service and End User. Please look above to see which authentication mode is right for you.
  2. Select the table you want to write you. You need an existing table to write to.
  3. Insert Batch Size
    • This determines the number of rows inserted into the BigQuery per API request.

Basic Troubleshooting Steps

Permissions Error Messages

  • 403 Invalid Permissions
    • Users will see 403 Invalid Permissions errors when attempting to write to a table for which they do not have update permissions
  • No Projects Available. Check your credentials and permissions.
    • This error is displayed when a user authenticates to Google but has no permissions for any BigQuery project

Limitations

Known Limitations for the Google Big Query Tools can be found here.

 

Outputting data from your designer workflow to Google BigQuery streams new rows to the table in BigQuery. Data will be held in a temporary streaming buffer for up to 90 minutes while processes in Google BigQuery convert the row-based data to columnar-based storage. In most cases, the data in the streaming buffer is still available for querying within seconds. However, you will not be able to see the inserted data in the Preview tab of the Google BigQuery console for the table until the extraction processes are complete. You can learn more about the streaming buffer in this Google Blog Post. More information about the availability of streamed data can be found on the Google BigQuery Documentation site.

Comments
Atom

Hi DiganP,

I can't seem to download the bigquery tool from the link: https://gallery.alteryx.com/#!app/Google-BigQuery-Tools/5b1f092a8a93372a64c28c09

 

I get an error message "Problem Loading App". Is there something I am missing?

Thanks,

Marnie

Alteryx_BigQuery.JPG

Alteryx
Alteryx

@marniedc Can you try deleting your cache from your browser? Did you also try multiple browsers (IE, Chrome)? It seems to work fine for me. 

Meteor

Hey!

 

@DiganP I just updated to the new tool version (1.0.3) and I get the following error:

 

Error: Google BigQuery Input (1): Traceback (most recent call last):
File "C:\Users\...\AppData\Roaming\Alteryx\Engine\../Tools\big_query_input_v1.0.3\big_query_input\engine.py", line 177, in pi_init
self.alteryx_engine.create_connect_metadata(self.n_tool_id, metadata_dict)
AttributeError: 'AlteryxEngine' object has no attribute 'create_connect_metadata'

 

I didn't have any problems with connecting with the old version of the tool but now I have no clue what happens..... Smiley Sad

Meteor

I'm now also having problems with tables that have 'numeric' fields... (using version 1.0.0, since I am still not able to work with the newest tool version) I have to use custom sql and cast the numeric fields as strings. An clue if there is going to be an update to include this?

Alteryx
Alteryx

@FabianS When you install the tool, please run Designer as admin (right click > run as admin) and then install the tool. Try this in the newer version and let me know of the results with 'numeric' fields. 

Meteor

@DiganP I upgraded to Alteryx Designer 2018.3 and everything is working fine with the 1.0.3 version of the tool (so to me it seems that it is incompatible with 2018.2).

Now the new tool is working fine, but when I try to select a numeric field it gives me "Google BigQuery Input (1) There was an unexpected error." If I do cast(_numeric field_ as string) then everything works perfect, except that only custom queries work....

Is this something that is not yet included??, because I understood that the numeric field is quite a new feature in Bigquery.

Alteryx Partner

I'm facing errors using the GBQ Output tool.  Is there a direct contact I can use to help ?

 

Basically I don't know what's wrong (first time using this tool), so I'm not sure if the problem is the field order, incompatible data types, etc..

Alteryx Partner

Maybe a simple problem but when Alteryx tries to write an Int16 field to a GBQ integer field it fails.  If you change the Alteryx field to Int64 it works perfectly.

Meteor

@DiganP I'm having a similar error to @FabianS. Were you able to identify the issue, I'm not sure what the error means or how to fix it:

 

Error: Google BigQuery Output (2): Traceback (most recent call last):
File "C:\Users\...\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\big_query_output\connections.py", line 85, in ii_push_record
self.parent.display_error_msg(errors)
File "C:\Users\...\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\connectors_core\alteryx\interfaces.py", line 150, in display_error_msg
self.alteryx_engine.output_message(self.n_tool_id, Sdk.EngineMessageType.error, msg_string)
Boost.Python.ArgumentError: Python argument types in
AlteryxEngine.output_message(AlteryxEngine, int, EngineMessageType, list)
did not match C++ signature:
output_message(class SRC:Smiley Tongueython::AlteryxEngine {lvalue}, int tool_id, int status, char const * __ptr64 message)

 

Thanks!

 

 

Alteryx Partner

Did you check that the datatypes in Alteryx match the datatypes in the GBQ table ?

 

What datatypes are you using in GBQ ? (some can be tricky to align in Alteryx)