Import data into Google BigQuery

Before you begin

Set up a connection to Google BigQuery.

Create an import

From the Tools menu, select Flow builder. For the Destination application, click Google BigQuery. Select your Google BigQuery connection from the connection list and click Next.
         –– OR ––
From the Resources menu, select Imports. In the resulting Imports page, click + New Import. From the application list, click Google BigQuery. Select your Google BigQuery connection, add a name and a description for your import.

Contents

• Before you begin
• Create an import
• How would you like your records imported?
• Advanced

One to many (required): There are advanced use cases where a parent record is being passed around in a flow, but you actually need to process child records contained within the parent record context. For example, if you're exporting Sales Order records out of NetSuite and importing them into Salesforce as Opportunity and Opportunity Line Item records, then you will need to import the Opportunity Line Item records using this option. One to many is used in cases where you have a single record that internally needs to create multiple records. This field cannot be used when importing a CSV file.

• Path to many (required): If the records being processed are JSON objects, then use this field to specify the JSON path to the child records. This field does not need to be set for array/row-based data.

How would you like your records imported?

Choose type (required):

• Use bulk insert SQL query – quickly inserts batches of data efficiently.
  • Target table (optional): Please enter the table name where the data needs to be inserted. Applicable only for bulk-inserts.
• Use SQL query once per page of records – executes a custom query per page of records.
  • SQL query (required): Enter the exact SQL query to be sent to your database. The query can be static or dynamic, which means that handlebars templates are used to source data and settings from your flow.
    SQL query once per page of records

                "query": [ "insert into `1234.IO_Once_Per_Page_Of_Records`
                values ({{batch_of_records.0.record.ID}},{{batch_of_records.0.record.NAME}},{{batch_of_records.0.record.POSITION}},{{batch_of_records.0.record.DEPARTMENT}},{{batch_of_records.0.record.DAYS_PRESENT}},{{batch_of_records.0.record.DAYS_ABSENT}},{{batch_of_records.0.record.SWIP_IN}},{{batch_of_records.0.record.SWIP_OUT}},{{batch_of_records.0.record.LEAVE_BALANCE}},{{batch_of_records.0.record.LEAVES_USED}})"
                

• Use SQL query on first page only – executes a custom query that runs only once in a flow on the first page of records.
  • SQL query (required): Enter the exact SQL query to be sent to your database. The query can be static or dynamic, which means that handlebars templates are used to source data and settings from your flow.
    SQL query on first page only

                "query": [ "insert into `1234.IO_First_Page_BQ` values ({{batch_of_records.0.record.ID}},{{batch_of_records.0.record.name}},{{batch_of_records.0.record.position}},{{batch_of_records.0.record.department}},{{batch_of_records.0.record.Days_present}},{{batch_of_records.0.record.Days_Absent}},{{batch_of_records.0.record.Swip_In}},{{batch_of_records.0.record.Swip_out}},{{batch_of_records.0.record.leave_Balance}},{{batch_of_records.0.record.leaves_used}})"
                

Advanced

Concurrency ID lock template (optional): This field can be used to help prevent duplicate records from being submitted at the same time when the connection associated with this import is using a concurrency level greater than 1.

Saying this another way, there are fields on the connection record associated with this import to limit the number of concurrent requests that can be made at any one time, and if you are allowing more than 1 request at a time then it is possible for imports to override each other (i.e. a race condition) if multiple messages/updates for the same record are being processed at the same time.

This field allows you to enforce an ordering across concurrent requests such that imports for a specific record ID will queue up and happen one at a time (while still allowing imports for different record ids to happen in parallel).

The value of this field should be a handlebars template that generates a unique id for each exported record (note: we are using the raw exported data when generating the IDs -- before any import or mapping logic is invoked), and then with this id the integrator.io back-end will make sure that no two records with the same id are submitted for import at the same time.

One example, if you are exporting Zendesk records and importing them into NetSuite then you would most likely use '{{id}}' (the field Zendesk uses to identify unique records), and then no two records with the same Zendesk ID value would import into NetSuite at the same time.

Data URI template (optional): When your flow runs but has data errors this field can be really helpful in that it allows you to make sure that all the errors in your job dashboard have a link to the target data in the import application (where possible).

This field uses a handlebars template to generate the dynamic links based on the data being imported.

Note: The template you provide will run against your data after it has been mapped, and then again after it has been submitted to the import application, to maximize the ability to link to the right place.

For example, if you are updating a customer record in Shopify, you would most likely set this field to the following value: https://your-store.myshopify.com/admin/customers/{{{id}}}.

 "idLockTemplate": "{{record.sku}}", 
         "dataURITemplate": "{{record.id}}",
         "method": "insertMany",
         "collection": "afe_dataurl_concurrency_import",
         "document": "{\n\"id\":{{record.id}},\n\"sku\":\"{{record.sku}}\",\n\"date\":\"{{record.createdate}}\"\n}"
 

Override child record trace key template (optional): Define a trace key that integrator.io will use to identify a unique record for a parent-child record combination. You can use a single field such as {{{field1}}} or use a handlebar expression.

When this field is set, you will override the platform default child record trace key field. The child record trace key template value will include the parent record trace key in the format parent_record_trace_key - child_record_trace_key.