Skip to main content

Connecting Amazon Redshift to Omni

In this guide, you'll learn how to connect your Redshift database to Omni.

Requirements​

To follow the steps in this guide, you'll need:

  • Organization Admin permissions in Omni
  • Permissions in Redshift that allow you to create database users and grant permissions

Step 1: Create a database user for Omni​

In your Redshift database, run the following command to create a database user for Omni:

Create an Omni database user
CREATE USER omni WITH PASSWORD '<password>';
GRANT USAGE ON SCHEMA '<schema_name>' TO omni;
GRANT SELECT ON TABLE information_schema.tables TO omni;
GRANT SELECT ON TABLE information_schema.columns TO omni;

Next, run the following command for each schema you want to use in Omni:

Grant Omni access to schemas
GRANT SELECT ON ALL TABLES IN SCHEMA '<schema_name>' TO omni;
tip

If you're using Redshift views via dbt, Omni may not be granted permissions if there are dependencies with other schemas. To grant access to those views, you'll also need to grant SELECT access to the views. If objects are declared as tables, they will be available in Omni.

Step 2: Set up a table upload schema​

note

This step is optional, but Omni recommends completing it as part of the initial connection setup.

In this step, you'll create a dedicated schema to use for table uploads. This schema can't be used for other modeled tables.

  1. Create the schema in Redshift.

  2. Run the following to grant the Omni user the required privileges:

    Grant Omni access to table upload schema
    GRANT USAGE, CREATE ON SCHEMA 'upload_schema_name' TO omni;

Step 3: Allowlist Omni's IP addresses​

If access to the Redshift database is limited by IP address, you'll need to add Omni's IPs to the allowlist before you create the database connection. For Redshift, this typically means creating a security group and adding an inbound traffic rule for each IP address.

Omni's IP addresses can be found on an individual connection's page, accessed by navigating to Settings > Connections and clicking a connection.

Step 4: Create the connection in Omni​

  1. In Omni, click Settings > Connections.

  2. Click Add connection.

  3. Click Redshift.

  1. Fill in the fields as follows:

    • Display name - Required. Enter a name for the connection, which will display in the connections list in Omni.

    • Host - Required. Enter the host address for the database.

      Refer to the Redshift documentation for help finding this in the AWS console.

    • Post - Required. Enter the port used by the database.

      The default for Redshift is 5439.

    • Database - Required. Enter the name of the database Omni should connect to.

    • Include schemas - To only include specific schemas, enter the names of the schemas as a comma-separated list. Leaving this field blank will allow Omni to access all schemas in the database.

    • Username - Required. Enter the username of the database user you want Omni to use.

      This is the Omni user you created in Step 1.

    • Password - Required. Enter the password for the database user.

      This is the password you assigned to the Omni user in Step 1.

    • Schema for Table Uploads - Enter the name of the schema where table uploads will be stored. If left blank, you'll still be able to upload tables but they won't be pushed to the database.

    • Database Timezone - Required. Select the timezone used by the database.

    • Query timezone - Required. If specified, data will be converted from the Database timezone to this timezone when querying. Refer to the Converting timezone data guide for more information.

    • Allow User-Specific Timezones - Check this option to allow individual users' Query timezone settings to be used as the query timezone for the connection.

    • Query timeout - Enter the number of seconds a query has to execute before the database cancels it. The default is 900 seconds.

  2. When finished, click Create connection.

What's next?​

Now that Redshift is set up, you can: