Amazon Redshift

Getting Started with Redshift Destination Configuration

Requirements:

• Active Redshift cluster.

• Allow connections from Databrain to your Redshift cluster.

  • For details on setting up IP whitelisting and ensuring secure connectivity, refer to our guide on

Allow Access to our IP

• Choose the Databrain Workspace to which you wish to connect the data.

Setup Guide:

1. Ensure Cluster Accessibility:

   • Make sure your cluster is active and accessible from the machine running Databrain.

   • This is dependent on your networking setup. The easiest way to verify if Databrain is able to connect to your Redshift cluster is via the Add a Data Source UI.

   • For a deeper dive on configuring your cluster's access, you can consult the AWS Redshift documentation.

2. Grant Necessary Permissions:

   • Read Access on Tables and information_schema: Grant read access permissions to the tables within Redshift as well as the information_schema schema. This allows Databrain to retrieve necessary information and replicate data accurately. You can assign appropriate permissions to the user or role accessing Redshift to enable reading from tables and accessing metadata stored in the information_schema. -- Grant SELECT permission on all tables in the specified schema GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>

3. Fill Up Connection Info:

   • Provide the necessary information to connect to your cluster.

     • Host Endpoint: [Part of the connection string or Endpoint accessible without the port and database name. It typically includes the cluster-id, region, and ends with redshift.amazonaws.com]

     • Port: [Port of the database, e.g., 5439]

     • Database Name: [Name of the database you want to sync data into]

     • Username: [Username to access the database]

     • Password: [Password associated with the username]

     • SSL Mode: [Specify if SSL mode is required and the type of authentication to be used.]

   • Note on Schema Configuration:

     • Default Schema: Public. This is the default schema tables are written to if the source does not specify a namespace. Unless specifically configured, the usual value for this field is "public".

Encryption:

• In the case of a private instance, SSH credentials should also be provided for authentication purposes along with Redshift Credentials.

Permissions:

• Permission to read information_schema.

• Whitelist the IP address.

• Grant read access to the schema (usage) and tables, noting that access to only tables may not suffice in certain databases.

Replace the placeholders inside the square brackets with actual values when filling in the details.

Locating the Configuration Details in Redshift

1. Destination Name:

   • This is a custom name you decide for identification within Databrain. Choose a descriptive name based on your use case.

2. Host Endpoint (Endpoint):

   • Navigate to the AWS Management Console and open the Amazon Redshift dashboard.

   • Select the desired cluster.

   • In the "Cluster Details" or "Properties" tab, you'll find the "Endpoint" listed.

3. Port:

   • Typically, this is 5439 for Amazon Redshift. Confirm this in the "Cluster Details" or "Properties" tab where you found the Endpoint.

4. Database Name:

   • This is the database name you specified when creating the cluster. View it under the "Configuration" details in the Redshift dashboard.

5. Username & Password:

   • These are the credentials set during cluster creation or when creating a specific user in the database. If you're an admin, you can manage and view users using SQL client tools connected to Redshift.

6. SSL Mode:

   • Amazon Redshift supports SSL connections. Check your cluster's security configurations or consult with your DBA to determine if you require SSL or the specific SSL mode used.

7. Default Schema:

   • As noted, the default schema in Amazon Redshift is typically "public". Verify and manage schemas using SQL client tools connected to your Redshift cluster.