This article focuses on how you'd like to configure your import records. It excludes the general and advanced settings that are common across database connector forms.
Set up a connection to Google BigQuery.
You can use standard SQL queries to modify data if required and import it into Google BigQuery. However, you can't use multiple SQL queries on a single flow step. If your flow requires multiple SQL queries, you must create one flow step for each query.
To import data into Google BigQuery, choose an import type based on your requirements. Each import type is explained in the sections below.
Tip
You can ask Celigo Ora to generate an SQL query. Enter a description of your query in plain English in the space provided, and Celigo Ora will attempt to generate a query based on your prompt.
Note
This option is being introduced via a phased rollout and will soon be available to all users soon.
This import type is ideal for large data volumes as it aggregates data before loading, reducing the need for numerous batch inserts and significantly improving performance. It chunks the large data volumes into smaller fixed-size files. You can load all the files together (default), or load them as they are created using the Load data as available checkbox.
Additionally, integrator.io automatically generates insert or merge queries based on the destination table and primary keys, allowing you to update existing data seamlessly without the need for a separate deduplication flow.
Destination table: Select the destination table into which the data must be inserted in either of these ways:
-
By validated table name: Begin typing the table name in the Destination table field, and any table that already exists in your database environment will display in a list filtered by the text you enter.
-
By referencing a dynamic lookup with a handlebars expression that identifies the destination table: Use a handlebars expression to reference the lookup using the following format:
{{lookup.<lookup name>}} Ex: {{lookup.GoogleBigQueryTableLookup}}
Primary keys: Select the primary keys so that your data can be matched based on these fields and then merged in the destination table. If your table already has primary keys, they will be automatically shown in this setting. You can select one or more fields to designate as primary keys. These fields uniquely identify each record in your table. (After you create your import, map these values (fields) that you've provided in your Primary keys setting.)
-
If you leave Primary keys blank, it creates an insert query. Insert without primary keys may lead to duplication.
-
If you select values in Primary keys, it creates a merge query.
Override merge/insert statement: If you check this box, you can override the existing merge/insert statment by entering custom load logic using Handlebars in the SQL box or Advanced Field Editor (AFE).
Load data as available: If you check this box, it loads files as they are created. It skips failed files, and the records are flagged as errors. If you don't check this box, then it loads all the files that are ready in one-go.
The batch insert data option is helpful for moderate data volumes. integrator.io builds the insert query for you automatically for each batch of records. Each batch's default number of records bound is 100, but you can use Batch size in the Advanced section to tune your imports as needed.
In the Destination table:, using metadata, you can:
-
Search for a specific project ID, dataset, table, or field to add to your query. You can use any dataset and table name regardless of what you used in your connection by using the following format in your import Destination table search:
project ID.dataset.table name -
Refresh your data to get new updates automatically
You can also use Mapper 2.0 to map imported records.
In BigQuery, different JSON field types (Nullable, Required, and Repeated) have specific rules for accepting objects and arrays. The list below outlines what can and cannot be inserted into each field type. To work around this limitation, use a different SQL option, like query per page.
-
Nullable field types: You can't insert arrays but can insert objects.
-
Required field types: You can't insert arrays but can insert objects.
-
Repeated field types: You can't insert objects but can insert arrays.
Execute a SQL query once per page of data. You can write your SQL command in the SQL query text field. Click to the right of the text field to open the SQL Query builder AFE.
Use this option if you want to run a SQL command once per flow run. The SQL will execute as soon as the first page of data reaches the flow step. This option should be used carefully to avoid race conditions in your flows.
Important
Do not use this option to delete or create tables that are also being loaded or updated in the same flow. To automate those tasks, use separate flows for each individual task, and then link the flows together to run consecutively. Using linked separate flows guarantees that the SQL commands will execute in the correct order without colliding with each other.
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.