Connecting Amazon Redshift 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

Setup

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;

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.

Set up a table upload schema

This step is optional. However, we recommend completing it as part of the initial set up or you won’t be able to use uploaded files like CSVs in joins.

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

Create the schema in Redshift.
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;

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.

Create the connection in Omni

In Omni, click Settings > Connections.
Click the Redshift option.
On the connection setup page, fill in the connection details:

required
A user-friendly name for the connection, which will be used throughout Omni.

required
The address of the database server.

default:"5439"
required
The port number for the database connection.

required
The database to connect to.

required
The username for database authentication.

required
The password for database authentication.

required
The timezone used by the database.

required
The timezone to use for Omni queries. If a timezone is specified, data will be converted from the Database Timezone to the selected timezone.

When enabled, users can override the connection timezone with their own when querying.

default:"enabled"
When enabled, Omni will automatically infer relationships between tables based on column name matching during schema refreshes. Defaults to enabled.

default:"900"
Maximum time in seconds before a query times out.

default:"enabled"
When enabled, Omni will prefix generated view names with schema and catalog, e.g. my_catalog_public__orders instead of orders. Defaults to enabled. See View name generation for more information.

required
Select the minimum level of access users should have to models in the connection.
When finished, click Create connection.

Connecting to Redshift datashares

Redshift data sharing lets you share data across Redshift databases and clusters. To make datashare tables available in Omni, you need to create an external schema in the database Omni connects to and grant the Omni user access to it.

Create an external schema

In the consumer database (the one Omni connects to), run:

Create an external schema for the datashare

CREATE EXTERNAL SCHEMA <external_schema_name>
FROM REDSHIFT DATABASE '<datashare_database_name>'
SCHEMA '<source_schema_name>';

Replace the following placeholders:

<external_schema_name> — the name the schema will have in your local database
<datashare_database_name> — the name of the shared database
<source_schema_name> — the schema within the shared database to expose

Grant the Omni user access

Grant the Omni user usage on the external schema:

Grant schema access to the Omni user

GRANT USAGE ON SCHEMA <external_schema_name> TO omni;

If the datashare was created with the WITH_PERMISSIONS flag, you also need to grant usage on the shared database:

Grant database access (WITH_PERMISSIONS datashares only)

GRANT USAGE ON DATABASE '<datashare_database_name>' TO omni;

Verify Omni can see the tables

To confirm the external schema and its tables are visible to the Omni user, run the following queries as the Omni user:

Verify table visibility

SELECT * FROM SVV_TABLES WHERE table_schema = '<external_schema_name>';

Verify column visibility

SELECT column_name FROM SVV_COLUMNS WHERE table_schema = '<external_schema_name>';

If both queries return rows, Omni will be able to discover and model the datashare tables.

What’s next?

Now that your database is set up, you can:

Use the Omni Agent’s quickstart skill to create your first topic from business questions
Configure user permissions, schema refreshes, environments and timezone settings
Learn how Omni generates the model associated with the connection

Connect data

Manage users and permissions

Manage organization

Identity Providers

Security

Monitor Omni

Connecting Amazon Redshift to Omni

Requirements

Setup

Create a database user for Omni

Set up a table upload schema

Allowlist Omni's IP addresses

Create the connection in Omni

Connecting to Redshift datashares

Create an external schema

Grant the Omni user access

Verify Omni can see the tables

What’s next?

​Requirements

​Setup

Create a database user for Omni

Set up a table upload schema

Allowlist Omni's IP addresses

Create the connection in Omni

​Connecting to Redshift datashares

​Create an external schema

​Grant the Omni user access

​Verify Omni can see the tables

​What’s next?

Requirements

Setup

Connecting to Redshift datashares

Create an external schema

Grant the Omni user access

Verify Omni can see the tables

What’s next?