SQL databases

Write events in micro batches to any database which supports JDBC Type 4 drivers

Overview

Joule can publish processed events to a configured SQL database using a SQL Publisher transport. All databases with JDBC type 4 drivers are supported. This includes Postgres, MySQL, Oracle, Snowflake, Big Query, HyperSQL etc;.

This is achieved by dynamically generating INSERT statements based on the StreamEvent, ensuring the event's attributes match the database table's schema.

This feature is ideal for use cases such as offline analytics, business reporting, dashboards and process testing.

Driver details: The necessary JDBC driver (Type 4) for the SQL database must be placed in the userlibs directory to be included in the Joule runtime classpath

Example & DSL attributes

This example configures the SQL Publisher to publish events to a PostgreSQL database.

It specifies the JDBC driver (org.postgresql.Driver), connects to the database testdb on localhost:5740 and inserts events into the s1mme table.

Timestamps are included in the insertions. The connection uses SSL with the username and password set to postgres.

sqlPublisher:
  jdbcDriver: org.postgresql.Driver
  host: jdbc:postgresql://localhost:5740
  database: testdb
  table: s1mme
  includeTimestamps: true
  properties:
    ssl: true
    user: postgres
    password: postgres

Attributes schema

Configuration parameters available for the InfluxDB publisher transport. The parameters are organised by order of importance, ranked from high to low.

Attribute	Description	Data Type	Required
jdbcDriver	Full jdbc namespace	String
host	JDBC url	String
database	Database name	String
table	Target loading table	String
includeTimestamps	Bucket where event measurements are persisted	Boolean Default: False
properties	Database specific properties	Properties map
batchSize	Insert batch size	Integer Default: 1024
timeout	Timeout to send queued events to be inserted into database	Long Default: 5000ms
autocommit	Sets connection's auto-commit mode to the given state. true to enable auto-commit mode; false to disable it	Boolean Default: false