Redshift

Mage doesn’t directly stream data into Redshift. Instead, Mage can stream data to Kinesis. You can configure streaming ingestion for your Amazon Redshift cluster and create a materialized view using SQL statements.

You can follow the guide from AWS to set it up.

Or you can follow the steps below to set up the materialized view.

​

1. Create and attach IAM role to Redshift cluster

Create an IAM Role which has the policies AmazonKinesisReadOnlyAccess and AmazonRedshiftFullAccess. Go to the Redshift cluster’s Properties tab. In the Cluster permissions section, attach the IAM role to the Redshift cluster.

​

2. Create an external schema from Kinesis

Go to Redshift cluster’s query editor, execute the command to create the schema:

CREATE EXTERNAL SCHEMA redshift_stream_test
FROM KINESIS
IAM_ROLE <kinesis-redshift-iam-role-arn>;

​

3. Create a materialized view which loads data from Kinesis stream

Execute the command in query editor to create the materialized view from the kinesis stream:

CREATE MATERIALIZED VIEW mage_kinesis_redshift_stream
AUTO REFRESH YES
AS
SELECT
    *,
    json_parse(kinesis_data) as payload
FROM redshift_stream_test."kinesis-stream-name"

You can change the materialized view name to the name you want to use.

​

4. Query data in the materialized view.

Execute the command to query the data:

SELECT * FROM mage_kinesis_redshift_stream;