示例#1
0
        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
        }
示例#2
0
        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
        }
示例#3
0
        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);
        }
示例#4
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");
        }
示例#5
0
        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();
            }
        }
示例#9
0
        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
        }
示例#10
0
        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
        }
示例#11
0
        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();
            }
        }
示例#12
0
        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();
            }
        }
示例#13
0
            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);
                    }
                }

            }