Articles in this section

See more

Amazon Redshift + Smart Ingest

Smart Ingest lets you pull data stored in your Amazon Redshift data warehouse and import it to Iterable. Most of the setup occurs in the Iterable app, but you need access to Redshift for information like your host, port, database name, and credentials.

NOTE

Contact your Iterable customer success manager to discuss adding Smart Ingest to your plan.

Smart Ingest can only import data from Amazon Redshift.

In this article

Connection requirements

To connect with Smart Ingest, you need the following items from Amazon Redshift:

  • A connection type - direct, or a standard or reverse SSH tunnel

  • Host – The hostname or IP address of your Redshift cluster.

    Example: my-cluster.abc12345.us-east-1.redshift.amazonaws.com

  • Port – The port number of your Redshift cluster. The default is 5439, but yours may be different.

  • Database – The name of the database in your Redshift cluster. Most clusters have only one database.

  • User – This can be your personal Redshift login, or a dedicated user for Smart Ingest. At minimum, this user must have read access to the data you wish to sync. If using the Lightning sync engine, you must also grant this user additional permissions.

  • Password – The password for the user specified above.

Before you connect Smart Ingest to Amazon Redshift, make sure to allow Smart Ingest IPs in your AWS account.

Connecting to Amazon Redshift

To connect Smart Ingest with Amazon Redshift:

  1. Log in to Iterable as a user with the Manage Integrations project permission and open the project you’re working on.

  2. Go to Integrations > Smart Ingest.

  3. Click Connect a New Source.

  4. Select Amazon Redshift and click Continue.

  5. In Step 1 select the connection type your sync should use to connect:

    • Connect directly with Amazon Redshift. This creates a direct connection with no additional setup needed.

    • Connect via SSH Tunnel. This connects using SSH. Add a standard or reverse tunnel, or select an existing tunnel if you’ve already set one up.

      To learn more about SSH tunneling and how to set up standard and reverse tunnels for Smart Ingest, read SSH Tunneling for Smart Ingest.

  6. In Step 2, enter your Redshift account details: Host, Port, and Database

  7. In Step 3, choose from the Lightning or Basic sync engine. Lightning is faster and more efficient, but requires the database user to have write access. Basic is slower and less efficient, but doesn’t require write access.

    To view the additional permissions required for the database user, select the Lightning sync engine. These permissions are necessary for Smart Ingest to create and manage the sync schema, and are customized based on the other inputs you provide in the source setup form.

    To learn more about sync engines, read Optimizing Smart Ingest Sync Performance.

    NOTE

    Smart Ingest is co-developed by Iterable and Hightouch. Hightouch is a data processor for this feature. Smart Ingest data operations and schemas may contain the Hightouch name, but the feature is fully supported by Iterable.

  8. In Step 4, enter your user credentials for Redshift: User and Password

    This user must have the necessary permissions to use the sync engine you selected in Step 3.

  9. Click Continue. Smart Ingest automatically tests the connection.

  10. When the connection test is successful, click Continue. (If there are problems connecting, click Back and review the connection details for accuracy.)

  11. Add a name for your data source to display in Iterable.

  12. Click Finish.

Next steps

You've now connected your data warehouse to Smart Ingest. The next thing you can do is create a sync.

Troubleshooting Amazon Redshift

Spectrum nested query error

You may receive a Spectrum nested query error if you are using Amazon Redshift Spectrum as your data source. Smart Ingest uses a wrapper query around your model query for obtaining the COUNT of query results. Spectrum has limitations around nested data that this can cause issues with.

To resolve the error, you can try the following:

  • Create a materialized view of the results set you want Smart Ingest to query.

  • Add a commented out -- ORDER BY statement to your model definition. Smart Ingest disables the row counter wrapper query for a Redshift source if there's an ORDER BY in the query. However, Redshift Spectrum doesn't allow ORDER BY statements, hence the need to comment it out. Even commented out, Smart Ingest disables the wrapper query.

Error: could not identify an ordering operator for type "unknown"

You may receive this error if you are using many UNION set operators within your SQL query. To resolve the error, you can try replacing UNION with UNION ALL. To learn more about these SQL functions, read What is the difference between UNION and UNION ALL (Atlassian.com).

Related articles