Note: The following steps must be performed by an Admin user or a user with permissions to create an instance on AWS. Permissions and roles are managed through the IAM page in AWS.

1. Log in to your AWS instance

Do one of the following:

• Create an AWS account

  Note: You can also use the AWS Free Tier that offers you a two month free trial of Amazon Redshift.

  1. Go to aws.amazon.com and click Create an AWS account.

     

  2. On the Sign up for AWS page, specify the following:

     

     • Root user email address: A valid email address that can be used to manage the AWS account. The root user serves as the account admin.

     • AWS account name: A unique name for your AWS account. You can change this later.

  3. Click Verify email address and check for a verification email in your inbox.

• Sign-in to an existing AWS instance

  1. Go to aws.amazon.com and click Sign In to the Console.

     

  2. Select the Root user option to log in as the account admin (root user) or the IAM user to log in as a user with IAM role-based permissions. The account admin may create roles based on AWS’ IAM policies to define permissions on a database, and assign these to users.

  3. Specify your email address, and then click Next.

     

  4. Provide the root user password.

  5. Click Sign in.

2. Connect to Amazon Redshift

1. On the AWS Console Home page, click Services icon at the top left.

   

2. Select Analytics in the left navigation pane, and then select Amazon Redshift in the right navigation pane.

   

3. On the Complete sign-up page, click Complete your AWS Registration.

4. Complete the required steps to create your AWS account.

The core component of the Amazon Redshift data warehouse is a Cluster. Your databases are created within a cluster.

1. Create a Cluster

1. Log in to the Amazon Redshift dashboard.

2. Click the hamburger menu at the top left corner of the dashboard.

   

3. In the left navigation pane, click Clusters.

   

4. In the In my account tab, Clusters section, click Create cluster.

   

5. On the Create cluster page, Cluster configuration section, specify the following:

   

   • Cluster identifier: A unique name for your cluster.

   • Node type: The primary data processing purpose for which the node is to be used.

     • Select RA3 if storage is the priority.

     • Select Dense Compute 2 or DC2 if you want to use more of compute capabilities.

     • Select RA3 for the best of storage and compute capabilities.

     Each node type has a different cost depending on the storage and compute resources, as described in the Configuration summary.

   • AZ configuration: The deployment setting for your cluster across different availability zones (AZ).
     • Select Single-AZ if you want to deploy your cluster in a single availability zone.
     • Select Multi-AZ if you want to deploy your cluster in two availability zones.

   • Number of nodes: The number of nodes, based on the amount of data or computational processing you require. Select 1 to start with. You can increase this value later.

     Note: For the RA3 node type, you must select at least two nodes.

6. In the Database configurations section, select Manually add the admin password and specify the following:

   

   • Admin user name: A unique name for the admin of the database you plan to create.

   • Admin user password: The password for the admin user.

7. In the Additional configurations section, you can use the default settings provided by AWS or disable the Use defaults option and specify any additional settings you need.

   

8. Click Create cluster. You can view the new cluster in the Clusters section. Once the cluster configurations are complete, the status changes to Available.

   

9. (Optional) Click on the cluster to view its details.

2. Make your Redshift cluster publicly accessible

This is required to be able to connect to your Redshift cluster and create a database.

1. Log in to the Amazon Redshift dashboard.

2. Click the hamburger menu at the top left corner of the dashboard.

   

3. In the left navigation pane, click Clusters.

   

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

   

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

   

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

   

3. Connect to the Redshift Warehouse from an external tool

You can use any tool that connects to Amazon Redshift, such as DBeaver or Postico, to create your Redshift database, schemas, and tables. These tools allow you to query your database and manage your data efficiently. Alternatively, use the Amazon Redshift Query Editor V2.

Note: The steps in this section have been performed using DBeaver.

1. On <Cluster Name> page, do the following:

   

   • Click the copy ( ) icon corresponding to the Endpoint to copy it.

     Remove the :<portnumber>/<databasename> parts from the endpoint to obtain the database host.

     For example, if exampleendpoint.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev is the endpoint, use exampleendpoint.abc123xyz789.us-west-2.redshift.amazonaws.com as the database host.

   • Navigate to the Properties tab and copy the Port.

2. Access DBeaver.

3. Click the New Database Connection ( ) icon at the top left, and then search and select Redshift.

   

4. Click Next.

5. In the Connection Settings window, specify the following:

   

   • Host/Instance: The endpoint obtained in Step 1 above.

   • Port: The port number obtained in Step 1 above.

   • Database: The Redshift database to which you want to connect. The default database created by AWS for Redshift is dev.

   • Username: The admin user you created for the Redshift database.

   • Password: The password for the admin user.

6. Click Finish.

4. Create the Redshift database

1. In the left pane under Database Navigator, right-click your Redshift cluster, and click SQL Editor.

   

2. Enter the following SQL query to create the database:

   CREATE DATABASE <database_name>
   
   // Example
   
   CREATE DATABASE test_analytics
   

   

3. Press CMD + Enter (Mac) or CTRL + Enter (Windows) to execute the SQL query.

4. In the left pane, under Database Navigator, right-click your Redshift cluster, and click Edit Connection.

5. Update the Database name to the newly created database, and then click OK.

6. In the Connection changed pop-up dialog, click Yes to access the new database.

7. Run the following SQL query to create tables in the database:

   CREATE TABLE <table_name> (
       column1_name data_type constraints,
       column2_name data_type constraints,
       ...
   );
   
   // Example
   
   CREATE TABLE employees (
       employee_id INT PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       department VARCHAR(50),
       salary DECIMAL(10, 2)
   );
   

8. Run the following SQL query to insert a new row:

   INSERT INTO <table_name> (column1_name, column2_name, ...)
   VALUES (value1, value2, ...);
   

9. Run the following SQL query to add a new column to an existing table:

   INSERT INTO <table_name> (column1_name, column2_name, ...)
   VALUES (value1, value2, ...);
   

10. The rows and columns subsequently define the schema of the table. Similarly, create all the tables that you require.

5. View the database in your Amazon Redshift cluster

1. Log in to the Amazon Redshift dashboard.

2. Click the hamburger menu at the top left corner of the dashboard.

   

3. In the left navigation pane, click Clusters.

   

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

   

5. On the <Cluster Name> page, click the Databases tab, and then click Connect to database.

   

6. On the Connect to database page, specify the following:

   

   • Connection: Select Create a new connection.

   • Authentication: Select Temporary credentials if you want to generate your access credentials based on your assigned IAM role. Temporary credentials need to be generated each time you connect to the database. To use a previously saved key (password), select AWS Secrets Manager.

   • Database name: The database you are connecting to. For example, test_analytics.

   • Database user: The user you want to connect as. For example, root.

7. Click Connect.

You can access the different tabs of the Database Objects section to view the objects, tables and views within the database:

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

2. Enter the following command:

   CREATE USER <database_username> WITH PASSWORD '<password>';
   

   Note: Replace the placeholder values in the command above with your own. For example, <database_username> with hevouser.

1. Log in to the Amazon Redshift dashboard.

2. Click the hamburger menu at the top left corner of the dashboard.

   

3. In the left navigation pane, click Clusters.

   

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

   

5. On <Cluster Name> page, do the following:

   

   • Click the copy ( ) icon corresponding to the Endpoint to copy it.

     Remove the :<portnumber>/<databasename> part from the endpoint to obtain the database cluster identifier, and use it while configuring your Destination in Hevo.

     For example, if examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev is the endpoint, use examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com as the identifier.

   • Navigate to the Properties tab and copy the Port.