Skip to main content

Amazon SQS

The sqs connector reads messages from, and publishes messages to, Amazon SQS queues, standard and FIFO. Most of the time you use it from DuckDB jobs and Spark jobs: a consume step reads a batch, and a publish output sends messages. It works like the Kafka connector.

In short

Messages are deleted from the queue only after the batch's outputs succeed. If a job fails, they return to the queue when their visibility timeout ends.

Install​

sw connectors -i sqs

Connect​

connections:
sqs_bus:
provider: sqs
region: "us-east-1"
KeyWhat it isRequired / default
regionThe queue's AWS regionRequired
key_idAccess key id. SecretOptional
secretSecret access key. SecretOptional
session_tokenSession token of temporary credentials. SecretOptional
endpointAn http(s):// endpoint, for example LocalStack's http://localhost:4566Optional

Without key_id and secret, the connector uses the platform's credentials (IAM role, environment). If you set them, set both:

connections:
sqs_bus:
provider: sqs
region: "us-east-1"
key_id: "{{ secrets.aws_key }}"
secret: "{{ secrets.aws_secret }}"

Read data​

Consume a queue (jobs)​

A consume step reads one batch of messages into the table input. A job has at most one.

steps:
- name: queue_messages
connection: sqs_bus
operation:
service: message
method: consume
arguments:
destination: "https://sqs.us-east-1.amazonaws.com/123456789012/events-queue" # the queue URL
max_messages: 100 # per batch (received 10 at a time)
max_wait: 20s # long polling
visibility_timeout: 300 # seconds
sql: |
SELECT message_id, body, headers['tenant'] AS tenant
FROM input
ArgumentWhat it does
destinationThe queue URL. Required
max_messagesMessages per batch. SQS returns at most 10 per receive call, so the connector calls it repeatedly
max_waitLong-poll wait, for example 20s
visibility_timeoutSeconds a received message stays hidden from other consumers
optionsAdditional SQS request parameters

The input table:

ColumnWhat it holds
message_idThe SQS message id
keyThe message group id (FIFO queues)
bodyThe payload as text
headersThe message attributes (string values), as a map of text
timestampSentTimestamp (epoch milliseconds, as text)

To run more than one batch per run, use --drain or --follow. --available-now runs as --drain, because SQS has no end offsets. See DuckDB batch modes and the Spark CLI.

Peek at a queue (sources)​

method: read receives messages with a zero visibility timeout, so it never hides or deletes them. Use it on streamwright run, or in a DuckDB job as a call step.

streams/messages.yaml
requests:
- name: raw_messages
operation:
service: message
method: read
arguments:
destination: "https://sqs.us-east-1.amazonaws.com/123456789012/events-queue"
max_messages: 50
max_wait: 5s
transform:
mode: page
steps:
- name: messages
select: |
SELECT record->>'message_id' AS message_id,
record->>'body' AS body
FROM raw_messages
export:
messages:
step: messages
primary_key: [message_id]

Each record has message_id, body, attributes and message_attributes.

Write data​

A publish output sends each row of its sql (or step) as one JSON message, in batches of ten. It takes no mode.

outputs:
- name: send_notifications
connection: sqs_bus
operation:
service: message
method: publish
arguments:
destination: "https://sqs.us-east-1.amazonaws.com/123456789012/completed-queue"
sql: |
SELECT event_id, status, current_timestamp AS processed_at
FROM queue_messages
  • Messages go through the batch's outbox and are delivered at least once, after the batch's writes commit.
  • For FIFO queues, set group_id (the message group). Each message's MessageDeduplicationId comes from the dedupe key job:batch:output and its position, so SQS drops duplicates.
  • Publish also takes options.

Good to know​

  • Engines. Consume steps and publish outputs run in DuckDB and Spark jobs. method: read runs on streamwright run, and in a DuckDB job as a call step.
  • Aliases. max_messages can also be written MaxNumberOfMessages, max_wait as WaitTimeSeconds, and visibility_timeout as VisibilityTimeout. Setting a key and its alias together is an error.
  • Vendor arguments. boto3 receive and send parameters pass through unchanged, for example MessageAttributeNames: [All].

See also​