---
title: SQL collector
slug: reference/sql-collector
docTags: 
createdAt: 2025-09-09T09:40:01.032Z
---

## Basic settings

### ConnectionString

**Description**: The connection string used to connect to the SQL database. Use `$USER` and `$PASSWORD` as placeholders for the necessary credentials
**Required**: yes
**Examples**:

- [postgres](https://www.postgresql.org/docs/current/libpq-connect.html): postgres\://$USER:$PASSWORD\@host\:port/database
- [oracle](https://docs.oracle.com/en/database/other-databases/essbase/21/essoa/connection-string-formats.html): oracle://$USER:$PASSWORD\@server/service\_name
- [sqlserver](https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn): sqlserver://$USER:$PASSWORD\@host/instance?param1=value\&param2=value
- [mysql](https://github.com/go-sql-driver/mysql#dsn-data-source-name): $USER:$PASSWORD\@protocol(address)/dbname?param=value
- [sqlite](https://pkg.go.dev/modernc.org/sqlite#hdr-Connecting_to_a_database): file\:test.db?cache=shared\&mode=memory
- [hdb](https://github.com/SAP/go-hdb#hana-cloud-connection): hdb://$USER:$ PASSWORD\@something.hanacloud.ondemand.com :443?TLSServerName=something.hanacloud.ondemand.com
- [ase](https://github.com/SAP/go-ase#data-source-names): ase://$USER:$PASSWORD\@host\:port/?prop1=val1\&prop2=val2
- [odbc](https://github.com/alexbrainman/odbc): DSN=my-odbc-datasource-name;UID=$USER;PWD=$PASSWORD

**MariaDB**

- MariaDB was created as a fork of MySQL. Therefore, it uses the same connection string format as MySQL.
- It is required to add `parseTime=true` in the connection string to parse the timestamp correctly for a MariaDB connection using the MySQL driver.
- The NULL values should be handled, e.g. by using `COALESCE(column_name, '')` in the SQL query to read values from a column having NULL values, as otherwise NULL values will cause parsing issues for a MariaDB connection using the MySQL driver.

**Oracle**

The oracle driver used in the SQL collector supports v9+ Oracle SQL databases.
Find the service name or SID name by performing the following command in powershell/cmd on the Windows Oracle Database server:

```bash
lsnrctl status
```

Alternatively, look for the content of the `tnsnames.ora` file typically found in a similar path as `C:\oracle\ora90\network\ADMIN`.
The hostname and the tcp port is mentioned on which the Oracle DB is listening (IP and/or DNS name). Each item lists the service name, which is needed to setup a SQL connection.

Make sure that the ODBC library path is added to $PATH on the machine running the SQL collector (typically is automatically performed on installation of the ODBC driver).

**ODBC**

The ODBC driver for the underlying SQL database and the ODBC connection must be configured on the same machine that runs the SQL collector. For Linux that is performed in `unixODBC` and for Windows that is done in the `ODBC Data Source Administrator`.
The name of the ODBC connection should be used after `DSN=` in the ODBC connection string.

### User

**Description**: The user used to connect to the SQL database
**Required**: no

### Password

**Description**: The password used to connect to the SQL database
**Required**: no

### Driver

**Description**: The database driver that will be used to connect to the SQL database.
**Required**: yes
**Options**: postgres | oracle | sqlserver | mysql | sqlite | hdb | ase | odbc

### QueryInterval

**Description**: The interval in seconds between execution of the SQL query.
**Required**: yes
**Default**: 5
**Minimum**: 1

### QueryTimeout

**Description**: The timeout in seconds of the SQL query.
**Required**: yes
**Default**: 5
**Minimum**: 1

### QueryString

**Description**: The query string used to query the SQL database.
**Required**: no
**Examples**:

- `postgres`: SELECT \* FROM table\_name WHERE updated > $1;
- `oracle`: SELECT \* FROM table\_name WHERE updated > :1;
- `sqlserver`: SELECT \* FROM table\_name WHERE updated > @p1;
- `mysql/mariadb`: SELECT \* FROM table\_name WHERE updated > ?;
- `sqlite`: SELECT \* FROM table\_name WHERE updated > ?;
- `hdb`: SELECT \* FROM table\_name WHERE updated > ?;
- `ase`: SELECT \* FROM table\_name WHERE updated > ?;
- `odbc`: SELECT \* FROM table\_name WHERE updated > ?;

For the ODBC driver, the syntax of the underlying SQL database is used.

## Advanced settings

### HeartBeatInterval

**Description**: The SQL heartbeat timeout in milliseconds, minimum 5s.
**Required**: no
**Default**: 10000
**Minimum**: 5000

### TimestampLayout

**Description**: How the `ts` and `updated` columns are parsed when the driver returns them as a string or an integer, which SQLite and some ODBC drivers do. Accepts a Go layout, or one of `UNIX`, `UNIXMILLIS`, `UNIXMICROS` and `UNIXNANOS` for epoch timestamps. Without a matching layout those rows are dropped.
**Required**: no
**Default**: RFC3339

## Measurement settings

The measurement settings reflect the configuration possibilities for mapping a measurement to a row of the query result.

### MeasurementKey

**Description**: The key that will get mapped to this measurement from the query result.
**Required**: yes

## Data collection method

The SQL collector allows you to collect data from a SQL database. This is done by periodically executing the [configured query](docId\:ARYaQtYz4Bk_KoyZry6Ui). The collector expects the following columns to be returned:

- `measurement_key`: used to link the row to a measurement.
- `quality`: the quality of the data, will get added as the status tag. An empty value is considered as a “Good” status.
- `value`: the value of the data point, should be of the corresponding measurement datatype.
- `ts`: the timestamp of the data point.
- `updated`: this value used to make sure we don’t query data more than once, the most recent timestamp is kept and passed to the query as a parameter.
- `tag_`: all columns starting with `tag_` are considered as tags with everything after `tag_` being used as the tag name.

## Example query

```sql
SELECT
  a.ID AS measurement_key,
  a.VAL AS value,
  b.TIMESTAMP AS ts,
  a.UPDATED AS updated,
  'Good' AS quality,
  b.PRODUCT_NR AS tag_prodnr,
FROM FACTRY.A a
INNER JOIN FACTRY.B b ON b.NR = a.B_NR
WHERE updated > ?;
```

| measurement\_key | value | ts                     | updated                | quality | tag\_prodnr |
| ---------------- | ----- | ---------------------- | ---------------------- | ------- | ----------- |
| example\_key     | 12.0  | 2022-09-29 11:30:00+00 | 2022-09-29 11:50:00+00 | Good    | 42          |