mac使用thingsboard-gateway 采集postgresql odbc的方式

依赖安装

unixODBC + pyodbc + psqlodbc

解释一下：

pyodbc是thingsboard-gateway依赖的python库。

unixOdbc是pyodbc依赖的unix包。

psqlodbc是postgresql的odbc驱动。

brew install unixodbc
brew install psqlodbc

unixODBC 配置

vim odbc.ini

[pg]

Description = PostgreSQLODBC

Driver = PostgreSQL

Database = thingsboard

Servername = 192.168.0.190

UserName = postgres

Password = postgres

Port = 5432

ReadOnly = 0

vim odbcinst.ini

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/local/Cellar/psqlodbc/16.00.0000/lib/psqlodbcw.so

Setup           = /usr/local/Cellar/psqlodbc/16.00.0000/lib/psqlodbca.so

Driver64        = /usr/local/Cellar/psqlodbc/16.00.0000/lib/psqlodbcw.so

Setup64         = /usr/local/Cellar/psqlodbc/16.00.0000/lib/psqlodbca.so

FileUsage       = 1

验证unixODBC和psqlodbc是否安装和配置正确

配置thingsboard-gateway文件

{
  "connection": {
    "str": "Driver={PostgreSQL};Server=192.168.0.190;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": 10,
    "iterator": {
      "column": "ts",
      "query": "SELECT MIN(ts) - 1 FROM ts_kv",
      "persistent": false
    }
  },
  "mapping": {
    "device": {
      "type": "postgres",
      "name": "'ODBC ' + entity_id"
    },
    "sendDataOnlyOnChange": false,
    "attributes": "*",
    "timeseries": [
      {
        "name": "value",
        "value": "[i for i in [str_v, long_v, dbl_v,bool_v] if i is not None][0]"
      }
    ]
  },
  "serverSideRpc": {
    "enableUnknownRpc": false,
    "overrideRpcConfig": true,
    "methods": [
      "procedureOne",
      {
        "name": "procedureTwo",
        "args": [
          "One",
          2,
          3.0
        ]
      }
    ]
  }
}