Skip to content

Latest commit

 

History

History
205 lines (159 loc) · 9.29 KB

sql.md

File metadata and controls

205 lines (159 loc) · 9.29 KB
Raw

sql

Monitor Type: sql (Source)

Accepts Endpoints: Yes

Multiple Instances Allowed: Yes

Overview

Run arbitrary SQL queries against a relational database and use the results to generate dataponts.

For example, if you had a database table customers that looked like:

id name country status
1 Bill USA active
2 Mary USA inactive
3 Joe USA active
4 Elizabeth Germany active

You could use the following monitor config to generate metrics about active users and customer counts by country:

monitors:
  - type: sql
    host: localhost
    port: 5432
    dbDriver: postgres
    params:
      user: admin
      password: s3cr3t
    # The `host` and `port` values from above (or provided through auto-discovery) should be interpolated
    # to the connection string as appropriate for your database driver.
    # Also, the values from the `params` config option above can be
    # interpolated.
    connectionString: 'host={{.host}} port={{.port}} dbname=main user={{.user}} password={{.password}} sslmode=disable'
    queries:
      - query: 'SELECT COUNT(*) as count, country, status FROM customers GROUP BY country, status;'
        metrics:
          - metricName: "customers"
            valueColumn: "count"
            dimensionColumns: ["country", "status"]

This would generate a series of timeseries, all with the metric name customers that includes a county and status dimension. The value is the number of customers that belong to that combination of country and status. You could also specify multiple metrics items to generate more than one metric from a single query.

Metric Expressions

Metric Expressions are a beta feature and may break in subsequent non-major releases. The example documented will be maintained for backwards compatibility, however.

If you need to do more complex logic than simply mapping columns to metric values and dimensions, you can use the datapointExpressions option to the individual metric configurations. This allows you to use the expr expression language to derive datapoints from individual rows using more sophisticated logic. These expressions should evaluate to datapoints created by the GAUGE or CUMULATIVE helper functions available in the expression's context. You can also have the expression evaluate to nil if no datapoint should be generated for a particular row.

The signature for both the GAUGE and CUMULATIVE functions is (metricName, dimensions, value), where metricName should be a string value, dimensions should be a map of string keys and values, and value should be any numeric value.

Each of the columns in the row is mapped to a variable in the context of the expression with the same name. So if there was a column called name in your SQL query result, there will be a variable called name that you can use in the expression. Note that literal string values used in your expressions must be surrounded by ".

For example, the MySQL SHOW SLAVE STATS query does not let you pre-process columns using SQL but let us say you wanted to convert the Slave_IO_Running column, which is a string Yes/No value, to a gauge datapoint that has a value of 0 or 1. You can do that with the following configuration:

   - type: sql
     # Example discovery rule, your environment will probably be different
     discoveryRule: container_labels["mysql.slave"] == "true" && port == 3306
     dbDriver: mysql
     params:
       user: root
       password: password
     connectionString: '{{.user}}:{{.password}}@tcp({{.host}})/mysql'
     queries:
      - query: 'SHOW SLAVE STATUS'
        datapointExpressions:
          - 'GAUGE("mysql.slave_sql_running", {master_uuid: Master_UUID, channel: Channel_name}, Slave_SQL_Running == "Yes" ? 1 : 0)'

This would generate a single gauge datapoint for each row in the slave status output, with two dimension, master_uuid and channel and with a value of 0 or 1 depending on if the slave's SQL thread is running.

Supported Drivers

The dbDriver config option must specify the database driver to use. These are equivalent to the name of the Golang SQL driver used in the agent. The connectionString option will be formatted according to the driver that is going to receive it. Here is a list of the drivers we currently support and documentation on the connection string:

Parameterized Connection String

The connectionString config option acts as a template with a context consisting of the variables: host, port, and all the values from the params config option map. You interpolate variables into it with the Go template syntax {{.varname}} (see example config above).

Snowflake Performance and Usage Metrics

To configure the agent to collect Snowflake performance and usage metrics:

  • Copy pkg/sql/snowflake-metrics.yaml from this repo into the same location as your agent.yaml file (for example, /etc/signalfx).
  • Configure the sql monitor as follows:
monitors:
  - type: sql
    intervalSeconds: 3600
    dbDriver: snowflake
    params:
      account: "account.region"
      database: "SNOWFLAKE"
      schema: "ACCOUNT_USAGE"
      role: "ACCOUNTADMIN"
      user: "user"
      password: "password"
    connectionString: "{{.user}}:{{.password}}@{{.account}}/{{.database}}/{{.schema}}?role={{.role}}"
    queries: 
      {"#from": "/etc/signalfx/snowflake-metrics.yaml"}

You can also cut/paste the contents of snowflake-metrics.yaml into agent.yaml under "queries" if needed or preferred. And you can edit snowflake-metrics.yaml to only include metrics you care about.

Configuration

To activate this monitor in the Smart Agent, add the following to your agent config:

monitors:  # All monitor config goes under this key
 - type: sql
   ...  # Additional config

For a list of monitor options that are common to all monitors, see Common Configuration.

Config option Required Type Description
host no string
port no integer (default: 0)
params no map of strings Parameters to the connectionString that can be templated into that option using Go template syntax (e.g. {{.key}}).
dbDriver no string The database driver to use, valid values are postgres, mysql, sqlserver, and snowflake.
connectionString no string A URL or simple option string used to connect to the database. For example, if using PostgreSQL, see the list of connection string params.
queries yes list of objects (see below) A list of queries to make against the database that are used to generate datapoints.
logQueries no bool If true, query results will be logged at the info level. (default: false)

The nested queries config object has the following fields:

Config option Required Type Description
query yes string A SQL query text that selects one or more rows from a database
params no list of any Optional parameters that will replace placeholders in the query string.
metrics no list of objects (see below) Metrics that should be generated from the query.
datapointExpressions no list of strings A set of [expr] expressions that will be used to convert each row to a set of metrics. Each of these will be run for each row in the query result set, allowing you to generate multiple datapoints per row. Each expression should evaluate to a single datapoint or nil.

The nested metrics config object has the following fields:

Config option Required Type Description
metricName yes string The name of the metric as it will appear in SignalFx.
valueColumn yes string The column name that holds the datapoint value
dimensionColumns no list of strings The names of the columns that should make up the dimensions of the datapoint.
isCumulative no bool Whether the value is a cumulative counters (true) or gauge (false). If you set this to the wrong value and send in your first datapoint for the metric name with the wrong type, you will have to manually change the type in SignalFx, as it is set in the system based on the first type seen. (default: false)
dimensionPropertyColumns no map of lists The mapping between dimensions and the columns to be used to attach respective properties

The agent does not do any built-in filtering of metrics coming out of this monitor.