# Amazon Redshift

## Overview

[Amazon Redshift](https://aws.amazon.com/redshift/) can either be used as a data source or a data destination in Zuar Runner.

.. image:: assets/redshift-1.png
   :alt: Zuar Runner and Amazon Redshift

Source plugin example: [Query](/jobs/query)

Destination plugin examples: [CSV](/jobs/csv), [Salesforce](/connectors/salesforce), [SQL](/jobs/sql)

## Amazon Redshift as a Data Destination

- Zuar Runner automatically creates the Amazon Redshift database schema if it doesn't exist
- Zuar Runner automatically creates the Amazon Redshift database tables if they don't exist
- Zuar Runner automatically determines data types for Amazon Redshift columns
- Zuar Runner automatically adds new columns to Amazon Redshift tables based on new fields in source systems 
- Zuar Runner automatically adjusts Amazon Redshift tables based on changes in source data

## Amazon Redshift Specific Setup

Below is the [database url](/databases/database-urls) structure for connecting to a Amazon Redshift database:

`redshift+psycopg2://<username>:<password>@<hostname>:5439/<database>`

Here's an example of using an Amazon Redshift database as a destination in a CSV job:

.. image:: assets/amazon_redshift__output.png
   :alt: Redshift Zuar Runner Output

.. NOTE::
   
   When outputting to an Amazon Redshift database, 
   leaving the "Schema" blank will create a table in the **public** schema.

    

## Additional Redshift Required Key Values

Click **DONE** after complete the _Specify the output_ screen. You will be taken to the job page. You now need to edit the job to add some additional key values that are unique to Redshift. Here is an example output with the required key values:

```json
"output": {
            "dbo": "redshift+psycopg2://{username}:{password}@{host}:5439/{database}",
            "s3_access_key": "{access_key}",
            "s3_secret_key": "{secret_key}",
            "bucket": "{bucket}",
            "schema": "{schema}",
            "tablename": "{tablename}",
            "use": "call:mitto.iov2.db#todb"
        },
```
*Replace {values} with your specific values without { }.*

## AWS IAM and S3 Setup

Zuar Runner uses [COPY](https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html) when using Amazon Redshift as a data destination. COPY requires additional AWS IAM and S3 credentials:

- S3 access key
- S3 secret key
- S3 bucket

Zuar Runner requires an [AWS IAM](https://aws.amazon.com/iam/) user with [Programmatic access](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_users_create.html) for the access key and secret key.

Here is an example S3 policy that can be attached to the AWS IAM user:

```json
    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Action": [
                    "s3:*"
                ],
                "Effect": "Allow",
                "Resource": [
                    "arn:aws:s3:::bucket.name.zuar.com",
                    "arn:aws:s3:::bucket.name.zuar.com/*"
                ]
            }
        ]
    }
```

*Replace bucket.name.zuar.com with the name of the S3 bucket Zuar Runner will use for data transfer.*

## SQL

Zuar Runner can send [SQL](/jobs/sql) statements to an Amazon Redshift database. Use [Amazon Redshift syntax](https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-sql.html) in these Zuar Runner SQL jobs.