README
¶
SQL Input Plugin
This plugin reads metrics from performing SQL queries against a SQL
server. Different server types are supported and their settings might differ
(especially the connection parameters). Please check the list of supported SQL
drivers for the driver name and options
for the data-source-name (dsn) options.
Global configuration options
In addition to the plugin-specific configuration settings, plugins support additional global and plugin configuration settings. These settings are used to modify metrics, tags, and field or create aliases and configure ordering, etc. See the CONFIGURATION.md for more details.
Secret-store support
This plugin supports secrets from secret-stores for the dsn option.
See the secret-store documentation for more details on how
to use them.
Configuration
# Read metrics from SQL queries
[[inputs.sql]]
## Database Driver
## See https://github.com/influxdata/telegraf/blob/master/docs/SQL_DRIVERS_INPUT.md for
## a list of supported drivers.
driver = "mysql"
## Data source name for connecting
## The syntax and supported options depends on selected driver.
dsn = "username:password@mysqlserver:3307/dbname?param=value"
## Timeout for any operation
## Note that the timeout for queries is per query not per gather.
# timeout = "5s"
## Connection time limits
## By default the maximum idle time and maximum lifetime of a connection is unlimited, i.e. the connections
## will not be closed automatically. If you specify a positive time, the connections will be closed after
## idleing or existing for at least that amount of time, respectively.
# connection_max_idle_time = "0s"
# connection_max_life_time = "0s"
## Connection count limits
## By default the number of open connections is not limited and the number of maximum idle connections
## will be inferred from the number of queries specified. If you specify a positive number for any of the
## two options, connections will be closed when reaching the specified limit. The number of idle connections
## will be clipped to the maximum number of connections limit if any.
# connection_max_open = 0
# connection_max_idle = auto
## Specifies plugin behavior regarding disconnected servers
## Available choices :
## - error: telegraf will return an error on startup if one the servers is unreachable
## - ignore: telegraf will ignore unreachable servers on both startup and gather
# disconnected_servers_behavior = "error"
[[inputs.sql.query]]
## Query to perform on the server
query="SELECT user,state,latency,score FROM Scoreboard WHERE application > 0"
## Alternatively to specifying the query directly you can select a file here containing the SQL query.
## Only one of 'query' and 'query_script' can be specified!
# query_script = "/path/to/sql/script.sql"
## Name of the measurement
## In case both measurement and 'measurement_col' are given, the latter takes precedence.
# measurement = "sql"
## Column name containing the name of the measurement
## If given, this will take precedence over the 'measurement' setting. In case a query result
## does not contain the specified column, we fall-back to the 'measurement' setting.
# measurement_column = ""
## Column name containing the time of the measurement
## If omitted, the time of the query will be used.
# time_column = ""
## Format of the time contained in 'time_col'
## The time must be 'unix', 'unix_ms', 'unix_us', 'unix_ns', or a golang time format.
## See https://golang.org/pkg/time/#Time.Format for details.
# time_format = "unix"
## Column names containing tags
## An empty include list will reject all columns and an empty exclude list will not exclude any column.
## I.e. by default no columns will be returned as tag and the tags are empty.
# tag_columns_include = []
# tag_columns_exclude = []
## Column names containing fields (explicit types)
## Convert the given columns to the corresponding type. Explicit type conversions take precedence over
## the automatic (driver-based) conversion below.
## NOTE: Columns should not be specified for multiple types or the resulting type is undefined.
# field_columns_float = []
# field_columns_int = []
# field_columns_uint = []
# field_columns_bool = []
# field_columns_string = []
## Column names containing fields (automatic types)
## An empty include list is equivalent to '[*]' and all returned columns will be accepted. An empty
## exclude list will not exclude any column. I.e. by default all columns will be returned as fields.
## NOTE: We rely on the database driver to perform automatic datatype conversion.
# field_columns_include = []
# field_columns_exclude = []
Options
Driver
The driver and dsn options specify how to connect to the database. As
especially the dsn format and values vary with the driver refer to the list
of supported SQL drivers for possible
values and more details.
Connection limits
With these options you can limit the number of connections kept open by this plugin. Details about the exact workings can be found in the golang sql documentation.
Query sections
Multiple query sections can be specified for this plugin. Each specified query
will first be prepared on the server and then executed in every interval using
the column mappings specified. Please note that tag and field columns are
not exclusive, i.e. a column can be added to both. When using both include and
exclude lists, the exclude list takes precedence over the include
list. I.e. given you specify foo in both lists, foo will never pass the
filter. In case any the columns specified in measurement_col or time_col are
not returned by the query, the plugin falls-back to the documented
defaults. Fields or tags specified in the includes of the options but missing in
the returned query are silently ignored.
Types
This plugin relies on the driver to do the type conversion. For the different properties of the metric the following types are accepted.
Measurement
Only columns of type string are accepted.
Time
For the metric time columns of type time are accepted directly. For numeric
columns, time_format should be set to any of unix, unix_ms, unix_ns or
unix_us accordingly. By default the a timestamp in unix format is
expected. For string columns, please specify the time_format accordingly. See
the golang time documentation for
details.
Tags
For tags columns with textual values (string and bytes), signed and unsigned
integers (8, 16, 32 and 64 bit), floating-point (32 and 64 bit), boolean and
time values are accepted. Those values will be converted to string.
Fields
For fields columns with textual values (string and bytes), signed and
unsigned integers (8, 16, 32 and 64 bit), floating-point (32 and 64 bit),
boolean and time values are accepted. Here bytes will be converted to
string, signed and unsigned integer values will be converted to int64 or
uint64 respectively. Floating-point values are converted to float64 and
time is converted to a nanosecond timestamp of type int64.
Example Output
Using the MariaDB sample database and the configuration
[[inputs.sql]]
driver = "mysql"
dsn = "root:password@/nation"
[[inputs.sql.query]]
query="SELECT * FROM guests"
measurement = "nation"
tag_columns_include = ["name"]
field_columns_exclude = ["name"]
Telegraf will output the following metrics
nation,host=Hugin,name=John guest_id=1i 1611332164000000000
nation,host=Hugin,name=Jane guest_id=2i 1611332164000000000
nation,host=Hugin,name=Jean guest_id=3i 1611332164000000000
nation,host=Hugin,name=Storm guest_id=4i 1611332164000000000
nation,host=Hugin,name=Beast guest_id=5i 1611332164000000000
Metrics
The format of metrics produced by this plugin depends on the content and data format of the file.
Documentation
¶
Index ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type Query ¶
type Query struct {
Query string `toml:"query"`
Script string `toml:"query_script"`
Measurement string `toml:"measurement"`
MeasurementColumn string `toml:"measurement_column"`
TimeColumn string `toml:"time_column"`
TimeFormat string `toml:"time_format"`
TagColumnsInclude []string `toml:"tag_columns_include"`
TagColumnsExclude []string `toml:"tag_columns_exclude"`
FieldColumnsInclude []string `toml:"field_columns_include"`
FieldColumnsExclude []string `toml:"field_columns_exclude"`
FieldColumnsFloat []string `toml:"field_columns_float"`
FieldColumnsInt []string `toml:"field_columns_int"`
FieldColumnsUint []string `toml:"field_columns_uint"`
FieldColumnsBool []string `toml:"field_columns_bool"`
FieldColumnsString []string `toml:"field_columns_string"`
// contains filtered or unexported fields
}
type SQL ¶
type SQL struct {
Driver string `toml:"driver"`
Dsn config.Secret `toml:"dsn"`
Timeout config.Duration `toml:"timeout"`
MaxIdleTime config.Duration `toml:"connection_max_idle_time"`
MaxLifetime config.Duration `toml:"connection_max_life_time"`
MaxOpenConnections int `toml:"connection_max_open"`
MaxIdleConnections int `toml:"connection_max_idle"`
Queries []Query `toml:"query"`
Log telegraf.Logger `toml:"-"`
DisconnectedServersBehavior string `toml:"disconnected_servers_behavior"`
// contains filtered or unexported fields
}
Source Files