Redshift Setup Guide

Prerequisites

This page guides you through the process of setting up the Redshift destination connector.

You'll have to do the following:

• Make sure that you have an up an running Redshift (either serverless or cluster)

• Make sure you have sufficiant privilages in order to create a Redshift account

Get Endpoint(Hostname), Port, and Database.

1. Navigate to Redshift's console
2. Follow the next step according to your Redshift type:

• Cluster - In the left menu click Clusters
• Serverless -

  • In the left menu click Redshift Serverless
  • look for Namespaces / Workgroups and choose your workgroup

3. Select the cluster you want Matia to connect to.

4. Locate the Endpoint field in the General Information pane. Copy it (It includes the port and database as well). Separate the Endpoint into 3 components(Hostname, Port, and Database).

   • The format is: hostname:port/database
   • Be sure to separate the port and remove the preceding colon (:) from the host string.

5. Then, fill the fields in forms with the corresponding component.

Allow Matia to connect

1. In the Redshift console, click on "Clusters".

2. Select the cluster you want Matia to connect to.

3. Click on "Properties".

4. Scroll down to the "Network and security settings section".

5. In the VPC security group field, click on the security group that is linked to your Redshift and open it.

6. In the "Security Groups" window, click on "Inbound rules".

7. In the "Edit Inbound rules" window, follow the steps below to create custom TCP rules for each of Matia's IPs in your region:

• Select Custom TCP in the drop-down menu.
• Enter your Redshift port number.
• Enter Matia IP addresses from the following list:
  • 52.20.96.22/32
  • 23.21.86.124/32
  • 44.219.180.239/32

Setup Guide (Username, Password)

• The following commands' purpose is to create a new Redshift user and provide it to Matia
• Run the following commands

1. Create a user and grant permissions to the source schema.

Notes:
i. For each schema you want to read data from, repeat the steps below for <"your schema">
ii. In Redshift if there are views in your schema that reference tables in other schema, you will also need to give Matia read access to those other schema.

-- Give the matia user the ability to sign in with a password
CREATE USER matia WITH PASSWORD '<strong, unique password>';

-- Let the matia user see this schema
GRANT USAGE ON SCHEMA "<your schema>" TO matia;

-- Let the matia user read all existing tables in this schema
GRANT SELECT ON ALL TABLES IN SCHEMA "<your schema>" TO matia;

-- Let the matia user read any new tables added to this schema
ALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT SELECT ON TABLES TO matia;

-- Let the matia user execute any existing functions in this schema
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "<your schema>" TO matia;

-- Let the matia user execute any new functions added to this schema
ALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT EXECUTE ON FUNCTIONS TO matia;

2. Create audit schema.

-- create a SCHEMA that Matia uses for operational purposes.
CREATE SCHEMA MATIA_AUDIT_RETL;
-- Give the matia user full access to the bookkeeping schema
GRANT ALL ON SCHEMA MATIA_AUDIT_RETL TO MATIA;
-- Ensure the matia user has access to any objects that may have already existed in the Matia audit schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA MATIA_AUDIT_RETL TO matia;

General Settings

After the commands above were performed you will be able to go ahead and provide the parameters of you newly existing resources.

• Enter the Username for the Redshift source.

• Enter the Password for the Redshift source.

• Enter the Hostname for the Redshift source.

• Enter the port for the Redshift source.

• Enter the Database for the Redshift source.

• Enter a Asset Name of your preference.

• (Optional) Enter a Description of your preference.

• Click Connect.