Databases

InfluxDB

Characteristics - InfluxDB

In case of a time series data use case where you need to ingest data in a fast and efficient way you can use InfluxDB.

Information Model Requirements

Inserts using Events

  • The node after the root model in this case is of the type Event eventNode which represent a database table.

  • Fields are represented by Variables variableNode.

ExampleInsertModel

Inserts using Custom Data Types

ExampleInsertModelWithComplexVariable
  • Arrays arrayNode can be used to set use an index

ExampleInsertModelWithComplexVariableAndArrays

How to configure InfluxDB

  1. Select the root model node in the tree on the left.

  2. Configure the InfluxDB.

    • Enter the URL to the database

    • Enter the database name

    • Enter the database username and password or select it from the Credentials Manager

    • Enter the Batch size - writes data in batches to minimize network overhead when writing data to InfluxDB

    • Enter the Flush interval (ms) - if data should be written every 10 seconds enter an flush interval of 10000ms

InfluxDBConfiguration

Event Configuration

  1. Select the event node

  2. Enable the checkbox to configure the event

    • Enter the measurement - if it differs from the event name

    • Enter tags - comma separated

InfluxDBConfigurationEvent

Configuration of tags

  1. Select the variable which should be a tag

  2. Enable Extended Configuration

    • Enter a name - if it differs from the variable name

    • Enable the checkbox isTag

InfluxDBConfigurationEventVariable

Configuration of fields

  1. Select the variable which should be a field

  2. Enable Extended Configuration

    • Enter a name - if it differs from the variable name

    • Leave the checkbox isTag disabled

InfluxDBConfigurationEventVariable

Array Configuration

  1. Select the Array

  2. To configure the Array select Extended Configuration

  • (Optional) Enter a index name

  • (Optional) Enter a field names if the event node name differs from the actual name in InfluxDB.

  • (Optional) Enter tags separated by commas e.g., (location=NewYork, street=xxx)

InfluxDBConfigurationArrays

SQL Database

Characteristics - SQL Database

  • The SQL Channel can be configured for the following two scenarios:

    • Inserting data

    • Updating data

    • Retrieving data

  • When inserting values into the database please note that “infinity” values are converted automatically into “null” values.

Information Model Requirements

Insert/Update

  • The node after the root model node must be of type Event eventNode which represent a database table.

  • In case of relational databases: Tables which are dependent on each other require a List listNode.

  • Columns of databases are represented by Variables variableNode.

Example insert model

Select

  • The Command commandNode defines that after a request is made, a reply with a result is expected.

  • Parameters commandParameterNode within a Command represent a collection of query parameter - query parameters are defined as Variables variableNode.

  • Reply commandReplyNode within a Command represents the result of the Command - results are defined as Variables variableNode.

Example Select Model

How to configure the SQL-Database

  1. Select the root model node in the tree on the left.

  2. Configure the database connection

    • Select the database type.

    • Specify a reconnection interval.

    • Enter the database connection url for the specific database type.

      • DB2: jdbc:db2:server:port/database

      • HSQLDB: jdbc:hsqldb:file:databaseFileName;properties

      • ORACLE: jdbc:oracle:thin:prodHost:port:sid

      • PostgreSQL: jdbc:postgresql://host:port/database

      • SQLServer: jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

      • MariaDB: jdbc:(mysql|mariadb):[replication:|loadbalance:|sequential:|aurora:]//<host>[:<portnumber>]/[database][?<key1>=<value1>[&<key2>=<value2>]]

    • Enter the database username and password or select it from the Credentials Manager.

SQL Database Configuration

Property

Description

Example

Type

Type of the database

MariaDB, SQLServer, ORACLE, HSQLDB, DB2, PostgreSQL

ReconnectInterval

Time to reconnect if connection fails

10 (in milliseconds)

JdbcUrl

Url to connect to database

Username and password

Credentials of the database

Note

The configuration of specific information model nodes differs whether you want to perform an insert or an select statement on the database. Inserting data into the database requires an event node whereas selecting data requires a command node in the Information Model.

Select Statement

  1. Select the command node in the tree on the left.

  2. Check the custom query checkbox and enter the sql query.

SQL Database Configuration
  1. Each variable under Parameters and Reply needs to be assigned to a database column. Select the variable node under Parameters and in the tree select what needs to be configured.

  2. Check the assign database column checkbox and enter the column name as it is defined in the used database.

SQL Database Configuration Assign Database Column

Insert Statement

  1. Select the event node in the tree on the left.

  2. Check the insert checkbox and enter the table name. If required enter a schema name.

SQL Database Configuration Insert