Google BigQuery

Last modified: July 13, 2020
Connection Type

Alteryx Tool

Driver Details

Administrator action in Google Cloud Platform may be necessary before configuring a connection in the Alteryx Google BigQuery tools.

Type of Support

Read & Write

Validated On


Release Notes
Version Description
  • Requires Alteryx version 2019.3 or higher.
  • Issue resolved where Input tool would fail when run on gallery without Pre and Post SQL configuration.
  • Minor UI text changes.
  • Requires Alteryx version 2019.3 or higher.
  • Support for pre/post SQL queries is now available.
  • Tool now supports Append Existing and Overwrite table functionality.
  • Tool allows multi-level nested schema.
  • Support for repeated data types (i.e. BigQuery arrays) is now available.
  • Tool supports reading and writing None values.
  • Tool now shows more than 50 tables per schema.
  • Tool no longer fails when using Decimal\Numeric data types.
  • Fixed bug where connector connector would crash if a BigQuery dataset did not have any tables.
  • Fix issue where default value settings were occasionally not respected.
  • Disabled production logging to prevent permissions issues for different installations/configurations of Designer and support scheduled workflow functionality.
  • Fixed issue preventing packages from being installed successfully.
  • Initial release - Google BigQuery Input and Google BigQuery Output.


Alteryx Tools Used to Connect

Google BigQuery Input Tool Icon

Google BigQuery Input Tool

Google BigQuery Output Tool Icon

Google BigQuery Output Tool

Authentication and Permissions

Contact your Google Cloud Administrator for help with your Google BigQuery credentials.

Service-to-Service Authentication Setup

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.

End User Authentication Setup

Obtain an OAuth client ID and secret from the credentials page of the Google Cloud Console. Ensure the client type is set to Other.

Use Service-to-service authentication over End user authentication whenever possible. Managing distinct keys for various applications authenticating for the same service account allows the revocation of access for a specific application without requiring the other applications to update their authentication credentials.

Access Permissions

Permissions to read and write data within a Google BigQuery project service account must be granted via the Google Cloud Console. More information about permissions can be found on the Google Big Query Documentation site.

Known Limitations

FLOAT BigQuery Fields

FLOAT type fields in a BigQuery table are automatically promoted to double types in the Alteryx engine. Therefore, you might see extra decimals in values input from and then output back to Google BigQuery.

Max Nested, Repeated Record Depth

Nested, repeated records are supported at a depth of one level. Nexted repated records that are nested more than one level deep are not supported. More information about nested, repeated records, can be found on the Google BigQuery Documentation site.

Nested, Repeated Record Flattening

On Input

When pulling nested or repeated records from a Google BigQuery table, the Alteryx workflow flattens the nested and repeated records according to this naming scheme:

A nested record nested_attr of the top-level column top_attr creates a new column named nr_top_attr_nexted_attr.

On Output

When pushing records from the Alteryx workflow to an existing Google BigQuery table, the need to fold record fields into nested and repeated records is specified by renaming record fields flowing into the ouput connector to follow these naming conventions.

A record nested_attr that should be nested onto the top-level column top_attr must be renamed to nr_top_attr_nested_attr.

A record nested_attr that should be nested and repeated an arbitrary number of times under the top-level column top_attr must be renamed to nrr_top_attr_{repetition_index}_nested_attr. Any repetitions that contain all NULL values on the nested fields are dropped before the API call to insert new rows to the Google BigQuery table This implies that records in your designer workflow can have an arbitrary number of maximum repetitions, and not every record must have the same number of repetitions.

Output Connector Schemas

Field names and types passed to the Google BigQuery Output tool must match those in the destination Google BigQuery table. Columns that exist in the Google BigQuery table but do not exist in the workflow field list will produce Null values for those columns in the inserted records. This causes an error in the case where the destination column is not configured as NULLABLE.

Input Progress Indicator

In the case that there are rows in the streaming buffer for the selected table of an input tool without a custom query, the input tool uses the estimated number of rows in the streaming buffer reported by Google while updating the percentage of the progress indicator in a running workflow. If Google's estimate is incorrect, then the value reported in the progress indicator could be incorrect as well.

In the case that the table being queried is empty and the streaming buffer for that table is non-empty, then it is possible for Google to estimate the number of rows in the streaming buffer as zero. However, Google might still return rows from the streaming buffer. When this happens, you do not see the progress indicator percentage update and a warning displays.

Was This Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.