【Furion】InfluxDB数据获取

前面帖子中实现了【Furion】JMeter+Influxdb+Grafana压测监控 的功能，但是这些数据存储在influxdb中，而influxdb的详细数据可能会被定期清理，所以需要将报告的概要数据拉取并永久保存在mysql数据库中，方便后续查看

一、导入依赖

<dependency>
	<groupId>org.influxdb</groupId>
	<artifactId>influxdb-java</artifactId>
	<version>2.10</version>
</dependency>

二、Influxdb工具类

package com.lluozh.utils;

import lombok.Data;
import okhttp3.OkHttpClient;
import org.influxdb.InfluxDB;
import org.influxdb.InfluxDBFactory;
import org.influxdb.dto.Point;
import org.influxdb.dto.Point.Builder;
import org.influxdb.dto.Query;
import org.influxdb.dto.QueryResult;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * @author lluozh
 * @Description:
 * @date 2021/3/10
 */
@Data
@Component
@Configuration
public class InfluxDbUtil {

    @Value("${spring.influxdb.host}")
    public String host;

    @Value("${spring.influxdb.port}")
    public String port;

    @Value("${spring.influxdb.username}")
    public String username;

    @Value("${spring.influxdb.password}")
    public String password;

    @Value("${spring.influxdb.database}")
    public String database;

    @Value("${spring.influxdb.retentionPolicy}")
    public String retentionPolicy;

    @Value("${spring.influxdb.influxHTTPScheme}")
    public String influxHTTPScheme;

    private InfluxDB influxDB;
	
	// 设置很大的超时时间
    static OkHttpClient.Builder client = new OkHttpClient.Builder().readTimeout(1000000, TimeUnit.SECONDS);

    /**
     *  * 设置数据保存策略
     *  * defalut 策略名 /database 数据库名/ 30d 数据保存时限30天/ 1 副本个数为1/ 结尾DEFAULT 表示 设为默认的策略
     *  
     */
    public void createRetentionPolicy() {
        String command = String.format("CREATE RETENTION POLICY \"%s\" ON \"%s\" DURATION %s REPLICATION %s DEFAULT", "defalut", database, "30d", 1);
        this.query(command);
    }

    /**
     *  * 查询
     *  * @param command 查询语句
     *  * @return
     *  
     */
    public QueryResult query(String command) {
        InfluxDB connect = InfluxDBFactory.connect(influxHTTPScheme + "://" + host + ":" + port, username, password, client);
        //数据库和查询语句
        return connect.query(new Query(command, database));
    }

    /**
     *  * 插入
     *  * @param tags 标签
     *  * @param fields 字段
     *  
     */
    public void insert(Map<String, String> tags, Map<String, Object> fields, String measurement) {
        Builder builder = Point.measurement(measurement);
        builder.tag(tags);
        builder.fields(fields);

        influxDB.write(database, "", builder.build());
    }

    /**
     *  * 删除
     *  * @param command 删除语句
     *  * @return 返回错误信息
     *  
     */
    public String deleteMeasurementData(String command) {
        QueryResult result = influxDB.query(new Query(command, database));
        return result.getError();
    }

    /**
     *  * 创建数据库
     *  * @param dbName
     *  
     */
    @SuppressWarnings("deprecation")
    public void createDB(String dbName) {
        influxDB.createDatabase(dbName);
    }
}

三、基本数据查询

String sqlQueryGroup = String.format("SELECT count(responseTime) as sampleCount, mean(responseTime) as meanResTime, min(responseTime) as minResTime, median(responseTime) as Median, percentile(responseTime, 90) as pct1ResTime, percentile(responseTime, 95) as pct2ResTime,percentile(responseTime, 99) as pct3ResTime, max(responseTime) as maxResTime, sum(errorCount) as errorCount, (sum(errorCount)/count(responseTime)) as errorPct FROM requestsRaw WHERE runId = '%s' GROUP BY requestName", taskId);

String sqlQueryTotal = String.format("SELECT count(responseTime) as sampleCount, mean(responseTime) as meanResTime, min(responseTime) as minResTime, median(responseTime) as Median, percentile(responseTime, 90) as pct1ResTime, percentile(responseTime, 95) as pct2ResTime,percentile(responseTime, 99) as pct3ResTime, max(responseTime) as maxResTime, sum(errorCount) as errorCount, (sum(errorCount)/count(responseTime)) as errorPct FROM requestsRaw WHERE runId = '%s'", taskId);

QueryResult queryGroup = influxDbUtil.query(sqlQueryGroup)

QueryResult queryTotal = influxDbUtil.query(sqlQueryTotal)

四、时间数据查询

private long parseQueryTime(String taskId) throws ParseException {

    ReportDto reportDto = reportDao.getReportSummary(taskId);

    String testName = reportDto.getSceneName();

    String sqlQueryStart = String.format("SELECT LAST(*) FROM testStartEnd WHERE testName = '%s' AND type ='started'", testName);

    String sqlQueryEnd = String.format("SELECT LAST(*) FROM testStartEnd WHERE testName = '%s' AND type = 'finished'", testName);

    // 查询开始时间
    QueryResult queryStartTime=  influxDbUtil.query(sqlQueryStart);
    String testStartTime = (String) queryStartTime.getResults().get(0).getSeries().get(0).getValues().get(0).get(0);

    // 查询开始时间
    QueryResult  queryEndTime=  influxDbUtil.query(sqlQueryEnd);
    String testEndTime = (String) queryEndTime.getResults().get(0).getSeries().get(0).getValues().get(0).get(0);

    // 获取执行的时间差
    DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    return  (df.parse(testEndTime).getTime() - df.parse(testStartTime).getTime()) / 1000;
}

五、数据处理

private void parseQueryResult(QueryResult queryResults, long second) throws IllegalAccessException, InstantiationException, InvocationTargetException {

    List<Map> queryResultList = new ArrayList<>();
    for(QueryResult.Result result :queryResults.getResults()){
        List<QueryResult.Series> seriess = result.getSeries();//有结果组装返回对象
        if(seriess!=null){
            //直接返回对象
            for(QueryResult.Series series :seriess){
                String name = series.getName();
                List<List<Object>>  value = series.getValues();
                List<String> key = series.getColumns();
                for(List<Object> object :value){
                    Map<String,Object> beanProperMap =new HashMap<>();
                    //匹配属性和属性值
                    for(int i=0;i<object.size();i++){
                        String k=key.get(i);
                        Object v =object.get(i);
                        beanProperMap.put(k,v);
                    }
                    // 特殊处理获取transaction
                    if(series.getTags() == null){
                        beanProperMap.put("transaction","TOTAL");
                    } else {
                        beanProperMap.put("transaction",series.getTags().get("requestName"));
                    }

                    beanProperMap.put("throughput", new BigDecimal((Double)beanProperMap.get("sampleCount")/second).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());

                    //保存集合
                    queryResultList.add(beanProperMap);
                }
            }
        }
    }
    for(Map queryResult: queryResultList){
        // 创建对象并且赋值
        ReportStatisticsDto bean =ReportStatisticsDto.class.newInstance();
        BeanUtils.populate(bean,queryResult);
        resultList.add(bean);
    }
}