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
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;