I got a call the other day with a question: how can we store huge amount of sensor data. They are measuring air temperature in different rooms every 5 seconds. That means 17280 data points per data, 6307200 data points per year and for 15 rooms 94608000 data points per year.
Because I never had a situation where I needed to store a huge amount of sensor data, I didn’t know the answer. But I started digging. There are many questions online regarding what database to use to store this kind of data. Some recommend old-school databases like MySQL or Oracle. Some recommend Redis, Riak or MongoDB. But one recommendation beat them all: OpenTSDB.
OpenTSDB – The Scalable Time Series Database
Store and server massive amounts of time series data without losing granularity.
Currently in version 2.0, OpenTSDB is a tested solution build on top of HBase. It was designed especially for time series data and can handle
– up to 100+ billion data points and
– 2000 new data points per second (tested by OpenTSDB developers on a old dual-core Intel Xeon CPU from 2006; I tested on a newer machine and could easily insert 20000 points in few seconds).
Long story short. It’s perfect database for huge amount of sensor data. It has great options to query data (I will explain it below), has additional features to annotate data and it’s under active development.
Installation and running it for the first time
To run OpenTSDB, you need to first install HBase. The procedure is pretty straightforward. First you need to install HBase. Download HBase, unpack, define configuration and run it with
|
1 |
./bin/start-hbase.sh |
If everything was defined correctly, you should get a message
|
1 |
starting master, logging to path/to/hbase/hbase-0.94.17/bin/../logs/hbase-erol-master-erol.out |
Next step is installing OpenTSDB. There is a great tutorial how to install OpenTSDB. In short, download it and unpack or clone git repository, and run build.
|
1 2 3 |
git clone git://github.com/OpenTSDB/opentsdb.git cd opentsdb ./build.sh |
It should take few minutes to compile everything. Next step is to create tables with command
|
1 |
env COMPRESSION=NONE HBASE_HOME=path/to/hbase-0.94.17 ./src/create_table.sh |
You can see created tables with few opensource HBase viewers like hrider. Currently the compression is set to none. It’s highly recommend to use compression LZO, because there is no performance impact but it can highly reduce the size of your data.
Because we will store temperatures in metric temperatures, we need to create it first. OpenTSDB has a configuration to enable auto creation of metrics, but it’s not recommended, so we will do it manually.
|
1 |
./build/tsdb mkmetric temperatures |
The last step is to run everything.
|
1 2 3 |
tsdtmp=${TMPDIR-'/tmp'}/tsd # For best performance, make sure mkdir -p "$tsdtmp" # your temporary directory uses tmpfs ./build/tsdb tsd --port=4242 --staticroot=build/staticroot --cachedir="$tsdtmp" --zkquorum=myhost:2181 |
If everything went well, you should see OpenTSDB page at localhost:4242. It’s that simple.
How data is stored
How OpenTSDB is storing the data is in my opinion the biggest difference compared to other databases. It does support tables, but they are actually called metrics. In each metric we can store data points. Each data points is structured as
|
1 |
<timestamp> <value> <tagk1=tagv1[ tagk2=tagv2 ...tagkN=tagvN]> |
Timestamp (unix time or ISO 9601 format) is the time of the data point. Value is a number (integer or float). Then we have tags. With tags we separate data points. In our example, we are storing value for bedroom on our first floor. This structure enables us to separate data and later make advanced queries; for example average temperature on first floor or sum of all rooms.
|
1 |
1356998400 23.5 room=bedroom floor=1 |
Storing data
With version 2.0, OpenTSDB has 2 ways to store and access data (plus one additional to store by importing data). They are Telnet API, HTTP API and batch import from a file. Make sure you have OpenTSDB running before you try the examples below.
Storing with Telnet API [Java]
|
1 2 3 4 5 6 7 8 9 10 |
try(Socket sock = new Socket("localhost", 4242)) { // make sure you include \n at the end String point = "put temperatures 1356998400 23.5 room=bedroom floor=1\n"; sock.getOutputStream().write(point.getBytes()); } catch(UnknownHostException | IOException ex) { // log exception } |
We need to execute command PUT with metric and data. še dopiši
Storing with HTTP API
When working with HTTP API, we have to make a PUT request to the URL localhost:4242/api/put with JSON data.
|
1 2 3 4 5 6 7 8 9 |
{ "metric": "temperatures", "timestamp": 1356998400, "value": 23.5, "tags": { "room": "bedroom", "floor": "1" } } |
There is also a possibility to make a batch insert. Just wrap all metrics in an array.
|
1 2 3 4 5 |
[ { .. metric 1 ..}, { .. metric 2 ..}, { .. metric 3 ..} ] |
Personally I had few problems inserting a large amount of data with the API. I ended up using Telnet API and it seems to work really well.
Querying the data
The whole beauty of OpenTSDB is it’s ability to not only to store huge amount of data, but to also query it fast. I will be showing how to query data with HTTP API, but the same query parameters can be used with Telnet API.
For the examples, we will first insert some data. Of course we can insert a much large dataset, but for this tutorial lets keep it simple.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
// imaginary data put temperatures 1356998400 23.5 room=bedroom floor=1 put temperatures 1356998405 23.2 room=bedroom floor=1 put temperatures 1356998410 23.7 room=bedroom floor=1 put temperatures 1356998415 23.1 room=bedroom floor=1 put temperatures 1356998400 24.2 room=childroom floor=1 put temperatures 1356998405 23.9 room=childroom floor=1 put temperatures 1356998410 23.4 room=childroom floor=1 put temperatures 1356998415 23.6 room=childroom floor=1 put temperatures 1356998400 23.5 room=livingroom floor=0 put temperatures 1356998405 23.5 room=livingroom floor=0 put temperatures 1356998410 23.5 room=livingroom floor=0 put temperatures 1356998415 23.5 room=livingroom floor=0 put temperatures 1356998400 18.2 room=basement floor=-1 put temperatures 1356998405 18.1 room=basement floor=-1 put temperatures 1356998410 18.1 room=basement floor=-1 put temperatures 1356998415 18.0 room=basement floor=-1 |
Getting all temperatures
|
1 |
GET http://localhost:4242/api/query?start=1356998400&m=sum:temperatures{room=*,floor=*} |
Let’s break down the request:
1. We can make GET or POST requests
2. The HTTP API URL is http://localhost:4242/api/query
3. We must define start, but end is optional. It can be unix timestamp or you can define nx-ago where n is unit and x is metric. For example, 1day-ago or 1h-ago. OpenTSDB will automatically convert it to timestamp based on your time.
4. m is the metric, where we are using aggregation = sum and metric = temperatures.
5. The last is grouping_operator (inside {}), which is used to group the data. If we define it with *, then it will not group the data. We can also use it to filter. For example room=bedroom will only fetch data from bedroom.
You can read more about different parameters and what they do at http://opentsdb.net/docs/build/html/api_http/query/index.html.
Our above request returns JSON.
|
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 55 56 57 58 59 60 61 62 63 64 65 66 |
[ { "metric":"temperatures", "tags":{ "floor":"1", "room":"bedroom" }, "aggregateTags":[ ], "dps":{ "1356998400":23.5, "1356998405":23.200000762939453, "1356998410":23.700000762939453, "1356998415":23.100000381469727 } }, { "metric":"temperatures", "tags":{ "floor":"1", "room":"childroom" }, "aggregateTags":[ ], "dps":{ "1356998400":24.200000762939453, "1356998405":23.899999618530273, "1356998410":23.399999618530273, "1356998415":23.600000381469727 } }, { "metric":"temperatures", "tags":{ "floor":"0", "room":"livingroom" }, "aggregateTags":[ ], "dps":{ "1356998400":23.5, "1356998405":23.5, "1356998410":23.5, "1356998415":23.5 } }, { "metric":"temperatures", "tags":{ "floor":"-1", "room":"basement" }, "aggregateTags":[ ], "dps":{ "1356998400":18.200000762939453, "1356998405":18.100000381469727, "1356998410":18.100000381469727, "1356998415":18.0 } } ] |
Getting temperatures in the bedroom
As mentioned above, we can query by tags. In our case by room=bedroom.
|
1 |
GET http://localhost:4242/api/query?start=1356998400&m=sum:temperatures{room=bedroom,floor=*} |
returns
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[ { "metric":"temperatures", "tags":{ "floor":"1", "room":"bedroom" }, "aggregateTags":[ ], "dps":{ "1356998400":23.5, "1356998405":23.200000762939453, "1356998410":23.700000762939453, "1356998415":23.100000381469727 } } ] |
Getting average temperature on first floor
To calculate the average of the temperatures on first floor, we have to group by tags. Be careful to define correct aggregation function (in our case avg). See all aggregators at http://opentsdb.net/docs/build/html/user_guide/query/aggregators.html#available-aggregators.
|
1 |
GET http://localhost:4242/api/query?start=1356998400&m=avg:temperatures{floor=1} |
produces
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[ { "metric":"temperatures", "tags":{ "floor":"1" }, "aggregateTags":[ "room" ], "dps":{ "1356998400":23.850000381469727, "1356998405":23.550000190734863, "1356998410":23.550000190734863, "1356998415":23.350000381469727 } } ] |
We can see tag room in aggregateTags. It means it used this tag to aggregate (or if you are familiar with other databases, GROUP BY) data.
Getting average temperatures per day
Let’s imagine a situation where we want to create reports of the temperatures on a daily basis. We could load all the data and then manually calculate the averages. For larger datasets it could take some time. OpenTSDB has an answer. We can also define downsampling. Downsampling will automatically calculate the values based on our downsampling aggregation function and timeframe.
|
1 |
GET http://localhost:4242/api/query?start=1356998400&m=avg:1d-avg:temperatures{floor=1} |
Notice different parameter m? We added 1d-avg (be careful to separate everything correctly with “:”), which will downsample by 1 day and calculate average. Compared to manual way, it’s much faster and it just gives us results, which we can use in graphs.
Other awesome features
OpenTSDB has few additional features to cover real-life situations. Of course we can easily add more with plugins. But 2 of them worth mentioning are Annotations and CLI Tools.
Annotations
Annotations enable us to add additional meta data to data points. For example, we could store information when we opened and closed window in each room or when we changed the heating level.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{ "tsuid": "000001000001000001", "description": "Window Opened", "notes": "Jane has opened the window and Mike has closed it", "custom": { "room": "bedroom", "floor": "1", "opened_by": "Jane", "closed_by": "Mike" }, "endTime": 1356998420, "startTime": 1356998400 } |
Read more at http://opentsdb.net/docs/build/html/api_http/annotation.html.
CLI Tools
CLI Tools are just simple tools to perform additional task like fixing the data storage (in case if something breaks down), querying and deleting data and creating metrics. One of the most common tools I use it scan, because it has the feature to delete data. t’s useful when we are doing different tests.
To delete all temperatures for basement, we execute command
|
1 |
./build/scan --delete 1356998400 sum temperatures room=basement floor=-1 |
Again, we can filter what to delete with start and end parameters, metric and tags.
Wrap up
OpenTSDB has been proved to be an excellent solution. It’s scalable, fast and has really neat features. Most importantly, it’s under active development and has many people contributing. With the era of IoT and Big Data upon us, it has a bright future ahead.
If you are ready, start with http://opentsdb.net/docs/build/html/index.html.
