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
- Advanced
# Common config fields, showing default valuesinput:label: ""sql_select:driver: ""dsn: ""table: ""columns: []where: ""args_mapping: ""
# All config fields, showing default valuesinput:label: ""sql_select:driver: ""dsn: ""table: ""columns: []where: ""args_mapping: ""prefix: ""suffix: ""
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​
- Consume a Table (PostgreSQL)
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: postgresdsn: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disabletable: footablecolumns: [ '*' ]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:
Driver | Data Source Name Format |
---|---|
clickhouse | tcp://[netloc][:port][?param1=value1&...¶mN=valueN] |
mysql | [username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN] |
postgres | postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...] |
mssql | sqlserver://[user[:password]@][netloc][:port][?database=dbname¶m1=value1&...] |
Please note that the postgres
driver enforces SSL by default, you
can override this with the parameter sslmode=disable
if required.
Type: string
# Examplesdsn: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000dsn: foouser:foopassword@tcp(localhost:3306)/foodbdsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable
table
​
The table to select from.
Type: string
# Examplestable: foo
columns
​
A list of columns to select.
Type: array
# Examplescolumns:- '*'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
# Exampleswhere: 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
# Examplesargs_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