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 get familiar with ODBC connector configuration for ThingsBoard IoT Gateway. Use general configuration to enable this connector. We will describe the connector configuration file below.

Example of ODBC Connector config file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
{
  "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 needs to connect to.
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 on how to connect or reconnect to ODBC database.

Note: For more information about encoding/decoding, refer to the documentation.

Subsection “attributes”

This optional subsection provides several options to tune the connection procedure.

Subsection “decoding”

This optional subsection provides information on 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 the pyodbc Python library, which works under the hood of ODBC Connector.

1
2
3
4
"pyodbc": {
  "pooling": false,
  "native_uuid": true
},

Property “converter”

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

1
"converter": "CustomOdbcUplinkConverter",

Section “polling”

The main idea of ODBC connector is in periodically querying ODBC database to check whether new data has appeared.

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

The requirements for the query option:

1. Valid SQL SELECT statement that meets requirements of SQL dialect of the database the ThingsBoard gateway needs to connect to.
2. Include attributes or/and timeseries columns in SELECT list.
3. Include the device column in the SELECT list to determine to which device data belongs to.
4. Include the iterator column in the 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:

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

1
2
3
4
5
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 which 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 is stored in the config/odbc/ folder. After each polling iteration the connector saves iterator data (see below the persistent option) to such file.

- How does the connector distinguish iterator files from each other?

- The short answer: it 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 on how to map the result set received from a database to device attributes and timeseries values.

Subsection “device”

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

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

For example,

1
2
3
"device": {
  "name": "'ODBC' + entity_id"
}

means that the 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 provide information on which database columns are to be treated as attributes, which ones as time series keys, and what pre-processing tasks should be done before sending data to the ThingsBoard server.

The connector supports several configuration modes for these subsections:

• list of database columns

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

• list of configurations

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  "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 their names in the Python eval() context.

• combining mode

  1
  2
  3
  4
  5
  6
  7
  "timeseries": [
  "ts",
  {
    "name": "value",
    "value": "[i for i in [str_v, long_v, dbl_v,bool_v] if i is not None][0]"
  }
  ]
  

• globbing

  1
  "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 obtained 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

  1
  "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.

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  "methods": [
  {
    "name": "rpcProcOne",
    "args": [ "One", 2, 3.0 ],
    "query": "CALL procedureOne(?,?,?)"
  },
  {
    "name": "functionOne",
    "args": [ false ]
  }
  ]
  

  Procedure / function configuration parameters

• combining mode

  1
  2
  3
  4
  5
  6
  7
  "methods": [
  "procedureOne",
  {
    "name": "procedureTwo",
    "args": [ "One", 2, 3.0 ]
  }
  ]
  

IMPORTANT

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

If overrideRpcConfig is set to true, RPC params may contain all or some of the procedure/function configuration parameters to override those 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.

1
2
3
4
5
6
7
8
9
{
  "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.