Connect hive-sql
Tsdb storage handler
TSDB and hive connection is achieved by HiveStorageHandler of a TSDB, which supports reading the tsdb data.
Jar download address:http://tsdb-bos.gz.bcebos.com/hive-tsdb-handler-all.jar
If it's a local hive cluster, download jar to a local address; and if you use bmr, upload it to bos or directly use the address bos://iot-tsdb/hive-tsdb-handler-all.jar
Supporting versions: hive 1.2.0 and jdk 1.7.
Use in Hive CLI or Hue
Examples and parameters are described as follows:
add jar /path/to/hive-tsdb-handler-all.jar; /* add jar, if the path is in bmr, use bos address, such as bos://path/to/hive-tsdb-handler-all.jar */
CREATE EXTERNAL TABLE `wind`(`time` bigint, `value` double, `city` string) /* create table */
STORED BY 'com.baidubce.tsdb.hive.storage.TsdbStorageHandler' /* set storage as TsdbStorageHandler */
TBLPROPERTIES (
"tsdb.metric_name" = "wind", /* Metric name, default table name is metric */
"tsdb.timestamp_name" = "time", /* column name corresponding to Timestamp, default time is corresponding column name */
"tsdb.field_names" = "value", /* field list, enter what is used in the table, split multiple fields by comma */
"tsdb.tag_keys" = "city", /* tagKey list, enter what is used in the table, remove the row in this example, but if the tagKey contains capital letters, entering is compulsory */
"tsdb.endpoint" = "ENDPOINT", /* endpoint of TSDB instance */
"tsdb.access_key" = "AK", /* AK */
"tsdb.secret_key" = "SK" /* SK */
);
select time, value from wind where time>=1451500000000 and time<=1451577600000;Scenario Example
Calculation of Wind Speed
The wind speed data is regularly uploaded by the sensor to tsdb and contains two fields, namely, x and y, representing the wind speed in the direction of x-axis and y-axis. As shown in the following, the total wind speed is calculated by the wind speed in the two vertical directions.
add jar /path/to/hive-tsdb-handler-all.jar; /* add jar, if the path is in bmr, use bos address, such as bos://path/to/hive-tsdb-handler-all.jar */
CREATE EXTERNAL TABLE `WindSpeed`(`time` bigint, `x` double, `y` double) /* create table */
STORED BY 'com.baidubce.tsdb.hive.storage.TsdbStorageHandler' /* set storage as TsdbStorageHandler */
TBLPROPERTIES (
"tsdb.metric_name" = "WindSpeed", /* Metric name, default table name is metric */
"tsdb.timestamp_name" = "time", /* column name corresponding to Timestamp, default time is corresponding column name */
"tsdb.field_names" = "x,y", /* field list, split multiple fields by comma */
"tsdb.endpoint" = "ENDPOINT", /* endpoint of TSDB instance */
"tsdb.access_key" = "AK", /* AK */
"tsdb.secret_key" = "SK" /* SK */
);
select time, sqrt(pow(x, 2) + pow(y, 2)) as speed from WindSpeed;Raw data
metric:WindSpeed
| time | field : x | field : y |
|---|---|---|
| 1512086400000 | 3.0 | 4.0 |
| 1512086410000 | 1.0 | 2.0 |
| 1512086420000 | 2.0 | 3.0 |
Result
| time | speed |
|---|---|
| 1512086400000 | 5.000 |
| 1512086410000 | 2.236 |
| 1512086420000 | 3.606 |
Calculate Time Usage of Vehicle
The vehicle is timed (every 10 seconds) during moving, the data is uploaded to tsdb, and the data contains the speed of the vehicle. Three kinds of time need to be counted:
(1) Stop time: For a period of time the car has reported data, but the reported speed is 0, which indicates that the car is waiting at red lights.
(2) Running time: For a period of time the car has reported data, and the reported speed is greater than 0, which indicates that the car is moving.
(3) Off-line time: For a period of time the car has not reported data, which indicates that the car has stopped and the engine is shut off.
add jar /path/to/hive-tsdb-handler-all.jar; /* add jar, if the path is in bmr, use bos address, such as bos://path/to/hive-tsdb-handler-all.jar */
CREATE EXTERNAL TABLE `vehicle`(`time` bigint, `speed` bigint, `carId` string) /* create table */
STORED BY 'com.baidubce.tsdb.hive.storage.TsdbStorageHandler' /* set storage as TsdbStorageHandler */
TBLPROPERTIES (
"tsdb.metric_name" = "vehicle", /* Metric name, default table name is metric */
"tsdb.timestamp_name" = "time", /* column name corresponding to Timestamp, default time is corresponding column name */
"tsdb.field_names" = "speed", /* field list, split multiple fields by comma */
"tsdb.tag_keys" = "carId", /* tag list, split multiple tags by comma */
"tsdb.endpoint" = "ENDPOINT", /* endpoint of TSDB instance */
"tsdb.access_key" = "AK", /* AK */
"tsdb.secret_key" = "SK" /* SK */
);
/* Daily stop time for the car with ID "123" in December 2017 */
select floor((time - 1512057600000) / 86400000) + 1 as day, count(*) * 10 as stop_seconds from vehicle where carId='123' and time >= 1512057600000 and time < 1514736000000 and speed = 0 group by floor((time - 1512057600000) / 86400000);
/* Daily running time for the car with ID "123" in December 2017 */
select floor((time - 1512057600000) / 86400000) + 1 as day, count(*) * 10 as run_seconds from vehicle where carId='123' and time >= 1512057600000 and time < 1514736000000 and speed > 0 group by floor((time - 1512057600000) / 86400000);
/* Daily running time for the vehicle with ID "123" in December 2017 */
select floor((time - 1512057600000) / 86400000) + 1 as day, 2678400 - count(*) * 10 as offline_seconds from vehicle where carId='123' and time >= 1512057600000 and time < 1514736000000 group by floor((time - 1512057600000) / 86400000);Raw data
metric:vehicle
| time | field : speed | tag |
|---|---|---|
| 1512086400000 | 40 | carId=123 |
| 1512086410000 | 60 | carId=123 |
| 1512086420000 | 50 | carId=123 |
| ... | ... | carId=123 |
| 1512086460000 | 10 | carId=123 |
Result
| day | stop_seconds |
|---|---|
| 1 | 3612 |
| 2 | 3401 |
| ... | ... |
| 31 | 3013 |
| day | run_seconds |
|---|---|
| 1 | 17976 |
| 2 | 17968 |
| ... | ... |
| 31 | 17377 |
| day | offline_seconds |
|---|---|
| 1 | 64812 |
| 2 | 65031 |
| ... | ... |
| 31 | 66010 |