Input: sql

Query a SQL database

Currently only Postgres and Sqlite (embedded) are supported

Field Summary

Field Name	Type	Description	Default
when	message_filter	Fire this input when a specific internal message occurs	-
interval	duration	How often to run the command	-
cron	cron	How often to run the command. Note that unlike standard Cron, Pipes use a Cron syntax that includes a column for seconds. See full discussion	-
immediate	bool	Run as soon as invoked, instead of waiting for the specified cron interval	false
random-offset	duration	Sets a random offset to the schedule, then sticks to it	0s
window	Window	For resources that need a time window to be specified	-
block	bool	Block further input schedules from triggering if the pipe output is retrying	false
query	string	An SQL SELECT statement	-
source	SqlSources	SQL connection options	-

Fields

`when`

Type: message_filter

Fire this input when a specific internal message occurs

This field overloads time-based scheduling with a scheduler that fires on matching messages.

Example

Pipe Language Snippet:

input:
  http-poll:
    when:
      message-received:
        filter-type:
          - pipe-idle
    url: "http://localhost:8888"
    raw: true
    ignore-line-breaks: true

`interval`

Type: duration

How often to run the command

By default, interval: 0s which means: once. Note that scheduled inputs set document markers. See full discussion

Example

Pipe Language Snippet:

exec:
  command: echo 'once a day'
  interval: 1d

`cron`

Type: cron

How often to run the command. Note that unlike standard Cron, Pipes use a Cron syntax that includes a column for seconds. See full discussion

Example: Once a day

Pipe Language Snippet:

exec:
  command: echo 'once a day'
  cron: '0 0 0 * * *'

Example: Once a day, using a convenient shortcut

Pipe Language Snippet:

exec:
  command: echo 'once a day'
  cron: '@daily'

`immediate`

Type: bool

Default: false

Run as soon as invoked, instead of waiting for the specified cron interval

Example: Run immediately on invocation, and thereafter at 10h every morning

Pipe Language Snippet:

exec:
  command: echo 'hello'
  immediate: true
  cron: '0 0 10 * * *'

`random-offset`

Type: duration

Default: 0s

Sets a random offset to the schedule, then sticks to it

This can help avoid the thundering herd problem, where you do not, for example, want to overload some service at 00:00:00

Example: Would fire up to a minute after every hour

Pipe Language Snippet:

exec:
  command: echo 'hello'
  random-offset: 1m
  cron: '0 0 * * * *'

`window`

Type: Window

For resources that need a time window to be specified

Field Name	Type	Description	Default
size	duration	Window size	-
offset	duration	Window offset	0s
start-time	time	Allows the windowing to start at a specified time	-
highwatermark-file	path	Specify file where timestamp would be stored in order to resume, for when Pipe has been restarted	-

`size`

Type: duration

Window size

Example

Pipe Language Snippet:

exec:
  command: echo 'one two'
  window:
    size: 1m

`offset`

Type: duration

Default: 0s

Window offset

Example

Pipe Language Snippet:

exec:
  command: echo 'one two'
  window:
    size: 1m
    offset: 10s

`start-time`

Type: time

Allows the windowing to start at a specified time

It should in the following format: 2019-07-10 18:45:00.000 +0200

Example

Pipe Language Snippet:

exec:
  command: echo 'one two'
  window:
    size: 1m
    start-time: 10s

`highwatermark-file`

Type: path

Specify file where timestamp would be stored in order to resume, for when Pipe has been restarted

Example

Pipe Language Snippet:

exec:
  command: echo 'one two'
  window:
    size: 1m
    highwatermark-file:: /tmp/mark.txt

`block`

Type: bool

Default: false

Block further input schedules from triggering if the pipe output is retrying

`query`

Type: string

An SQL SELECT statement

`source`

Type: SqlSources

SQL connection options

Field Name	Type	Description	Default
postgres	SqlSource	Specify kind and connection for an SQL database	-
sqlite	SqlSource	Specify kind and connection for an SQL database	-
my-sql	SqlSource	Specify kind and connection for an SQL database	-
odbc	OdbcSqlSource	SQL options	-

`postgres`

Type: SqlSource

Specify kind and connection for an SQL database

Field Name	Type	Description	Default
connection	connection-string	For 'sqlite', a path to a file, for 'postgres' strings like "host=localhost user=postgres"	-

`connection`

Type: connection-string

For 'sqlite', a path to a file, for 'postgres' strings like "host=localhost user=postgres"

`sqlite`

Type: SqlSource

Specify kind and connection for an SQL database

Field Name	Type	Description	Default
connection	connection-string	For 'sqlite', a path to a file, for 'postgres' strings like "host=localhost user=postgres"	-

`connection`

Type: connection-string

For 'sqlite', a path to a file, for 'postgres' strings like "host=localhost user=postgres"

`my-sql`

Type: SqlSource

Specify kind and connection for an SQL database

Field Name	Type	Description	Default
connection	connection-string	For 'sqlite', a path to a file, for 'postgres' strings like "host=localhost user=postgres"	-

`connection`

Type: connection-string

For 'sqlite', a path to a file, for 'postgres' strings like "host=localhost user=postgres"

`odbc`

Type: OdbcSqlSource

SQL options

Field Name	Type	Description	Default
quick	DsnFields	Simple connection to ODBC (assumes system or user configuration)	-
connection-string	DriverFields	Create a compatible connection string to connect to ODBC this may vary between ODBC drivers	-
max-str-len	integer	The maximum string buffer to allocate when parsing returned strings this is specified in `bytes` and should be large enough to accommodate the largest possible string returned	-
max-bin-len	integer	The maximum string buffer to allocate when parsing returned binary blobs this is specified in `bytes` and should be large enough to accommodate the largest possible binary blobs returned	-

`quick`

Type: DsnFields

Simple connection to ODBC (assumes system or user configuration)

Field Name	Type	Description	Default
dsn	string	The configured DSN name	-
username	string	The username for the ODBC connection	-
password	string	The password for the ODBC connection	-

`dsn`

Type: string

The configured DSN name

`username`

Type: string

The username for the ODBC connection

`password`

Type: string

The password for the ODBC connection

`connection-string`

Type: DriverFields

Create a compatible connection string to connect to ODBC this may vary between ODBC drivers

Field Name	Type	Description	Default
driver	string	The ODBC driver to use	-
attributes	Map	Attributes in a "String: Value" format	-

`driver`

Type: string

The ODBC driver to use

`attributes`

Type: Map

Attributes in a "String: Value" format

`max-str-len`

Type: integer

The maximum string buffer to allocate when parsing returned strings this is specified in bytes and should be large enough to accommodate the largest possible string returned

`max-bin-len`

Type: integer

The maximum string buffer to allocate when parsing returned binary blobs this is specified in bytes and should be large enough to accommodate the largest possible binary blobs returned

Input: sql

Field Summary​

Fields​

when​

interval​

cron​

immediate​

random-offset​

window​

size​

offset​

start-time​

highwatermark-file​

block​

query​

source​

postgres​

connection​

sqlite​

connection​

my-sql​

connection​

odbc​

quick​

dsn​

username​

password​

connection-string​

driver​

attributes​

max-str-len​

max-bin-len​

Field Summary

Fields

`when`

`interval`

`cron`

`immediate`

`random-offset`

`window`

`size`

`offset`

`start-time`

`highwatermark-file`

`block`

`query`

`source`

`postgres`

`connection`

`sqlite`

`connection`

`my-sql`

`connection`

`odbc`

`quick`

`dsn`

`username`

`password`

`connection-string`

`driver`

`attributes`

`max-str-len`

`max-bin-len`