Amazon Redshift (Edge)

Prerequisites

• The Amazon Redshift cluster is running and can access Amazon S3.

• The cluster is publicly accessible, or an SSH server that can access it is available.

• Hevo’s IP addresses for your region are allowlisted in the security group attached to your cluster.

• You have superuser access to the Amazon Redshift database, or access to a user that can create users and schemas.

• A dedicated schema named hevo is available in the database that your datasets read from. Activate records the progress of each sync there.

• Hevo is assigned the USAGE privilege on each schema that your datasets read from, and the SELECT privilege on the current and future tables in those schemas.

• Hevo is assigned full access to the hevo schema, which allows it to create, read, write, rename, and drop its own bookkeeping tables.

• The database hostname and port number of your cluster are available.

Perform the following steps to configure your Amazon Redshift Source in Activate:

(Optional) Create an Amazon Redshift Cluster

Allowlist Hevo IP Addresses for your region

To connect Hevo directly to your Amazon Redshift cluster, you need the following:

• A cluster that is publicly accessible, so that it can be reached from outside its virtual private cloud (VPC).

• The Hevo IP addresses for your region, allowed by the security group attached to your cluster.

These settings allow Hevo to reach your cluster over the internet and to connect to it. If either one is missing, the connection test fails even when the credentials are correct.

Note: If you connect through an SSH tunnel, your cluster does not need to be publicly accessible. Allowlist the Hevo IP addresses on your SSH server instead. Read Connecting Through SSH for the steps.

1. Make your Redshift cluster publicly accessible

Perform the following steps to make your cluster publicly accessible:

1. Follow steps 1-3 from the Create an Amazon Redshift Cluster section to open the Clusters page.

2. In the In my account tab, Clusters section, click the cluster you want to connect to Hevo.

   

3. On the <Cluster Name> page, click the Actions drop-down, and then click Modify publicly accessible setting.

   

4. On the Edit publicly accessible pop-up window, select the Turn on Publicly accessible check box, and then click Save changes.

   

   Note: It can take about ten minutes for the setting to change and for connections to succeed.

Your cluster now has an endpoint that can be reached from outside its VPC. However, the security group attached to the cluster decides which addresses can connect to that endpoint, so proceed to allowlist the Hevo IP addresses for your region.

2. Allowlist the Hevo IP addresses

A security group controls the traffic that is allowed to reach your cluster, so you must add an inbound rule to it for each Hevo IP address.

Perform the following steps to allowlist the Hevo IP addresses for your region:

1. Follow steps 1-3 from the Create an Amazon Redshift Cluster section to open the Clusters page.

2. In the In my account tab, Clusters section, click the cluster you want to connect to Hevo.

   

3. On the <Cluster Name> page, click the Properties tab.

4. Scroll to the Network and security settings section, and click the link text under VPC security group.

   

5. On the Security Groups page, select the check box for your security group, and from the Actions drop-down, click Edit inbound rules.

   

6. On the Edit inbound rules page, do the following:

   

   1. Click Add rule.

   2. In the Type column, select Redshift from the drop-down. The Port range is set to 5439 automatically.

   3. In the Source column, select Custom from the drop-down, and specify one of the Hevo IP addresses for your region, in the CIDR format. For example, 13.228.214.171/32.

   4. Repeat steps 1-3 to allowlist all the IP addresses.

7. Click Save rules.

The Hevo IP addresses are allowlisted, and Activate can connect to your Amazon Redshift cluster.

Create the Bookkeeping Schema

Activate records the progress of each sync in a dedicated schema named hevo, in the same database that your datasets read from. This schema helps Activate identify and send only the rows that changed since the last successful run, and stores the records that failed to sync. Your own data is never stored here. Read bookkeeping for more information.

Activate does not create the schema for you. If it is missing, the connection test fails.

Perform the following steps to create the bookkeeping schema:

1. Connect to your Amazon Redshift database as a superuser or a user with the CREATE privilege, using any SQL client tool, such as DBeaver or Postico.

2. Run the following command to create the bookkeeping schema:

   CREATE SCHEMA hevo;
   

   Ensure that the schema name is lowercase. By default, Amazon Redshift stores identifiers in lowercase, so both CREATE SCHEMA hevo and CREATE SCHEMA HEVO result in a schema named hevo. The enable_case_sensitive_identifier parameter changes this behavior. When set to TRUE, Amazon Redshift preserves the case of identifiers enclosed in double quotes. For example, CREATE SCHEMA "HEVO" results in a schema named HEVO.

   Activate treats uppercase and lowercase letters in the schema name as the same during the connection test, but uses the lowercase name when sending data. As a result, a schema named HEVO passes the connection test but causes syncs to fail.

Note: Do not place your own data in the hevo schema. Activate reserves it for bookkeeping and manages the tables inside it, so anything you store there can be changed.

Once the schema is created, you must grant the Hevo user access to it.

Create a Database User and Grant Privileges

Hevo does not need a superuser to connect to your Amazon Redshift database. You can create a user with only the privileges that Activate requires, which are read access to the data your datasets return, and write access to the schema you created in the Create the Bookkeeping Schema section.

The following table lists the privileges that the database user for Hevo requires:

Perform the following steps to create the user and grant it the required privileges:

1. Connect to your Amazon Redshift database as a superuser or a user with the CREATE privilege, using any SQL client tool, such as DBeaver or Postico.

2. Run the following command to create the user:

   CREATE USER hevo_activate PASSWORD '<password>';
   

   Note: Replace the placeholder value in the command above with your own. The password must be 8-64 characters long and contain at least one uppercase letter, one lowercase letter, and one number.

3. Run the following commands to grant the user read access to a schema that your datasets read from:

   GRANT USAGE ON SCHEMA <schema_name> TO hevo_activate;
   GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO hevo_activate;
   ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO hevo_activate;
   

   Note:

   • Replace the placeholder value in the commands above with your own. For example, <schema_name> with analytics.

   • If your datasets read from more than one schema, run these commands again for each additional schema. The ALTER DEFAULT PRIVILEGES command applies only to the schema you specify in it, and grants access to the tables created in that schema later.

   • If a view in your schema reads from a table in another schema, grant access to that schema as well. Otherwise, the dataset query fails even though the view itself is readable.

4. Run the following commands to grant the user write access to the bookkeeping schema:

   GRANT ALL ON SCHEMA hevo TO hevo_activate;
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA hevo TO hevo_activate;
   ALTER DEFAULT PRIVILEGES IN SCHEMA hevo GRANT ALL PRIVILEGES ON TABLES TO hevo_activate;
   

5. (Optional) If your dataset queries call user-defined functions, run the following commands:

   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <schema_name> TO hevo_activate;
   ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT EXECUTE ON FUNCTIONS TO hevo_activate;
   

The ALTER DEFAULT PRIVILEGES command applies only to the tables that you create. If another user creates the tables that your datasets read from, the Hevo user loses its access each time those tables are dropped and created again. To prevent this, connect to your Amazon Redshift database as a superuser, and run the following command to grant the access to the tables that the other user creates later:

ALTER DEFAULT PRIVILEGES FOR USER <owner_username> IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO hevo_activate;

Note: Replace the placeholder values in the command above with your own. For example, <owner_username> with the user that runs your dbt models.

(Optional) Retrieve the Database Hostname and Port Number