初探TDengine(二) Java测试集成

Java操作TDengine进行时序数据写入

---

过程中遇到的一点小坑:
单机版目前客户端和数据库不在一台服务器上网络延迟较高 , 严重影响写入查询速率 并且存在数据丢失情况

1.生成测试数据CSV

package com.taos.test;

import com.taos.test.bean.ShipBean;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

public class TdMkTest {

    public static void main(String[] args) throws IOException {
        int rowNum = 10000;
        int shipNum = 1000;
        String filePath = "D:/100CSVFile";
        for (int i = 100000000; i < 100000000 + shipNum; i++) {
            File file = new File(filePath + "/" + i + "ship.csv");
            File parentFile = file.getParentFile();
            // 如果父文件夹不存在 创建
            if (parentFile != null && !parentFile.exists()) {
                parentFile.mkdir();
            }
            BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "utf-8"));
            for (ShipBean aisDynamic : createShip(i, rowNum)) {
                bufferedWriter.write(aisDynamic.toRow());
                bufferedWriter.newLine();
            }
            bufferedWriter.flush();
            if (bufferedWriter != null) {
                bufferedWriter.close();
            }
        }
    }

    /**
     * 生成每个船舶模拟轨迹
     */
    private static List<ShipBean> createShip(int mmsi, int num) {
        List<ShipBean> aisDynamicList = new ArrayList<ShipBean>();
        int time = 1526187800;
        Random rand = new Random();
        int longitude = (rand.nextInt(181) * 600000);
        int latitude = (rand.nextInt(181) * 600000);
        int direction = rand.nextInt(3601);
        int heading = (direction / 10);
        int speed = rand.nextInt(1024);
        int status = rand.nextInt(15);
        int rot = rand.nextInt(128);
        int positionAccuracy = rand.nextInt(2);
        int messageId = rand.nextInt(4);

        for (int i = 0; i < num; i++) {
            int timetemp = ++time;
            ShipBean aisDynamic = new ShipBean();
            aisDynamic.setRecordDatetime(timeStamp2Date(String.valueOf(timetemp)));
            aisDynamic.setMmsi(mmsi);
            aisDynamic.setLongitude(longitude);
            aisDynamic.setLatitude(latitude);
            aisDynamic.setDirection(direction);
            aisDynamic.setHeading(heading);
            aisDynamic.setSpeed(speed);
            aisDynamic.setStatus(status);
            aisDynamic.setRot(rot);
            aisDynamic.setPositionAccuracy(positionAccuracy);
            aisDynamic.setUtcHour(24);
            aisDynamic.setUtcMinute(60);
            aisDynamic.setUtcSecond(60);
            aisDynamic.setMessageId(messageId);
            aisDynamic.setRevDatetime(timetemp - 10);
            aisDynamic.setSourceId(114);
            aisDynamicList.add(aisDynamic);
        }
        return aisDynamicList;
    }

    /**
     * 时间戳转换成时间格式
     *
     * @param seconds
     * @return
     */
    private static String timeStamp2Date(String seconds) {
        String format = "yyyy-MM-dd HH:mm:ss";
        if (seconds == null || seconds.isEmpty() || seconds.equals("null")) {
            return "";
        }
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        if (seconds.length() == 10) {
            seconds = seconds + "000";
        }
        return sdf.format(new Date(Long.valueOf(seconds)));
    }

}

2.实现单线程写入线程

进行SQL拼接最大支持4KB

package com.taos.test;


import com.taos.test.bean.ShipBean;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class InsertThread implements Runnable {


    @Override
    public void run() {

        Statement stmt = InsertDaSoUtil.stmt;

        if (stmt==null){
            InsertDaSoUtil.getStat(false);
        }
        long sum =0L;
        for (int i = 0; i < InsertDaSoUtil.aisDynamicList.size(); i++) {

            List<ShipBean> aisDynamicsList = InsertDaSoUtil.aisDynamicList.get(i);

            int flag = 0, counts = 0;
            StringBuilder insertSQL = new StringBuilder("INSERT INTO ais_" + aisDynamicsList.get(0).getMmsi() + " USING ais_model TAGS (" + aisDynamicsList.get(0).getMmsi() + ") VALUES");
            InsertTestDemo.LOGGER.info("[ {} ]--------[ {} ]", InsertDaSoUtil.getNowTime(), " start Insert Data : (第 "+(i+1)+" 艘)");
            long start = System.currentTimeMillis();
            boolean bol = true;
            while (bol) {
                counts++;
                if (counts == 30) {
//                    System.out.println(insertSQL);

                    try {
                        stmt.executeQuery(insertSQL.toString());
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                    insertSQL = new StringBuilder("INSERT INTO ais_" + aisDynamicsList.get(0).getMmsi() + " USING ais_model TAGS (" + aisDynamicsList.get(0).getMmsi() + ") VALUES");
                    counts = 0;
                } else {
                    if (flag == aisDynamicsList.size()) {
//                        System.out.println(insertSQL);
                        try {
                            stmt.executeQuery(insertSQL.toString());
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                        bol = false;
                        break;
                    }
                    ShipBean obj = aisDynamicsList.get(flag);

                    insertSQL.append("('" + obj.getRecordDatetime() + "'," + obj.getLongitude() + "," + obj.getLatitude() + "," + obj.getDirection() + "," + obj.getHeading() + "," +
                            obj.getSpeed() + "," + obj.getStatus() + "," + obj.getRot() + "," + obj.getPositionAccuracy() + "," + obj.getUtcHour() + "," +
                            obj.getUtcMinute() + "," + obj.getUtcSecond() + "," + obj.getMessageId() + "," + obj.getRevDatetime() + "," + obj.getSourceId() + ") ");
                    flag++;

                }

            }
            long time = System.currentTimeMillis()-start;
            InsertTestDemo.LOGGER.info("[ {} ]--------[ {} ]--------[ {} ]", InsertDaSoUtil.getNowTime(), " end   Insert Data : (第 "+(i+1)+" 艘)"," 耗时 : " +(time)+ " 毫秒");
            sum+=time;
        }

        InsertTestDemo.LOGGER.info("[     Insert Final     ]");
        InsertTestDemo.LOGGER.info("[ {} ]--------[ {} ]--------[ {} ]"," sum("+ InsertDaSoUtil.aisDynamicList.size()+"0000条数据)   "," sumTime : "+sum," avgSpeed : "+InsertDaSoUtil.aisDynamicList.size()*10000/sum/1000);
    }


}

3.Utils

package com.taos.test;

import com.taos.test.bean.ShipBean;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class InsertDaSoUtil {

    InsertDaSoUtil(boolean path) throws IOException {
        aisDynamicList = dto2Bean(path);
        getStat(path);
    }

    public static List<List<ShipBean>> aisDynamicList;

    static Statement stmt = null;

    public static List<List<ShipBean>> dto2Bean(boolean flas) throws IOException {

        List<List<ShipBean>> aisDynamicList = new ArrayList<>();
        String filePath = "";
        for (int i = 100000000; i < 100000000 + 500; i++) {
            if (flas) {
                filePath = "D:\\100CSVFile\\" + i + "ship.csv";
            } else {
                filePath = "/root/ictxieshiqi/100CSVFile/" + i + "ship.csv";
            }
            File file = new File(filePath);

            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file), "utf-8"));
            String line = null;
            List<ShipBean> aisDynamics = new ArrayList<>();
            while ((line = bufferedReader.readLine()) != null) {
                // 数据�?
                String[] items = line.split(",");
                ShipBean aisDynamic = new ShipBean();
                aisDynamic.setRecordDatetime(items[0]);
                aisDynamic.setMmsi(Integer.parseInt(items[1]));
                aisDynamic.setLongitude(Integer.parseInt(items[2]));
                aisDynamic.setLatitude(Integer.parseInt(items[3]));
                aisDynamic.setDirection(Integer.parseInt(items[4]));
                aisDynamic.setHeading(Integer.parseInt(items[5]));
                aisDynamic.setSpeed(Integer.parseInt(items[6]));
                aisDynamic.setStatus(Integer.parseInt(items[7]));
                aisDynamic.setRot(Integer.parseInt(items[8]));
                aisDynamic.setPositionAccuracy(Integer.parseInt(items[9]));
                aisDynamic.setUtcHour(Integer.parseInt(items[10]));
                aisDynamic.setUtcMinute(Integer.parseInt(items[11]));
                aisDynamic.setUtcSecond(Integer.parseInt(items[12]));
                aisDynamic.setMessageId(Integer.parseInt(items[13]));
                aisDynamic.setRevDatetime(Integer.parseInt(items[14]));
                aisDynamic.setSourceId(Integer.parseInt(items[15]));
                aisDynamics.add(aisDynamic);
            }
            if (bufferedReader != null) {
                bufferedReader.close();
            }
            aisDynamicList.add(aisDynamics);
        }
        return aisDynamicList;
    }


    public static Statement getStat(Boolean falss) {
        String IP = "";

        if (!falss){
            IP = "localhost";
        }else {
            IP = "XXX.XXX.XXX.XXX";
        }

        try {

            if (stmt == null) {
                Connection conn = DriverManager.getConnection("jdbc:TAOS://"+IP+":6020/aisdata?user=root&password=taosdata&useSSL=false");
                stmt = conn.createStatement();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stmt;
    }

    public static String getNowTime() {
        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd :hh:mm:ss");
        return dateFormat.format(date);
    }

}

4.测试main

package com.taos.test;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;

public class InsertTestDemo {

    protected static Logger LOGGER = LoggerFactory.getLogger(InsertTestDemo.class);


    static {
        try {

            Class.forName("com.taosdata.jdbc.TSDBDriver");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    public static void main(String[] args) throws IOException {

        LOGGER.info("[ {} ]--------[ {} ]", InsertDaSoUtil.getNowTime(), " start Import Dart ");
        long start = System.currentTimeMillis();
        // true 本地环境 false 线上环境
        new InsertDaSoUtil(true);
        long end = System.currentTimeMillis();
        LOGGER.info("[ {} ]--------[ {} ]--------[ {} ]", InsertDaSoUtil.getNowTime(), " end   Import Data ", " 耗时 : " + (end - start) + " 毫秒");
        Thread td = new Thread(new InsertThread());
        td.run();
        td.start();
        td.stop();
    }
}