# Redshift

## Setup

The following credentials keys are accepted:

* `host` **(required)** -> The hostname / ip of the instance
* `user` **(required)** -> The username to access the instance
* `database` **(required)** -> The database name of the instance
* `aws_bucket` **(required)** -> The name of the S3 Bucket for Bulk Loading / Unloading
* `aws_access_key_id` (optional) -> The AWS Access Key ID to access the bucket for Bulk Loading / Unloading
* `aws_secret_access_key` (optional) -> The AWS Secret Key to access the bucket for Bulk Loading / Unloading
* `aws_session_token` (optional) -> The AWS Session token to access the bucket for Bulk Loading / Unloading
* `aws_role_arn` (optional since *v1.4.21*) -> The AWS Role to use to access the bucket for Bulk Loading / Unloading
* `schema` (optional) -> The default schema to use when loading
* `password` (optional) -> The password to access the instance
* `port` (optional) -> The port of the instance. Default is `5439`.
* `ssh_tunnel` (optional) -> The URL of the SSH server you would like to use as a tunnel (example `ssh://user:password@db.host:22`)
* `ssh_private_key` (optional) -> The private key to use to access a SSH server (raw string or path to file).
* `ssh_passphrase` (optional) -> The passphrase to use to access a SSH server.

### Using `sling conns`

Here are examples of setting a connection named `REDSHIFT`. We must provide the `type=redshift` property:

{% code overflow="wrap" %}

```bash
$ sling conns set REDSHIFT type=redshift host=<host> user=<user> database=<database> password=<password> port=<port> aws_bucket=<aws_bucket> aws_access_key_id=<aws_access_key_id> aws_secret_access_key=<aws_secret_access_key>

# OR use url
$ sling conns set REDSHIFT url="redshift://myuser:mypass@host.ip:5439/mydatabase" aws_bucket=<aws_bucket> aws_access_key_id=<aws_access_key_id> aws_secret_access_key=<aws_secret_access_key>
```

{% endcode %}

### Environment Variable

See [here](https://docs.slingdata.io/sling-cli/environment#dot-env-file-.env.sling) to learn more about the `.env.sling` file.

{% code overflow="wrap" %}

```bash
export REDSHIFT='redshift://myuser:mypass@host.ip:5439/mydatabase'
export AWS_BUCKET='<aws_bucket>'
export AWS_ACCESS_KEY_ID='<aws_access_key_id>'
export AWS_SECRET_ACCESS_KEY='<aws_secret_access_key>'

export REDSHIFT='{ type: redshift, url: "redshift://myuser:mypass@host.ip:5439/mydatabase", aws_bucket: "<aws_bucket>", aws_access_key_id: "<aws_access_key_id>", aws_secret_access_key: "<aws_secret_access_key>" }'
```

{% endcode %}

### Sling Env File YAML

See [here](https://docs.slingdata.io/sling-cli/environment#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
connections:
  REDSHIFT:
    type: redshift
    host: <host>
    user: <user>
    password: <password>
    port: <port>
    database: <database>
    schema: <schema>
    aws_bucket: <aws_bucket>
    aws_access_key_id: <aws_access_key_id>
    aws_secret_access_key: <aws_secret_access_key>

  REDSHIFT_URL:
    url: "redshift://myuser:mypass@host.ip:5439/mydatabase"
    aws_bucket: <aws_bucket>
    aws_access_key_id: <aws_access_key_id>
    aws_secret_access_key: <aws_secret_access_key>
```

## Database user creation

To allow Sling to access your database, we need to create a user with the proper privileges. Please follow the steps below:

1. Create a user `sling` (or whatever you prefer) by running:

   ```sql
   CREATE USER sling WITH PASSWORD '<password>';
   ```
2. If you are planning to load data into this connection, grant the following privileges to that user:

   ```sql
   GRANT CREATE ON DATABASE <database_name> TO sling;
   ```
3. If you are planning to extract data from this connection, you need to give permission to read the tables you'd like Sling to extract.

   ```sql
   -- Need this to read table & column names 
   GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO sling;
   GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO sling;

   -- run this to grant SELECT permission to all tables in schema `marketing` to user sling
   GRANT SELECT ON ALL TABLES IN SCHEMA marketing TO sling;
   ```

If you are facing issues connecting, please reach out to us at <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).