Skip to main content
Version: 3.0 (next)

MaestroHub and Amazon Redshift

Write rows to Redshift over the Data API, bulk-load Parquet from S3 with COPY, or let Firehose deliver, then read results back to the line.

Connectors
Amazon Redshift · S3 · Amazon Data Firehose
Start with
B · Parquet in S3, then COPY
Network
Outbound HTTPS 443, or 5439 for a direct connection

The Amazon Redshift connector writes rows, runs SQL, and loads from S3 with COPY. Redshift is fastest with bulk loads, so most plants write Parquet files to S3 and call Copy From S3 in the same pipeline.

Find your route​

Answer a few questions; the drawing below follows your answer.

Where does the data go?
How fresh must it be?
What can the plant reach?

AWrite rows. Moderate volumes, or rows that change.

Architecture​

Context Engine runs next to your machines, and every route starts or ends there. Select a route to trace it.

Into Amazon Redshift
Back to the plant
PLANTYOUR NETWORK OR CLOUD, OPTIONALAMAZON REDSHIFTSourcesPLCs, OPC UAMQTT, MESHistoriansShop floorUNS, setpointsTeams, SlackContext Enginepipelines, UNSTablesand viewsAAmazon RedshiftWrite, ExecuteData API over HTTPS 443Redshiftprovisioned or ServerlessBParquet to S3then Copy From S3Amazon S3Parquet, partitioned by timeCOPYbulk load from S3CData FirehosePut RecordsFirehose streamstages in S3, then COPYRedshiftloaded by FirehoseDAmazon RedshiftQueryData API over HTTPS 443Redshiftruns each query
  • Set up in MaestroHub
  • Your infrastructure
  • Set up in Amazon Redshift
BLand in S3, then COPY. Minutes, as often as you copy. The cluster runs each COPY. What each side sets up

Routes​

A

Into Amazon Redshift

Write rows​

For moderate rates and records
Freshness
Per call, seconds
Amazon Redshift compute
The cluster, or Serverless capacity while it runs
Delivery
Retried on failure; use MERGE so a retry cannot duplicate

In MaestroHub

  1. Create an Amazon Redshift connection over the Data API with IAM.
  2. Use Write, which creates or widens the table to fit.
  3. Batch rows with a Buffer or Aggregator node so each call carries many rows.

In Amazon Redshift

  1. An IAM principal allowed to call the Data API on the cluster or workgroup, and database grants on the target schema.

Choose it when Moderate volumes, or rows that change.

Look elsewhere when High volume: Redshift prefers bulk loads. Use B.

Redshift guide

B

Into Amazon Redshift

Land in S3, then COPY​

Start here for machine data
Freshness
Minutes, as often as you copy
Amazon Redshift compute
The cluster runs each COPY
Delivery
Copy each file once; track loaded paths

In MaestroHub

  1. Batch rows with a Buffer or Aggregator node so each call carries many rows.
  2. Add Convert to File with format Parquet and a declared column schema.
  3. Write with the S3 node, Input Format base64.
  4. Call the Redshift Copy From S3 function with the same path. Both steps run in one pipeline.

In your infrastructure

  1. An S3 bucket for the landed files.

In Amazon Redshift

  1. An IAM role attached to the cluster with read access to the bucket, and the target table.

Choose it when You want the cheapest bulk route into Redshift and minutes are fine.

Look elsewhere when You need seconds: use A or C.

Redshift guideConvert to FileS3 guide

C

Into Amazon Redshift

Through Firehose​

Delivery managed by AWS
Freshness
About a minute, set by the Firehose buffer
Amazon Redshift compute
Firehose bills per GB; the cluster runs its COPY
Delivery
At least once

In MaestroHub

  1. Create an Amazon Data Firehose connection with IAM.
  2. Use Put Records in batches of up to 500 records or 4 MiB, and keep store-and-forward on.

In your infrastructure

  1. A Firehose stream with a Redshift destination and an S3 bucket for staging.

In Amazon Redshift

  1. A database user and table for Firehose's COPY.

Choose it when Your team prefers delivery that AWS manages to scheduling COPY yourself.

Look elsewhere when You want the fewest moving parts: B runs entirely from the pipeline.

Firehose guide

D

Back to the plant

Read results back​

Close the loop on a schedule
Freshness
Your schedule
Amazon Redshift compute
The cluster runs each query
Delivery
Reads current state; safe to repeat

In MaestroHub

  1. Build a Query function. For large exports, use Unload To S3 and read the files.
  2. Pipeline: Schedule trigger, then the query, then publish to a UNS topic or write a setpoint through the machine's connector.

In Amazon Redshift

  1. A results view with SELECT granted.

Choose it when Analytics in Redshift produce values the plant should use.

Look elsewhere when Large exports: unload to S3 instead.

Redshift guideSchedule trigger

Compare the routes​

Routes combine: most plants run one route in and one route back.

RouteDirectionFreshnessAmazon Redshift computeDeliveryNetworkBest for
Into Amazon RedshiftPer call, secondsThe cluster, or Serverless capacity while it runsRetried on failure; use MERGE so a retry cannot duplicateOutbound HTTPS 443 to AWSWork orders, batch records, moderate telemetry
Into Amazon RedshiftMinutes, as often as you copyThe cluster runs each COPYCopy each file once; track loaded pathsOutbound HTTPS 443 to AWSHigh volume and backfills
Into Amazon RedshiftAbout a minute, set by the Firehose bufferFirehose bills per GB; the cluster runs its COPYAt least onceOutbound HTTPS 443 to AWSSteady telemetry without scheduling loads
Back to the plantYour scheduleThe cluster runs each queryReads current state; safe to repeatOutbound HTTPS 443 to AWSTargets, predictions, reference data