Skip to main content

sql_select

EXPERIMENTAL

This component is experimental and therefore subject to change or removal outside of major version releases.

Executes a select query and creates a message for each row received.

Introduced in version 3.59.0.

# Common config fields, showing default values
input:
label: ""
sql_select:
driver: ""
dsn: ""
table: ""
columns: []
where: ""
args_mapping: ""

Once the rows from the query are exhausted this input shuts down, allowing the pipeline to gracefully terminate (or the next input in a sequence to execute).

Examples#

Here we define a pipeline that will consume all rows from a table created within the last hour by comparing the unix timestamp stored in the row column "created_at":

input:
sql_select:
driver: postgres
dsn: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disable
table: footable
columns: [ '*' ]
where: created_at >= ?
args_mapping: |
root = [
now().format_timestamp_unix() - 3600
]

Fields#

driver#

A database driver to use.

Type: string
Options: mysql, postgres, clickhouse, mssql.

dsn#

A Data Source Name to identify the target database.

Drivers#

The following is a list of supported drivers and their respective DSN formats:

DriverData Source Name Format
clickhousetcp://[netloc][:port][?param1=value1&...&paramN=valueN]
mysql[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]
postgrespostgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
mssqlsqlserver://[user[:password]@][netloc][:port][?database=dbname&param1=value1&...]

Please note that the postgres driver enforces SSL by default, you can override this with the parameter sslmode=disable if required.

Type: string

# Examples
dsn: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000
dsn: foouser:foopassword@tcp(localhost:3306)/foodb
dsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable

table#

The table to select from.

Type: string

# Examples
table: foo

columns#

A list of columns to select.

Type: array

# Examples
columns:
- '*'
columns:
- foo
- bar
- baz

where#

An optional where clause to add. Placeholder arguments are populated with the args_mapping field. Placeholders should always be question marks, and will automatically be converted to dollar syntax when the postgres driver is used.

Type: string

# Examples
where: type = ? and created_at > ?
where: user_id = ?

args_mapping#

An optional Bloblang mapping which should evaluate to an array of values matching in size to the number of placeholder arguments in the field where.

Type: string

# Examples
args_mapping: root = [ "article", now().format_timestamp("2006-01-02") ]

prefix#

An optional prefix to prepend to the select query (before SELECT).

Type: string

suffix#

An optional suffix to append to the select query.

Type: string