Google BigQuery

Google BigQuery always quotes tables.

Appends happen by field name. If the name or data type don’t match, Google BigQuery throws an error.

Check Support Null Values in the format specific options (checked by default) to allow loading null data into Google BigQuery. If the option is not checked, Google BigQuery will reject null values.

Extra columns in the target table will load as null if they are not included in Alteryx.

A column name must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_), and it must start with a letter or underscore. The maximum column name length is 300 characters. For more information, see Google BigQuery documentation.

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 will cause 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 will use 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 may still return rows from the streaming buffer. When this happens, you will not see the progress indicator percentage update, and a warning will display.

Using ODBC to write, Designer creates date/time fields as DATETIME, using the bulk loader to write, it creates date/time fields as TIMESTAMP.

In Google BigQuery TIMESTAMP represents the same as DATETIME and you can use EXTRACT to parse it.

On Input:

When pulling nested or repeated records from a Google BigQuery table, the Alteryx workflow will flatten the nested and/or repeated records according to the following naming scheme:

A nested record nested_attr of the top-level column top_attr will create 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/or 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.