Categories: PLUGINS ENTERPRISE ANALYTICS

JDBC (SQL)

The JDBC plugin writes Step measurements to a SQL-based database such as PostgreSQL or MySQL. This allows you to query and visualize Step execution data using any SQL-compatible tool.

The plugin has been tested with PostgreSQL 12 and MySQL 8.

Data model

Since keywords in Step can carry arbitrary custom attributes, each measurement contains a set of fixed fields and an open-ended set of custom fields:

{
  begin: ... ,
  eId: ... ,
  name: ... ,
  planId: ... ,
  rnStatus: ... ,
  taskId: ... ,
  type: ... ,
  value: ... ,
  customField1: ... ,
  customField2: ... ,
  ...
}

Since SQL databases are not designed for dynamic schema changes, the plugin exposes the fixed fields as dedicated columns and writes the custom fields into a single JSON column. PostgreSQL and MySQL both support JSON as a column type; for databases that do not, the JSON column can be disabled.

Enabling the plugin

To use the plugin:

  1. Add the JDBC driver library for your database to CONTROLLER_DIR/lib. For PostgreSQL, the driver is already included.

  2. Enable the plugin in step.properties:

plugins.JdbcMeasurementControllerPlugin.enabled=true
  1. Configure the database connection in step.properties:
plugins.measurements.jdbc.url=jdbc:postgresql://localhost/step
plugins.measurements.jdbc.user=postgres
plugins.measurements.jdbc.password=test
  1. Optionally, enable the JSON column for custom measurement attributes:
plugins.measurements.jdbc.jsonColumn=true

PostgreSQL with TimescaleDB

For best performance, we recommend PostgreSQL together with TimescaleDB. TimescaleDB is an open-source time-series extension for PostgreSQL. Its “hypertable” format allows time-series data to be processed many times faster than a plain PostgreSQL table, while remaining fully queryable with standard SQL.

The community edition of TimescaleDB is available under two open-source licenses — consult the official TimescaleDB documentation for details.

Installing TimescaleDB (optional)

Install PostgreSQL and TimescaleDB following the official instructions. We recommend running the automatic tuning tool after installation.

After installation, TimescaleDB must be enabled in the database with a CREATE EXTENSION command (see schema below).

Installing t-digest (optional)

For fast percentile calculations, we recommend the t-digest algorithm. Its SQL implementation by Tomas Vondra is available as a PostgreSQL extension.

On Linux:

sudo apt-get install postgresql-server-dev-12
make
sudo make install

Adjust the package name if you are using a different PostgreSQL version.

On Windows: the extension must be compiled to a DLL. See these instructions for guidance. A pre-compiled DLL for 64-bit Windows 10 is provided for convenience, though we cannot guarantee compatibility with all systems. Once compiled, copy:

  1. tdigest.dll → POSTGRES_DIR\lib
  2. tdigest.control, tdigest--1.0.0.sql, tdigest--1.0.0--1.0.1.sql → POSTGRES_DIR\share\extension

On all systems, enable the extension in the database with CREATE EXTENSION (see schema below).

Required schema

Create the database and enable extensions if needed:

CREATE DATABASE step;
\c step
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS tdigest;

Create the table:

CREATE TABLE measurements (
    begin timestamptz NOT NULL,
    eId text,
    status text,
    planId text,
    taskId text,
    name text,
    type text,
    value integer,
    info jsonb
);

Convert to a TimescaleDB hypertable:

SELECT create_hypertable('measurements', 'begin');

Additional indexes may be added depending on your query patterns.

MySQL

Create the database:

CREATE DATABASE step;
USE step;

Create the table:

CREATE TABLE measurements (
    begin timestamp NOT NULL,
    eId text,
    status text,
    planId text,
    taskId text,
    name text,
    type text,
    value integer,
    info json
);
CREATE INDEX begin ON measurements (begin);
CREATE INDEX begin_eId ON measurements (eId(24), begin);

See Also