private async Task RunQuerySeriesSampleWithInlineVariables(TimeSeriesInsightsQueries queriesClient, TimeSeriesId tsId)
{
// Query for two series, one with the temperature values in Celsius and another in Fahrenheit
#region Snippet:TimeSeriesInsightsSampleQuerySeriesWithInlineVariables
Console.WriteLine("\n\nQuery for temperature series in Celsius and Fahrenheit over the past 10 minutes.\n");
var celsiusVariable = new NumericVariable(
new TimeSeriesExpression("$event.Temperature"),
new TimeSeriesExpression("avg($value)"));
var fahrenheitVariable = new NumericVariable(
new TimeSeriesExpression("$event.Temperature * 1.8 + 32"),
new TimeSeriesExpression("avg($value)"));
var querySeriesRequestOptions = new QuerySeriesRequestOptions();
querySeriesRequestOptions.InlineVariables["TemperatureInCelsius"] = celsiusVariable;
querySeriesRequestOptions.InlineVariables["TemperatureInFahrenheit"] = fahrenheitVariable;
QueryAnalyzer seriesQuery = queriesClient.CreateSeriesQuery(
tsId,
TimeSpan.FromMinutes(10),
null,
querySeriesRequestOptions);
await foreach (TimeSeriesPoint point in seriesQuery.GetResultsAsync())
{
double?tempInCelsius = (double?)point.GetValue("TemperatureInCelsius");
double?tempInFahrenheit = (double?)point.GetValue("TemperatureInFahrenheit");
Console.WriteLine($"{point.Timestamp} - Average temperature in Celsius: {tempInCelsius}. Average temperature in Fahrenheit: {tempInFahrenheit}.");
}
#endregion Snippet:TimeSeriesInsightsSampleQuerySeriesWithInlineVariables
}
private async Task RunQueryAggregateSeriesSample(TimeSeriesInsightsQueries queriesClient, TimeSeriesId tsId)
{
#region Snippet:TimeSeriesInsightsSampleQueryAggregateSeriesWithNumericVariable
Console.WriteLine("\n\nQuery for the average temperature over the past 30 seconds, in 2-second time slots.\n");
var numericVariable = new NumericVariable(
new TimeSeriesExpression("$event.Temperature"),
new TimeSeriesExpression("avg($value)"));
var requestOptions = new QueryAggregateSeriesRequestOptions();
requestOptions.InlineVariables["Temperature"] = numericVariable;
requestOptions.ProjectedVariableNames.Add("Temperature");
QueryAnalyzer aggregateSeriesQuery = queriesClient.CreateAggregateSeriesQuery(
tsId,
TimeSpan.FromSeconds(2),
TimeSpan.FromSeconds(30),
null,
requestOptions);
await foreach (TimeSeriesPoint point in aggregateSeriesQuery.GetResultsAsync())
{
double?averageTemperature = point.GetNullableDouble("Temperature");
if (averageTemperature != null)
{
Console.WriteLine($"{point.Timestamp} - Average temperature: {averageTemperature}.");
}
}
#endregion Snippet:TimeSeriesInsightsSampleQueryAggregateSeriesWithNumericVariable
#region Snippet:TimeSeriesInsightsSampleQueryAggregateSeriesWithAggregateVariable
Console.WriteLine("\n\nCount the number of temperature events over the past 3 minutes, in 1-minute time slots.\n");
// Get the count of events in 60-second time slots over the past 3 minutes
DateTimeOffset endTime = DateTime.UtcNow;
DateTimeOffset startTime = endTime.AddMinutes(-3);
var aggregateVariable = new AggregateVariable(
new TimeSeriesExpression("count()"));
var countVariableName = "Count";
var aggregateSeriesRequestOptions = new QueryAggregateSeriesRequestOptions();
aggregateSeriesRequestOptions.InlineVariables[countVariableName] = aggregateVariable;
aggregateSeriesRequestOptions.ProjectedVariableNames.Add(countVariableName);
QueryAnalyzer query = queriesClient.CreateAggregateSeriesQuery(
tsId,
startTime,
endTime,
TimeSpan.FromSeconds(60),
aggregateSeriesRequestOptions);
await foreach (TimeSeriesPoint point in query.GetResultsAsync())
{
long?temperatureCount = (long?)point.GetValue(countVariableName);
Console.WriteLine($"{point.Timestamp} - Temperature count: {temperatureCount}");
}
#endregion Snippet:TimeSeriesInsightsSampleQueryAggregateSeriesWithAggregateVariable
}
public void NoArgumentQuery()
{
var command = new SqliteCommand();
var environment = QueryEnvironment.Default;
FormattableString plain = $"SELECT 1";
QueryAnalyzer.SetQueryToDbCommand(plain, command, environment);
command.CommandText.Is("SELECT 1");
command.Parameters.Count.Is(0);
FormattableString brackets = $"SELECT '{{foo}}{{bar'";
QueryAnalyzer.SetQueryToDbCommand(brackets, command, environment);
command.CommandText.Is("SELECT '{foo}{bar'", "Unescape brackets");
command.Parameters.Count.Is(0);
}
public void BasicExtract()
{
Expression<Func<Table1, Table2, FormattableString>> expr =
(t1, t2) => $"SELECT {t1:*} FROM {t1:AS \"T1\"}, {t2} WHERE {t1.Id} = {t2.Id} AND {t1.ColumnName:C} = {10,2:X}";
var environment = QueryEnvironment.Default;
var fs = QueryAnalyzer.ExtractFormattableString(expr, environment);
fs.Format.Is("SELECT {0:*} FROM {1:AS \"T1\"}, {2} WHERE {3} = {4} AND {5:C} = {6,2:X}");
var arguments = fs.GetArguments();
var arg0 = arguments[0]!.IsInstanceOf<QueryAnalyzer.TableReference>("arguments[0] is a TableReference");
arg0.TableMapper.GetTableName().Is(nameof(Table1));
arg0.EscapedAlias.Is("\"T1\"");
var arg1 = arguments[1]!.IsInstanceOf<QueryAnalyzer.TableReference>("arguments[1] is a TableReference");
arg1.IsSameReferenceAs(arg0, "arguments[0] == arguments[1]");
var arg2 = arguments[2]!.IsInstanceOf<QueryAnalyzer.TableReference>("arguments[2] is a TableReference");
arg2.TableMapper.GetTableName().Is("TableTwo");
arg2.EscapedAlias.IsNull("Table2 has no alias");
var arg3 = arguments[3]!.IsInstanceOf<QueryAnalyzer.ColumnReference>("arguments[3] is a ColumnReference");
arg3.Table.IsSameReferenceAs(arg1, "The table of arguments[3] is Table1");
arg3.ColumnName.Is(nameof(Table1.Id));
var arg4 = arguments[4]!.IsInstanceOf<QueryAnalyzer.ColumnReference>("arguments[4] is a ColumnReference");
arg4.Table.IsSameReferenceAs(arg2, "The table of arguemnts[4] is Table2");
arg4.ColumnName.Is(nameof(Table2.Id));
var arg5 = arguments[5]!.IsInstanceOf<QueryAnalyzer.ColumnReference>("arguments[5] is a ColumnReference");
arg5.Table.IsSameReferenceAs(arg1, "The table of arguemnts[5] is Table1");
arg5.ColumnName.Is("FooColumn");
arguments[6]!.IsInstanceOf<int>("arguments[6] is a int").Is(10);
var command = new SqliteCommand();
QueryAnalyzer.SetQueryToDbCommand(fs, command, environment);
command.CommandText.Is(@"SELECT ""T1"".""Id"", ""T1"".""FooColumn"", ""T1"".""NullableField"" FROM ""Table1"" AS ""T1"", ""TableTwo"" WHERE ""T1"".""Id"" = ""TableTwo"".""Id"" AND ""FooColumn"" = @QuelimbParam0");
command.Parameters.Count.Is(1, "The command has 1 parameter");
var param0 = command.Parameters[0];
param0.ParameterName.Is("@QuelimbParam0");
param0.Value.Is(" A", "10 in hexadecimal and padding");
}
public void InvalidFormat()
{
var command = new SqliteCommand();
var environment = QueryEnvironment.Default;
Expression<Func<Table1, FormattableString>> tableAlign = t1 => $"{t1,1}";
Assert.Throws<FormatException>(() => RunSetQuery(tableAlign));
Expression<Func<Table1, FormattableString>> tableInvalidFormat = t1 => $"{t1:INVALID}";
Assert.Throws<FormatException>(() => RunSetQuery(tableInvalidFormat));
Expression<Func<Table1, FormattableString>> columnAlign = t1 => $"{t1.Id,1}";
Assert.Throws<FormatException>(() => RunSetQuery(columnAlign));
Expression<Func<Table1, FormattableString>> columnInvalidFormat = t1 => $"{t1.Id:INVALID}";
Assert.Throws<FormatException>(() => RunSetQuery(columnInvalidFormat));
void RunSetQuery(LambdaExpression expr)
{
QueryAnalyzer.SetQueryToDbCommand(QueryAnalyzer.ExtractFormattableString(expr, environment), command, environment);
}
}
public async Task TimeSeriesInsightsQuery_AggregateSeriesWithNumericVariable()
{
// Arrange
TimeSeriesInsightsClient tsiClient = GetClient();
DeviceClient deviceClient = await GetDeviceClient().ConfigureAwait(false);
// Figure out what the Time Series Id is composed of
TimeSeriesModelSettings modelSettings = await tsiClient.ModelSettings.GetAsync().ConfigureAwait(false);
// Create a Time Series Id where the number of keys that make up the Time Series Id is fetched from Model Settings
TimeSeriesId tsiId = await GetUniqueTimeSeriesInstanceIdAsync(tsiClient, modelSettings.TimeSeriesIdProperties.Count)
.ConfigureAwait(false);
try
{
// Send some events to the IoT hub with with a second between each event
await QueryTestsHelper.SendEventsToHubAsync(
deviceClient,
tsiId,
modelSettings.TimeSeriesIdProperties.ToArray(),
30)
.ConfigureAwait(false);
DateTimeOffset now = Recording.UtcNow;
DateTimeOffset endTime = now.AddMinutes(10);
DateTimeOffset startTime = now.AddMinutes(-10);
var temperatureNumericVariable = new NumericVariable(
new TimeSeriesExpression($"$event.{QueryTestsHelper.Temperature}"),
new TimeSeriesExpression("avg($value)"));
var queryAggregateSeriesRequestOptions = new QueryAggregateSeriesRequestOptions();
queryAggregateSeriesRequestOptions.InlineVariables[QueryTestsHelper.Temperature] = temperatureNumericVariable;
queryAggregateSeriesRequestOptions.ProjectedVariables.Add(QueryTestsHelper.Temperature);
// This retry logic was added as the TSI instance are not immediately available after creation
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer queryAggregateSeriesPages = tsiClient.Queries.CreateAggregateSeriesQueryAnalyzer(
tsiId,
startTime,
endTime,
TimeSpan.FromSeconds(5),
queryAggregateSeriesRequestOptions);
var nonNullFound = false;
await foreach (Page <TimeSeriesPoint> aggregateSeriesPage in queryAggregateSeriesPages.GetResultsAsync().AsPages())
{
foreach (TimeSeriesPoint point in aggregateSeriesPage.Values)
{
point.GetUniquePropertyNames().Should().HaveCount(1).And.Contain((property) => property == QueryTestsHelper.Temperature);
var value = (double?)point.GetValue(QueryTestsHelper.Temperature);
if (value != null)
{
nonNullFound = true;
}
}
nonNullFound.Should().BeTrue();
}
queryAggregateSeriesPages.Progress.Should().Be(100);
return(null);
}, MaxNumberOfRetries, s_retryDelay);
// Add an interpolated variable
var linearInterpolationNumericVariable = new NumericVariable(
new TimeSeriesExpression($"$event.{QueryTestsHelper.Temperature}"),
new TimeSeriesExpression("left($value)"));
linearInterpolationNumericVariable.Interpolation = new InterpolationOperation
{
Kind = InterpolationKind.Linear,
Boundary = new InterpolationBoundary()
{
Span = TimeSpan.FromSeconds(1),
},
};
const string linearInterpolation = "linearInterpolation";
queryAggregateSeriesRequestOptions.InlineVariables[linearInterpolation] = linearInterpolationNumericVariable;
queryAggregateSeriesRequestOptions.ProjectedVariables.Add(linearInterpolation);
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer queryAggregateSeriesPages = tsiClient.Queries.CreateAggregateSeriesQueryAnalyzer(
tsiId,
startTime,
endTime,
TimeSpan.FromSeconds(5),
queryAggregateSeriesRequestOptions);
await foreach (Page <TimeSeriesPoint> aggregateSeriesPage in queryAggregateSeriesPages.GetResultsAsync().AsPages())
{
aggregateSeriesPage.Values.Should().HaveCountGreaterThan(0);
foreach (var point in aggregateSeriesPage.Values)
{
point.GetUniquePropertyNames().Should().HaveCount(2)
.And
.Contain((property) => property == QueryTestsHelper.Temperature)
.And
.Contain((property) => property == linearInterpolation);
}
}
return(null);
}, MaxNumberOfRetries, s_retryDelay);
// Send 2 events with a special condition that can be used later to query on
IDictionary <string, object> messageBase = QueryTestsHelper.BuildMessageBase(modelSettings.TimeSeriesIdProperties.ToArray(), tsiId);
messageBase[QueryTestsHelper.Temperature] = 1.2;
messageBase[QueryTestsHelper.Humidity] = 3.4;
string messageBody = JsonSerializer.Serialize(messageBase);
var message = new Message(Encoding.ASCII.GetBytes(messageBody))
{
ContentType = "application/json",
ContentEncoding = "utf-8",
};
Func <Task> sendEventAct = async() => await deviceClient.SendEventAsync(message).ConfigureAwait(false);
sendEventAct.Should().NotThrow();
// Send it again
sendEventAct.Should().NotThrow();
// Query for the two events with a filter
queryAggregateSeriesRequestOptions.Filter = "$event.Temperature.Double = 1.2";
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer queryAggregateSeriesPages = tsiClient.Queries.CreateAggregateSeriesQueryAnalyzer(
tsiId,
startTime,
endTime,
TimeSpan.FromSeconds(5),
queryAggregateSeriesRequestOptions);
var valueFound = false;
await foreach (TimeSeriesPoint point in queryAggregateSeriesPages.GetResultsAsync())
{
point.GetUniquePropertyNames().Should().HaveCount(2)
.And
.Contain((property) => property == QueryTestsHelper.Temperature)
.And
.Contain((property) => property == linearInterpolation);
var value = (double?)point.GetValue(QueryTestsHelper.Temperature);
if (value == 1.2)
{
valueFound = true;
}
}
valueFound.Should().BeTrue();
return(null);
}, MaxNumberOfRetries, s_retryDelay);
}
finally
{
deviceClient?.Dispose();
}
}
public async Task TimeSeriesInsightsQuery_AggregateSeriesWithCategoricalVariable()
{
// Arrange
TimeSeriesInsightsClient tsiClient = GetClient();
DeviceClient deviceClient = await GetDeviceClient().ConfigureAwait(false);
// Figure out what the Time Series Id is composed of
TimeSeriesModelSettings modelSettings = await tsiClient.ModelSettings.GetAsync().ConfigureAwait(false);
// Create a Time Series Id where the number of keys that make up the Time Series Id is fetched from Model Settings
TimeSeriesId tsiId = await GetUniqueTimeSeriesInstanceIdAsync(tsiClient, modelSettings.TimeSeriesIdProperties.Count)
.ConfigureAwait(false);
try
{
// Send some events to the IoT hub with with a second between each event
await QueryTestsHelper.SendEventsToHubAsync(
deviceClient,
tsiId,
modelSettings.TimeSeriesIdProperties.ToArray(),
30)
.ConfigureAwait(false);
DateTimeOffset now = Recording.UtcNow;
DateTimeOffset endTime = now.AddMinutes(10);
DateTimeOffset startTime = now.AddMinutes(-10);
var queryAggregateSeriesRequestOptions = new QueryAggregateSeriesRequestOptions();
var categoricalVariable = new CategoricalVariable(
new TimeSeriesExpression($"tolong($event.{QueryTestsHelper.Temperature}.Double)"),
new TimeSeriesDefaultCategory("N/A"));
categoricalVariable.Categories.Add(new TimeSeriesAggregateCategory("good", new List <object> {
1
}));
queryAggregateSeriesRequestOptions.InlineVariables["categorical"] = categoricalVariable;
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer queryAggregateSeriesPages = tsiClient.Queries.CreateAggregateSeriesQueryAnalyzer(
tsiId,
startTime,
endTime,
TimeSpan.FromSeconds(5),
queryAggregateSeriesRequestOptions);
await foreach (TimeSeriesPoint point in queryAggregateSeriesPages.GetResultsAsync())
{
point.GetUniquePropertyNames().Should().HaveCount(3)
.And
.Contain((property) => property == "categorical[good]")
.And
.Contain((property) => property == "categorical[N/A]");
}
return(null);
}, MaxNumberOfRetries, s_retryDelay);
}
finally
{
deviceClient?.Dispose();
}
}
public async Task TimeSeriesInsightsQuery_AggregateSeriesWithAggregateVariable()
{
// Arrange
TimeSeriesInsightsClient tsiClient = GetClient();
DeviceClient deviceClient = await GetDeviceClient().ConfigureAwait(false);
// Figure out what the Time Series Id is composed of
TimeSeriesModelSettings modelSettings = await tsiClient.ModelSettings.GetAsync().ConfigureAwait(false);
// Create a Time Series Id where the number of keys that make up the Time Series Id is fetched from Model Settings
TimeSeriesId tsiId = await GetUniqueTimeSeriesInstanceIdAsync(tsiClient, modelSettings.TimeSeriesIdProperties.Count)
.ConfigureAwait(false);
try
{
// Send some events to the IoT hub with with a second between each event
await QueryTestsHelper.SendEventsToHubAsync(
deviceClient,
tsiId,
modelSettings.TimeSeriesIdProperties.ToArray(),
30)
.ConfigureAwait(false);
// Query for temperature events with two calculateions. First with the temperature value as is, and the second
// with the temperature value multiplied by 2.
DateTimeOffset now = Recording.UtcNow;
DateTimeOffset endTime = now.AddMinutes(10);
DateTimeOffset startTime = now.AddMinutes(-10);
var aggregateVariable = new AggregateVariable(
new TimeSeriesExpression("count()"));
var queryAggregateSeriesRequestOptions = new QueryAggregateSeriesRequestOptions();
queryAggregateSeriesRequestOptions.InlineVariables["Count"] = aggregateVariable;
queryAggregateSeriesRequestOptions.ProjectedVariables.Add("Count");
// This retry logic was added as the TSI instance are not immediately available after creation
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer queryAggregateSeriesPages = tsiClient.Queries.CreateAggregateSeriesQueryAnalyzer(
tsiId,
startTime,
endTime,
TimeSpan.FromSeconds(5),
queryAggregateSeriesRequestOptions);
long?totalCount = 0;
await foreach (TimeSeriesPoint point in queryAggregateSeriesPages.GetResultsAsync())
{
var currentCount = (long?)point.GetValue("Count");
totalCount += currentCount;
}
totalCount.Should().Be(30);
return(null);
}, MaxNumberOfRetries, s_retryDelay);
}
finally
{
deviceClient?.Dispose();
}
}
private async Task RunQueryEventsSample(TimeSeriesInsightsQueries queriesClient, TimeSeriesId tsId)
{
#region Snippet:TimeSeriesInsightsSampleQueryEvents
Console.WriteLine("\n\nQuery for raw temperature events over the past 10 minutes.\n");
// Get events from last 10 minute
DateTimeOffset endTime = DateTime.UtcNow;
DateTimeOffset startTime = endTime.AddMinutes(-10);
QueryAnalyzer temperatureEventsQuery = queriesClient.CreateEventsQuery(tsId, startTime, endTime);
await foreach (TimeSeriesPoint point in temperatureEventsQuery.GetResultsAsync())
{
TimeSeriesValue temperatureValue = point.GetValue("Temperature");
// Figure out what is the underlying type for the time series value. Since you know your Time Series Insights
// environment best, you probably do not need this logic and you can skip to directly casting to the proper
// type. This logic demonstrates how you can figure out what type to cast to in the case where you are not
// too familiar with the property type.
if (temperatureValue.Type == typeof(double?))
{
Console.WriteLine($"{point.Timestamp} - Temperature: {point.GetNullableDouble("Temperature")}");
}
else if (temperatureValue.Type == typeof(int?))
{
Console.WriteLine($"{point.Timestamp} - Temperature: {point.GetNullableInt("Temperature")}");
}
else
{
Console.WriteLine("The type of the Time Series value for Temperature is not numeric.");
}
}
#endregion Snippet:TimeSeriesInsightsSampleQueryEvents
// Query for raw events using a time interval
#region Snippet:TimeSeriesInsightsSampleQueryEventsUsingTimeSpan
Console.WriteLine("\n\nQuery for raw humidity events over the past 30 seconds.\n");
QueryAnalyzer humidityEventsQuery = queriesClient.CreateEventsQuery(tsId, TimeSpan.FromSeconds(30));
await foreach (TimeSeriesPoint point in humidityEventsQuery.GetResultsAsync())
{
TimeSeriesValue humidityValue = point.GetValue("Humidity");
// Figure out what is the underlying type for the time series value. Since you know your Time Series Insights
// environment best, you probably do not need this logic and you can skip to directly casting to the proper
// type. This logic demonstrates how you can figure out what type to cast to in the case where you are not
// too familiar with the property type.
if (humidityValue.Type == typeof(double?))
{
Console.WriteLine($"{point.Timestamp} - Humidity: {point.GetNullableDouble("Humidity")}");
}
else if (humidityValue.Type == typeof(int?))
{
Console.WriteLine($"{point.Timestamp} - Humidity: {point.GetNullableInt("Humidity")}");
}
else
{
Console.WriteLine("The type of the Time Series value for Humidity is not numeric.");
}
}
#endregion Snippet:TimeSeriesInsightsSampleQueryEventsUsingTimeSpan
}
private async Task RunQuerySeriesSampleWithPreDefinedVariables(TimeSeriesInsightsClient client, TimeSeriesId tsId)
{
// Setup
TimeSeriesInsightsInstances instancesClient = client.GetInstancesClient();
TimeSeriesInsightsTypes typesClient = client.GetTypesClient();
TimeSeriesInsightsQueries queriesClient = client.GetQueriesClient();
// First create the Time Series type along with the numeric variables
var timeSeriesTypes = new List <TimeSeriesType>();
var celsiusVariable = new NumericVariable(
new TimeSeriesExpression("$event.Temperature"),
new TimeSeriesExpression("avg($value)"));
var fahrenheitVariable = new NumericVariable(
new TimeSeriesExpression("$event.Temperature * 1.8 + 32"),
new TimeSeriesExpression("avg($value)"));
var celsiusVariableName = "TemperatureInCelsius";
var fahrenheitVariableName = "TemperatureInFahrenheit";
var variables = new Dictionary <string, TimeSeriesVariable>
{
{ celsiusVariableName, celsiusVariable },
{ fahrenheitVariableName, fahrenheitVariable }
};
timeSeriesTypes.Add(new TimeSeriesType("TemperatureSensor", variables)
{
Id = "TemperatureSensorTypeId"
});
Response <TimeSeriesTypeOperationResult[]> createTypesResult = await typesClient
.CreateOrReplaceAsync(timeSeriesTypes)
.ConfigureAwait(false);
if (createTypesResult.Value.First().Error != null)
{
Console.WriteLine($"\n\nFailed to create a Time Series Insights type. " +
$"Error Message: '{createTypesResult.Value.First().Error.Message}.' " +
$"Code: '{createTypesResult.Value.First().Error.Code}'.");
}
// Get the Time Series instance and replace its type with the one we just created
Response <InstancesOperationResult[]> getInstanceResult = await instancesClient
.GetAsync(new List <TimeSeriesId> {
tsId
});
if (getInstanceResult.Value.First().Error != null)
{
Console.WriteLine($"\n\nFailed to retrieve Time Series instance with Id '{tsId}'. " +
$"Error Message: '{getInstanceResult.Value.First().Error.Message}.' " +
$"Code: '{getInstanceResult.Value.First().Error.Code}'.");
}
TimeSeriesInstance instanceToReplace = getInstanceResult.Value.First().Instance;
instanceToReplace.TypeId = createTypesResult.Value.First().TimeSeriesType.Id;
Response <InstancesOperationResult[]> replaceInstanceResult = await instancesClient
.ReplaceAsync(new List <TimeSeriesInstance> {
instanceToReplace
});
if (replaceInstanceResult.Value.First().Error != null)
{
Console.WriteLine($"\n\nFailed to retrieve Time Series instance with Id '{tsId}'. " +
$"Error Message: '{replaceInstanceResult.Value.First().Error.Message}.' " +
$"Code: '{replaceInstanceResult.Value.First().Error.Code}'.");
}
// Now that we set up the instance with the property type, query for the data
#region Snippet:TimeSeriesInsightsSampleQuerySeries
Console.WriteLine($"\n\nQuery for temperature series in Celsius and Fahrenheit over the past 10 minutes. " +
$"The Time Series instance belongs to a type that has predefined numeric variable that represents the temperature " +
$"in Celsuis, and a predefined numeric variable that represents the temperature in Fahrenheit.\n");
DateTimeOffset endTime = DateTime.UtcNow;
DateTimeOffset startTime = endTime.AddMinutes(-10);
QueryAnalyzer seriesQuery = queriesClient.CreateSeriesQuery(
tsId,
startTime,
endTime);
await foreach (TimeSeriesPoint point in seriesQuery.GetResultsAsync())
{
double?tempInCelsius = point.GetNullableDouble(celsiusVariableName);
double?tempInFahrenheit = point.GetNullableDouble(fahrenheitVariableName);
Console.WriteLine($"{point.Timestamp} - Average temperature in Celsius: {tempInCelsius}. " +
$"Average temperature in Fahrenheit: {tempInFahrenheit}.");
}
#endregion Snippet:TimeSeriesInsightsSampleQuerySeries
}
public async Task TimeSeriesInsightsQuery_GetSeriesLifecycle()
{
// Arrange
TimeSeriesInsightsClient tsiClient = GetClient();
DeviceClient deviceClient = await GetDeviceClient().ConfigureAwait(false);
// Figure out what the Time Series Id is composed of
TimeSeriesModelSettings modelSettings = await tsiClient.ModelSettings.GetAsync().ConfigureAwait(false);
// Create a Time Series Id where the number of keys that make up the Time Series Id is fetched from Model Settings
TimeSeriesId tsiId = await GetUniqueTimeSeriesInstanceIdAsync(tsiClient, modelSettings.TimeSeriesIdProperties.Count)
.ConfigureAwait(false);
try
{
// Send some events to the IoT hub
await QueryTestsHelper.SendEventsToHubAsync(
deviceClient,
tsiId,
modelSettings.TimeSeriesIdProperties.ToArray(),
10)
.ConfigureAwait(false);
// Act
// Query for temperature events with two calculations. First with the temperature value as is, and the second
// with the temperature value multiplied by 2.
DateTimeOffset now = Recording.UtcNow;
DateTimeOffset endTime = now.AddMinutes(10);
DateTimeOffset startTime = now.AddMinutes(-10);
var temperatureNumericVariable = new NumericVariable(
new TimeSeriesExpression($"$event.{QueryTestsHelper.Temperature}"),
new TimeSeriesExpression("avg($value)"));
var temperatureNumericVariableTimesTwo = new NumericVariable(
new TimeSeriesExpression($"$event.{QueryTestsHelper.Temperature} * 2"),
new TimeSeriesExpression("avg($value)"));
var temperatureTimesTwoVariableName = $"{QueryTestsHelper.Temperature}TimesTwo";
var querySeriesRequestOptions = new QuerySeriesRequestOptions();
querySeriesRequestOptions.InlineVariables[QueryTestsHelper.Temperature] = temperatureNumericVariable;
querySeriesRequestOptions.InlineVariables[temperatureTimesTwoVariableName] = temperatureNumericVariableTimesTwo;
// This retry logic was added as the TSI instance are not immediately available after creation
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer querySeriesEventsPages = tsiClient.Queries.CreateSeriesQueryAnalyzer(
tsiId,
startTime,
endTime,
querySeriesRequestOptions);
await foreach (Page <TimeSeriesPoint> seriesEventsPage in querySeriesEventsPages.GetResultsAsync().AsPages())
{
seriesEventsPage.Values.Should().HaveCount(10);
for (int index = 0; index < seriesEventsPage.Values.Count; index++)
{
TimeSeriesPoint point = seriesEventsPage.Values[index];
point.Timestamp.Should().BeAfter(startTime).And.BeBefore(endTime);
point.GetUniquePropertyNames().Should().HaveCount(3);
point.GetUniquePropertyNames().Should().Contain((property) => property == QueryTestsHelper.Temperature)
.And
.Contain((property) => property == temperatureTimesTwoVariableName);
// Assert that the values for the Temperature property is equal to the values for the other property, multiplied by 2
var temperatureTimesTwoValue = (double?)point.GetValue(temperatureTimesTwoVariableName);
var temperatureValue = (double?)point.GetValue(QueryTestsHelper.Temperature);
temperatureTimesTwoValue.Should().Be(temperatureValue * 2);
}
}
return(null);
}, MaxNumberOfRetries, s_retryDelay);
// Query for all the series events using a timespan
QueryAnalyzer querySeriesEventsPagesWithTimespan = tsiClient
.Queries
.CreateSeriesQueryAnalyzer(tsiId, TimeSpan.FromMinutes(10), null, querySeriesRequestOptions);
await foreach (Page <TimeSeriesPoint> seriesEventsPage in querySeriesEventsPagesWithTimespan.GetResultsAsync().AsPages())
{
seriesEventsPage.Values.Should().HaveCount(10);
foreach (TimeSeriesPoint point in seriesEventsPage.Values)
{
point.GetUniquePropertyNames().Should().HaveCount(3);
}
}
// Query for temperature and humidity
var humidityNumericVariable = new NumericVariable(
new TimeSeriesExpression("$event.Humidity"),
new TimeSeriesExpression("avg($value)"));
querySeriesRequestOptions.InlineVariables[QueryTestsHelper.Humidity] = humidityNumericVariable;
querySeriesRequestOptions.ProjectedVariables.Add(QueryTestsHelper.Temperature);
querySeriesRequestOptions.ProjectedVariables.Add(QueryTestsHelper.Humidity);
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer querySeriesEventsPages = tsiClient.Queries.CreateSeriesQueryAnalyzer(tsiId, startTime, endTime, querySeriesRequestOptions);
await foreach (Page <TimeSeriesPoint> seriesEventsPage in querySeriesEventsPages.GetResultsAsync().AsPages())
{
seriesEventsPage.Values.Should().HaveCount(10);
foreach (TimeSeriesPoint point in seriesEventsPage.Values)
{
point.Timestamp.Should().BeAfter(startTime).And.BeBefore(endTime);
point.GetUniquePropertyNames().Should().HaveCount(2)
.And
.Contain((property) => property == QueryTestsHelper.Temperature)
.And
.Contain((property) => property == QueryTestsHelper.Humidity);
}
}
return(null);
}, MaxNumberOfRetries, s_retryDelay);
// Send 2 events with a special condition that can be used later to query on
IDictionary <string, object> messageBase = QueryTestsHelper.BuildMessageBase(modelSettings.TimeSeriesIdProperties.ToArray(), tsiId);
messageBase[QueryTestsHelper.Temperature] = 1.2;
messageBase[QueryTestsHelper.Humidity] = 3.4;
string messageBody = JsonSerializer.Serialize(messageBase);
var message = new Message(Encoding.ASCII.GetBytes(messageBody))
{
ContentType = "application/json",
ContentEncoding = "utf-8",
};
Func <Task> sendEventAct = async() => await deviceClient.SendEventAsync(message).ConfigureAwait(false);
sendEventAct.Should().NotThrow();
// Send it again
sendEventAct.Should().NotThrow();
// Query for the two events with a filter
querySeriesRequestOptions.Filter = "$event.Temperature.Double = 1.2";
await TestRetryHelper.RetryAsync <AsyncPageable <QueryResultPage> >(async() =>
{
QueryAnalyzer querySeriesEventsPages = tsiClient.Queries.CreateSeriesQueryAnalyzer(tsiId, startTime, endTime, querySeriesRequestOptions);
await foreach (Page <TimeSeriesPoint> seriesEventsPage in querySeriesEventsPages.GetResultsAsync().AsPages())
{
seriesEventsPage.Values.Should().HaveCount(2);
foreach (TimeSeriesPoint point in seriesEventsPage.Values)
{
point.GetUniquePropertyNames().Should().HaveCount(2);
var temperatureValue = (double?)point.GetValue(QueryTestsHelper.Temperature);
temperatureValue.Should().Be(1.2);
}
}
return(null);
}, MaxNumberOfRetries, s_retryDelay);
// Query for the two events with a filter, but only take 1
querySeriesRequestOptions.MaximumNumberOfEvents = 1;
QueryAnalyzer querySeriesEventsPagesWithFilter = tsiClient.Queries.CreateSeriesQueryAnalyzer(tsiId, startTime, endTime, querySeriesRequestOptions);
await foreach (Page <TimeSeriesPoint> seriesEventsPage in querySeriesEventsPagesWithFilter.GetResultsAsync().AsPages())
{
seriesEventsPage.Values.Should().HaveCount(1);
}
}
finally
{
deviceClient?.Dispose();
}
}
public async Task TimeSeriesInsightsQuery_GetEventsLifecycle()
{
// Arrange
TimeSeriesInsightsClient tsiClient = GetClient();
TimeSeriesInsightsModelSettings timeSeriesModelSettings = tsiClient.GetModelSettingsClient();
TimeSeriesInsightsInstances instancesClient = tsiClient.GetInstancesClient();
TimeSeriesInsightsQueries queriesClient = tsiClient.GetQueriesClient();
DeviceClient deviceClient = await GetDeviceClient().ConfigureAwait(false);
// Figure out what the Time Series Id is composed of
TimeSeriesModelSettings modelSettings = await timeSeriesModelSettings.GetAsync().ConfigureAwait(false);
// Create a Time Series Id where the number of keys that make up the Time Series Id is fetched from Model Settings
TimeSeriesId tsiId = await GetUniqueTimeSeriesInstanceIdAsync(instancesClient, modelSettings.TimeSeriesIdProperties.Count)
.ConfigureAwait(false);
try
{
var initialEventsCount = 50;
// Send some events to the IoT hub
await QueryTestsHelper.SendEventsToHubAsync(
deviceClient,
tsiId,
modelSettings.TimeSeriesIdProperties.ToArray(),
initialEventsCount)
.ConfigureAwait(false);
// Act
// Get events from last 10 minute
DateTimeOffset now = Recording.UtcNow;
DateTimeOffset endTime = now.AddMinutes(10);
DateTimeOffset startTime = now.AddMinutes(-10);
// This retry logic was added as the TSI instance are not immediately available after creation
await TestRetryHelper.RetryAsync <AsyncPageable <TimeSeriesPoint> >(async() =>
{
QueryAnalyzer queryEventsPages = queriesClient.CreateEventsQuery(tsiId, startTime, endTime);
var count = 0;
await foreach (TimeSeriesPoint timeSeriesPoint in queryEventsPages.GetResultsAsync())
{
count++;
timeSeriesPoint.Timestamp.Should().BeAfter(startTime).And.BeBefore(endTime);
var temperatureValue = timeSeriesPoint.GetNullableDouble(QueryTestsHelper.Temperature);
temperatureValue.Should().NotBeNull();
var humidityValue = (double?)timeSeriesPoint.GetValue(QueryTestsHelper.Humidity);
humidityValue.Should().NotBeNull();
}
count.Should().Be(initialEventsCount);
return(null);
}, MaxNumberOfRetries, s_retryDelay);
// Send 2 events with a special condition that can be used later to query on
IDictionary <string, object> messageBase = QueryTestsHelper.BuildMessageBase(
modelSettings.TimeSeriesIdProperties.ToArray(),
tsiId);
messageBase[QueryTestsHelper.Temperature] = 1.2;
messageBase[QueryTestsHelper.Humidity] = 3.4;
string messageBody = JsonSerializer.Serialize(messageBase);
var message = new Message(Encoding.ASCII.GetBytes(messageBody))
{
ContentType = "application/json",
ContentEncoding = "utf-8",
};
Func <Task> sendEventAct = async() => await deviceClient.SendEventAsync(message).ConfigureAwait(false);
await sendEventAct.Should().NotThrowAsync();
// Send it again
sendEventAct.Should().NotThrow();
// Query for the two events with a filter
// Only project Temperature and one of the Id properties
var queryRequestOptions = new QueryEventsRequestOptions
{
Filter = new TimeSeriesExpression("$event.Temperature.Double = 1.2"),
Store = StoreType.WarmStore,
};
queryRequestOptions.ProjectedProperties.Add(
new TimeSeriesInsightsEventProperty
{
Name = QueryTestsHelper.Temperature,
PropertyValueType = "Double",
});
queryRequestOptions.ProjectedProperties.Add(
new TimeSeriesInsightsEventProperty
{
Name = modelSettings.TimeSeriesIdProperties.First().Name,
PropertyValueType = modelSettings.TimeSeriesIdProperties.First().Type.ToString(),
});
await TestRetryHelper.RetryAsync <AsyncPageable <TimeSeriesPoint> >(async() =>
{
QueryAnalyzer queryEventsPages = queriesClient.CreateEventsQuery(tsiId, startTime, endTime, queryRequestOptions);
await foreach (Page <TimeSeriesPoint> page in queryEventsPages.GetResultsAsync().AsPages())
{
page.Values.Should().HaveCount(2);
foreach (TimeSeriesPoint point in page.Values)
{
var value = (double?)point.GetValue(QueryTestsHelper.Temperature);
value.Should().Be(1.2);
}
}
return(null);
}, MaxNumberOfRetries, s_retryDelay);
// Query for the two events with a filter, but only take 1
queryRequestOptions.MaxNumberOfEvents = 1;
QueryAnalyzer queryEventsPagesWithFilter = queriesClient.CreateEventsQuery(tsiId, startTime, endTime, queryRequestOptions);
await foreach (Page <TimeSeriesPoint> page in queryEventsPagesWithFilter.GetResultsAsync().AsPages())
{
page.Values.Should().HaveCount(1);
}
await TestRetryHelper.RetryAsync <AsyncPageable <TimeSeriesPoint> >(async() =>
{
// Query for all the events using a timespan
QueryAnalyzer queryEventsPagesWithTimespan = queriesClient
.CreateEventsQuery(tsiId, TimeSpan.FromMinutes(20), endTime);
await foreach (Page <TimeSeriesPoint> page in queryEventsPagesWithTimespan.GetResultsAsync().AsPages())
{
page.Values.Should().HaveCount(52);
foreach (TimeSeriesPoint point in page.Values)
{
point.Timestamp.Should().BeAfter(startTime).And.BeBefore(endTime);
var temperatureValue = (double?)point.GetValue(QueryTestsHelper.Temperature);
temperatureValue.Should().NotBeNull();
var humidityValue = (double?)point.GetValue(QueryTestsHelper.Humidity);
humidityValue.Should().NotBeNull();
}
}
return(null);
}, MaxNumberOfRetries, s_retryDelay);
}
finally
{
deviceClient?.Dispose();
}
}
protected override void Run()
{
#if DEBUG
//System.Diagnostics.Debugger.Launch();
#endif
string TableName = QlArgsUnescape(DSpace_ExecArgs[0]); // nul-delimited.
string DfsOutputName = DSpace_ExecArgs[1];
string QlArgsSelectWhat = DSpace_ExecArgs[2];
long TopCount = long.Parse(DSpace_ExecArgs[3]);
string QlArgsOps = DSpace_ExecArgs[4]; // Still encoded with QlArgsEscape.
string sOptions = DSpace_ExecArgs[5]; // "DFSTEMP" or "-", etc
bool dfstemp = -1 != sOptions.IndexOf("DFSTEMP");
bool Update = -1 != sOptions.IndexOf("GUPDATE"); // UPDATE (grouped MR).
bool GroupBy = -1 != sOptions.IndexOf("GBY");
bool OrderBy = -1 != sOptions.IndexOf("OBY");
bool Order2By = -1 != sOptions.IndexOf("O2BY");
bool dfsref = -1 != sOptions.IndexOf("DFSREF");
bool queryresults = dfstemp || dfsref;
bool topdfstemp = -1 != sOptions.IndexOf("TOPTEMP");
bool distinct = -1 != sOptions.IndexOf("DISTINCT");
bool joined = -1 != sOptions.IndexOf("JOINED");
#if DEBUG
if (TopCount == 4242)
{
System.Diagnostics.Debugger.Launch();
}
#endif
string SelectWhat = QlArgsUnescape(QlArgsSelectWhat);
bool WhatFunctions = -1 != SelectWhat.IndexOf('('); // Select clause has functions (aggregates and/or scalars).
if (Order2By)
{
OrderBy = true;
WhatFunctions = false;
#if DEBUG
if (GroupBy)
{
throw new Exception("DEBUG: Not supposed to find GroupBy here with Order2By");
}
#endif
#if DEBUG
//System.Diagnostics.Debugger.Launch();
#endif
}
bool HasKeyOn = OrderBy || GroupBy;
if (OrderBy)
{
if (WhatFunctions || GroupBy)
{
if ("*" == SelectWhat)
{
throw new Exception("Invalid query: cannot SELECT * with ORDER BY and GROUP BY");
}
string sOrderByCols = null; // null, or "foo,bar" from "ORDER BY foo,bar".
List<string> OrderByCols = new List<string>();
string[] Ops = QlArgsUnescape(QlArgsOps).Split('\0');
List<string> NewOps = new List<string>(Ops.Length);
for (int iop = 0; iop < Ops.Length; iop++)
{
if (0 == string.Compare("ORDER", Ops[iop], true))
{
if (iop + 1 < Ops.Length
&& 0 == string.Compare("BY", Ops[iop + 1], true))
{
iop++;
int nparens = 0;
StringBuilder sob = new StringBuilder();
StringBuilder curobcol = new StringBuilder();
for (; ; )
{
iop++;
if (iop >= Ops.Length)
{
break;
}
sob.Append(Ops[iop]);
curobcol.Append(Ops[iop]);
if ("(" == Ops[iop])
{
nparens++;
}
else if (nparens > 0)
{
if (")" == Ops[iop])
{
nparens--;
}
}
iop++;
if (iop >= Ops.Length)
{
if (0 != nparens)
{
throw new Exception("Expected ) in ORDER BY");
}
if (curobcol.Length != 0)
{
OrderByCols.Add(curobcol.ToString());
curobcol.Length = 0;
}
break;
}
if (0 == nparens)
{
if (curobcol.Length != 0)
{
OrderByCols.Add(curobcol.ToString());
curobcol.Length = 0;
}
if ("," != Ops[iop])
{
break;
}
}
else
{
curobcol.Append(Ops[iop]);
}
sob.Append(Ops[iop]);
}
sOrderByCols = sob.ToString();
if (iop >= Ops.Length)
{
break;
}
}
}
NewOps.Add(Ops[iop]);
}
QlArgsOps = QlArgsEscape(string.Join("\0", NewOps.ToArray()));
#if DEBUG
//System.Diagnostics.Debugger.Launch();
#endif
QueryAnalyzer qa1 = new QueryAnalyzer();
string sel1 = qa1.Exec(
"SELECT DFSTEMP", QlArgsEscape(sOrderByCols + "," + SelectWhat.Replace('\0', ',')),
"FROM", QlArgsEscape(TableName),
QlArgsEscape(string.Join(" ", NewOps.ToArray()))
);
{
if (-1 != TableName.IndexOf(Qa.DFS_TEMP_FILE_MARKER))
{
try
{
System.Xml.XmlElement tt = GetDfsTableInfo(TableName);
string ttf = tt["file"].InnerText;
if (-1 != ttf.IndexOf(Qa.DFS_TEMP_FILE_MARKER))
{
dfsclient.DeleteFile(ttf);
}
}
catch
{
}
}
}
Qa.PrepareSelect.queryresults qr = Qa.PrepareSelect.GetQueryResults(sel1);
for (int iobc = 0; iobc < OrderByCols.Count; iobc++)
{
qr.fields[iobc].name = "~OBY.~" + qr.fields[iobc].name;
}
try
{
string newopts = "";
if (dfstemp)
{
newopts += ";DFSTEMP";
}
if (dfsref)
{
newopts += ";DFSREF";
}
if (topdfstemp)
{
newopts += ";TOPTEMP";
}
if (distinct)
{
newopts += ";DISTINCT";
}
PrepareSelect ps2 = new PrepareSelect();
string neworderby;
{
StringBuilder sbnob = new StringBuilder();
//"ORDER\0BY\0" + sOrderByCols.Replace(',', '\0')
sbnob.Append("ORDER\0BY");
bool firstsbnob = true;
for (int iobc = 0; iobc < OrderByCols.Count; iobc++)
{
if (!firstsbnob)
{
sbnob.Append("\0,");
}
firstsbnob = false;
sbnob.Append('\0');
sbnob.Append(qr.fields[iobc].name); // Includes "~OBY.~"
}
neworderby = sbnob.ToString();
}
#if DEBUG
//System.Diagnostics.Debugger.Launch();
#endif
newopts += ";O2BY"; // Order-by phase 2.
// [QlArgs]SelectWhat might have () but they are NOT evaluated at this point (no SFUNC)
string sel2 = ps2.Exec(
Qa.QlArgsEscape(qr.GetPseudoTableName()),
DfsOutputName,
//(OrderByCols.Split(',').Length + 2).ToString() + "-*", // Select N-* where N is after OrderByCols
QlArgsSelectWhat,
TopCount.ToString(),
QlArgsEscape(neworderby),
newopts
);
DSpace_Log(sel2);
}
finally
{
if (qr.IsTempTable)
{
{
string delfile = qr.temptable;
{
int iat = delfile.IndexOf('@');
if (-1 != iat)
{
delfile = delfile.Substring(0, iat);
}
}
dfsclient.DeleteFile(delfile);
}
}
}
return;
}
}
string[] awhat = null;
string[] UserColumnNames = null;
if ("*" != SelectWhat && "^" != SelectWhat)
{
awhat = SelectWhat.Split('\0');
UserColumnNames = new string[awhat.Length];
for (int iww = 0; iww < awhat.Length; iww++)
{
int ilas = awhat[iww].LastIndexOf(" AS ", StringComparison.OrdinalIgnoreCase);
if (-1 == ilas)
{
UserColumnNames[iww] = awhat[iww];
}
else
{
string asname = awhat[iww].Substring(ilas + 4);
for (int iy = 0; iy < asname.Length; iy++)
{
if (!char.IsLetterOrDigit(asname[iy])
&& '_' != asname[iy])
{
asname = null;
break;
}
}
if (!string.IsNullOrEmpty(asname) && !char.IsDigit(asname[0]))
{
UserColumnNames[iww] = asname;
awhat[iww] = awhat[iww].Substring(0, ilas);
}
else
{
UserColumnNames[iww] = awhat[iww];
}
}
}
}
#if DEBUG
if (Update && !dfstemp)
{
throw new Exception("DEBUG: (Update && !dfstemp)");
}
#endif
System.Xml.XmlDocument systables;
using (GlobalCriticalSection.GetLock())
{
systables = LoadSysTables_unlocked();
}
System.Xml.XmlElement xeTable;
string DfsTableFilesInput; // Note: can be multiple semicolon-separated input files. Includes record length info (@N).
string sRowSize;
int RowSize;
if (-1 != TableName.IndexOf('\0'))
{
string[] tables = TableName.Split('\0');
string tn = tables[0];
xeTable = FindTable(systables, tn);
if (null == xeTable)
{
throw new Exception("Table '" + tn + "' does not exist");
}
sRowSize = xeTable["size"].InnerText;
RowSize = int.Parse(sRowSize);
DfsTableFilesInput = xeTable["file"].InnerText + "@" + sRowSize;
System.Xml.XmlNodeList xnlTableCols = xeTable.SelectNodes("column");
int numcols = xnlTableCols.Count;
for (int tni = 1; tni < tables.Length; tni++)
{
tn = tables[tni];
System.Xml.XmlElement xett = FindTable(systables, tn);
if (null == xett)
{
throw new Exception("Table '" + tn + "' does not exist");
}
System.Xml.XmlNodeList xnltcols = xett.SelectNodes("column");
bool colsmatch = false;
if (xnltcols.Count == numcols)
{
bool fail = false;
for (int ic = 0; ic < numcols; ic++)
{
if (xnlTableCols[ic]["type"].InnerText != xnltcols[ic]["type"].InnerText)
{
fail = true;
break;
}
if (0 != string.Compare(xnlTableCols[ic]["name"].InnerText, xnltcols[ic]["name"].InnerText, true))
{
fail = true;
break;
}
}
colsmatch = !fail;
}
if (!colsmatch)
{
throw new Exception("Columns of table " + tn + " do not match columns of table " + xeTable["name"].InnerText);
}
DfsTableFilesInput += ";" + xett["file"].InnerText + "@" + sRowSize;
}
if (-1 != DfsTableFilesInput.IndexOf("qa://", StringComparison.OrdinalIgnoreCase))
{
throw new Exception("Cannot union with a system table"); /////////
}
}
else
{
xeTable = FindTable(systables, TableName);
if (null == xeTable)
{
throw new Exception("Table '" + TableName + "' does not exist");
}
sRowSize = xeTable["size"].InnerText;
RowSize = int.Parse(sRowSize);
DfsTableFilesInput = xeTable["file"].InnerText + "@" + sRowSize;
}
if (dfsref)
{
DfsOutputName = xeTable["file"].InnerText;
}
if (queryresults)
{
DSpace_Log("<?xml version=\"1.0\"?>");
DSpace_Log(" <queryresults>");
if (dfsref)
{
DSpace_Log(" <reftable>" + DfsOutputName + "</reftable>");
}
else
{
DSpace_Log(" <temptable>" + DfsOutputName + "</temptable>");
}
}
string RowInfo;
string DisplayInfo; // Display
cols = new List<DbColumn>();
colswidths = new List<string>();
{
StringBuilder sbRowInfo = new StringBuilder();
StringBuilder sbDisplayInfo = new StringBuilder(); // Display
int totsize = 0;
string xtablename = xeTable["name"].InnerText;
foreach (System.Xml.XmlNode xn in xeTable.SelectNodes("column"))
{
if (0 != sbRowInfo.Length)
{
sbRowInfo.Append('\0');
sbDisplayInfo.Append(','); // Display
}
string stsize = xn["bytes"].InnerText;
int tsize = int.Parse(stsize);
string RealColName = xn["name"].InnerText;
string UserColName = RealColName;
if (null != awhat)
{
for (int iww = 0; iww < awhat.Length; iww++)
{
if (0 == string.Compare(awhat[iww], RealColName, true))
{
UserColName = UserColumnNames[iww];
break;
}
}
}
string xcolname;
if (-1 == UserColName.IndexOf('.'))
{
xcolname = xtablename + "." + UserColName;
}
else
{
xcolname = UserColName;
}
sbRowInfo.Append(xcolname); // Note: doesn't consider sub-select.
sbRowInfo.Append('=');
sbRowInfo.Append(stsize);
sbDisplayInfo.Append(xn["type"].InnerText); // Display
sbDisplayInfo.Append('='); // Display
sbDisplayInfo.Append(xn["dw"].InnerText); // Display
colswidths.Add(xn["dw"].InnerText);
{
DbColumn c;
c.Type = DbType.Prepare(xn["type"].InnerText, tsize);
c.RowOffset = totsize;
c.ColumnName = xcolname;
cols.Add(c);
}
totsize += tsize;
}
RowInfo = sbRowInfo.ToString();
DisplayInfo = sbDisplayInfo.ToString(); // Display
}
int KeyLength;
if (HasKeyOn)
{
KeyLength = RowSize;
}
else
{
KeyLength = 4;
}
bool IsSpecialOrdered = -1 != sOptions.IndexOf("SPECIALORDER");
string DfsInput;
string DfsTempInputFile = null;
// FIXME: look for all qa:// and run them through separately,...
if (DfsTableFilesInput.StartsWith("qa://", true, null))
{
if (dfsref)
{
throw new Exception("Cannot DFSREF with non-user table");
}
string SysGenOutputFile;
if (IsSpecialOrdered)
{
SysGenOutputFile = DfsOutputName + "@" + sRowSize;
DfsInput = null;
}
else
{
SysGenOutputFile = "dfs://RDBMS_QaTemp_" + Guid.NewGuid().ToString() + "@" + sRowSize;
DfsTempInputFile = SysGenOutputFile;
DfsInput = DfsTempInputFile;
}
{
string qafile = DfsTableFilesInput;
int iat = qafile.IndexOf('@');
if (-1 != iat)
{
qafile = qafile.Substring(0, iat);
}
{
RemoteCall rc = GetRemoteCallSysGen();
//rc.OverrideInput = #;
rc.OverrideOutput = SysGenOutputFile;
//rc.OverrideKeyLength = #;
rc.Call("\"" + qafile + "\" \"" + Qa.QlArgsEscape(RowInfo) + "\" \"" + DisplayInfo + "\" \"" + Qa.QlArgsEscape(TableName) + "\"").Trim();
}
}
}
else
{
DfsInput = DfsTableFilesInput;
}
#if DEBUG
if (null != DfsInput)
{
if (-1 == DfsInput.IndexOf('@'))
{
throw new Exception("Expected @ in DfsInput");
}
}
#endif
string OutputRowInfo;
string OutputDisplayInfo;
long OutputRowSize;
string OutputsRowSize;
List<string> outputcolswidths;
List<DbColumn> outputcols;
if (null != awhat)
{
outputcols = new List<DbColumn>(awhat.Length);
outputcolswidths = new List<string>();
StringBuilder sbRowInfo = new StringBuilder();
StringBuilder sbDisplayInfo = new StringBuilder();
long xRowSize = 0;
for (int iww = 0; iww < UserColumnNames.Length; iww++)
{
string w = UserColumnNames[iww];
string sdw; // String display width.
DbColumn c = GetDbColumn(w, out sdw);
if (c.Type.Size == 0)
{
throw new Exception("No such column named " + w);
}
if (0 != sbRowInfo.Length)
{
sbRowInfo.Append('\0');
sbDisplayInfo.Append(',');
}
{
outputcols.Add(c);
}
//sbRowInfo.Append(c.ColumnName); // Already includes "TableName."
sbRowInfo.Append(UserColumnNames[iww]);
sbRowInfo.Append('=');
sbRowInfo.Append(c.Type.Size);
sbDisplayInfo.Append(c.Type.Name);
sbDisplayInfo.Append('=');
sbDisplayInfo.Append(sdw);
xRowSize += c.Type.Size;
outputcolswidths.Add(sdw);
}
OutputRowInfo = sbRowInfo.ToString();
OutputDisplayInfo = sbDisplayInfo.ToString();
OutputRowSize = xRowSize;
OutputsRowSize = xRowSize.ToString();
}
else
{
// Same values!
outputcols = new List<DbColumn>(cols);
OutputRowInfo = RowInfo;
OutputDisplayInfo = DisplayInfo;
OutputRowSize = RowSize;
OutputsRowSize = sRowSize;
{
outputcolswidths = new List<string>(colswidths.Count);
for (int icw = 0; icw < colswidths.Count; icw++)
{
outputcolswidths.Add(colswidths[icw]);
}
}
}
string QlArgsNewSelectWhat = QlArgsSelectWhat;
if (null != UserColumnNames)
{
QlArgsNewSelectWhat = QlArgsEscape(string.Join("\0", UserColumnNames));
}
string shelloutputSelect1 = "";
if (!dfsref) // Important! Don't run Select DBCORE if DFSREF.
{
if (!IsSpecialOrdered)
{
if (WhatFunctions)
{
string FuncDfsInput = DfsInput;
string FuncDfsOutput = "dfs://RDBMS_SelectFunc_" + Guid.NewGuid().ToString();
FuncDfsOutput = DfsOutputName; // For now...
string FuncArgsOptions = "SFUNC"; // Select clause functions (aggregates and/or scalars).
if (GroupBy)
{
FuncArgsOptions += ";GBY";
}
string FuncSelectOutput1;
{
MapReduceCall mrc = GetMapReduceCallSelect(FuncDfsInput);
mrc.OverrideOutputMethod = "grouped";
mrc.OverrideInput = FuncDfsInput;
mrc.OverrideOutput = FuncDfsOutput + "@" + OutputsRowSize;
mrc.OverrideKeyLength = KeyLength;
FuncSelectOutput1 = mrc.Call("\"" + TableName + "\" \"" + DfsOutputName + "\" \"" + QlArgsNewSelectWhat + "\" " + TopCount.ToString() + " \"" + QlArgsOps + "\" \"" + Qa.QlArgsEscape(RowInfo) + "\" \"" + DisplayInfo + "\" \"" + Qa.QlArgsEscape(OutputRowInfo) + "\" " + FuncArgsOptions).Trim();
}
string[] FuncOutputTypeNames;
{
const string AOTIBEGINSTRING = "BEGIN:{AC596AA3-8E2F-41fa-B9E1-601D92F08AEC}";
int aotiBegin = FuncSelectOutput1.IndexOf(AOTIBEGINSTRING);
if (-1 == aotiBegin)
{
string et = "Function (aggregate and/or scalar) Select MR output invalid (expected begin output type information)";
//#if DEBUG
et += "\r\nMR output:\r\n" + FuncSelectOutput1 + "\r\n";
//#endif
throw new Exception(et);
}
int aotiEnd = FuncSelectOutput1.IndexOf("{AC596AA3-8E2F-41fa-B9E1-601D92F08AEC}:END");
if (aotiEnd < aotiBegin)
{
throw new Exception("Function (aggregate and/or scalar) Select MR output invalid (expected end output type information)");
}
{
string stypes = FuncSelectOutput1.Substring(aotiBegin + AOTIBEGINSTRING.Length, aotiEnd - aotiBegin - AOTIBEGINSTRING.Length);
FuncOutputTypeNames = System.Text.RegularExpressions.Regex.Split(stypes, @"\{264E73F6-E3C9-43de-A3FD-9AC36F905087\}");
}
}
if (FuncOutputTypeNames.Length != outputcolswidths.Count)
{
throw new Exception("DEBUG: (FuncOutputTypeNames.Length != outputcolswidths.Count)");
}
{
StringBuilder sbOutputDisplayInfo = new StringBuilder();
for (int icw = 0; icw < FuncOutputTypeNames.Length; icw++)
{
if (icw > 0)
{
sbOutputDisplayInfo.Append(',');
}
sbOutputDisplayInfo.Append(FuncOutputTypeNames[icw]);
sbOutputDisplayInfo.Append('=');
sbOutputDisplayInfo.Append(outputcolswidths[icw]);
}
OutputDisplayInfo = sbOutputDisplayInfo.ToString();
}
if (FuncOutputTypeNames.Length != outputcols.Count)
{
throw new Exception("DEBUG: (FuncOutputTypeNames.Length != outputcols.Count)");
}
{
// Fix output type, since I didn't know until the output was generated.
for (int oic = 0; oic < FuncOutputTypeNames.Length; oic++)
{
DbColumn c = outputcols[oic];
c.Type = DbType.Prepare(FuncOutputTypeNames[oic], c.Type.Size);
outputcols[oic] = c;
}
}
DfsInput = FuncDfsOutput + "@" + OutputsRowSize;
{
RowInfo = OutputRowInfo;
DisplayInfo = OutputDisplayInfo;
QlArgsNewSelectWhat = "*";
}
}
else if (GroupBy)
{
string GByDfsInput = DfsInput;
string GByDfsOutput = "dfs://RDBMS_SelectGroupBy_" + Guid.NewGuid().ToString();
GByDfsOutput = DfsOutputName; // For now...
string GByArgsOptions = "GBY";
{
MapReduceCall mrc = GetMapReduceCallSelect(GByDfsInput);
mrc.OverrideOutputMethod = "grouped";
mrc.OverrideInput = GByDfsInput;
mrc.OverrideOutput = GByDfsOutput + "@" + OutputsRowSize;
mrc.OverrideKeyLength = KeyLength;
if (RDBMS_DBCORE.Qa.FaultTolerantExecution)
{
mrc.OverrideFaultTolerantExecutionMode = "enabled";
}
mrc.Call("\"" + TableName + "\" \"" + DfsOutputName + "\" \"" + QlArgsNewSelectWhat + "\" " + TopCount.ToString() + " \"" + QlArgsOps + "\" \"" + Qa.QlArgsEscape(RowInfo) + "\" \"" + DisplayInfo + "\" \"" + Qa.QlArgsEscape(OutputRowInfo) + "\" " + GByArgsOptions).Trim();
}
DfsInput = GByDfsOutput + "@" + OutputsRowSize;
{
RowInfo = OutputRowInfo;
DisplayInfo = OutputDisplayInfo;
QlArgsNewSelectWhat = "*";
}
}
else
{
{
MapReduceCall mrc = GetMapReduceCallSelect(DfsInput);
if (!OrderBy || Update)
{
mrc.OverrideOutputMethod = "grouped";
if (RDBMS_DBCORE.Qa.FaultTolerantExecution)
{
mrc.OverrideFaultTolerantExecutionMode = "enabled";
}
}
mrc.OverrideInput = DfsInput;
mrc.OverrideOutput = DfsOutputName + "@" + OutputsRowSize;
mrc.OverrideKeyLength = KeyLength;
shelloutputSelect1 = mrc.Call("\"" + TableName + "\" \"" + DfsOutputName + "\" \"" + QlArgsNewSelectWhat + "\" " + TopCount.ToString() + " \"" + QlArgsOps + "\" \"" + Qa.QlArgsEscape(RowInfo) + "\" \"" + DisplayInfo + "\" \"" + Qa.QlArgsEscape(OutputRowInfo) + "\"").Trim();
}
}
}
else
{
if (null != awhat)
{
// This case shouldn't happen anyways.. becuase if custom columns, it's not a even special order command anymore.
throw new Exception("Special order commands must select all columns: SELECT * FROM " + TableName + " ...");
}
}
}
if (null != DfsTempInputFile)
{
{
string delfile = DfsTempInputFile;
{
int iat = delfile.IndexOf('@');
if (-1 != iat)
{
delfile = delfile.Substring(0, iat);
}
}
dfsclient.DeleteFile(delfile);
}
}
if (distinct)
{
string outtablefn = DfsOutputName + "_out_" + Guid.NewGuid().ToString();
{
MapReduceCall mrc = GetMapReduceCallDistinct();
//mrc.OverrideOutputMethod = #;
mrc.OverrideInput = DfsOutputName + "@" + OutputsRowSize;
mrc.OverrideOutput = outtablefn + "@" + OutputsRowSize;
mrc.OverrideKeyLength = int.Parse(OutputsRowSize);
mrc.Call().Trim();
}
Shell("dspace swap \"" + outtablefn + "\" \"" + DfsOutputName + "\"");
dfsclient.DeleteFile(outtablefn);
}
if (queryresults)
{
{
int dtfieldindex = 0;
foreach (DbColumn c in outputcols)
{
LogFieldInfo(dtfieldindex++, c, !joined);
}
}
string sDfsOutputSize = dfsclient.GetFileSizeString(DfsOutputName);
long DfsOutputSize = long.Parse(sDfsOutputSize);
long NumRowsOutput = DfsOutputSize / OutputRowSize;
if (0 != (DfsOutputSize % OutputRowSize))
{
throw new Exception("Output file size miscalculation (DfsOutputSize{" + DfsOutputSize + "} % OutputRowSize{" + OutputRowSize + "}) for file: " + DfsOutputName);
}
long recordcount = NumRowsOutput;
if (TopCount >= 0)
{
if (recordcount > TopCount)
{
recordcount = TopCount;
if (topdfstemp)
{
string outtablefn = DfsOutputName + "_out_" + Guid.NewGuid().ToString();
{
RemoteCall rc = GetRemoteCallWriteTop();
rc.OverrideInput = DfsOutputName + "@" + OutputsRowSize;
rc.OverrideOutput = outtablefn + "@" + OutputsRowSize;
rc.Call(TopCount.ToString()).Trim();
}
Shell("dspace swap \"" + DfsOutputName + "\" \"" + outtablefn + "\"");
dfsclient.DeleteFile(outtablefn);
}
}
}
DSpace_Log(" <recordcount>" + recordcount.ToString() + "</recordcount>");
DSpace_Log(" <recordsize>" + OutputsRowSize + "</recordsize>");
string sPartCount = dfsclient.GetFilePartCountString(DfsOutputName);
DSpace_Log(" <parts>" + sPartCount + "</parts>");
DSpace_Log(" </queryresults>");
}
else
{
DSpace_Log(shelloutputSelect1);
string sTopOptions = "-";
if (joined)
{
sTopOptions += ";JOINED";
}
string topoutput1;
{
RemoteCall rc = GetRemoteCallTop(DfsOutputName);
rc.OverrideInput = DfsOutputName + "@" + OutputsRowSize;
topoutput1 = rc.Call("\"" + TableName + "\" \"" + DfsOutputName + "\" \"" + Qa.QlArgsEscape(OutputRowInfo) + "\" \"" + OutputDisplayInfo + "\" " + TopCount.ToString() + " " + sTopOptions).Trim();
}
DSpace_Log(topoutput1);
{
string delfile = DfsOutputName;
{
int iat = delfile.IndexOf('@');
if (-1 != iat)
{
delfile = delfile.Substring(0, iat);
}
}
dfsclient.DeleteFile(delfile);
}
}
}
