pushing data from MSK to Redshift using EventBridge Pipe

I apologize, but there seems to be a misunderstanding in your question. Amazon Redshift does not have a feature called "Event Pipe" for ingesting data from Kafka. However, Amazon Redshift does support streaming ingestion directly from Apache Kafka sources, including self-managed Kafka instances, without the need for an intermediary service.

To stream JSON data from your self-managed Kafka instance to Amazon Redshift, you can use Amazon Redshift's streaming ingestion feature. Here's a general approach to set this up:

1. First, you need to create an external schema in Redshift that references your Kafka cluster as the data source. This is done using the CREATE EXTERNAL SCHEMA command.

2. Next, you create a materialized view in Redshift to consume the stream data from your Kafka topic. This materialized view will automatically ingest and store the data from Kafka.

3. The materialized view can be created with a SQL statement similar to this:

   CREATE MATERIALIZED VIEW your_mv_name AUTO REFRESH YES AS SELECT kafka_partition, kafka_offset, JSON_PARSE(kafka_value) as Data FROM your_schema."your_topic_name" WHERE CAN_JSON_PARSE(kafka_value);

In this query, replace 'your_mv_name' with the name you want to give your materialized view, 'your_schema' with the name of the external schema you created, and 'your_topic_name' with the name of your Kafka topic.

The JSON_PARSE function is used to parse the JSON data from Kafka, and the CAN_JSON_PARSE function is used to guard against errors from malformed JSON.

This approach allows you to stream data directly from your Kafka instance to Redshift without needing to stage the data in S3 first, providing low-latency, high-speed ingestion of your streaming data.

Remember to configure the necessary authentication and network settings to allow Redshift to connect to your self-managed Kafka instance. Depending on your setup, you might need to use mTLS authentication and configure your VPC appropriately.
Sources
Best practices to implement near-real-time analytics using Amazon Redshift Streaming Ingestion with Amazon MSK | AWS Big Data Blog
Simplify data streaming ingestion for analytics using Amazon MSK and Amazon Redshift | AWS Big Data Blog
Getting started with streaming ingestion from Apache Kafka sources - Amazon Redshift

Sorry for confusion, but I was referring EventBridge Pipes when mentioned Event Pipe...