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 which represent a database table.

• Fields are represented by Variables .

Inserts using Custom Data Types

• Complex Variables (ModuleA) represents Measurements

• Variables underneath within the complex variable (Temperature) represents Fields

• Arrays can be used to set use an index

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 a flush interval of 10000ms

Event Configuration

3. Select the event node

4. Enable the checkbox to configure the event

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

   • Enter tags - comma separated

Configuration of tags

5. Select the variable which should be a tag

6. Enable Extended Configuration

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

   • Enable the checkbox isTag

Configuration of fields

7. Select the variable which should be a field

8. Enable Extended Configuration

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

   • Leave the checkbox isTag disabled

Array Configuration

9. Select the Array

10. To configure the Array select Extended Configuration

• (Optional) Enter an index name

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

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

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 which represent a database table.

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

• Columns of databases are represented by Variables .

Select

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

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

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

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.

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	jdbc:sqlserver://localhost:1433;databaseName=unifier;trustServerCertificate=true jdbc:mariadb://localhost:3306/unifier?connectTimeout=5000 jdbc:db2://127.0.0.1:50000/TESTDB jdbc:hsqldb:file:$dbFileName;shutdown=true jdbc:oracle:thin:@localhost:1521/MYCDB jdbc:postgresql://127.0.0.1:5432/postgres
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.

5. 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.

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

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.