Backing Up and Analyzing Data from the System¶
Data Files and Local Backups¶
All of the sensor reading data in the Building Monitoring System is stored in a SQLite database file. The Sensor Reading SQLite database file is stored as:
In addition to the Sensor Reading data, the metadata that adds information about sensors, buidlings, organizations etc. is available in the Django database supporting the BMON application. This is also a SQLite database and is located at:
These two databases are automatically backed up locally by the BMON application.
The Sensor Reading database is backed
up every 3 days and stored in the directory
The backup is performed by calling the backup script
The data file is gzipped and stored with a file name indicating when it
was backed up. Backup files older than 21 days are deleted.
The Django database is backed up daily into the local directory
<project root>/bak. It is also compressed with gzip and stored
with a filename indicating the
date of backup.
It is advisable to also store these backup files off-server, and the last section on this page suggests a method to do that.
Web-based Administration of the Sensor Reading Database¶
In some cases, it may be useful to install a web-based database
administration tool onto the server where BMON is hosted. This will
facilitate archiving, exporting, and data cleaning operations. One such
tool is phpLiteAdmin,
which can provide a web-based interface to the sensor reading database.
The tool allows viewing the sensor data, executing SQL statements, and
exporting sensor reading tables. Installation of the tool is
straight-forward and documented on the web page link above. When using
the Webfaction hosting service, installation of the
application is required to run the phpLiteAdmin tool, as this tool is a
PHP web application.
Analyzing the Sensor Reading Data¶
Any ad hoc analysis of the sensor reading data can be done by simply copying the SQLite data file or one of the backed up versions of the data file onto a separate system for analysis. A number of client applications are available to display and query SQLite database data. Most programming and scripting languages have drivers for accessing SQLite data. So, there are many tools available to analyze the data.
The data structure within the SQLite database is simple. The data from
each sensor occupies its own table. The name of the table is the
Sensor ID that is entered in the Admin interface for the Building
Monitoring System. For example, for Monnit Wireless Sensors, the sensor
ID is the Monnit ID, e.g.
27613. (When using SQL statements to query
the data, table names that are numeric, such as
27613 should be
enclosed in square brackets, e.g.
, in order for proper
execution of the SQL statement.)
Each sensor table has two fields:
tsThe time the sensor reading occurred as an integer UNIX timestamp (seconds past Midnight Jan 1, 1970 UTC).
valThe value of the sensor reading in the final engineering units. Those units can be found in the Sensors table in the Admin interface.
If it is necessary to remove older data from the active SQLite database, normal SQL commands can be used to select and delete data prior to a particular timestamp. The easiest approach is probably to write a Python script to perform the deletion. Note that code similar to the following can be used to iterate across each sensor table present in the database:
import sqlite3 conn = sqlite3.connect(fname) # use the SQLite Row row_factory for all Select queries conn.row_factory = sqlite3.Row # now create a cursor object cursor = conn.cursor() # iterate across the sensor tables for rec in cursor.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall(): print rec['name'] # Prints the table name, which is the Sensor ID
Note that there are two special tables in the database that are not
sensor tables. Those are the
_last_raw table, which holds the last
reading posted from certain tables, and the
_junk table, which is
used to lock the database when a backup is occurring.
Off-Server Backup of Data Files¶
It is advisable to back up the two database files to a location off of the BMON server. One approach to this is to use the Amazon Web Services (AWS) S3 Storage Service as the destination of those backups. To use the service, there are a number of requirements:
- An AWS Account.
- Installation of the AWS Command Line Interface on the BMON server as described here.
- Installation of AWS credentials on the BMON server as described here.
- Creation of an S3 Bucket to copy the data files into. This can be done through the online AWS console.
- Adding lines to the BMON server crontab file to periodically copy backup files to the S3 bucket.
Here is an example of the crontab jobs used to backup both the sensor reading and Django database files:
18 11,23 * * * ~/bin/aws s3 sync ~/webapps/django/bmon/bak s3://bmon.xyz.com/ahfc/django 25 11,23 * * * ~/bin/aws s3 sync ~/webapps/django/bmon/bmsapp/readingdb/data/bak s3://bmon.xyz.com/ahfc/data --storage-class ONEZONE_IA
These crontab jobs copy any new files from the two BMON backup directories to the S3 bucket. Both jobs run twice a day. The large sensor reading database file uses the S3 storage class of ONEZONE_IA (One Zone Infrequent Access) to reduce storage costs.
A Lifecycle Rule was established on the S3 storage bucket to delete backup files that are older than 90 days.
For Alaska-owned BMON Servers, an expense-free S3 bucket with associated credentials is available to backup BMON data files. Contact Alan Mitchell at email@example.com.