private static async Task <List <IInfluxSeries> > Select(long start, long end, params string[] measurements) { try { List <IInfluxSeries> queries = new List <IInfluxSeries>(); foreach (string measurement in measurements) { string query = $"select * from {measurement} where time >= {start}s and time < {end}s"; logger.Info("INFLUX QUERY: " + query); var result = await influxDBClient.QueryMultiSeriesAsync("statistics", query); logger.Info("INFLUX QUERY RESULT COUNT: " + result.Count); if (result.Count > 0) { queries.Add(result[0]); } } //string query = $"select * from {measurements} where time = {start}s"; //if (add_where_clause != null) // query = query + " and " + add_where_clause; return(queries); } catch (Exception ex) { Console.WriteLine(ex.Message); throw ex; } }
private static async Task <MonthlyValues> GetCurrentMonthAggregation() { if (DateTime.Now.Hour == 01 && DateTime.Now.Minute == 00) //we make this check only at 01:00 at night once a day { //get first and last day of last month string firstDayOfLastMonth = DateTime.Now.PreviousMonth().FirstDayOfMonth().BeginningOfDay().ToString("s") + "Z"; string lastDayOfLastMonth = DateTime.Now.PreviousMonth().LastDayOfMonth().EndOfDay().ToString("s") + "Z"; string monthIndex = DateTime.Now.PreviousMonth().FirstDayOfMonth().ToString("MM"); string yearIndex = DateTime.Now.PreviousMonth().FirstDayOfMonth().ToString("yyyy"); List <IInfluxSeries> resultsLastMonth = null; try { resultsLastMonth = await influxClient.QueryMultiSeriesAsync(influxDbNameLongterm, $"SELECT * from {measurement_PVmonthly} WHERE YearIndex = '{yearIndex}' AND MonthIndex ='{monthIndex}' limit 1"); } catch (InfluxDBException ex) { Console.Write(ex.Message); } //If e have not craeted the result for last month, we write it now. if (resultsLastMonth == null || !resultsLastMonth.Any()) { //get aggregated values for the last month List <IInfluxSeries> aggregatedLastMonth = null; MonthlyValues monthlyValues = null; try { aggregatedLastMonth = await influxClient.QueryMultiSeriesAsync(influxDbName, $"SELECT INTEGRAL(Pv,1h)/1000 as Pv_month,INTEGRAL(Load,1h)/1000 as Load_month, INTEGRAL(Inv,1h)/1000 as Inv_month, INTEGRAL(Demand,1h)/1000 as Demand_month, INTEGRAL(FeedIn,1h)/1000 as FeedIn_month FROM {measurement_points} WHERE time > '{firstDayOfLastMonth}' AND time < '{lastDayOfLastMonth}'"); monthlyValues = new MonthlyValues(); monthlyValues.PvMonth = float.Parse(aggregatedLastMonth.FirstOrDefault()?.Entries[0].Pv_month); monthlyValues.LoadMonth = float.Parse(aggregatedLastMonth.FirstOrDefault()?.Entries[0].Load_month); monthlyValues.InvMonth = float.Parse(aggregatedLastMonth.FirstOrDefault()?.Entries[0].Inv_month); monthlyValues.DemandMonth = float.Parse(aggregatedLastMonth.FirstOrDefault()?.Entries[0].Demand_month); monthlyValues.FeedInMonth = float.Parse(aggregatedLastMonth.FirstOrDefault()?.Entries[0].FeedIn_month); monthlyValues.MonthIndex = monthIndex; monthlyValues.YearIndex = yearIndex; } catch (InfluxDBException ex) { Console.Write(ex.Message); } return(monthlyValues); } } return(null); }
public async Task TestPartialResponseBecauseOfMaxRowLimit() { try { var client = new InfluxDBClient(influxUrl, dbUName, dbpwd); var points = new List <IInfluxDatapoint>(); var today = DateTime.Now.ToShortDateString(); var now = DateTime.UtcNow; var nowString = DateTime.Now.ToShortTimeString(); var measurement = "Partialtest"; for (int i = 0; i < 200000; i++) { var valMixed = new InfluxDatapoint <InfluxValueField>(); valMixed.Tags.Add("TestDate", today); valMixed.Tags.Add("TestTime", nowString); valMixed.UtcTimestamp = now + TimeSpan.FromMilliseconds(i * 100); valMixed.Fields.Add("Open", new InfluxValueField(DataGen.RandomDouble())); valMixed.Fields.Add("High", new InfluxValueField(DataGen.RandomDouble())); valMixed.Fields.Add("Low", new InfluxValueField(DataGen.RandomDouble())); valMixed.Fields.Add("Close", new InfluxValueField(DataGen.RandomDouble())); valMixed.Fields.Add("Volume", new InfluxValueField(DataGen.RandomDouble())); valMixed.MeasurementName = measurement; valMixed.Precision = TimePrecision.Nanoseconds; points.Add(valMixed); } Assert.IsTrue(await client.PostPointsAsync(dbName, points, 25000), "PostPointsAsync retunred false"); var r = await client.QueryMultiSeriesAsync(dbName, "SELECT * FROM Partialtest"); Assert.IsTrue(r.All(s => s.Partial), "Not all influx series returned by the query contained the flag 'partial=true'"); r = await client.QueryMultiSeriesAsync(dbName, "SELECT * FROM Partialtest limit 50000"); Assert.IsTrue(!r.Any(s => s.Partial), "At least one of the influx series returned by the query contained the flag 'partial=true'"); } catch (Exception e) { Assert.Fail($"Unexpected exception of type {e.GetType()} caught: {e.Message}"); return; } }
public async Task TestPostPointsAsync_DifferentPrecisions() { try { var client = new InfluxDBClient(influxUrl, dbUName, dbpwd); var time = DateTime.Now; var today = DateTime.Now.ToShortDateString(); var now = DateTime.Now.ToShortTimeString(); var points = new List <IInfluxDatapoint>(); foreach (TimePrecision precision in Enum.GetValues(typeof(TimePrecision))) { var point = new InfluxDatapoint <long>(); point.UtcTimestamp = DateTime.UtcNow; point.MeasurementName = $"Precision{precision.ToString()}"; point.Precision = precision; point.Tags.Add("Precision", precision.ToString()); point.Fields.Add("Ticks", point.UtcTimestamp.Ticks); points.Add(point); } var r = await client.PostPointsAsync(dbName, points); Assert.IsTrue(r, "PostPointsAsync retunred false"); var values = await client.QueryMultiSeriesAsync(dbName, "select * from /Precision[A-Za-z]/"); foreach (var val in values) { var x = val?.Entries?.FirstOrDefault(); var d = new DateTime(long.Parse(x.Ticks)); TimeSpan t = d - x.Time; TimePrecision p = Enum.Parse(typeof(TimePrecision), x.Precision); switch (p) { case TimePrecision.Hours: Assert.IsTrue(t.TotalHours < 1); break; case TimePrecision.Minutes: Assert.IsTrue(t.TotalMinutes < 1); break; case TimePrecision.Seconds: Assert.IsTrue(t.TotalSeconds < 1); break; case TimePrecision.Milliseconds: Assert.IsTrue(t.TotalMilliseconds < 1); break; case TimePrecision.Microseconds: Assert.IsTrue(t.Ticks < (TimeSpan.TicksPerMillisecond / 1000)); break; case TimePrecision.Nanoseconds: Assert.IsTrue(t.Ticks < 1); break; } } } catch (Exception e) { Assert.Fail($"Unexpected exception of type {e.GetType()} caught: {e.Message}"); return; } }
public async Task <IEnumerable <StockSymbol> > GetByUserAsync(string user) { using (InfluxDBClient client = await influxContext.GetDatabaseClient()) { List <IInfluxSeries> result = await client.QueryMultiSeriesAsync( influxContext.DatabaseName, $"SELECT * FROM {MeasureName} WHERE UserName='******'", TimePrecision.Hours); return(result.Any() ? result.First().Entries.Select(CreateStockSymbol).ToList() : Enumerable.Empty <StockSymbol>()); } }
public async Task TestQueryMultiSeriesAsync_Timeseries() { var client = new InfluxDBClient(influxUrl, dbUName, dbpwd); Stopwatch s = new Stopwatch(); s.Start(); var r = await client.QueryMultiSeriesAsync(internalDb, "show stats"); s.Stop(); Debug.WriteLine($"Elapsed{s.ElapsedMilliseconds}"); Assert.IsTrue(r != null && r.Count > 0, "QueryMultiSeriesAsync retunred null or invalid data"); }
public async Task TestQueryMultiSeriesAsync() { var client = new InfluxDBClient(influxUrl, dbUName, dbpwd); Stopwatch s = new Stopwatch(); s.Start(); var r = await client.QueryMultiSeriesAsync(dbName, "SHOW STATS"); s.Stop(); Debug.WriteLine($"Elapsed{s.ElapsedMilliseconds}"); Assert.IsTrue(r != null, "QueryMultiSeriesAsync retunred null or invalid data"); }
IEnumerable <InfluxDbQueryResult> IInfluxDbRead.Read(string database, string query) { var result = _client.QueryMultiSeriesAsync(database, query).Result; foreach (var cur in result.Where(x => x.HasEntries)) { yield return(new InfluxDbQueryResult { SeriesName = cur.SeriesName, Data = cur.Entries.ToArray() }); } }
public async Task <IEnumerable <StockSymbol> > GetByUserAndSymbolAsync(string user, string symbolName, DateTime?from = null, DateTime?to = null) { using (InfluxDBClient client = await influxContext.GetDatabaseClient()) { List <IInfluxSeries> result = await client.QueryMultiSeriesAsync( influxContext.DatabaseName, $"SELECT * FROM {MeasureName} WHERE UserName='******' AND SymbolName='{symbolName}'", TimePrecision.Hours); return(result.Any() ? result.First().Entries.Select(CreateStockSymbol).Where( s => (!from.HasValue || s.Date >= from) && (!to.HasValue || s.Date <= to)).ToList() : Enumerable.Empty <StockSymbol>()); } }
public static async Task <IList <IDictionary <string, object> > > ExecuteInfluxDBQuery(string query, InfluxDBLoginInformation loginInformation) { using (var influxDbClient = new InfluxDBClient(loginInformation.DBUri.ToString(), loginInformation.User, loginInformation.Password)) { var series = await influxDbClient.QueryMultiSeriesAsync(loginInformation.DB, query, precision : TimePrecision.Seconds).ConfigureAwait(false); var accumatedList = new List <IDictionary <string, object> >(); foreach (var serie in series) { accumatedList.AddRange(serie.Entries.Select(x => (IDictionary <string, object>)x)); } return(accumatedList); } }
public async Task TestQueryMultiSeriesAsync_ToObject() { var client = new InfluxDBClient(influxUrl, dbUName, dbpwd); var tagId = DataGen.RandomString(); var points = Enumerable.Range(0, 3).Select(i => new PointObjectWithStringRetention { Time = DateTime.UtcNow, Measurement = measurementName, Precision = TimePrecision.Nanoseconds, StringTag = tagId, IntTag = i, StringField = DataGen.RandomString(), DoubleField = DataGen.RandomDouble(), IntField = DataGen.RandomInt(), BoolField = i % 2 == 0, } ).ToList(); var postResult = await client.PostPointsAsync(dbName, points); Assert.IsTrue(postResult, "PostPointsAsync returned false"); Stopwatch s = new Stopwatch(); s.Start(); var queryResult = await client.QueryMultiSeriesAsync <PointObjectWithStringRetention>(dbName, $"SELECT * from {measurementName} WHERE StringTag='{tagId}'"); s.Stop(); Debug.WriteLine($"Elapsed {s.ElapsedMilliseconds}ms"); Assert.IsTrue(queryResult != null, "QueryMultiSeriesAsync returned null or invalid data"); Assert.IsTrue(queryResult.Count == 1, "QueryMultiSeriesAsync returned invalid number of series"); foreach (var point in points) { var matching = queryResult[0].Entries.FirstOrDefault(e => e.IntTag == point.IntTag); Assert.IsTrue(matching != null, $"Missing record corresponding to record {point.IntTag}"); Assert.IsTrue(matching.StringField == point.StringField, $"Mismatching string field on record {point.IntTag}"); Assert.IsTrue(Math.Abs(matching.DoubleField - point.DoubleField) < .0000001, $"Mismatching double field on record {point.IntTag}"); Assert.IsTrue(matching.IntField == point.IntField, $"Mismatching int field on record {point.IntTag}"); Assert.IsTrue(matching.BoolField == point.BoolField, $"Mismatching bool field on record {point.IntTag}"); } }
public async Task TestQueryMultiSeriesAsync_Chunking_BySeries() { try { var client = new InfluxDBClient(influxUrl, dbUName, dbpwd); var time = DateTime.Now; var TestDate = time.ToShortDateString(); var TestTime = time.ToShortTimeString(); var chunkSize = 10; var points = new List <IInfluxDatapoint>(); for (var i = 0; i < chunkSize * 10; i++) { await Task.Delay(1); var point = new InfluxDatapoint <long>(); point.UtcTimestamp = DateTime.UtcNow; point.MeasurementName = "ChunkTest"; point.Precision = TimePrecision.Nanoseconds; point.Tags.Add("ChunkSeries", point.UtcTimestamp.Ticks % 2 == 0 ? "Chunk0" : "Chunk1"); point.Tags.Add("TestDate", TestDate); point.Tags.Add("TestTime", TestTime); point.Fields.Add("Val", i); points.Add(point); } var r = await client.PostPointsAsync(dbName, points); Assert.IsTrue(r, "PostPointsAsync retunred false"); var values = await client.QueryMultiSeriesAsync(dbName, $"select sum(Val) from ChunkTest where TestTime ='{ TestTime}' group by ChunkSeries", chunkSize * 10); foreach (var val in values) { var x = val?.Entries?.Count; //the series should be smaller than the chunk size, each resultset will only one series Assert.IsTrue(x == 1); } } catch (Exception e) { Assert.Fail($"Unexpected exception of type {e.GetType()} caught: {e.Message}"); return; } }
/// <summary> /// 执行Influx查询语句并转换结果为指定的实体类型 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="influxQL">查询语句</param> /// <returns>转换后的实体集合</returns> public async Task <List <InfluxQueryItem <T> > > Query <T>(string influxQL) { try { List <InfluxQueryItem <T> > queryResult = new List <InfluxQueryItem <T> >(); Stopwatch watch = new Stopwatch(); watch.Start(); //执行查询语句 var result = await influxClient.QueryMultiSeriesAsync(_influxDbName, influxQL); if (result != null) { //InfluxDB支持一次性执行多条查询语句,但是本函数支持传递单条查询语句 foreach (var series in result) { if (series.HasEntries) { foreach (var data in series.Entries) { //转换为指定的实体类型,采用json格式进行中转,可以思考有没有更加高效的转换方法 string tempJson = Newtonsoft.Json.JsonConvert.SerializeObject(data); T obj = Newtonsoft.Json.JsonConvert.DeserializeObject <T>(tempJson); //由于直接转换为实体类会丢失时间戳,所以通过 InfluxQueryItem 来保留时间戳 InfluxQueryItem <T> queryItem = new InfluxQueryItem <T>(); queryItem.LocalTime = DataModel.DateTimeConverter.ToLocalTime((DateTime)data.Time); queryItem.Data = obj; queryResult.Add(queryItem); } } } } watch.Stop(); _logger.LogDebug($"查询耗时:{watch.Elapsed.TotalSeconds}s,InfluxQL:{influxQL}"); return(queryResult); } catch (Exception ex) { _logger.LogError($"查询出错,InfluxQL:{influxQL},原因:{ex.Message}\r\n{ex.StackTrace}"); } return(null); }
/// <summary> /// Get All List /// </summary> /// <returns></returns> public async Task <List <IInfluxSeries> > GetAllListAsync() { var list = await InfluxDBConnection.QueryMultiSeriesAsync(DbName, $"select * from {TableName} WHERE 1=1"); return(list.ToList());; }