时序数据库Influxdb安装使用问题与c#客户端

安装与使用

docker 安装：docker run influxdb -d --name influxdb -p8086:8086 influxdb

交互式

进入docker容器：使用influx命令，会进入交互模式，并显示当前版本；

// 创建数据库 create database Test // 显示当前所有存在的数据库 show databases // 进入数据库 use Test // 创建表——插入数据时直接创建表和字段 // 插入数据 insert test,Name=hyn,Sex=man age=24 // 查看表 show measurements // 查询 select "Name","age" from test where "Name"='hyn'

web api

influxdb主要提供api进行数据交互，服务默认端口为8086

查询Api

/query支持通过sql创建数据库 ：

http://ip:8086/query?q=create database Test2

表查询：

http://ip:8086/query?db=Test&q=select * from test

插入Api

http://192.168.75.128:8086/write?db=test

插入只支持post方式调用，通过二进制流的方式传递sql，可以通过PostMan进行测试，在文件中编辑sql即可

插入数据

influxdb插入操作语句为：

insert <measurement>,<tagNames>=<tagValues> <fieldNames>=<fieldValues> <time>

其中tag和time为可选项，time为纳秒计时间戳，为空时系统会自动已当前时间生成。

tag

tag为数据的标签，可以设置多个，属性值只能为字符串，插入时系统会自动转为字符串，会建立索引

field

field至少需要一个，为量测值，不会建立索引，field支持的数据类型为字符串，整型，浮点型。由于measurement创建后字段类型会确定下来，以后插入数据时数据类型不一致将导致插入失败，因此建议只使用字符串和浮点类型，极端情况下可以只使用字符串类型，查询时依需求进行转换。

sql中标点符号问题

空格用于tag field 和time的分隔符，除此之外sql中不能有其它多余空格；

tag的值系统默认转为字符串，sql中不要再加""，否则会产生多余的""，field的值如果为字符串，则需要加""，且需要考虑字符串中的特殊符号，否则插入时会产生异常。

查询数据

influxdb的查询操作语句为：

select <filedName> from Test where ... group by ... order by ... limit n

其实很大程度上其sql与关系型数据库sql一致

查询时至少需要包含一个field，否则返回为空

查询时标点符号使用问题

字段名最好加上""，否则一些特殊字段的查询可能会有问题，不信试试"name"；

where、orderby等中的=右边的value需要加’’

C#读写influxdb示例

public class InfluxdbClient { private static string host_; private static string port_; private static string user_; private static string password_; private static bool https_; private static string InitUrl() { var http = "http://"; if (https_) { http = "https://"; } var url = http + host_ + ":" + port_; return url; } public InfluxdbClient(string host, string port = "8086", string user = null, string password = null, bool https = false) { host_ = host; port_ = port; user_ = user; password_ = password; https_ = https; } private string AddAuthToUrl(string url) { if (string.IsNullOrEmpty(user_)) { return url; } url += $"&u={user_}&p={password_}"; return url; } public string Query(string dbName, string sql) { var url = InitUrl() + $"/query?db={dbName}&q={sql}"; url = AddAuthToUrl(url); string resultStr = HttpRequestUtil.Get(url); return resultStr; } public void CreateDB(string dbName) { string sql = $"create database {dbName}"; var url = InitUrl() + $"/query?q={sql}"; HttpRequestUtil.Get(url); } public void Write(string dbName, string sql) { var url = InitUrl() + $"/write?db={dbName}"; url = AddAuthToUrl(url); HttpRequestUtil.Post(url, sql); } public static string CombineInsertSql(string measurement, List<InfluxField> fileds) { StringBuilder sqlBuilder = new StringBuilder(measurement); var timeObj = fileds.Find(x => x.Name == "time"); var time = timeObj == null?null:timeObj.ToString(); var meases = fileds.Where(x => x.ValueType == ValueTypeEnum.Field); var tags = fileds.Where(x => x.ValueType == ValueTypeEnum.Tag); // 先拼接tag foreach (var tag in tags) { AddTagTo(sqlBuilder, tag); } sqlBuilder.Append(" "); foreach (var field in meases) { AddFieldTo(sqlBuilder, field); } // field肯定会有，这里要删除最后的一个逗号 sqlBuilder = sqlBuilder.Remove(sqlBuilder.Length - 1, 1); if (!string.IsNullOrEmpty(time)) { sqlBuilder.Append(" "); sqlBuilder.Append(time); } return sqlBuilder.ToString(); } private static void AddFieldTo(StringBuilder sqlBuilder, InfluxField field) { if (field.Value != null) { string value = field.Value; // 所有的字段全部转换为字符串存入，field如果是字符串型，则需要加双引号，JsonConvert.SerializeObject会自动给字符串加上双引号 value = JsonConvert.SerializeObject(value); sqlBuilder.Append($"{field.Name}=" + value); sqlBuilder.Append(","); } } private static void AddTagTo(StringBuilder sqlBuilder, InfluxField field) { if (field.Value != null) { sqlBuilder.Append(","); // tag始终作为字符串存入 string value = field.Value.ToString(); sqlBuilder.Append($"{field.Name}=" + value); } } } public class HttpRequestUtil { private static WebRequest GetRequest(string url,string method,int timeout=10000,string contentType= "text/html;charset=UTF-8") { WebRequest request = WebRequest.Create(@url); request.Method = method; request.ContentType = contentType; request.Timeout = timeout; return request; } public static string Get(string url) { var request = GetRequest(url,"GET"); Stream responseStream = request.GetResponse().GetResponseStream(); StreamReader myStreamReader = new StreamReader(responseStream, Encoding.GetEncoding("utf-8")); string retString = myStreamReader.ReadToEnd(); return retString; } public static string Post(string url,string param) { var request = GetRequest(url, "POST",contentType: "application/json"); if (!string.IsNullOrEmpty(param)) { byte[] bs = Encoding.UTF8.GetBytes(param); request.ContentLength = bs.Length; Stream newStream = request.GetRequestStream(); newStream.Write(bs, 0, bs.Length); newStream.Close(); } Stream responseStream = request.GetResponse().GetResponseStream(); StreamReader myStreamReader = new StreamReader(responseStream, Encoding.GetEncoding("utf-8")); string retString = myStreamReader.ReadToEnd(); responseStream.Close(); return retString; } } public class InfluxField { public string Name { get; set; } /// <summary> /// Value始终为字符型，取用时根据需要解析 /// </summary> public string Value { get; set; } public ValueTypeEnum ValueType { get; set; } public InfluxField(string name, string value, ValueTypeEnum type = ValueTypeEnum.Field) { Name = name; Value = value; ValueType = type; } public enum ValueTypeEnum { Field = 0, Tag = 1, Time = 2 } }

使用示例：

// 创建客户端 InfluxdbClient client = new InfluxdbClient("127.0.0.1"); // 查询 string querySql = "select * from test"; string db = "Test"; var queryResult = client.Query(db, querySql); // 插入 string measurement = "test"; // 首先定义tag、field、time List<InfluxField> fields = new List<InfluxField>(); fields.Add(new InfluxField("Name","hyn"),ValueTypeEnum.Tag); fields.Add(new InfluxField("Weight",60),ValueTypeEnum.Field); string insertSql = InfluxdbClient.CombineInsertSql(measurement,fields); client.Write(db, insertSql);