Tip
Click to open the Build SQL query editor and ask Celigo AI to generate a query. Enter a description of your query in plain English in the space provided, and Celigo AI will attempt to generate a query based on your prompt.
Set up a connection to Amazon Redshift.
Use optimized bulk load (recommended for larger imports)
This import type is ideal for large data volumes. It aggregates data before loading, reducing the number of batch inserts and improving performance. integrator.io splits the data into fixed-size files, stages them in encrypted, Celigo-managed storage, and loads them into Amazon Redshift with the COPY command. By default, all files load together. To load each file as soon as it's created, check Load data as available.
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.RedshiftTableLookup}}
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. After you create the import, map the fields you selected.
Override merge/insert statement: Check this box to replace the default statement with your own load logic. Write it with handlebars in the SQL box or the Advanced Field Editor (AFE).
Load data as available: Check this box to load each file as it's created. If a file fails, integrator.io skips it and flags its records as errors. If you leave the box unchecked, integrator.io loads all ready files together.
Use batch insert SQL query
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 the Batch size field in the Advanced section to tune your imports as needed. There is a query limit of 100KB, so for larger batch sizes, the number of records imported in a single request is adjusted to the size limit.
By using metadata, you can now:
- Search for a specific database, dataset, table, or column to add to your query.
- Use lookups to find data.
- Refresh your data to get new updates automatically.
- You can also use Mapper 2.0 to map imported records.
Note
You can use any schema and table name regardless of what you used in your connection with the following format in your import’s Destination table search: database.cluster.table name.
Important
Celigo platform integrator.io can’t process databases with a dot in the name. To work around this, create a connection with the database (name with dot in it) and the required schema. Then, in the "destination table" import field, enter the table instead of database.cluster.table.
Execute a SQL query once for each record.
You can write your SQL command in the SQL query text field. Click Edit () to the right of the text field to open the SQL Query builder AFE.
Execute a SQL query once per page of data. You can write your SQL command in the SQL query text field. Click Edit () to the right of the text field to open the SQL Query builder AFE.