This is a Premium connector for direct connection to Google's BigQuery database service. Our platform enables you to quickly and easily insert your Form Entries as rows in a target BigQuery database table. This enables a one-way synchronization to occur - where any Forms Entries are pushed to tables in a Google BigQuery database.
Before you add a BigQuery connector, you must ensure that you have a valid Google Cloud Platform Connection set up on your organization. You also need to set up a few basic things in the GCP console.
Adding a Google BigQuery Connector to your Form
The Form Connectors page can be accessed from your Form designer, settings or from the Screens listing if you mouse over the Form row in question.
Once you are in the Connectors page, click on the "ADD CONNECTOR" button to see a list of the available Connectors and click the Google BigQuery option. This will refresh the page and display your Connector, ready for configuration:
This will refresh the page and display your Connector, ready for configuration: 
| Property | Description |
| Project ID | Specify the Google Cloud Platform Project ID that Contains your target BigQuery dataset. |
| Dataset Name | specify the Dataset Name where the table is located. |
Table Link Type:
| Choose how you would like this table data to be managed: Auto Maintain: (add rows) This is a recommended setting, as columns will be automatically added/altered as the Form design changes between versions. Our platform will automatically create and maintain a table in your BigQuery dataset when enabled. Due to restrictions on the BigQuery API our connectors cannot alter the columns on a BigQuery table. As such, each new version of your Form will create a new table in BigQuery, with the form version added to the given table name as a suffix. e.g. MyExampleTable_v1, MyExampleTable_v2, MyExampleTable_v3 and so on. Mapped Fields (update/add rows) By default, the connector will insert new rows into the target table. If you want the connector to update pre-existing rows, then use the Key option to define a key column which will be used to find a matching row when the connector is executed. If a matching row is found, then that will be updated. Otherwise, a new row will be added. Custom SQL Enter a custom SQL query to be executed by this connector within a single transaction. Use our data templating syntax to inject Form field placeholders and define repeating sections. Example: DELETE FROM DataSetName.MyTableName WHERE Id = CAST({{%ENTRYGUIDFULL}} as string); INSERT INTO DataSetName.MyTableName ( id, itemno, formversion, myField1, myfield2, myfieldn ) VALUES {{!REPEATSTART}} ( CAST({{%ENTRYGUIDFULL}} as string), CAST({{%ITEMNO as int64}}), CAST({{%FORMVERSION}} as int64), CAST({{myField1}} as datetime), CAST({{myfield2}} as string), CAST({{myfield2}} as int64), ), {{!REPEATEND}}; A unique built-in function is available to database connectors {{%UTCNOW}}, containing the date and time when the query is being executed in UTC. These built-in ENTRYGUID, ENTRYGUIDFULL, and ITEMNO are always guaranteed to have a value regardless of the 'Use NULL in Repeat Rows' setting. |
| Table Name | Specify the table name which will receive entry data for this Form. A default table name will be generated using the Form External ID if left blank. |
| Data Columns | Specify which Form fields to populate as row values in your target dataset. Upon your first Form entry, each selected Form field will auto-create a column of the same name if it does not already exist. |
| User URL Media Fields | If enabled, media filenames will be converted to their complete URLs. |
| Replace Placeholders with Display Text | By default, placeholders will be replaced with the raw answer value from the form entry. For example, date values are stored as UTC in ISO 8601 raw format: 2015-10-23T13:05:07Z. Select this option if you wish to replace it with user-displayed text instead: 23-Oct-2023 05:05:07 assuming your time zone is UTC-10. |
| Use NULL in Repeat Rows | If enabled, form fields appearing in repeat sections will be replaced with NULL values in all but the first repeat generated. The following are NOT affected by this option:
|
| Anonymize Personal Data | If enabled, data from fields marked as Person Data will be converted to a nonhuman-readable format to aid in privacy. |
At this stage, your Connector is ready to go. Hit the SAVE button to save your changes and activate the Connector.
| NOTE: The Google BigQuery connector does not support adding additional columns to a table once the table has been created. We get around this limitation by appending the form version suffix to the table name. If you however, make changes to the connector or fomr design such that new fields are mapped to the new columns, subsquent runs of the connector will fail while still in test mode. To fix this, either delete the table in Google BigQuery so that the next run of the connector recreates it, or simply publish the form design so that _v* suffix is used. |