influxdb数据库操作

influxdb数据库操作：

influxDB存储的数据是用特定的格式存储

1.shell连接 输入influx或者influx -precision rfc3339

2.use iot-paas

3.模糊查找：

查询以包含某字符串“200”的数据

select *from iot_data WHERE sensor_uuid =~/^200*/ order by time desc limit 205

查询以固定字节“20012”开头的数据

select *from iot_data WHERE sensor_uuid =~/20012/ order by time desc limit 50

2.JGQ（23FWQ）

select * from iot_data WHERE sensor_uuid =~/^200*/ order by time desc limit 205

> exit

[root@centos20 ~]# influx -precision rfc3339

Connected to http://localhost:8086 version 1.7.1

InfluxDB shell version: 1.7.1

Enter an InfluxQL query

> use iot-paas

using database iot-paas

> select *from iot_data WHERE sensor_uuid =~/^200*/ order by time desc limit 205

name: iot_data

 

时间再拿这个时间加一个八小时就对了。

3.有三种导出格式，json/csv/column

执行导出语句时，不需要进influx，要指定存的文件： 

influx -precision rfc3339 -database 'iot-paas' -execute "select value, sensor_uuid, type from iot_data where sensor_uuid = '38' and (type = '23' or type='24')and time >= 1588262400000000000 and time <=1589385600000000000 tz('Asia/Shanghai')" -format='csv' >/data/data07/38.csv

 

最大最小平均值

select max(value),min(value),mean(value) from iot_data where time>'2020-01-01 00:00:00' and time<'2020-01-01 01:00:00' group by sensor_uuid

1.TYN

Influxdb数据导出：

influx -precision rfc3339 -database 'iot_data' -execute "select max(value),min(value),mean(value) from iot_data where time>'2020-01-01 00:00:00' and time<'2020-01-01 01:00:00' group by sensor_uuid tz('Asia/Shanghai')" -format='csv' >/2020010101.csv

2.TYN

influx -precision rfc3339 -database 'iot_data' -execute "select max(value),min(value),mean(value) from iot_data where time>'2020-01-01 00:00:00' and time<'2020-01-01 01:00:00' group by sensor_uuid,type tz('Asia/Shanghai')" -format='csv' >/2020010102.csv

3.JGQ

influx -precision rfc3339 -database 'iot-paas' -execute "select max(value),min(value),mean(value) from iot_data where time>'2020-01-01 00:00:00' and time<'2020-01-02 00:00:00' group by sensor_uuid,type tz('Asia/Shanghai')" -format='csv' >/data/data07/2020010101.csv

 

参考：

influxdb导出成csv文件格, 以","分隔;

influx -database 'paradata_dev' -execute 'select * from lsfCluster'  -format='csv' >lsfCluster.txt

可参考网址:https://blog.youkuaiyun.com/peng_zhi_hui/article/details/80422431

将csv文件导入到hive中，可用:

在hive中创建中相应的表:

可参考网址

https://blog.youkuaiyun.com/zengmingen/article/details/52636385

不过，需要注意的是，influxdb中导出的数据，包含了表名;

如果想hive每行，没有这个表名的话，得用python做一下数据处理了;