# Redshift

With Codatum, you can connect, query, and manage data of Redshift.

{% hint style="warning" %}
This feature is currently in preview. Functionality and availability may change without notice.

We'd love your feedback.
{% endhint %}

## How to Set Up

Redshift integration targets RA3 node clusters and serverless configurations.

### 1. Necessary preparations in AWS Console

#### 1. Grant the Necessary Privileges

Create a role for integration and grant privileges.

* Grant `USAGE` privilege on the schema you want to use to that role
* Grant `SELECT` privilege on the tables you want to use to that role
* Grant `SELECT` privilege on the system view `svv_table_info` to that role

#### 2. Create Secret in Secrets Manager

* Specify the Data Warehouse you want to connect to and create a Secret
* Add a tag named `RedshiftDataFullAccess` to the created Secret (Value can be empty)

#### **3. Create IAM User and Grant Policies**

* Create an IAM user for integration and grant the following policy

```json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "DataAPIPermissions",
            "Effect": "Allow",
            "Action": [
                "redshift-data:BatchExecuteStatement",
                "redshift-data:ExecuteStatement",
                "redshift-data:CancelStatement",
                "redshift-data:ListStatements",
                "redshift-data:GetStatementResult",
                "redshift-data:DescribeStatement",
                "redshift-data:ListDatabases",
                "redshift-data:ListSchemas",
                "redshift-data:ListTables",
                "redshift-data:DescribeTable"
            ],
            "Resource": "*"
        },
        {
            "Sid": "SecretsManagerPermissions",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
                "Specify the Secret ARN created in step 1-2"
            ],
            "Condition": {
                "StringLike": {
                    "secretsmanager:ResourceTag/RedshiftDataFullAccess": "*"
                }
            }
        },
        {
            "Sid": "ServiceLinkedRole",
            "Effect": "Allow",
            "Action": "iam:CreateServiceLinkedRole",
            "Resource": "arn:aws:iam::*:role/aws-service-role/redshift-data.amazonaws.com/AWSServiceRoleForRedshift",
            "Condition": {
                "StringLike": {
                    "iam:AWSServiceName": "redshift-data.amazonaws.com"
                }
            }
        }
    ]
}
```

#### **4. Additional Steps Required Only for Serverless**

* Add a tag named `RedshiftDataFullAccess` to the Workgroup you want to integrate with (Value can be empty)
* Create the following policy in IAM and grant the following policy

```json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "ServerlessWorkgroupInfo",
            "Effect": "Allow",
            "Action": [
                "redshift-serverless:GetWorkgroup"
            ],
            "Resource": "*"
        }
    ]
}
```

### 2. Register Redshift connection in Codatum

Finally, create a Redshift connection in Codatum.

* Select `Add Connection` from `Connections` in `Workspace Settings`
* Enter `Connection Name`
* Select `Access Level`
* Enter `Account ID`
  * This is your AWS account ID.
* Enter `Region`
  * This is the region where your Redshift resources are located.
* Select `Connection Type`
  * If you select Cluster:
    * Enter `Cluster Identifier`
    * Enter `Default Database`
      * This is the target database for connection. This setting does not restrict queryable databases, schemas, or tables.
  * If you select Serverless:
    * Enter `Serverless Namespace`
    * Enter `Serverless Workgroup Name`
    * Enter `Default Database`
      * This is the target database for connection. This setting does not restrict queryable databases, schemas, or tables.
* Enter `AWS Access Key`
  * Use the access key of the IAM user prepared in Step 1.
* Enter `AWS Secret Access Key`
  * Use the secret access key of the IAM user prepared in Step 1.
* Enter `Secret ARN`
  * Use the Secret ARN of the Secret prepared in Step 1.

Once you have completed the input, perform a connection test and then save the connection.