sqlDashboards Blog

Real-Time Charting of Electrical Sensor Data

Let’s look at how we can display real-time and historical sensor data for Household Electrical usage using sqlDashboards.

The historical data is taken from the UCI Machine Learning Repository. This is a single CSV file with 1 minute readings between December 2006 and November 2010. i.e.

The Sensor Data Sql Dashboard

Time Series Data Format

The data came in the following format:

Where the columns have the following meaning:

• date: Date in format dd/mm/yyyy
• time: time in format hh:mm:ss
• global_active_power: household global minute-averaged active power (in kilowatt)
• global_reactive_power: household global minute-averaged reactive power (in kilowatt)
• voltage: minute-averaged voltage (in volt)
• global_intensity: household global minute-averaged current intensity (in ampere)
• sub_metering_1: energy sub-metering No. 1 (in watt-hour of active energy). It corresponds to the kitchen, containing mainly a dishwasher, an oven and a microwave (hot plates are not electric but gas powered).
• sub_metering_2: energy sub-metering No. 2 (in watt-hour of active energy). It corresponds to the laundry room, containing a washing-machine, a tumble-drier, a refrigerator and a light.
• sub_metering_3: energy sub-metering No. 3 (in watt-hour of active energy). It corresponds to an electric water-heater and an air-conditioner.

Creating the SQL Dashboard Charts

To create the live charts I:

1. Imported the data to MySQL database.

[raw]
<br /> CREATE TABLE `power` (<br /> `date` date NOT NULL,<br /> `time` time NOT NULL,<br /> Global_active_power double DEFAULT NULL,<br /> Global_reactive_power double DEFAULT NULL,<br /> Voltage double DEFAULT NULL,<br /> Global_intensity double DEFAULT NULL,<br /> Sub_metering_1 double DEFAULT NULL,<br /> Sub_metering_2 double DEFAULT NULL,<br /> Sub_metering_3 double DEFAULT NULL);</p> <p>LOAD DATA INFILE ‘household_power_consumption.txt’ INTO TABLE power FIELDS TERMINATED BY ‘;’ IGNORE 2075000 LINES (@Date,Time,Global_active_power,Global_reactive_power,Voltage,Global_intensity,Sub_metering_1,Sub_metering_2,Sub_metering_3) SET Date = str_to_date(@Date, ‘%d/%m/%Y’);<br />
[/raw]

2. Created a java process to insert live sensor data.
3. Opened sqlDashboards
4. Added a time series monthly graph and using the sql query:
   
   select STR_TO_DATE(DATE_FORMAT(Date, '%Y-%m-01'),'%Y-%m'),AVG(Global_active_power),AVG(Global_reactive_power) FROM power GROUP BY DATE_FORMAT(Date, '%Y-%m-01')
5. Added a daily time series rollup graph and using the sql query:
   
   CREATE TABLE daily AS select Date,AVG(Global_active_power),AVG(Global_reactive_power) FROM power WHERE Date>'2009-11-11' GROUP BY Date;
6. Added a bar chart showing the different meter readings as bar charts per year:
   
   select CAST(YEAR(Date) AS CHAR(4)) AS Year,AVG(Sub_metering_1),AVG(Sub_metering_2),AVG(Sub_metering_3) from power GROUP BY YEAR(Date)
7. Added further queries to show the live data for today…

This gave me the final dashboard:

[raw]

[/raw]

SQL Pie Chart MYSQL

I came across this interesting post on ASCII pie charts for MySQL. I thought I’d use our command line tool sqlChart todraw the database pie chart instead.

[raw]
<br /> c:\Program Files (x86)\TimeStored.com\sqlDashboards>mysql -s mysql -u root –password=pp -D sakila<br /> -e “SELECT * FROM (SELECT table_name,data_length + index_length AS size FROM information_schema.TABLES WHERE table_schema = ‘sakila’) a<br /> WHERE SIZE IS NOT NULL ORDER BY SIZE DESC LIMIT 8”</p> <p>table_name size<br /> rental 2850816<br /> payment 2228224<br /> inventory 376832<br /> film_text 325440<br /> film_actor 278528<br /> film 278528<br /> customer 131072<br /> staff 98304</p> <p>c:\Program Files (x86)\TimeStored.com\sqlDashboards>sqlchart -s mysql -u root –password=pp -D sakila -e “SELECT *<br /> FROM (SELECT table_name,data_length + index_length AS size FROM information_schema.TABLES WHERE table_schema = ‘sakila’) a<br /> WHERE SIZE IS NOT NULL ORDER BY SIZE DESC LIMIT 8” -o c:\temp\aa\sizes.png -c piechart<br /> c:\temp\aa\sizes.png<br />
[/raw]

Simply changing our mysql command to sqlchart and adding the additional arguments
"-o c:\temp\aa\sizes.png -c piechart"
gives us the following SQL Pie Chart:

MySQL Sakila Table Sizes Pie Chart

Notice because sqlchart’s command line arguments closely resemble the mysql command line options, we can quickly change the calls from querying to graphing.