Skip to main content
Version: Next

Input: sql

Query a SQL database

Currently only Postgres and Sqlite (embedded) are supported

Field Summary

Field NameTypeDescriptionDefault
whenmessage_filterFire this input when a specific internal message occurs-
intervaldurationHow often to run the command-
croncronHow often to run the command. Note that unlike standard Cron, Pipes use a Cron syntax that includes a column for seconds. See full discussion-
immediateboolRun as soon as invoked, instead of waiting for the specified cron intervalfalse
random-offsetdurationSets a random offset to the schedule, then sticks to it0s
windowWindowFor resources that need a time window to be specified-
blockboolBlock further input schedules from triggering if the pipe output is retryingfalse
querystringAn SQL SELECT statement-
sourceSqlSourcesSQL 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 NameTypeDescriptionDefault
sizedurationWindow size-
offsetdurationWindow offset0s
start-timetimeAllows the windowing to start at a specified time-
highwatermark-filepathSpecify 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 NameTypeDescriptionDefault
postgresSqlSourceSpecify kind and connection for an SQL database-
sqliteSqlSourceSpecify kind and connection for an SQL database-
my-sqlSqlSourceSpecify kind and connection for an SQL database-
odbcOdbcSqlSourceSQL options-

  postgres

Type: SqlSource

Specify kind and connection for an SQL database

Field NameTypeDescriptionDefault
connectionconnection-stringFor '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 NameTypeDescriptionDefault
connectionconnection-stringFor '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 NameTypeDescriptionDefault
connectionconnection-stringFor '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 NameTypeDescriptionDefault
quickDsnFieldsSimple connection to ODBC (assumes system or user configuration)-
connection-stringDriverFieldsCreate a compatible connection string to connect to ODBC this may vary between ODBC drivers-
max-str-lenintegerThe 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-lenintegerThe 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 NameTypeDescriptionDefault
dsnstringThe configured DSN name-
usernamestringThe username for the ODBC connection-
passwordstringThe 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 NameTypeDescriptionDefault
driverstringThe ODBC driver to use-
attributesMapAttributes 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