Overview

Amazon Redshift can either be used as a data source or a data destination in Mitto.

Mitto and Amazon Redshift

Source plugin example: Query

Destination plugin examples: CSV, Salesforce, SQL

Amazon Redshift as a data destination

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

Amazon Redshift specific setup

Below is the database url structure for connecting to a Snowflake 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:

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

AWS IAM and S3 setup

Mitto uses COPY when using Amazon Redshift as a data destination. COPY requires additional AWS IAM and S3 credentials:

  • S3 access key
  • S3 secret key
  • S3 bucket

Mitto requires an AWS IAM user with Programmatic access for the access key and secret key.

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

{
    "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 Mitto will use for data transfer.

Example IO Job Output

"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 { }.

SQL

Mitto can send SQL statements to an Amazon Redshift database. Use Amazon Redshift syntax in these Mitto SQL jobs.

Relevant Articles:

How to Load Data From an Amazon S3 Bucket Into Amazon Redshift | Zuar
See how to load data from an Amazon S3 bucket into Amazon Redshift. We’ll cover using the COPY command to load tables in both singular and multiple files. with some options available with COPY that allow the user to handle various delimiters, NULL data types, and other data characteristics.
Amazon Redshift vs. Amazon Simple Storage Solutions (S3) | Zuar
Data storage procurement decisions can significantly impact the overall cost and performance that comes with querying said data. This means that it is essential for individuals and businesses to determine which platform or platforms will provide the most effective data storage solutions. While some …