AWS Redshift Integration

Amazon Redshift is a fully managed data warehouse service in the cloud. It is built on Amazon Web Services

Steps

• Step 1: Whitelist Locale’s IP Addresses
• Step 2: Create a User and Grant Privileges
• Step 3: Retrieve the Hostname and Port Number (Optional)
• Step 4: Configure Amazon Redshift as a Data Connection on Locale

Step 1: Whitelist Locale’s IP Addresses

You need to whitelist the IP address to enable Locale to connect to your Amazon Redshift database.

To do this, you need to add the inbound and outbound rules for your database, and configure a virtual private cloud (VPC):

Add Inbound and Outbound Rules

1. Log in to the Amazon Redshift dashboard.
2. In the left navigation pane, click Clusters.
3. Click the Cluster that you want to connect to Locale

4. In the Properties tab, Network and security settings, click the link text under the VPC security group to open the Security Groups page

5. In the Security Groups page, click the Inbound rules tab, and then, click Edit inbound rules.

6. In the Edit inbound rules page:

7. ‍

          Click Add rule.

          In the Type column, select Redshift from the drop-down.

          In the Port Range column, enter the port of your Amazon Redshift cluster. Default value: 5439.

          In the Source column, select Custom from the drop-down and enter Locale’s IP addresses for your region.

          Click Save rules

          In the Security Groups page, click the Outbound rules tab, and then, click Edit outbound rules

8. Repeat Step 6 in the Outbound rules page to configure outbound rules.

‍

Step 2: Create a User and Grant Privileges

1. Create a user (optional)

1. Log in to your Amazon Redshift database as a superuser or a user with CREATE privilege.
2. Enter the following command:
3. `CREATE USER <USER_NAME> WITH PASSWORD '<password>';
4. Example
5. CREATE USER locale WITH PASSWORD '<password>';`

2. Grant privileges to the user

1. Log in to your Amazon Redshift database as a superuser.
2. Grant the required privileges as follows:
   1. Grant CREATE and USAGE privileges to the database user for an existing database:
   2. `# Grant privileges to the database user that you created earlier
   3. GRANT CREATE ON DATABASE <database_name> TO <USER_NAME>;
   4. Grant privileges to the database user on the schema.
   5. GRANT CREATE ON SCHEMA <SCHEMA_NAME> TO <USER_NAME>; GRANT USAGE ON SCHEMA <SCHEMA_NAME> TO <USER_NAME>;`
   6. Grant SELECT privilege to the database user on specific tables:
   7. GRANT SELECT ON TABLE <schema_name>.<table_name> TO <USER_NAME>; # specific table

Step 3: Retrieve the Hostname and Port Number (Optional)

1. Log in to the Amazon Redshift dashboard.
2. In the left navigation pane, click Clusters.
3. Click the Cluster that you want to connect to Locale
4. On the Clusters page, locate the Endpoint.
   Use this Endpoint URL as the Host Address inside Locale (Remove the port number and the database name as they are given separately). An example of how the endpoint will look like is given below -
5. <cluster-name>.<identifier>.<region>.redshift.amazonaws.com

Step 4: Configure Amazon Redshift as a Data Connection

Perform the following steps to configure Amazon Redshift as an Activate Warehouse:

1. Go to Data Connections and select Redshift
2. Fill in the basic details we configured in the previous steps
3. Click on Test Connection and wait for it to be autheticated
4. Once the connection is tested, you can then click on Save Connection and start using it to create your alerts.

Note: Locale will cancel queries that run for more than 90 seconds. This is to protect your database from running rogue queries and also to prevent a backlog of alerts in the Locale system. If you have a use case where this needs to be increased then please get in touch with our Support.

Happy Alerting!