Queries

Queries allow to mine data inside the Iotellect unified data model using an SQL-style language. Since most data within the unified model is stored in the form of data tables, it's easy and natural to address them like regular database tables for slicing and dicing this data according to the business objectives of your app.

Iotellect Query Language is a powerful tool for analyzing collected machine data, app users' input, and any data generated within the platform. The specific SQL dialect of Iotellect supports most SQL clauses including SELECT, JOIN, FROM, WHERE, GROUP BY, HAVING, UNION, ORDER BY, and LIMIT.

Queries are useful for:

• Viewing/editing several properties of multiple resources/devices in a single aggregated table

• Finding/filtering some data and triggering an alert if selected tabular data matches some condition

• Building a report

• Exporting data to an external system or file

• Sorting and filtering existing tabular data

This section of the manual is not a tutorial of the SQL language itself. Rather, it assumes you already know SQL and SQL syntax. If you are unfamiliar with SQL, consider acquiring a general understanding before continuing with this chapter. Once you’re familiar with SQL, come back and learn how it is implemented in Iotellect. See the SQL References section for some helpful resources.

The Iotellect Query Language has two primary uses:

• Obtaining data from server objects or hardware devices, filtering, sorting, and grouping the data and presenting it to the user in a convenient form (as a table).

• Modifying the properties of various contexts (For example, to configure various hardware devices) by entering values for settings in the table.

The first task is performed using a normal data selection ("SELECT") query, creating a Data Table viewable through a Iotellect Server User Interface (such as Iotellect Client).

For example, you can write a query that shows all users in the system in a single convenient sortable table.

Queries results can also be edited. Allowing users to enter data, for example to configure several instances of the same device. From the previous example, you can use a query to access the all the users in the system and edit them from the results table:

Queries can be also used in different Iotellect Server facilities. For example, you can set up an alert that will be raised if a query result satisfies some condition (see the Hardware Devices in Trouble example in the Alerts chapter for details). Query results may be converted into a printable report when they're browsed (e.g. using the Generate Report feature of Data Table Editor in Iotellect Client). Widgets may also be used to present data previously aggregated and filtered by a query.

There are two primary differences between "normal" SQL used by most modern Database Engines (like MySQL or Oracle) and Iotellect Query Language.

Normal SQL uses tables as the primary data source. Every SELECT statement refers to one or more tables from which data is selected. These references appear in the FROM clause.

Iotellect Query Language works with data stored within Iotellect Server contexts. This data may be accessed by getting context variables (properties) or calling context functions. The value of every context variable is represented by a Data Table, a data structure that has fields, records and cells. These Data Tables are used by the Query Language engine as the tables from which data is selected.

The same method may be used to refer to tables containing the output values of context functions. When a SELECT statement refers to such a table (containing output values of a function), the reference should also include the input parameters for that function. When the query is executed, the function is called with these parameters. That function then generates a data table containing its output, which in turn will be used by the query.

In the Query Language Syntax article, these references to context variables or functions are called contextReference. Every contextReference may have an alias (tableAlias) that is used to refer to it from other parts of the query.

Just like normal SQL, Iotellect Query Language supports nested SELECT statements, where the FROM section of a SELECT statement contains another SELECT statement.

The second difference concerns referring to the fields of tables mentioned in the FROM clause.

In normal SQL, a reference to a table field includes two parts: tableAlias.fieldName. The first part is the table's name, an alias given to it in the FROM clause or an alias given to the nested SELECT query. The second part is the field's name.

In Iotellect, a reference to a table field consists of three parts. It looks like this: tableAlias.fieldAlias. tableAlias is the alias assigned to the table generated from context's data (see First Difference, above). fieldAlias is in the form entityName$fieldName. entityName is the name of a context variable or function (such as childInfo, which is the "user information" variable of the user context). fieldName is a specific field of that variable or function output (such as firstname, the user's first name, for the childInfo context).

If you are familiar with classic SQL language, consider the following analogies which illustrate how a query in the Iotellect Query Language differs from a normal SQL query: Table names used in normal SQL are substituted by Context References. However, every Context Reference is used to build a table before the query is executed. So the Iotellect Query Language processor deals with tables, just like a normal database engine. Field names used in normal SQL are substituted by Field References. But these Field References behave exactly like normal field names in the classic SQL query. They just have a slightly different syntax, allowing a user to refer to the fields of "virtual" tables generated from Context References. To see the exact differences in the syntax, and the logic behind them, please read about Field References. When an SQL query is executed, you get a "dumb" table, containing just values of data. However, when an Iotellect Query is executed, you get something we call a " Data Table ", which is smarter than "just" a table. A data table contains data validation rules, field types, and other such metadata.

This section describes the step-by-step process of how a query text is used to generate query results.

• At the first stage, the query processing engine finds all Context References in the FROM clause of the query text. These references are then used to build ad-hoc tables, filled with data from the actual contexts, on which the query will later be executed. This process is described here.

• A Classic SQL query is executed using the tables that were created at the previous step. The query produces a table, built according to the query syntax.

• The table is now converted to a Data Table. This makes it simpler to understand: the final Data Table contains all field descriptions, formatting and validation rules and any other metadata that may be fetched from the Data Tables on which the query was executed. So, for all intents and purposes. Iotellect uses a query to generate a Data Table. The only reason we mentioned the previous step, is to give you a glimpse at the internals of the system.

• The query result is shown to the user. If the results are editable, the user can now make changes to the data presented.

• If the user saves the modified results, the query processing engine writes all modified data back to the context variables from which it came.

As stated in the previous chapter, the difference between Iotellect queries and a normal SQL queries is that they are executed on "virtual" tables generated from context data rather than on "plain" tables.

The only operation supported in the Iotellect Query Language is the data selection operation which is performed with the SELECT keyword. SELECT retrieves data from a specified table, or from multiple related tables, that are created ad hoc, when the Context References in the query are processed.

Each query has several clauses:

• The primary SELECT clause, defining which fields of the original tables should be included in the query result.

• The FROM clause, indicating the source table or tables from which the data is to be retrieved. The FROM clause can include optional JOIN clauses, joining related tables with each other based on user-specified criteria.

• The WHERE clause includes a conditional expression, which is used to filter data returned by the query. The WHERE clause is applied before the GROUP BY clause. Technically speaking, the WHERE clause eliminates all rows from the result set where the conditional expression does not evaluate to True.

• The GROUP BY clause is used to combine, or group, rows with related values. GROUP BY is often used to calculate statistics (total, average, etc) of similar rows or to eliminate duplicate rows from a result set.

• The HAVING clause includes a conditional expression used to eliminate rows after the GROUP BY clause is applied to the result set.

• The UNION clause allows to combine the results of two or more SELECT statements into a single result.

• The ORDER BY clause is used to designate the columns used to sort resulting data, and whether they should be ascending or descending. The order of rows returned by an SQL query is never guaranteed unless an ORDER BY clause is specified.

• The LIMIT clause restricts the final result to a predefined range of rows. This is just a numeric range -- for example, if you have a query resulting in 100 records, but you just want 10 arbitrary records (the first ten, last ten, ten records starting from the 15th record, etc.), you can use a LIMIT clause to get just as many records as you want.

Iotellect Query Language supports most features of SQL standard selection queries. It has support for nested SELECT queries, SQL aggregation functions used to perform calculations on various data values (COUNT, MIN, MAX, SUM, AVG, etc), and built-in functions (numerical, string, date/time and system).

For a detailed description of Iotellect Query Language syntax, click here.

Two contexts are used to administer queries: One is the general Queries context, which serves as a container. The other is the Query context, which holds the information for a single query. See descriptions below.

Every user has their own set of Queries.

Several queries are built into Iotellect Server and appear under the default administrator's user account:

• Active Alerts. Shows pending and escalated alerts summary.

• All Users. This query allows viewing and editing the settings of all user accounts in a single table.

• Device Status Summary. Shows connection and synchronization status of every Device.

• Offline Devices. Shows offline Device summary.

These queries are described in the Query Examples chapter.

For more information on SQL language check the following links:

http://en.wikipedia.org/wiki/SQL

http://www.w3schools.com/sql/sql_select.asp

http://sqlzoo.net/

http://www.fluffycat.com/SQL/

http://www.baycongroup.com/tocsql.htm

Was this page helpful?

Tell us more

Send

Thank you!