on 07-13-2018 01:16 PM - edited on 07-15-2021 11:23 AM by csalgado5
Google BigQuery Tools
You can download the BigQuery Connector Tools here.
There are two ways to log into the Google BigQuery Connector.
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:
Google Big Query Output Tool
Basic Troubleshooting Steps
Permissions Error Messages
Limitations
Known Limitations for the Google BigQuery 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 thisGoogle Blog Post. More information about the availability of streamed data can be found on theGoogle BigQuery Documentationsite.
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
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..... :(
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?
@FabianS_dup_107 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.
@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.
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..
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.
@DiganP I'm having a similar error to @FabianS_dup_107. 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::Python::AlteryxEngine {lvalue}, int tool_id, int status, char const * __ptr64 message)
Thanks!
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)
Hi
Running Alteryx as ADMIN fixed my issues.
I am running into an error when I try to install the connector. Any help you could provide would be greatly appreciated. Thanks.
Hi @PatrickSullivan,
Could you please try workarounds of page https://community.alteryx.com/t5/Alteryx-Knowledge-Base/YXI-installation-Fix-quot-Exception-quot-err...
Best,
PaulN
Hey,
Really hoping someone can help with the below error (I'm fairly new to Alteryx).
The below error code generates when I try to go from Alteryx to Google BigQuery Output (using this connector). I've set up the new table scheme in BigQuery the same as Alteryx, and tried to match up the data types as close as possible, but the below error code doesn't give me much to work on.
Error: Google BigQuery Output (2): Traceback (most recent call last):
File "\\mainline2016dc\Redirect$\jason.abbott\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\big_query_output\connections.py", line 74, in ii_push_record
row = self.schema_writer.build_row_from_record(in_record, self.parent.alteryx_engine)
File "\\mainline2016dc\Redirect$\jason.abbott\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\connectors_core\bigquery\schemas.py", line 151, in build_row_from_record
field_val = alteryx_helpers.get_value_for_field(field, record_ref)
File "\\mainline2016dc\Redirect$\jason.abbott\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\connectors_core\alteryx\helpers.py", line 39, in get_value_for_field
return accessor(record_ref)
File "\\mainline2016dc\Redirect$\jason.abbott\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\connectors_core\alteryx\helpers.py", line 32, in <lambda>
Sdk.FieldType.date: lambda ref: datetime.datetime.strptime(field.get_as_string(ref), DATE_FORMAT),
TypeError: strptime() argument 1 must be str, not None
Hoping this is a simple one and just me being new!
Thanks for any help
Hi Jason ,
I had the same issue at the start, I will try an narrow down how I got it working by asking the below:
which version Alteryx are you running?
Are all your python libraries up too date?
-Inside the big query connector zip file, there is a requirements text file. Do you have all those installed?
Have you added the path of Python to your environment?
Which version python are you using?
Regrads
Hey,
Apologies I'm definitely a noob in this regard.
Alteryx Version - 2019.2.5.62427
In terms of installation, it was a case of just running the base file that downloaded from the Alteryx Gallery, nothing specific selected when installing so it should have installed all included (took around 20 minutes).
How would I go about finding the python related information? As in terms of this, I haven't wrote any python myself when setting this up.
Is potentially my issue how I am trying to link the two? At present, it goes from a Calgary Input (loading my dataset into a Calgary Loader to boost the speed) to the Google BigQuery Output - is it potentially that I need something inbetween the two?
Thanks
Hi all,
Good news - I've managed to get the upload link working...
Bad news - the data duplicates for each time I refresh the workflow (e.g. my end workflow of data is just over 400k, I've ran the workflow twice today - around an hour each time - and now Big Query is saying over 800k records).
How is this fixed? Obviously just reuploading 400k records a time is not efficient, nor is it when it then just adds them all on the existing table, rather than just adding new rows.
Help - there seems to be no configuration around this.
Jason
Thanks for developing the connector. Is there a way to specify whether I can 'append' instead of 'replace' the data exported to bigquery?
Folks,
I am trying to do a query via BigQuery connector and this is the error message I get....
Error: Google BigQuery Input (1): Traceback (most recent call last):
File "main.py", line 4, in <module>
File "C:\Users\MainUser\AppData\Roaming\Alteryx\Engine\../Tools\big_query_input_v1.0.3\big_query_input\__init__.py", line 1, in
from .engine import BigQueryInputEngine
File "C:\Users\MainUser\AppData\Roaming\Alteryx\Engine\../Tools\big_query_input_v1.0.3\big_query_input\engine.py", line 5, in
from connectors_core.bigquery import BigQuerySchemaReader
File "C:\Users\MainUser\AppData\Roaming\Alteryx\Engine\../Tools\big_query_input_v1.0.3\connectors_core\bigquery\__init__.py", line 1, in
from .schemas import BigQuerySchemaReader
File "C:\Users\MainUser\AppData\Roaming\Alteryx\Engine\../Tools\big_query_input_v1.0.3\connectors_core\bigquery\schemas.py", line 4, in
import google.cloud.bigquery
ModuleNotFoundError: No module named 'google'
Any idea what is wrong?
-cliff
This is the first time I am connecting to Google BigQuery Input through Alteryx. I used service to service authentication mode and was able to establish the connection and see list available table. When I select any table or use a custom query, I am keep getting "There was an unexpected error". I do not see any additional details around that error. I am using version 2019.4.
Am I missing something? Why am I keep getting this error? Is there a different approach to use BigQuery tables in Alteryx.
HI
Also hitting the same error:
Error: Google BigQuery Output (252): Traceback (most recent call last):
File "C:\Users\Gavin\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\big_query_output\connections.py", line 74, in ii_push_record
row = self.schema_writer.build_row_from_record(in_record, self.parent.alteryx_engine)
File "C:\Users\Gavin\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\connectors_core\bigquery\schemas.py", line 151, in build_row_from_record
field_val = alteryx_helpers.get_value_for_field(field, record_ref)
File "C:\Users\Gavin\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\connectors_core\alteryx\helpers.py", line 39, in get_value_for_field
return accessor(record_ref)
File "C:\Users\Gavin\AppData\Roaming\Alteryx\Engine\../Tools\big_query_output_v1.0.3\connectors_core\alteryx\helpers.py", line 32, in <lambda>
Sdk.FieldType.date: lambda ref: datetime.datetime.strptime(field.get_as_string(ref), DATE_FORMAT),
TypeError: strptime() argument 1 must be str, not None
Diagnosed this to the Big Query Output tool not liking Date fields. Change these to string and it solves the issue.
Also, in case you were not aware, the table has to be pre-defined, including the schema, which you can do via console.
Update:
Ok, so after getting over my initial moan, and a bit of reading later, i have some further insight.
Part of the limitation is that the tool is using the stream data option using the insertALL call, suitable if you where streaming event data, but not quite sure this is the best route for a connector into bigquery.
Interestingly (and i need to delve into this a bit more...) it appears that the only other option is drop json files or other into some location then call a job to load these in.
more reading required, but i'll drop a post on my findings and keep updated as i go along.
humpf... shame, but such is life...
kr
Gavin
Hi guys,
I believe the Big Query connector is going through an update/fix at the moment. It is currently in Beta at the moment. You can sign up for beta and try the connector here!
@Ashwini_Bende The situation and error you are specifically describing sometimes occurs in proxy server environments (PAC or manual). We do have a fix for this, please do not hesitate to reach out to Alteryx support with questions. You may even mention this post for assistance, we are always happy to help. And @DiganP is correct! We will be releasing a new version of the BigQuery tool later this month that includes several important fixes and enhancements.
Thanks and have a great week,
Sabrina
Sr Customer Support Engineer - Connectors SME
I'm hoping someone can help with an issue we are having using the BigQuery connector. We are able to access BigQuery with the tool, but get the:
"Error: Google BigQuery Input (1): Schema exception: Field customDimensions has repeated record data type, which is not supported in Alteryx"
I know there have been some threads on flattening the records with a custom query, but I thought I also read on a link in a post on this site that version 2.0 of the tool solves for that? Any help you can render is appreciated as this has us stalled.
Thank you,
Here is a link to the other thread regarding a solution: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enable-complex-queries-for-Google-Big-Query/...
Thank you,
team
i followed steps given in following link
https://gallery.alteryx.com/#!app/Google-BigQuery-Tools/5b1f092a8a93372a64c28c09
downloaded Google BigQuery Tools v2.0.1 (June 9, 2020) to my machine
During installation getting following message for big_query_input and big_query_output
"no matching distribution found for azure-common==1.1.14(from -r requirements.txt[line1])
Alteryx designer version : 2019.4.8.22007
any help will be much appreciated
Hi
Do you have it installed?
In Alteryx use the Python connector and type
!pip list
Then hit run
That will show you a list of python libraries isntalled.
If its not there type
!pip install Azure-common
Then Run
Good Luck 😀
I need to use Google BigQuery Input. I logged in by my service account successfully but I found an error "You have found a bug . Replicate, then let us know. We shall fix it soon." before and after run a workflow. I don't know why because it doesn't tell me any reason.
Alteryx Designer Version: 2020.4.5.12471
Google BigQuery Tools v2.0.1
Hi @armakharin We are sorry you are running into that. Could you please try re-installing the Connector with fresh install? (remove existing tool folder and then reinstall BigQuery Connector YXI, should be exact version number/location so workflows are not affected) It would probably be best if you could contact Alteryx Support and we would be happy to assist.
Thanks,
Sabrina
Hi,
I would like to use this tool to retrieve data from BigQuery located in a location other than the US or EU.
According to the API specifications, it is mandatory to specify when using other locations. Is there a way to set it from the tool?
.
@ozawa , @SabrinaCB , @armakharin , @M_AUS , @DiganP
OTHER SOLUTION
I installed the official obdc driver Driver OBDC from goolge and for me it worked very well on alteryx just by pointing the connect tool in the database to the driver, the only problem I had was to write a new table inside Bigquery, I believe that if a template is created for bigquery where the SQL command "create" does not determine the type of data Alteryx can solve this problem too