OpenTSDB Storage Plugin
As of Drill 1.12, Drill provides a storage plugin for OpenTSDB. The OpenTSDB storage plugin uses the REST API with OpenTSDB.
When you install Drill, a preconfigured OpenTSDB storage plugin is available on the Storage page in the Drill Web UI. Once you enable the storage plugin, you can query OpenTSDB from Drill.
The following sections describe how to enable the OpenTSDB storage plugin in Drill and the types of queries Drill currently supports on OpenTSDB. You can refer to the README and OpenTSDB for additional information.
Enabling the OpenTSDB Storage Plugin
To enable the OpenTSDB storage plugin, enter the following URL in the address bar of your browser to access the Storage page in the Drill Web UI:
http://<drill-node-ip-address>:8047/storage/
In the Disabled Storage Plugins section, click Enable next to OpenTSDB. OpenTSDB moves to the Enabled Storage Plugins section. Click Update to see the default configuration:
{
"type": "openTSDB",
"connection": "http://localhost:4242",
"enabled": true
}
The URL, http://localhost:4242, works for TSD running locally. If you have TSD running on another machine, change the URL to reflect that. Verify that “enabled” is set to true. Click Update if you edit the configuration and then click Back to return to the Storage page.
Querying OpenTSDB
When querying OpenTSDB, you must include the required request parameters in the FROM clause of queries, using commas to separate the parameters, as shown in the following example:
SELECT * FROM openTSDB.`(metric=warp.speed.test, start=47y-ago, aggregator=sum)`;
In addition to the required request parameters, you can also include some optional request parameters in queries.
Required Request Parameters
The following table lists the required request parameters:
| Name | Data Type | Description |
|---|---|---|
| metric | string | The name of a metric stored in the system |
| start | string, integer | The start time for the query; a relative or absolute timestamp. |
| aggregator | string | The name of an aggregation function to use. |
Optional Request Parameters
The following table lists optional request parameters:
| Name | Data Type | Description |
|---|---|---|
| downsample | string | An optional downsampling function to reduce the amount of data returned. |
| end | string, integer | An end time for the query. If not supplied, the TSD assumes the local system time on the server. If this parameter is not specified, null is sent to the database in this field, but in this case the database assumes the local system time on the server. |
Supported Aggregators
For a list of supported aggegrators, see Aggregators.
Supported Dates and Times
For a list of supported dates and times, see Dates.
Examples of Queries that Drill Supports on OpenTSDB
Drill currently supports the following types of queries on the OpenTSDB:
USE openTSDB;
//Switches Drill to the OpenTSDB schema so that Drill only queries OpenTSDB.
SHOW tables;
//Prints the available metrics. The max number of the printed results is an Integer.MAX value.
SELECT * FROM openTSDB.`(metric=warp.speed.test, start=47y-ago, aggregator=sum)`;
//Returns aggregated elements from the warp.speed.test table from 47y-ago.
SELECT * FROM openTSDB.`(metric=warp.speed.test, aggregator=avg, start=47y-ago)`;
//Returns aggregated elements from the warp.speed.test table.
SELECT `timestamp`, sum(`aggregated value`) FROM openTSDB.`(metric=warp.speed.test, aggregator=avg, start=47y-ago)` GROUP BY `timestamp`;
//Returns aggregated and grouped value by standard Drill functions from the warp.speed.test table with the custom aggregator.
SELECT * FROM openTSDB.`(metric=warp.speed.test, aggregator=avg, start=47y-ago, downsample=5m-avg)`;
//Returns aggregated data limited by the downsample.
SELECT * FROM openTSDB.`(metric=warp.speed.test, aggregator=avg, start=47y-ago, end=1407165403000)`;
//Returns aggregated data limited by the end time.