IoTDB时序数据库-原生接口开发

---

前言

本文主要介绍针对IoTDB原生接口的数据库开发，以及开发过程中遇到的问题和解决方法。
参考文档：《IoTDB用户手册》编程-原生接口

---

一、依赖

• JDK >= 1.8
• Maven >= 3.1
• 在maven中导入，使用原生接口

<dependencies>
    <dependency>
      <groupId>org.apache.iotdb</groupId>
      <artifactId>iotdb-session</artifactId>
      <version>0.10.1</version>
    </dependency>
</dependencies>

二、原生接口使用示例

1.原生接口开发代码

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.uspacex.ratchet.iotdb.util.dateFormatUtil;
import com.uspacex.ratchet.iotdb.util.getHostPortUtil;
import com.uspacex.ratchet.platform.exception.BaseException;
import com.uspacex.ratchet.platform.response.ResponseCode;
import lombok.extern.slf4j.Slf4j;
import org.apache.iotdb.rpc.BatchExecutionException;
import org.apache.iotdb.rpc.IoTDBConnectionException;
import org.apache.iotdb.rpc.StatementExecutionException;
import org.apache.iotdb.rpc.TSStatusCode;
import org.apache.iotdb.session.Session;
import org.apache.iotdb.session.SessionDataSet;
import org.apache.iotdb.tsfile.file.metadata.enums.CompressionType;
import org.apache.iotdb.tsfile.file.metadata.enums.TSDataType;
import org.apache.iotdb.tsfile.file.metadata.enums.TSEncoding;
import org.apache.iotdb.tsfile.read.common.Field;
import org.apache.iotdb.tsfile.read.common.RowRecord;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.text.ParseException;
import java.util.*;

@Component
@Slf4j
public class IotdbPrimativeService {
    public static Session session;
    private static String url;
    private static String username;
    private static String password;

    @Value("${spring.datasource.iotdb.jdbc-url}")
    public void setUrl(String url) {
        IotdbPrimativeService.url = url;
    }

    @Value("${spring.datasource.iotdb.username}")
    public void setUsername(String username) {
        IotdbPrimativeService.username = username;
    }

    @Value("${spring.datasource.iotdb.password}")
    public void setPassword(String password) {
        IotdbPrimativeService.password = password;
    }

    /**
     * 原生接口创建时间序列
     */
    public static void createTimeseries(String deviceId, String sensorId,
                                        String timeSeriesId,
                                        String type,
                                        String encoding,
                                        String compressionType,
                                        Map<String, String> tags,
                                        Map<String, String> attributes,
                                        String measurementAlias) throws IoTDBConnectionException, StatementExecutionException {

        session = new Session(getHostPortUtil.getHost(url), getHostPortUtil.getPort(url), username, password);
        session.open(false);
        try {
            session.setStorageGroup("root." + deviceId);
        } catch (StatementExecutionException e) {
            if (e.getStatusCode() != TSStatusCode.PATH_ALREADY_EXIST_ERROR.getStatusCode())
                throw e;
        }

        if (!session.checkTimeseriesExists("root." + deviceId + "." + sensorId + "." + timeSeriesId)) {
            session.createTimeseries(
                    "root." + deviceId + "." + sensorId + "." + timeSeriesId,
                    TSDataType.valueOf(type),
                    TSEncoding.valueOf(encoding),
                    CompressionType.valueOf(compressionType),
                    null,
                    tags,
                    attributes,
                    measurementAlias);
        } else {
            throw new BaseException(ResponseCode.TIMESERIES_ALREADY_EXIST);
        }
        session.close();
    }

    /**
     * 更新时间序列的标签和属性
     * {
     * "newTag": "fruit1",
     * "newAttr": "v2"
     * }
     */
    public static void updateTagsAndAttributs(
            String deviceId,
            String sensorId,
            String timeSeriesId,
            Map<String, String> newTags,
            Map<String, String> newAttrs,
            String newAlias) throws IoTDBConnectionException, StatementExecutionException {
        session = new Session(getHostPortUtil.getHost(url), getHostPortUtil.getPort(url), username, password);
        session.open(false);
        try {
            session.setStorageGroup("root." + deviceId);
        } catch (StatementExecutionException e) {
            if (e.getStatusCode() != TSStatusCode.PATH_ALREADY_EXIST_ERROR.getStatusCode())
                throw e;
        }

        String tagsString = "";
        for (String key : newTags.keySet()) {
            tagsString += key + "=" + newTags.get(key) + ", ";
        }
        tagsString = tagsString.substring(0, tagsString.length() - 2);

        String attrsString = "";
        for (String key : newAttrs.keySet()) {
            attrsString += key + "=" + newAttrs.get(key) + ", ";
        }
        attrsString = attrsString.substring(0, attrsString.length() - 2);

        log.info("ALTER timeseries root." + deviceId + "." + sensorId + "." + timeSeriesId + " upsert alias=" + newAlias + " tags(" + tagsString + ") attributes(" + attrsString + ")");
        session.executeNonQueryStatement("ALTER timeseries root." + deviceId + "." + sensorId + "." + timeSeriesId + " upsert alias=" + newAlias + " tags(" + tagsString + ") attributes(" + attrsString + ")");
        session.close();

    }

    /**
     * 查询某设备传感器的所有时间序列
     */
    public static void getTimeseriess(
            String deviceId,
            String sensorId
    ) throws IoTDBConnectionException, StatementExecutionException {
        session = new Session(getHostPortUtil.getHost(url), getHostPortUtil.getPort(url), username, password);
        session.open(false);
        try {
            session.setStorageGroup("root." + deviceId);
        } catch (StatementExecutionException e) {
            if (e.getStatusCode() != TSStatusCode.PATH_ALREADY_EXIST_ERROR.getStatusCode())
                throw e;
        }
        SessionDataSet dataSet;
        dataSet = session.executeQueryStatement("show timeseries root." + deviceId + "." + sensorId);
        session.close();
    }

    /**
     * 插入数据
     * [
     * {
     * "timestamp": "2020-11-26 18:27:01",
     * "value": "36"
     * },
     * {
     * "timestamp": "2020-11-26 18:28:02",
     * "value": "37"
     * },
     * {
     * "timestamp": "2020-11-26 18:29:03",
     * "value": "38"
     * }
     * ]
     */
    public static void insertStrRecord(String deviceId, String sensorId, String timeSeriesId, JSONArray jsonArray)
            throws IoTDBConnectionException, BatchExecutionException, ParseException {

        session = new Session(getHostPortUtil.getHost(url), getHostPortUtil.getPort(url), username, password);
        session.open(false);

        List<String> deviceIds = new ArrayList<>();
        List<Long> times = new ArrayList<>();
        List<List<String>> measurementsList = new ArrayList<>();
        List<List<String>> valueList = new ArrayList<>();

        for (int i = 0; i < jsonArray.size(); i++) {

            JSONObject jsonObject = jsonArray.getJSONObject(i);
            String timeStamp = jsonObject.getString("timestamp");
            String value = jsonObject.getString("value");

            deviceIds.add("root." + deviceId + "." + sensorId);
            times.add(dateFormatUtil.timeToSecond(timeStamp));
            List<String> values = new ArrayList<>();
            values.add(value);
            valueList.add(values);
            List<String> measurements = new ArrayList<>();
            measurements.add(timeSeriesId);
            measurementsList.add(measurements);
        }

        session.insertRecords(
                deviceIds,
                times/*System.currentTimeMillis()*/,
                measurementsList,
                valueList);
        session.close();
    }

    /**
     * 查询时间序列某个时间段的数据
     * {
     * "startTime": "2020-11-26 18:27:01",
     * "endTime": "2020-11-26 18:29:03"
     * }
     */
    public static List<Map<String, Object>> query(String deviceId, String sensorId, String timeSeriesId, String startTime, String endTime)
            throws IoTDBConnectionException, StatementExecutionException, ParseException {

        session = new Session(getHostPortUtil.getHost(url), getHostPortUtil.getPort(url), username, password);
        session.open(false);
        try {
            session.setStorageGroup("root." + deviceId);
        } catch (StatementExecutionException e) {
            if (e.getStatusCode() != TSStatusCode.PATH_ALREADY_EXIST_ERROR.getStatusCode())
                throw e;
        }
        SessionDataSet dataSet;
        dataSet = session.executeQueryStatement("select " + timeSeriesId + " from root." + deviceId + "." + sensorId + " where time >= " + dateFormatUtil.timeToSecond(startTime) + " and time <= " + dateFormatUtil.timeToSecond(endTime));

        List<String> columnNames = dataSet.getColumnNames();
        List<Map<String, Object>> returnList = new LinkedList<>();
        dataSet.setFetchSize(1024); // default is 10000
        while (dataSet.hasNext()) {
            Map<String, Object> map = new HashMap<>();
            RowRecord rowRecord = dataSet.next();
            map.put("timestamp", dateFormatUtil.timeToFormat(rowRecord.getTimestamp()));
            List<Field> fields = rowRecord.getFields();
            for (int i = 0; i < fields.size(); i++) {
                if (fields.get(i) != null) {
                    TSDataType dataType = fields.get(i).getDataType();
                    map.put(columnNames.get(i + 1), fields.get(i).getObjectValue(dataType));
                }
            }
            returnList.add(map);
        }

        dataSet.closeOperationHandle();
        session.close();
        return returnList;
    }


    /**
     * 删除数据（指定时间之前的）
     */
    public static void deleteData(String deviceId, String sensorId, String timeSeriesId, String endTime)
            throws IoTDBConnectionException, StatementExecutionException, ParseException {

        session = new Session(getHostPortUtil.getHost(url), getHostPortUtil.getPort(url), username, password);
        session.open(false);
        try {
            session.setStorageGroup("root." + deviceId);
        } catch (StatementExecutionException e) {
            if (e.getStatusCode() != TSStatusCode.PATH_ALREADY_EXIST_ERROR.getStatusCode())
                throw e;
        }

        List<String> paths = new ArrayList<>();
        paths.add("root." + deviceId + "." + sensorId + "." + timeSeriesId);
        session.deleteData(
                paths,
                dateFormatUtil.timeToSecond(endTime));
        session.close();
    }

    /**
     * 删除指定时间序列
     */
    public static void deleteTimeSeries(String deviceId, String sensorId, String timeSeriesId)
            throws IoTDBConnectionException, StatementExecutionException {

        session = new Session(getHostPortUtil.getHost(url), getHostPortUtil.getPort(url), username, password);
        session.open(false);
        try {
            session.setStorageGroup("root." + deviceId);
        } catch (StatementExecutionException e) {
            if (e.getStatusCode() != TSStatusCode.PATH_ALREADY_EXIST_ERROR.getStatusCode())
                throw e;
        }

        List<String> paths = new ArrayList<>();
        paths.add("root." + deviceId + "." + sensorId + "." + timeSeriesId);
        session.deleteTimeseries(paths);

        session.close();
    }

}

2.用到的工具类

• 日期格式转换

import java.text.ParseException;
import java.text.SimpleDateFormat;

public class dateFormatUtil {
    public static long timeToSecond(String date) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.parse(date).getTime();
    }

    public static String timeToFormat(long time) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(time);
    }
}

• 字符串分割url获得host与port

public class getHostPortUtil {
    public static String getHost(String url) {
        if (url != null) {
            String[] sp = url.split("//|:|/");
            return sp[sp.length - 2];
        }
        return null;
    }

    public static int getPort(String url) {
        if (url != null) {
            String[] sp = url.split("//|:|/");
            return Integer.parseInt(sp[sp.length - 1]);
        }
        return 0;
    }
}

---

总结

在开发过程中，还是有很多细节需要注意的。比如参数的类型要保持一致，拼接SQL语句的时候尤其要注意空格的问题，不能落下或者多。