对接hive-sql

更新时间：2021-10-26

Tsdb storage handler

TSDB对接hive是通过实现一个TSDB的HiveStorageHandler，支持对tsdb数据的读取。

Jar下载地址：https://sdk.bce.baidu.com/console-sdk/hive-tsdb-handler_all.jar

如果是本地hive集群，请下载jar到本地；如果使用bmr，则上传到bos或者直接使用地址bos://iot-tsdb/hive-tsdb-handler_all.jar

支持的hive 1.2.0，jdk 1.7。

在Hive CLI或Hue中使用

示例及参数说明如下:

add jar /path/to/hive-tsdb-handler_all.jar;    /* 添加jar，如果在bmr中，请使用bos地址，如bos://path/to/hive-tsdb-handler_all.jar */ 
CREATE EXTERNAL TABLE `wind`(`time` bigint, `value` double, `city` string)     /* 创建表 */
STORED BY 'com.baidubce.tsdb.hive.storage.TsdbStorageHandler'                  /* 设置storage为TsdbStorageHandler */
TBLPROPERTIES (
"tsdb.metric_name" = "wind",                                                   /* Metric名字，默认表名则被当为metric */
"tsdb.timestamp_name" = "time",                                                /* Timestamp对应的列名，默认time为对应的列名 */
"tsdb.field_names" = "value",                                                  /* field列表，只需填写table中用到的，多个field通过逗号分割 */
"tsdb.tag_keys" = "city",                                                      /* tagKey列表，只需填写table中用到的，这个示例中可以去掉改行，但如果tagKey中包含大写字母，则必须填写 */
"tsdb.endpoint" = "ENDPOINT",                                                  /* TSDB实例的endpoint */
"tsdb.access_key" = "AK",                                                      /* AK */
"tsdb.secret_key" = "SK"                                                       /* SK */
);
 
select time, value from wind where time>=1451500000000 and time<=1451577600000;

在endpoint为IP:PORT的情形下：

add jar /path/to/hive-tsdb-handler_all.jar;    /* 添加jar，如果在bmr中，请使用bos地址，如bos://path/to/hive-tsdb-handler_all.jar */ 
CREATE EXTERNAL TABLE `wind`(`time` bigint, `value` double, `city` string)
STORED BY 'com.baidubce.tsdb.hive.storage.TsdbStorageHandler'
TBLPROPERTIES (
"tsdb.metric_name" = "wind",
"tsdb.timestamp_name" = "time",
"tsdb.field_names" = "value",
"tsdb.tag_keys" = "city",
"tsdb.endpoint" = "http://IP:PORT",
"tsdb.host" = "DATABASE_NAME.tsdb.iot.gz.baidubce.com",
"tsdb.grpc_port" = "GRPC_PORT",
"tsdb.access_key" = "AK",
"tsdb.secret_key" = "SK"
);
select time, value from wind where time>=1451500000000 and time<=1451577600000;

场景示例

计算风速

风速数据由传感器定时上传到tsdb中，数据包含两个field分别为x和y，表示x轴和y轴方向的风速，如下由两个垂直方向的风速来计算出总的风速。

add jar /path/to/hive-tsdb-handler_all.jar;    /* 添加jar，如果在bmr中，请使用bos地址，如bos://path/to/hive-tsdb-handler_all.jar */ 
CREATE EXTERNAL TABLE `WindSpeed`(`time` bigint, `x` double, `y` double)       /* 创建表 */
STORED BY 'com.baidubce.tsdb.hive.storage.TsdbStorageHandler'                  /* 设置storage为TsdbStorageHandler */
TBLPROPERTIES (
"tsdb.metric_name" = "WindSpeed",                                              /* Metric名字，默认表名则被当为metric */
"tsdb.timestamp_name" = "time",                                                /* Timestamp对应的列名，默认time为对应的列名 */
"tsdb.field_names" = "x,y",                                                    /* field列表，多个field通过逗号分割 */
"tsdb.endpoint" = "ENDPOINT",                                                  /* TSDB实例的endpoint */
"tsdb.access_key" = "AK",                                                      /* AK */
"tsdb.secret_key" = "SK"                                                       /* SK */
);
 
select time, sqrt(pow(x, 2) + pow(y, 2)) as speed from WindSpeed;

原始数据

metric:WindSpeed

time	field : x	field : y
1512086400000	3.0	4.0
1512086410000	1.0	2.0
1512086420000	2.0	3.0

结果

time	speed
1512086400000	5.000
1512086410000	2.236
1512086420000	3.606

计算车辆在时间上的使用情况

车辆在行驶过程中会定时（每10秒）将数据上传到tsdb中，数据中包含车速speed。需要统计三种时长：

（1）停止时长：一段时间内这台车子有上报数据，但是上报的车速显示是0，可能是车子在等红灯。

（2）运行时长：一段时间内这台车子有上报数据，且上报的车速显示大于0，这台车子正在行驶中。

（3）离线时长：一段时间内这台车子没有上报数据的时长，这台车子已经停下并熄火了。

add jar /path/to/hive-tsdb-handler_all.jar;    /* 添加jar，如果在bmr中，请使用bos地址，如bos://path/to/hive-tsdb-handler_all.jar */ 
CREATE EXTERNAL TABLE `vehicle`(`time` bigint, `speed` bigint, `carId` string) /* 创建表 */
STORED BY 'com.baidubce.tsdb.hive.storage.TsdbStorageHandler'                  /* 设置storage为TsdbStorageHandler */
TBLPROPERTIES (
"tsdb.metric_name" = "vehicle",                                                /* Metric名字，默认表名则被当为metric */
"tsdb.timestamp_name" = "time",                                                /* Timestamp对应的列名，默认time为对应的列名 */
"tsdb.field_names" = "speed",                                                  /* field列表，多个field通过逗号分割 */
"tsdb.tag_keys" = "carId",                                                     /* tag列表，多个tag通过逗号分割 */
"tsdb.endpoint" = "ENDPOINT",                                                  /* TSDB实例的endpoint */
"tsdb.access_key" = "AK",                                                      /* AK */
"tsdb.secret_key" = "SK"                                                       /* SK */
);
 
/* ID为“123”的车辆在2017年12月每天的停止时长 */
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);
 
/* ID为“123”的车辆在2017年12月每天的运行时长 */
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);
 
/* ID为“123”的车辆在2017年12月每天的运行时长 */
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);

原始数据

metric:vehicle

time	field : speed	tag
1512086400000	40	carId=123
1512086410000	60	carId=123
1512086420000	50	carId=123
...	...	carId=123
1512086460000	10	carId=123

结果

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

支持MySQL协议

对接spark-sql

百度智能云

时序时空数据库 TSDB