Contents

• Understanding the Data Warehouse Automation for Google BigQuery quickstart integration template
• Install the Data Warehouse Automation for Google BigQuery quickstart integration template
•  Configure and run flows in the Data Warehouse Automation for Google BigQuery quickstart template
  • Integration settings
  • Configured source application export to Google BigQuery
  • Configured source application to Google BigQuery production table - Merge & Delete
  • Add a resource from your configured source application to the Data Warehouse Automation for Google BigQuery  quickstart integration template
  • Add a configured source application to the Data Warehouse Automation for Google BigQuery  quickstart integration template
  • Add an unconfigured source application to the Data Warehouse Automation for Google BigQuery  quickstart integration template

Once you have completed the installation process, integrator.io creates two flows:

• Configured source application to Google BigQuery - exports selected resource data from your source application into a newly created temporary table in Google BigQuery.
• Configured source application to Google BigQuery production table - Merge & Delete - exports data from the temporary table in Google BigQuery to the production table in Google BigQuery (and deletes the temporary table).

Additional record mapping is required to integrate the template’s exports and imports with your Data Warehouse Automation for Google BigQuery apps.

Integration settings

Use the following steps to enter the Google BigQuery Project id and Dataset id in your integration settings. These values will be automatically populated in the flow settings of any newly created flows.

1. Open the Data Warehouse Automation for Google BigQuery integration tile and click the Settings tab.

2. Enter your Google BigQuery Project id and Dataset id.

Configured source application export to Google BigQuery

For configured flows, an export is automatically created for each resource you selected when configuring your source application. For example purposes, we’ve configured a Hubspot connection and only selected one Hubspot resource (contacts). An additional export is automatically added for each resource you select for a configured flow.

1. Once you have completed the installation process, open and enable the Configured source application to Google BigQuery production table - Merge & Delete flow.
2. Open the Configured source application export to Google BigQuery flow and click the gear icon in the upper right corner of Flow Builder.

Name (required): displays the name of the flow.

Description (optional): displays an optional description of the flow.

Next integration flow (optional): If you have enabled the Configured source application to Google BigQuery production table - Merge & Delete flow, the name of that flow displays here. You can also use the drop-down menu to select any enabled flow in the Data Warehouse Automation for Google BigQuery integration tile.

Registered Data source (required, uneditable): displays the name of the registered connection used to connect to the source application.

Project ID (required): enter the unique identifier for the BigQuery project that contains the datasets that will store the imported data. This field is automatically populated if you enter it on the Settings tab for your integration.

Dataset ID (required): enter the unique identifier for the dataset that contains the tables that will store the imported data.  This field is automatically populated if you enter it on the Settings tab for your integration.

Table name (required): enter the temporary table name that integrator.io will create in Google BigQuery when the flow runs. Make sure that the name of the temporary table does not already exist in your Google BigQuery environment. You will receive an error if the temporary table name matches the name of a table that already exists in your Google BigQuery environment.

Select resources (optional): You can use this drop-down menu to select any additional resource you would like to export from your source application (if not all preferred export resources were initially selected on installation).

The export settings are automatically configured to send all contacts retrieved from Hubspot to Google BigQuery’s temporary table. Each configured export step includes a hook that refines the exported data for import into the Google BigQuery temporary table.

Flow details

• Export source: Hubspot
  • Export: Get Contacts - Exports all products Hubspot contacts modified since the last time the flow ran.
• Import destination: Google BigQuery
  • Import: Import data into BigQuery’s temporary table - Adds or updates all exported data to Google BigQuery’s temporary table.

Configured source application to Google BigQuery production table - Merge & Delete

This flow exports data from the temporary table (imported during the previous flow), imports that data into the Google BigQuery production table, and delete the records from the temporary table.

Open the Configured source application to Google BigQuery production table - Merge & Delete flow and click the gear icon in the upper right corner of Flow Builder.

Name (required): displays the name of the flow.

Description (optional): displays an optional description of the flow.

Next integration flow (optional): If you would like another flow in your integration to run immediately after this flow completes, select it from this drop-down menu.

Project ID (required): enter the unique identifier for the BigQuery project that contains the datasets that will store the imported data. This field is automatically populated if you enter it on the Settings tab for your integration.

Dataset ID (required): enter the unique identifier for the dataset that contains the tables that will store the imported data. This field is automatically populated if you enter it on the Settings tab for your integration.

Temporary table (required): Enter the name of the temporary table you want to export data from. This name should match the Table name you defined in the Configured source application export to Google BigQuery flow settings.

Production table (required): Enter the name of the production table that you want to import data into with this flow.

Flow details

• Export source: Google BigQuery
  • Export: Check for existing records - Exports all records currently stored in the Google BigQuery temporary table..
• Import destination: Google BigQuery
  • Import: Merge temporary data with production data - Adds or updates all exported data to Google BigQuery’s production table.
  • Import: Delete temporary table records - removes all imported records from the temporary table.

Add a resource from your configured source application to your Data Warehouse Automation for Google BigQuery 

Use the following steps to add an additional resource to your Data Warehouse Automation for Google BigQuery.

1. Open the Configured source application export to Google BigQuery Data Warehouse flow.
2. Click the gear icon in the upper right corner to open the Settings menu.
3. Click the Resource table drop-down menu and check the box next to the resource you want to add.

In this example, we will add companies as an additional export resource.

4. Check the box next to any resource you want to export from your source application and click Done.
5. Click Save or Save & Close to add the new resource to the export list.

Add a configured source application to your Data Warehouse Automation for Google BigQuery

You can add a configured source application to your Data Warehouse Automation for Google BigQuery if you have registered the connection in your environment. Use the following steps to add an additional configured source application to your Data Warehouse Automation for Google BigQuery.

1. Open the Data Warehouse Automation for Google BigQuery integration tile and click the Settings tab.

2. In the Fully generated flows section, click the Registered connections drop-down menu to select the connection you want to use for the configured source application export you want to use.
3. Check the box next to each resource in the Export resources drop-down menu that you want to add to your Data Warehouse Automation for Google BigQuery and click Done.
4. Click Save, return to the Flows tab of the Data Warehouse Automation for Google BigQuery integration to see the new configured source application export to Google BigQuery flow.

Add an unconfigured source application to your Data Warehouse Automation for Google BigQuery

You can add any unconfigured source application to your Data Warehouse Automation for Google BigQuery if you have registered the connection in your environment. Use the following steps to add an additional unconfigured source application to your Data Warehouse Automation for Google BigQuery.

1. Open the Data Warehouse Automation for Google BigQuery integration tile and click the Settings tab.
2. In the Unconfigured flows section, click the Registered connections drop-down menu to select the connection you want to use for the unconfigured source application export you want to use.

3. Click Save, return to the Flows tab of the Google BigQuery Data Warehouse integration to see the new unconfigured source application export to Google BigQuery flow.
4. Open the flow and click Add source to set up the connection and manually configure your export settings.
5. Enter the required JSON in the Custom settings text field.
6. Click Launch Form builder.

7. Enter the following code snippet in the Form definition section:

{
    "fieldMap": {
        "resource": {
            "id": "resource",
            "name": "resource",
            "type": "text",
            "required": true,
            "helpText": "The name of the resource as it will be stored in the Google BigQuery column.",
            "label": "Resource table"
        },
        "primaryKey": {
            "id": "primaryKey",
            "name": "primaryKey",
            "type": "text",
            "required": true,
            "helpText": "The primary key that integrator.io will use to perform advanced operations in Google BigQuery.",
            "label": "Primary key"
        }
    },
    "layout": {
        "fields": [
            "resource",
            "primaryKey"
        ]
    }
}

8. Click Save.
9. The Custom settings section of your export panel displays the following fields:

Resource table (required): Enter the name of the resource to be exported from your source application.

Primary key (required): Enter the unique identifier for the record to be exported from your source application. 

10. Click Save.
11. Open the Source application export to Google BigQuery flow.
12. Click the gear icon in the upper right corner of Flow Builder and enter the Project id, Dataset id, Temporary table name to be created in Google BigQuery upon import.
13. Click Save.
14. Open the Source application to Google BigQuery production table - Merge & Delete flow.
15. Click the gear icon in the upper right corner of Flow Builder and enter the Project id, Dataset id, Temporary temporary table name, and Production table name.
16. Enable both flows, and run the Source application export to Google BigQuery flow.

Settings - Project & Dataset

In the settings of this template, you can find a section of Project & Dataset. The values for the Project id and Dataset id entered here shall be auto-filled in the flow settings of any newly created flows.