Skip to main content

Documentation Portal

How to set up a Generic JDBC data source?

Creating Generic JDBC provider

To integrate with and ingest data from a database that supports JDBC, a datasource of provider type Genertic JDBC can be added and configured in ConfigHub. 

To add a Generic JDBC data source, in ConfigHub go to Data in the left side menu, then Data sources, choose + Add data source. Type the name of the data source, select “Generic JDBC” from the Provider dropdown. Selecting the capabilities for the data source with the given checkboxes will open additional properties to enter, grouped in following sections:

  • Provider configuration - lists properties related to the configuration of the connection that will be established with Generic JDBC

  • Time series configuration – lists properties relevant for the timeseries data

  • Asset configuration - lists properties relevant for asset data

generic_jdbc_datasource.png

Following properties need to be populated to establish the connection to the JDBC provider:

  • “Name“ - а mandatory name needs to be specified.

  • "Provider" - select the option "Generic JDBC" from the dropdown.

  • "Capabilities" - the Generic JDBC provider supports the Timeseries and Asset capabilities.

  • “Prefix” - optional text can be entered here that will be used to prefix the tag names in TrendMiner, if a value is entered. Warning: this cannot be changed. The datasource can however be deleted and you can start the configuration over again.

  • "JDBC url" - mandatory JDBC url to establish a connection to.

  • "Driver properties" - additional  optional driver specific properties can be configured here as key;value, where key is the name of driver specific property and value the desired value for this property. Please consult the documentation of the specific JDBC driver for more information.

  • "Username" - optional convenience field to enter the username used to establish a database connection. As an alternative this information can be entered through the driver properties.

  • "Password" - optional convenience field to enter the password used to establish a database connection. As an alternative this information can be entered through the driver properties.

  • "Delimiter" - optional delimiter override, used to parse data values of type STRING_ARRAY. The default is a comma (,)."Delimiter" - optional delimiter override, used to parse data values of type STRING_ARRAY. The default is a comma (,).

generic_jdbc_datasource_add.png

Following properties need to be entered to enable time series capability:

  • “Tag list query” - a query used by TrendMiner to create tags. A query needs to be provided that given the database schema and tables used will return the following columns:

    • id - this is mandatory and must have a unique value for each row returned by the query.

    • name - this is mandatory and must have a value.

    • type - an optional type indication. If the type column is not in the query, TrendMiner will use the default type which is ANALOG. Custom types can be returned here not known to TrendMiner but then a type mapping must be added as well. See also type mapping below. Without additional type mapping it must be one of the following TrendMiner tag types: ANALOG, DIGITAL, DISCRETE, STRING

    • description - an optional description to describe the tag. Can return an empty value or null.

    • units - an optional indication of the units of measurement for this tag. Can return an empty value or null.

  • “Index query” - a query used by TrendMiner to ingest timeseries data for a specific tag. The query is expected to return a ts (timestamp) and a value. The following variables are available to be used in the WHERE clause of this query. The variables will be substituted at query execution with values. There should not be any NULL values returned by the SELECT statement so including "IS NOT NULL" in the WHERE clause is strongly recommended.

    • {ID}. The id as returned by the tag list query.

    • {STARTDATE} TrendMiner will ingest data per intervals of time. This denotes the start of such an interval.

    • {ENDDATE} TrendMiner will ingest data per intervals of time. This denotes the end of such an interval.

    • {INTERPOLATION}: Interpolation type of a tag, which can be one of two values: LINEAR or STEPPED.

    • {INDEXRESOLUTION}: Index resolution in seconds. It can be used to optimise a query to return the expected number of points.

  • “Internal plotting” - if this option is selected TrendMiner will use interpolation if needed to plot the timeseries on the chart on specific time ranges. If this value is not selected and no plot query was provided, TrendMiner will plot directly from the index.

  • “Plot query” – a query used by TrendMiner to plot timeseries data for a specific tag. The query is expected to return a ts (timestamp) and a value. The following variables are available to be used in the WHERE clause of this query. The variables will be substituted at query execution with values. There should not be any NULL values returned by the SELECT statement so including "IS NOT NULL" in the WHERE clause is strongly recommended.

    • {ID}. The id as returned by the tag list query.

    • {STARTDATE}. TrendMiner will plot data per intervals of time. This denotes the start of such an interval.

    • {ENDDATE}. TrendMiner will plot data per intervals of time. This denotes the end of such an interval.

    • {INTERPOLATION}: Interpolation type of a tag, which can be one of two values: LINEAR or STEPPED.

    • {INDEXRESOLUTION}: Index resolution in seconds. It can be used to optimise a query to return the expected number of points.

  • “Digital states query” - if tags of type DIGITAL are returned by the tag list query, TrendMiner will use this query to ask for the {stringValue, intValue} pairs for a specific tag to match a label with a number. The query expects to return a stringValue and an intValue and can make use of the {ID} variable in the where clause. The variable will be substituted at query execution with a value.

  • “Tag filter” - an optional regular expression to be entered. Only tags with names that match this regex will be retained when creating tags (using the tag list query).

  • “Type mapping” - if types differ from what TrendMiner uses and one wants to avoid overcomplicating the query, a type mapping can be entered here. It can contain multiple lines and expect per line the value to be representing a custom type followed by a ; and a TrendMiner type.  During the execution of the tag list query TrendMiner will then use this mapping to map the custom values returned in the query to TrendMiner tag types.

    1. customTypeA;ANALOG

    2. customTypeB;DIGITAL

    3. customTypeC;DISCRETE

    4. customTypeD;STRING

  • "DateTime format" - optional format your time series data are stored in.

  • "Timezone" – optional timezone your times series data are stored in. Default is UTC.

generic_jdbc_datasource_add_timeseries.png

Following properties need to be entered to enable asset capability:

  • "Root asset query" - this query is responsible for returning all top level elements from the asset tree structure. It will be used to traverse further down the tree using to queries below. The order of the columns in the result needs to be:

    • id. Required, unique identifier for this asset record.

    • type. Required, TrendMiner can handle asset of type ASSET or ATTRIBUTE. This column must be one of these two values.

    • name. Required, name for this asset record.

    • description. Optional, description for this asset record.

    • data type. Optional, TrendMiner can ingest 4 different types of data in case the asset is of type ATTRIBUTE. This column will impact on how the data value column will be interpreted:

      • STRING data value is interpreted as text

      • STRING_ARRAY comma delimited string

      • NUMERIC

      • DATA_REFERENCE

    • data value. Optional, this column will either be interpreted as text (STRING), a collection of text (STRING_ARRAY), a number (NUMERIC), or a reference to a tag (DATA_REFERENCE).

  • "Asset query" - this query returns details for a specific asset record. The following variables are available to be used in the where clause of this query. The variables will be substituted at query execution with values:

    • {ID}. The id as the unique asset identification.

    The order of the columns in the result needs to be:

    • id. Required, unique identifier for this asset record.

    • type. Required, TrendMiner can handle asset of type ASSET or ATTRIBUTE. This column must be one of these two values.

    • name. Required, name for this asset record.

    • description. Optional, description for this asset record.

    • data type. Optional, TrendMiner can ingest 4 different types of data in case the asset is of type ATTRIBUTE. This column will impact on how the data value column will be interpreted:

      • STRING data value is interpreted as text

      • STRING_ARRAY comma delimited string

      • NUMERIC

      • DATA_REFERENCE

    • data value. Optional, this column will either be interpreted as text (STRING), a collection of text (STRING_ARRAY), a number (NUMERIC), or a reference to a tag (DATA_REFERENCE).

  • "Asset children query" - this query returns the ids of the direct descendants of the asset. The id passed is the parent, the records returned are its child nodes.  The following variables are available to be used in the where clause of this query. The variables will be substituted at query execution with values:

    • {ID}. The id as the unique asset identification.

    The order of the columns in the result needs to be:

    • id. Required, unique identifier for this asset record.

generic_jdbc_datasource_add_asset.png
Example queries - Postgres

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts between TO_TIMESTAMP({STARTDATE}, 'YYYY/MM/DD HH24:MI:SS') and TO_TIMESTAMP({ENDDATE}, 'YYYY/MM/DD HH24:MI:SS') order by ts asc

Plot query

select ts, value from source where id={ID} and ts between TO_TIMESTAMP({STARTDATE}, 'YYYY/MM/DD HH24:MI:SS') and TO_TIMESTAMP({ENDDATE}, 'YYYY/MM/DD HH24:MI:SS') order by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

select stringValue, intValue from source where id={ID}

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name, description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}

Example queries - MariaDB

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts >= STR_TO_DATE({STARTDATE}, '%Y-%m-%d %H:%i:%S') and ts <=STR_TO_DATE({ENDDATE}, '%Y-%m-%d %H:%i:%S') order by ts asc

Plot query

select ts, value from source where id={ID} and ts >= STR_TO_DATE({STARTDATE}, '%Y-%m-%d %H:%i:%S') and ts <=STR_TO_DATE({ENDDATE}, '%Y-%m-%d %H:%i:%S') order by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

Select stringValue, intValue from source where id={ID}

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name,description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}

Example queries - SQLlite

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts>={STARTDATE} and ts<={ENDDATE} order by ts asc

Plot query

select ts, value from source where id={ID} and ts>={STARTDATE} and ts<={ENDDATE} order by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

select stringValue, intValue from source where id={ID}

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name, description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}

Example queries - MSSQL

Tag list query

select id, name, type, description, units from source

Index query

select ts, value from source where id={ID} and ts between convert(DATETIME, {STARTDATE}, 127) and convert(DATETIME, {ENDDATE}, 127) order by ts asc

Plot query

select ts, value from source where id={ID} and ts between convert(DATETIME, {STARTDATE}, 127) and convert(DATETIME, {ENDDATE}, 127) order by ts asc

Digital states query

This query is applicable when there are tags of type STRING returned through the tag list query.

select stringValue, intValue from source where id={ID}

Root asset query

select id, type, name, parentId, description, dataType, dataValue from source where parentId is NULL

Asset query

select id, type, name, description, dataType, dataValue from source where id in ({IDS})

Asset children query

select id from source where parentId={ID}
In this section: