An SQL Historian is available as a core service. The sql historian has been programmed to allow for inconsistent network connectivity (automatic re-connection to tcp based databases). All additions to the historian are batched and wrapped within a transaction with commit and rollback functions properly implemented. This allows the maximum throughput of data with the most protection. The following example configurations show the different options available for configuring the SQL Historian Agent.
MySQL requires a third party driver (mysql-connector) to be installed in order for it to work. Please execute the following from an activated shell in order to install it.
pip install --allow-external mysql-connector-python mysql-connector-python
or
python bootstrap.py --mysql
or
python bootstrap.py --databases
{ "agentid": "sqlhistorian-mysql", "connection": { "type": "mysql", "params": { "host": "localhost", "port": 3306, "database": "volttron", "user": "user", "passwd": "pass" } } }
An Sqlite Historian provides a convenient solution for under powered systems. The database is parameter is a location on the file system. By default it is relative to the agents installation directory, however it will respect a rooted or relative path to the database.
{ "agentid": "sqlhistorian-sqlite", "connection": { "type": "sqlite", "params": { "database": "data/historian.sqlite" } } }
- The PostgreSQL database driver supports recent PostgreSQL versions. It was tested on 10.x, but should work with 9.x and 11.x.
- The user must have SELECT, INSERT, and UPDATE privileges on historian tables.
- The tables in the database are created as part of the execution of the SQLHistorianAgent, but this will fail if the database user does not have CREATE privileges.
- Care must be exercised when using multiple historians with the same database. This configuration may be used only if there is no overlap in the topics handled by each instance. Otherwise, duplicate topic IDs may be created, producing strange results.
- Redshift databases do not support unique constraints. Therefore, it is possible that tables may contain some duplicate data. The Redshift driver handles this by using distinct queries. It does not remove duplicates from the tables.
The PostgreSQL and Redshift database drivers require the psycopg2 Python package.
From an activated shell execute:
pip install psycopg2-binary
The following are minimal configuration files for using a psycopg2-based historian. Other options are available and are documented http://initd.org/psycopg/docs/module.html Not all parameters have been tested, use at your own risk.
The following snippet demonstrates how to configure the SQLHistorianAgent to use a PostgreSQL database on the local system that is configured to use Unix domain sockets. The user executing volttron must have appropriate privileges.
{ "connection": { "type": "postgresql", "params": { "dbname": "volttron" } } }
The following snippet demonstrates how to configure the SQLHistorianAgent to use a remote PostgreSQL database.
{ "connection": { "type": "postgresql", "params": { "dbname": "volttron", "host": "historian.example.com", "port": 5432, "user": "volttron", "password": "secret" } } }
Both of the above PostgreSQL connection types can make use of TimescaleDB's high performance Hypertable backend for the primary timeseries table. The agent assumes you have completed the TimescaleDB installation and setup the database by following the instructions here: https://docs.timescale.com/latest/getting-started/setup To use, simply add 'timescale_dialect: true' to the connection params in the Agent Config as below
{ "connection": { "type": "postgresql", "params": { "dbname": "volttron", "host": "historian.example.com", "port": 5432, "user": "volttron", "password": "secret", "timescale_dialect": true } } }
The following snippet demonstrates how to configure the SQLHistorianAgent to use a Redshift database.
{ "connection": { "type": "redshift", "params": { "dbname": "volttron", "host": "historian.example.com", "port": 5432, "user": "volttron", "password": "secret" } } }