Cloud
Also in Self-Managed

sql_raw

Executes an arbitrary SQL query for each message.

When multiple queries are configured, all messages within a batch are executed in a single database transaction. Single-query configurations execute each message individually without a transaction, preserving per-message error granularity. When consuming from Kafka, messages are automatically ordered by partition within the transaction, allowing max_in_flight > 1 to parallelize across partitions while preserving consume order within each partition. Messages without kafka_partition metadata default to partition 0.

  • Common

  • Advanced

output:
  label: ""
  sql_raw:
    driver: "" # No default (required)
    dsn: "" # No default (required)
    query: "" # No default (optional)
    args_mapping: "" # No default (optional)
    queries: [] # No default (optional)
    max_in_flight: 64
    batching:
      count: 0
      byte_size: 0
      period: ""
      check: ""
output:
  label: ""
  sql_raw:
    driver: "" # No default (required)
    dsn: "" # No default (required)
    query: "" # No default (optional)
    unsafe_dynamic_query: false
    args_mapping: "" # No default (optional)
    queries: [] # No default (optional)
    max_in_flight: 64
    init_files: [] # No default (optional)
    init_statement: "" # No default (optional)
    conn_max_idle_time: "" # No default (optional)
    conn_max_life_time: "" # No default (optional)
    conn_max_idle: 2
    conn_max_open: 0 # No default (optional)
    batching:
      count: 0
      byte_size: 0
      period: ""
      check: ""
      processors: [] # No default (optional)

For some scenarios where you might use this output, see Examples.

Batch execution and ordered writes

Configure a batching policy to accumulate messages and write them together. When you set more than one query (see Conditional queries), all messages in a batch run in a single database transaction, which reduces round-trips to the database. A single-query configuration runs each message individually without a transaction, preserving per-message error granularity.

When consuming from Redpanda or Kafka, this output orders messages by partition within the transaction. This lets you raise max_in_flight above 1 to parallelize writes across partitions while preserving consume order within each partition, so you can scale throughput without breaking change-data-capture ordering. Messages without a kafka_partition metadata field are treated as partition 0.

This makes sql_raw suitable for high-throughput sink pipelines, including CDC, that previously required max_in_flight: 1 to stay ordered.

Conditional queries

Use the queries field to route each message to a different SQL statement based on its content or metadata, without a preprocessing mapping step or unsafe_dynamic_query. Set a when condition (a Bloblang expression) on each entry: the first query whose when evaluates to true, or the first query with no when, runs for that message.

If none of your queries have a when condition, every query runs for each message within the same transaction. Because an unconditioned query always matches, Redpanda Connect lints against configuring one before later queries in the list.

For example, route change-data-capture tombstones to a DELETE and everything else to an upsert:

output:
  sql_raw:
    driver: postgres
    dsn: "${DSN}"
    max_in_flight: 8
    batching:
      count: 100
      period: 100ms
    queries:
      - when: 'root = meta("kafka_tombstone_message") == "true"'
        query: 'DELETE FROM orders WHERE id = $1'
        args_mapping: 'root = [ meta("kafka_key").parse_json().id ]'
      - query: |
          INSERT INTO orders (id, name, updated_at)
          VALUES ($1, $2, $3)
          ON CONFLICT (id) DO UPDATE SET
            name = EXCLUDED.name,
            updated_at = EXCLUDED.updated_at
        args_mapping: 'root = [ this.id, this.name, this.updated_at ]'

Fields

args_mapping

An optional Bloblang mapping that includes the same number of values in an array as the placeholder arguments in the query field.

Type: string

# Examples:
args_mapping: root = [ this.cat.meow, this.doc.woofs[0] ]

# ---

args_mapping: root = [ meta("user.id") ]

batching

Configure a batching policy.

Type: object

# Examples:
batching:
  byte_size: 5000
  count: 0
  period: 1s

# ---

batching:
  count: 10
  period: 1s

# ---

batching:
  check: this.contains("END BATCH")
  count: 0
  period: 1m

batching.byte_size

The maximum total size (in bytes) that a batch can reach before it is flushed. When the combined size of all messages in the batch reaches or exceeds this limit, the batch is immediately sent to the next stage (such as a processor or output).

Set to 0 to disable size-based batching. When disabled, messages are flushed based on other conditions (such as count or period).

Type: int

Default: 0

batching.check

A Bloblang query that returns a boolean value indicating whether a message should end a batch.

Type: string

Default: ""

# Examples:
check: this.type == "end_of_transaction"

batching.count

The number of messages at which the batch is flushed. Set to 0 to disable count-based batching.

Type: int

Default: 0

batching.period

The length of time after which an incomplete batch is flushed regardless of its size. This field accepts Go duration format strings such as 100ms, 1s, or 5s. Supported time units are ns, us, ms, s, m, and h.

Type: string

Default: ""

# Examples:
period: 1s

# ---

period: 1m

# ---

period: 500ms

batching.processors[]

A list of processors to apply to a batch as it is flushed. This allows you to aggregate and archive the batch however you see fit. All resulting messages are flushed as a single batch, so splitting the batch into smaller batches with these processors has no effect.

Type: array<processor>

# Examples:
processors:
  - archive:
      format: concatenate


# ---

processors:
  - archive:
      format: lines


# ---

processors:
  - archive:
      format: json_array

conn_max_idle

An optional maximum number of connections in the idle connection pool. If conn_max_open is greater than 0 but less than the new conn_max_idle, then the new conn_max_idle will be reduced to match the conn_max_open limit. If value ⇐ 0, no idle connections are retained. The default max idle connections is currently 2. This may change in a future release.

Type: int

Default: 2

conn_max_idle_time

An optional maximum amount of time a connection may be idle. Expired connections may be closed lazily before reuse. If value ⇐ 0, connections are not closed due to a connections idle time.

Type: string

conn_max_life_time

An optional maximum amount of time a connection may be reused. Expired connections may be closed lazily before reuse. If value ⇐ 0, connections are not closed due to a connections age.

Type: string

conn_max_open

An optional maximum number of open connections to the database. If conn_max_idle is greater than 0 and the new conn_max_open is less than conn_max_idle, then conn_max_idle will be reduced to match the new conn_max_open limit. If value ⇐ 0, then there is no limit on the number of open connections. The default is 0 (unlimited).

Type: int

driver

A database driver to use.

Type: string

Options: mysql, postgres, pgx, clickhouse, mssql, sqlite, oracle, snowflake, trino, gocosmos, spanner, databricks

dsn

A Data Source Name to identify the target database.

Drivers

The following table lists the supported drivers and their DSN formats:

Driver Data Source Name Format

clickhouse

clickhouse://[username[:password]@][netloc][:port]/dbname[?param1=value1&…​&paramN=valueN]

mysql

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&…​&paramN=valueN]

postgres and pgx

postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&…​]

mssql

sqlserver://[user[:password]@][netloc][:port][?database=dbname&param1=value1&…​]

sqlite

file:/path/to/filename.db[?param&=value1&…​]

oracle

oracle://[username[:password]@][netloc][:port]/service_name?server=server2&server=server3

snowflake

username[:password]@account_identifier/dbname/schemaname[?param1=value&…​&paramN=valueN]

trino

http[s]://user[:pass]@host[:port][?parameters]

gocosmos

AccountEndpoint=<cosmosdb-endpoint>;AccountKey=<cosmosdb-account-key>[;TimeoutMs=<timeout-in-ms>][;Version=<cosmosdb-api-version>][;DefaultDb/Db=<db-name>][;AutoId=<true/false>][;InsecureSkipVerify=<true/false>]

spanner

projects/[PROJECT]/instances/[INSTANCE]/databases/[DATABASE]

databricks

token:<access-token>@<server-hostname>:<port>/<http-path>

Please note that the postgres and pgx drivers enforce SSL by default, you can override this with the parameter sslmode=disable if required. The pgx driver is an alternative to the standard postgres (pq) driver and comes with extra functionality such as support for array insertion.

The snowflake driver supports multiple DSN formats. Please consult the docs for more details. For key pair authentication, the DSN has the following format: <snowflake_user>@<snowflake_account>/<db_name>/<schema_name>?warehouse=<warehouse>&role=<role>&authenticator=snowflake_jwt&privateKey=<base64_url_encoded_private_key>, where the value for the privateKey parameter can be constructed from an unencrypted RSA private key file rsa_key.p8 using openssl enc -d -base64 -in rsa_key.p8 | basenc --base64url -w0 (you can use gbasenc instead of basenc on OSX if you install coreutils via Homebrew). If you have a password-encrypted private key, you can decrypt it using openssl pkcs8 -in rsa_key_encrypted.p8 -out rsa_key.p8. Also, make sure fields such as the username are URL-encoded.

The gocosmos driver is still experimental, but it has support for hierarchical partition keys as well as cross-partition queries. Please refer to the SQL notes for details.

Type: string

# Examples:
dsn: clickhouse://username:password@host1:9000,host2:9000/database?dial_timeout=200ms&max_execution_time=60

# ---

dsn: foouser:foopassword@tcp(localhost:3306)/foodb

# ---

dsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable

# ---

dsn: oracle://foouser:foopass@localhost:1521/service_name

# ---

dsn: token:dapi1234567890ab@dbc-a1b2345c-d6e7.cloud.databricks.com:443/sql/1.0/warehouses/abc123def456

init_files[]

An optional list of file paths containing SQL statements to execute immediately upon the first connection to the target database. This is a useful way to initialise tables before processing data. Glob patterns are supported, including super globs (double star).

Care should be taken to ensure that the statements are idempotent, and therefore would not cause issues when run multiple times after service restarts. If both init_statement and init_files are specified the init_statement is executed after the init_files.

If a statement fails for any reason a warning log will be emitted but the operation of this component will not be stopped.

Type: array<string>

# Examples:
init_files:
  - ./init/*.sql

# ---

init_files:
  - ./foo.sql
  - ./bar.sql

init_statement

An optional SQL statement to execute immediately upon the first connection to the target database. This is a useful way to initialise tables before processing data. Care should be taken to ensure that the statement is idempotent, and therefore would not cause issues when run multiple times after service restarts.

If both init_statement and init_files are specified the init_statement is executed after the init_files.

If the statement fails for any reason a warning log will be emitted but the operation of this component will not be stopped.

Type: string

# Examples:
init_statement: |

  CREATE TABLE IF NOT EXISTS some_table (
    foo varchar(50) not null,
    bar integer,
    baz varchar(50),
    primary key (foo)
  ) WITHOUT ROWID;

max_in_flight

The maximum number of batches to send in parallel at any given time. When multiple queries are configured and you consume from Redpanda or Kafka, messages are ordered by partition within each transaction, so you can keep this above 1 to parallelize writes across partitions while preserving consume order within each partition. Messages without a kafka_partition metadata field are treated as partition 0.

Type: int

Default: 64

queries[]

A list of database statements to run in addition to the main query. When a when condition is specified on entries, the first query whose condition evaluates to true (or that has no condition) is executed for each message. When no when conditions are present, all queries are executed for each message within a single transaction.

Type: array<object>

queries[].args_mapping

An optional Bloblang mapping that includes the same number of values in an array as the placeholder arguments in the query field.

Type: string

# Examples:
args_mapping: root = [ this.cat.meow, this.doc.woofs[0] ]

# ---

args_mapping: root = [ meta("user.id") ]

queries[].query

The query to execute.

You must include the correct placeholders for the specified database driver. Some drivers use question marks (?), whereas others expect incrementing dollar signs ($1, $2, and so on) or colons (:1, :2, and so on). The following table shows the placeholder style for each driver:

Driver Placeholder style

clickhouse

Dollar sign ($)

gocosmos

Colon (:)

mssql

Question mark (?)

mysql

Question mark (?)

oracle

Colon (:)

pgx

Dollar sign ($)

postgres

Dollar sign ($)

snowflake

Question mark (?)

spanner

Question mark (?)

sqlite

Question mark (?)

trino

Question mark (?)

Type: string

queries[].when

An optional Bloblang mapping that, when set, is evaluated for each message to determine whether to execute this query. The mapping should return a boolean value. The first query in the list whose when condition evaluates to true (or that has no when condition) is executed. This enables conditional query routing based on message content or metadata without requiring unsafe_dynamic_query.

Type: string

# Examples:
when: root = meta("kafka_tombstone_message") == "true"

# ---

when: root = this.operation == "delete"

query

The query to execute.

You must include the correct placeholders for the specified database driver. Some drivers use question marks (?), whereas others expect incrementing dollar signs ($1, $2, and so on) or colons (:1, :2, and so on). The following table shows the placeholder style for each driver:

Driver Placeholder style

clickhouse

Dollar sign ($)

gocosmos

Colon (:)

mssql

Question mark (?)

mysql

Question mark (?)

oracle

Colon (:)

pgx

Dollar sign ($)

postgres

Dollar sign ($)

snowflake

Question mark (?)

spanner

Question mark (?)

sqlite

Question mark (?)

trino

Question mark (?)

Type: string

# Examples:
query: INSERT INTO footable (foo, bar, baz) VALUES (?, ?, ?);

unsafe_dynamic_query

Whether to enable interpolation functions in the query. Make sure your queries are defended against injection attacks.

Type: bool

Default: false

Examples

Table Insert (MySQL)

Here we insert rows into a database by populating the columns id, name and topic with values extracted from messages and metadata:

output:
  sql_raw:
    driver: mysql
    dsn: foouser:foopassword@tcp(localhost:3306)/foodb
    query: "INSERT INTO footable (id, name, topic) VALUES (?, ?, ?);"
    args_mapping: |
      root = [
        this.user.id,
        this.user.name,
        meta("kafka_topic"),
      ]

Dynamically Creating Tables (PostgreSQL)

Here we dynamically create output tables transactionally with inserting a record into the newly created table.

output:
  processors:
    - mapping: |
        root = this
        # Prevent SQL injection when using unsafe_dynamic_query
        meta table_name = "\"" + metadata("table_name").replace_all("\"", "\"\"") + "\""
  sql_raw:
    driver: postgres
    dsn: postgres://localhost/postgres
    unsafe_dynamic_query: true
    queries:
      - query: |
          CREATE TABLE IF NOT EXISTS ${!metadata("table_name")} (id varchar primary key, document jsonb);
      - query: |
          INSERT INTO ${!metadata("table_name")} (id, document) VALUES ($1, $2)
          ON CONFLICT (id) DO UPDATE SET document = EXCLUDED.document;
        args_mapping: |
          root = [ this.id, this.document.string() ]

Conditional CDC Queries (PostgreSQL)

Route messages to different SQL operations based on message metadata. Tombstone messages trigger a DELETE, while all other messages perform an upsert. All operations within a batch execute in a single transaction, ordered by Kafka partition.

output:
  sql_raw:
    driver: postgres
    dsn: postgres://localhost/postgres
    max_in_flight: 8
    batching:
      count: 100
      period: 100ms
    queries:
      - when: 'root = meta("kafka_tombstone_message") == "true"'
        query: 'DELETE FROM users WHERE id = $1'
        args_mapping: 'root = [this.id]'
      - query: |
          INSERT INTO users (id, name, updated_at)
          VALUES ($1, $2, $3)
          ON CONFLICT (id) DO UPDATE SET
            name = EXCLUDED.name,
            updated_at = EXCLUDED.updated_at
        args_mapping: 'root = [this.id, this.name, this.updated_at]'