InfluxDB
Incluxdb overview
InfluxDB is a time series database built from scratch to handle high write and query loads. InfluxDB is designed to be used as a backup storage for any use case involving a large amount of timestamp data, including DevOps monitoring, application metrics, IOT sensor data and real-time analysis.
What is time series data? By definition, it is a string of data indexed by time dimension.
Time series database (TSDB) features:
- Continuous high concurrency write without update
- Data compression storage
- Low latency query
In addition to incluxdb, common time series databases include opentsdb, timeScaladb, Druid, etc.
Official website: https://www.influxdata.com/
GitHub: https://github.com/influxdata/influxdb
Chinese documents: https://jasper-zhang1.gitbooks.io/influxdb/content/Introduction/installation.html
Related concepts
| nfuxDB | Traditional database |
|---|---|
| database | database |
| measurement | Table in database |
| point | Rows in table |
Point is composed of time stamps, tags and fields
| point property | meaning |
|---|---|
| time | The time of data record. The primary index is automatically generated by default, which is equivalent to the columns of each row of data |
| tags | Equivalent to an indexed column. The value stored in tag is of type string |
| fields | Value, column without index. Value types stored in field: string, floating point number (Double), integer, Boolean. A field value is always associated with a timestamp |
Installing InfluxDB
Pull inluxdb
docker pull influxdb:1.8
Start incluxdb
docker run -d --name influxdb -p 8086:8086 influxdb:1.8
Enter the influxdb container
docker exec -it influxdb /bin/bash
Enter influx in the command line to enter the InfluxDB database environment
root@70783fdf1b06:/# influx Connected to http://localhost:8086 version 1.8.10 InfluxDB shell version: 1.8.10 >
Operation of incluxdb
user management
Create user
> create user "root" with password 'root' with all privileges > show users user admin ---- ----- root true >
delete user
> drop user root > > show users user admin ---- ----- >
Enable authentication
Configure influxdb Conf file enable authentication
docker cp influxdb:/etc/influxdb/influxdb.conf ./ docker cp ./influxdb.conf influxdb:/etc/influxdb/ docker restart influxdb
In influxdb Add the following contents to the conf configuration file
[http] auth-enabled= true
Login with account and password
root@70783fdf1b06:/# influx -username 'root' -password 'root' Connected to http://localhost:8086 version 1.8.10 InfluxDB shell version: 1.8.10 > show users user admin ---- ----- root true >
Incorrect password authentication
root@70783fdf1b06:/# influx -username 'root' -password 'root123' Connected to http://localhost:8086 version 1.8.10 InfluxDB shell version: 1.8.10 > show users ERR: authorization failed Warning: It is possible this error is due to not setting a database. Please set a database with the command "use <database>". >
Database operation
Display databases: show databases
> show databases name: databases name ---- _internal >
New database: create database test
> create database test > show databases name: databases name ---- _internal test >
Delete database command: drop database test
> drop database test > show databases name: databases name ---- _internal >
To use a database, you must first create create database test, and then use test
> create database test > use test Using database test >
Database table operation
Show all tables: Show measures
> SHOW MEASUREMENTS >
New table: insert
There is no explicit statement to create a new table in InfluxDB. You can only create a new table by inserting data, or the table will be created automatically when inserting data into the client program code.
The following command indicates that a data point whose measurement is cpu, tag is host and region, and value is 0.64 is written to the InfluxDB
> INSERT cpu,host=serverA,region=us_west value=0.64 > SHOW MEASUREMENTS name: measurements name ---- cpu >
Delete table: drop measurement cpu
> drop measurement cpu > SHOW MEASUREMENTS >
Query operation
InfluxQL is a SQL like query language used to interact with data in InfluxDB.
Table data query: SELECT * FROM tb
> SELECT "host", "region", "value" FROM "cpu" name: cpu time host region value ---- ---- ------ ----- 1642915045925737934 serverA us_west 0.64 >
Fuzzy search in incluxdb is the same as fuzzy search in mysql
The query contains data for the given field
select fieldName from measurementName where fieldName=~/Condition value/
Query data that starts with the given field
select fieldName from measurementName where fieldName=~/^Condition value/
Queries data that ends with a given field
select fieldName from measurementName where fieldName=~/Condition value $/
Number of statistical records
select count(value) from measurementName where time>='2021-01-01' and time<='2022-12-31'
paging
select * from measurementName where time>='2021-01-01' and time<='2022-12-31' order by desc limit 10 offset 0
Project integration use
Add dependency
<dependency>
<groupId>org.influxdb</groupId>
<artifactId>influxdb-java</artifactId>
<version>2.21</version>
</dependency>
Create database
create database demo
Configure incluxdb
spring:
influx:
user: root
password: root
url: http://IP:8086
db: demo
code
/**
* Number of package statistics records
**/
@Data
@Measurement(name = "bbu")
public class BbuCount {
@Column(name = "count")
private Long count;
}
/**
* Add the @ Measurement annotation on the class to mark the final stored table name, so that the Measurement will be automatically created when inserting data, with the name bbu
* <p>
* Corresponding to the mapping of tags in InfluxDB, add ` @ Column 'annotation on the definition field, use' name 'to set the corresponding tag name, and set' tag 'to' true 'to prove that it is tag
*/
@Data
@Measurement(name = "bbu")
public class BbuInfo {
/**
* Equipment number
*/
@Column(name = "deviceId", tag = true)
private String deviceId;
/**
* Indicator name
*/
@Column(name = "indicatorName", tag = true)
private String indicatorName;
/**
* Alarm 0: no alarm 1: alarm
*/
@Column(name = "alarm", tag = true)
private String alarm;
/**
* Alarm level
*/
@Column(name = "level", tag = true)
private String level;
/**
* Index value
*/
@Column(name = "value")
private Double value;
/**
* time series
*/
@Column(name = "time")
private String time;
}
import cn.ybzy.demo.model.BbuCount;
import cn.ybzy.demo.model.BbuInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.influxdb.InfluxDB;
import org.influxdb.dto.Point;
import org.influxdb.dto.Query;
import org.influxdb.dto.QueryResult;
import org.influxdb.impl.InfluxDBResultMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.TimeUnit;
@Component
@Slf4j
public class BbuInflux {
@Autowired
private InfluxDB influxDB;
@Value("${spring.influx.db}")
private String dbName;
/**
* Add data
*
* @param object
*/
public void add(Object object) {
Point.Builder pointBuilder = Point.measurementByPOJO(object.getClass());
// Convert all attributes in the object into tag s and add them to the point
Point point = pointBuilder.addFieldsFromPOJO(object)
// Call the time method to set the current time. The time automatically generated by incluxdb is listed as UTC time, so it is set separately during storage
.time(LocalDateTime.now().plusHours(8).toInstant(ZoneOffset.of("+8")).toEpochMilli(), TimeUnit.MILLISECONDS)
.build();
// Set the name of the database to store
influxDB.setDatabase(dbName);
// Insert data into the table (Measurement)
influxDB.write(point);
influxDB.close();
}
/**
* General query encapsulation
*
* @param sql
* @param clazz
* @param <T>
* @return
*/
public <T> List<T> query(String sql, Class<T> clazz) {
QueryResult queryResult = influxDB.query(new Query(sql, dbName));
influxDB.close();
InfluxDBResultMapper resultMapper = new InfluxDBResultMapper();
return resultMapper.toPOJO(queryResult, clazz);
}
/**
* Paging query
*
* @param page
* @param pageSize
* @param start
* @param end
* @param deviceId
* @return
*/
public HashMap<String, Object> queryBbu(Long page, Long pageSize, String start, String end, String deviceId) {
StringBuilder sql = new StringBuilder("select * from bbu ");
// Build where condition
sql.append(" where ");
if (StringUtils.isNoneBlank(start)) {
sql.append(" time>='" + start + "' ");
}
if (StringUtils.isNoneBlank(end)) {
sql.append("and time<='" + end + "' ");
}
if (StringUtils.isNoneBlank(deviceId)) {
sql.append("and deviceId=~/" + deviceId + "/ ");
}
// paging
sql.append("order by desc limit " + pageSize + " offset " + (page - 1) * pageSize);
// Query record statement
List<BbuInfo> quotaList = this.query(sql.toString(), BbuInfo.class);
// Statistical statement
String countSql = "select count(value) from bbu ";
List<BbuCount> bbuCount = this.query(countSql, BbuCount.class);
if (bbuCount == null || bbuCount.size() <= 0) {
return null;
}
Long totalCount = bbuCount.get(0).getCount();
HashMap<String, Object> map = new HashMap<>();
map.put("total", totalCount);
map.put("data", quotaList);
return map;
}
/**
* Query the latest data
*
* @param deviceId
* @return
*/
public HashMap<String, Object> queryLast(String deviceId) {
String sql = " select last(value),* from bbu where deviceId='" + deviceId + "' group by deviceId";
List<BbuInfo> quotaList = this.query(sql, BbuInfo.class);
HashMap<String, Object> map = new HashMap<>();
map.put("data", quotaList);
return map;
}
}
Perform test
@SpringBootTest
class SpringbootApplicationTests {
@Autowired
private BbuInflux bbuInflux;
@Test
public void testSave() {
BbuInfo bbuInfo = new BbuInfo();
bbuInfo.setDeviceId("0001");
bbuInfo.setIndicatorName("temperature");
bbuInfo.setAlarm("0");
bbuInfo.setLevel("normal");
bbuInfo.setValue(sixty-five.55D);
bbuInflux.add(bbuInfo);
}
@Test
public void testQuery() {
HashMap<String, Object> map = bbuInflux.queryBbu(1L, 10L, "2020-01-01", "2022-12-31", "");
System.out.println("Total:" + map.get("total"));
String toJSONString = JSON.toJSONString(map.get("data"));
List<BbuInfo> bbuInfoList = JSON.parseArray(toJSONString, BbuInfo.class);
for (BbuInfo bbuInfo : bbuInfoList) {
// Time format processing
LocalDateTime dateTime = LocalDateTime.parse(bbuInfo.getTime(), DateTimeFormatter.ISO_OFFSET_DATE_TIME);
String time = dateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss"));
bbuInfo.setTime(time);
System.out.println("bbuInfo:" + bbuInfo);
}
}
@Test
public void testQueryLast() {
HashMap<String, Object> map = bbuInflux.queryLast("0001");
String toJSONString = JSON.toJSONString(map.get("data"));
List<BbuInfo> bbuInfoList = JSON.parseArray(toJSONString, BbuInfo.class);
for (BbuInfo bbuInfo : bbuInfoList) {
// Time format processing
LocalDateTime dateTime = LocalDateTime.parse(bbuInfo.getTime(), DateTimeFormatter.ISO_OFFSET_DATE_TIME);
String time = dateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss"));
bbuInfo.setTime(time);
System.out.println("bbuInfo:" + bbuInfo);
}
}
}
> use demo Using database demo > select * from bbu name: bbu time alarm deviceId indicatorName level value ---- ----- -------- ------------- ----- ------ 1642948114035000000 0 0001 temperature Normal fifty-six.5 >
> select * from bbu where time>='2020-01-01' and time<='2022-12-31' order by desc limit 10 offset 0 name: bbu time alarm deviceId indicatorName level value ---- ----- -------- ------------- ----- ----- 1642950703148000000 0 0001 temperature normal 65.55 1642949227228000000 0 0001 temperature normal eighty-nine.56 1642948114035000000 0 0002 temperature normal 56.5
> select last(value),* from bbu where deviceId='0001' group by deviceId name: bbu tags: deviceId=0001 time last alarm indicatorName level value ---- ---- ----- ------------- ----- ----- 1642950703148000000 65.55 0 temperature Normal 65.55 >