sql

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.

Configuration

This section contains the default TOML to configure the plugin. You can generate it using telegraf --usage <plugin-name>.

[[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
  # 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

  [[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 ommited, 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_cols_include = ["name"]
    field_cols_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