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());;
}