ODBC Connector Configuration

• Prerequisites
• Section “connection”
  • Subsection “attributes”
  • Subsection “decoding”
• Section “pyodbc”
• Property “converter”
• Section “polling”
  • Subsection “iterator”
• Section “mapping”
  • Subsection “device”
  • Subsections “attributes” and “timeseries”
• Section “serverSideRpc”
• Next steps

This guide will help you to get familiar with ODBC connector configuration for ThingsBoard IoT Gateway. Use general configuration to enable this connector. We will describe connector configuration file below.

{
  "connection": {
    "str": "Driver={PostgreSQL};Server=localhost;Port=5432;Database=thingsboard;Uid=postgres;Pwd=postgres;",
    "attributes": {
      "autocommit": true,
      "timeout": 0
    },
    "encoding": "utf-8",
    "decoding": {
      "char": "utf-8",
      "wchar": "utf-8",
      "metadata": "utf-16le"
    },
    "reconnect": true,
    "reconnectPeriod": 60
  },
  "pyodbc": {
    "pooling": false
  },
  "polling": {
    "query": "SELECT bool_v, str_v, dbl_v, long_v, entity_id, ts FROM ts_kv WHERE ts > ? ORDER BY ts ASC LIMIT 10",
    "period": 5,
    "iterator": {
      "column": "ts",
      "query": "SELECT MIN(ts) - 1 FROM ts_kv",
      "save": false
    }
  },
  "mapping": {
    "device": {
      "type": "postgres",
      "name": "'ODBC ' + entity_id"
    },
    "sendDataOnlyOnChange": false,
    "attributes": "*",
    "timeseries": [
      "ts",
      {
        "name": "value",
        "value": "[i for i in [str_v, long_v, dbl_v,bool_v] if i is not None][0]"
      }
    ]
  },
  "serverSideRpc": {
    "enableUnknownRpc": true,
    "methods": [
      "procedureOne",
      {
        "name": "procedureTwo",
        "args": [ "One", 2, 3.0 ]
      }
    ]
  }
}

Prerequisites

To install and get ODBC connector working several additional steps need to be done:

1. Install Visual C++ Redistributable package for Windows or ODBC package for Linux.
2. Install ODBC driver(s) for database(s) the ThingsBoard gateway need to connect.
3. Add data source in ODBC Data source Administrator on Windows or add driver information (name, library path etc.) to ODBC configuration file odbcinst.ini on Unix systems.

Section “connection”

This mandatory section provides information how to connect or reconnect to ODBC database.

Note: More information about encoding/decoding read there.

Subsection “attributes”

This optional subsection provides several options to tune connection procedure.

Subsection “decoding”

This optional subsection provides information how to decode string data and metadata read from a database.

Note: More information about encoding/decoding read there.

Section “pyodbc”

This optional section provides options to tune pyodbc Python library which is working under the hood of ODBC Connector.

"pyodbc": {
  "pooling": false,
  "native_uuid": true
},

Property “converter”

ODBC connector is provided with built-in uplink data converter. One can specify custom converter class in this optional property.

"converter": "CustomOdbcUplinkConverter",

Section “polling”

The main idea of ODBC connector is periodically querying ODBC database whether new data is appeared.

This mandatory section provides information how often to query database, what data to select and which database column is used to iterate over result.

The requirements for the query option:

1. Valid SQL SELECT statement that meets requirements of SQL dialect of the database the ThingsBoard gateway need to connect.
2. Include attributes or/and timeseries columns in SELECT list.
3. Include the device column in SELECT list to find out to which device data belongs to.
4. Include the iterator column in SELECT list.
5. Among other conditions SQL WHERE clause must include the iterator condition.
6. Among other sorting expressions SQL ORDER BY clause must include the iterator sorting expression.
7. It is recommended to use SQL LIMIT clause to reduce memory consumption on each read from a database.

Example:

Each polling iteration the connector will read 10 records sorted by ts column (iterator).

Each record consists of timeseries columns (bool_v, str_v, dbl_v, long_v), device column (entity_id) and iterator column (ts).

After each polling iteration the connector remembers the value of the ts column of 10th record (the last record) and use it in WHERE clause on the next iteration.

SELECT bool_v, str_v, dbl_v, long_v, entity_id, ts   (2-3)
FROM ts_kv
WHERE ts > ?                                         (4)
ORDER BY ts ASC                                      (5)
LIMIT 10                                             (6)

Subsection “iterator”

This mandatory subsection provides information on what database column is used to iterate through the result set, where to get the initial value of the iterator and whether to use iterator data between gateway work sessions.

IMPORTANT

The main challenge of the iterator feature is to unambiguously figure out whether to restore iterator data from a previous gateway work session or to use values from the connector configuration file.

Each iterator has its own file that has been stored in config/odbc/ folder. After each polling iteration the connector saves iterator data (see below the persistent option) to such file.

- How does connector distinguish iterator files from each other?

- The short answer is a decision is based on the iterator file name.

In details, once the connector starts and connects to a database it checks whether the persistent flag (see below) is set to true. If so the connector calculates the iterator file name and checks if it exists in config/odbc/ folder.

If the file exists the connector loads iterator data from it. Otherwise iterator data is loaded from the connector’s configuration file.

The iterator file name is a hash of:

• ODBC driver name
• database server name
• database name
• iterator column (see below the column option)

DRAWBACK

There may happen that while using the same database the list of tables is totally changed but the iterator column name is not just because the same column name is used in the different tables. In this case the connector loads wrong iterator data.

CONCLUSION

1. For the same database use unique name for each iterator.
2. Enable iterator persistence feature only when other connector’s configuration parts have been debugged and the lists of attributes and timeseries are finalized.

Note: Options value and query are mutually exclusive. If both options are set value will be used.

Section “mapping”

This mandatory section provides information how to map the result set that is get from a database to device attributes and timeseries values.

Subsection “device”

This mandatory subsection provides information how to map the result set to unique device name and its type.

Note All database columns listed in SQL SELECT clause of the query option are available by its name in the Python eval() context.

For example,

"device": {
  "name": "'ODBC' + entity_id"
}

,means that device name is a result of concatenating two strings: ODBC and the value of database column entity_id.

Subsections “attributes” and “timeseries”

These optional subsections provides information on what database columns are treated as attributes and what as time series keys and what pre-processing job should be done before sending to ThingsBoard server.

The connector supports several configuration modes for these subsections:

• list of database columns

  "timeseries": [ "str_v", "ts" ]
  

• list of configurations

  "timeseries": [
  {
    "name": "boolValue",
    "column": "bool_v"
  },
  {
    "nameExpression": "key_name",
    "value": "[i for i in [str_v, long_v, dbl_v,bool_v] if i is not None][0]"
  },
  {
    "name": "value",
    "value": "[i for i in [str_v, long_v, dbl_v,bool_v] if i is not None][0]"
  }
  ]
  

Note All database columns listed in SQL SELECT clause of the query option are available by its name in the Python eval() context.

• combining mode

  "timeseries": [
  "ts",
  {
    "name": "value",
    "value": "[i for i in [str_v, long_v, dbl_v,bool_v] if i is not None][0]"
  }
  ]
  

• globbing

  "timeseries": "*"
  

  , means treating all database columns as timeseries.

Section “serverSideRpc”

The connector is able to call SQL procedures/functions with or without parameters. Parameters are get either from a connector’s configuration file or from data received from a server.

The connector supports several configuration modes for the methods subsection:

• list of procedures/functions without parameter

  "methods": [ "procedureOne", "procedureTwo" ]
  

• list of procedure/function configurations
  
  The order of arguments matters. It must be the same as the order of parameters in SQL procedure/function.

  "methods": [
  {
    "name": "rpcProcOne",
    "args": [ "One", 2, 3.0 ],
    "query": "CALL procedureOne(?,?,?)"
  },
  {
    "name": "functionOne",
    "args": [ false ]
  }
  ]
  

  Procedure / function configuration parameters

• combining mode

  "methods": [
  "procedureOne",
  {
    "name": "procedureTwo",
    "args": [ "One", 2, 3.0 ]
  }
  ]
  

IMPORTANT

If enableUnknownRpc is set to true, RPC params must have all needed procedure/function configuration parameters.

If overrideRpcConfig is set to true, RPC params may contain all or some of procedure/function configuration parameters to override ones that are specified in the connector configuration file.

The order of arguments matters. It must be the same as the order of parameters in SQL procedure/function.

{
  "device": "ODBC Device 1", 
  "data": {
    "method": "procedureOne", 
    "params": {
      "args": [ "OverridedValue", 123, 3.14 ]
    }
  }
}

Next steps

Explore guides related to main ThingsBoard features:

• Data Visualization - how to visualize collected data.
• Device attributes - how to use device attributes.
• Telemetry data collection - how to collect telemetry data.
• Using RPC capabilities - how to send commands to/from devices.
• Rule Engine - how to use rule engine to analyze data from devices.