Skip to main content

Hive Queries

A query lets you load, filter, sort and reshape a collection of data with Hive code. Queries are most commonly used to load data from a data source via a table component, but they can also run standalone in any rule, directly against a list or map.

Let's assume our data source is called BikeParts, then the simplest query we could write is:

from BikeParts select all

This will load all records from the data source and include all its columns. The same syntax works on a list or map component, or standalone in any rule – the result always has the same kind of collection as the source, i.e. a table stays a table, a list stays a list and a map stays a map.

We can choose to only load specific records and columns too:

from BikeParts select {
  .partNumber,
  .name,
  .price
}
filter .partNumber > 100

This will produce a table that only has 3 columns – partNumber, name and price – and it will only contain parts that have a partNumber of 100 or greater.

note

Each column name must start with a period (.)

The query item​

Every query works with a "query item" – the single record, list item or MapEntry that is currently being looked at while filtering, sorting or projecting.

By default, the query item is named all, i.e. from source is short for from all in source. This is why you can write ...all or all.column – you're simply referring to the query item by its default name.

You can also give the query item your own name:

from item in BikeParts select [ item.name ]

Here we named the query item item; note that all is not available anymore in this case, you have to use item instead.

The ambient member syntax (.column) is a shorthand for all.column (or whatever name you've given the query item). It works no matter what kind of source you query – a record's columns in a table, the item itself in a list, or .key/.value of a MapEntry in a map.

Filtering Data​

The filter of a query has no fixed syntax; you can write any Hive code you want, as long as it results in either true or false.
The filter will be run for each item of the source. When it results in true, the item will be loaded, otherwise it won't.
You can access all columns of the query item in your filter, but also components (though there are some limitations for components).

Here is an example of a more advanced filter:

from BikeParts select all
filter
  if BikeCategory <> "" then .category.toLower() = BikeCategory else true end
  and .netWeight + AccessoryWeight < MaxWeight

A few things to note:

  • a filter can be made optional by using an if/then/else expression
    • in the example we only filter by .category when the BikeCategory component is not an empty text
    • by using true or false in the else branch of an if, we can include or exclude the record
    • we could also rewrite the category filter to not require an if/then/else expression like this:
    • (BikeCatogery = "" or .category.toLower() = BikeCategory)
  • you can call functions on column values or use them in calculations:
    • we convert the .category to lower-case before comparing it with BikeCategory
    • we calculate the sum of .netWeight and the AccessoryWeight component and compare the result with MaxWeight
warning

Limitations: there are some types of components (or Hive values, more general) that cannot be used in queries, or only in specific ways. See the Limitations section for details.

Projecting Data​

Data can not only be filtered, it can also be molded to fit your use case: the select clause determines both the contents and the kind of collection that is produced.

  • select { ... } produces a table, e.g. select { .nr = .partNumber, .name }
  • select [ expr ] produces a list, e.g. select [ item * 3 ]
  • select [ key : value ] produces a map, e.g. select [ .id : .name ]

Table projections​

from BikeParts select {
  .nr = .partNumber,
  .name,
  .price = .price * if .onSale then 0.8 else 1 end,
}

A few things to note:

  • if you want to use a different name for a column, you can just assign that column to a new one
    • in the example, we create a column nr that will hold the value of the partNumber column
    • Note: column names of the resulting table must be unique; you cannot have 2 columns with the same name
  • you can adjust the value of a column; in the example we lower the price by 20% when the onSale column is true
    • Note: modified columns are not available in filters or when modifying other columns. For instance, you cannot use .nr in a filter or the .price calculation (you must use .partNumber). Similarly, when you type .price you will receive the value stored in the data source, not the lowered price! (this might become available in a future update)
  • when adjusting a column value, you can use any Hive feature you want (*) – there is no specialized syntax
    • (*) the same limitations as for filters apply, see the Limitations section

If you want to use all columns of the source and add your own, you can use the spread operator (...):

from BikeParts select {
  ...all,
  .formattedPrice = .price.toNumber("0.00 EUR"),
}

The spread operator can target any expression, not just all – for example ... .value to spread a map's values, or ...item.field to spread a field of an object in a list. It only works in table projections.

A field introduced by a spread can be overridden by an explicitly declared column, or by a later spread – the overriding column always wins and determines the field's name, type and value, even if it appears before the spread in your code. The position of the field in the resulting table is always determined by whichever spread or column introduced it first; spread fields simply "fill the gaps" between your explicitly declared columns.

List projections​

from item in BikeParts select [ item.name ]

This produces a list of the name column of every part.

Map projections​

from BikeParts select [ .partNumber : .name ]

This produces a map, using partNumber as key and name as value.

Sorting Data​

You can change the order in which items are returned by a query by sorting them by one or more values:

// sort first by category, then by net weight
from BikeParts select all
sort by .category, .netWeight desc

The sort by statement must come after a filter (if there is one).
By default, values are sorted in ascending order. You can add desc after a sort expression to sort in descending order (you can also add asc to clarify your intent).

While the value to sort by will typically involve a column, you can also write more complex expressions.

// sort first by rounded weight, then sort so parts without a description come at thet end
from BikeParts select all
sort by
  .netWeight.round(),
  (if .Description = "" then 0 else 1 end) desc

Note that a sort value must be a Number, Text or Logic value.
Furthermore, you must put complex sort expressions in parenthesis (), as in the example above (a column name or function call works without (), but anything more advanced requires them).

tip

Text is sorted in a case-sensitive manner, but you can achieve a case-insensitive ordering by converting the text to all-upper or all-lower case first: sort by .myTextColumn.toUpper() .

Limiting the number of rows​

You can optionally restrict how many rows can be returned by a query or skip over a number of records:

// ignore the first record, then return up to 1000 records
from BikeParts select all
sort by .category
skip 1
take 1000

NOTE: this feature is best used in combination with sort by, as the order of rows in your data source may be subject to how it is imported. If you e.g. use a workflow to automatically update a data source, the order of rows may not be guaranteed.

Both skip and take expect a Number. This can be a literal value like 1 or an expression that involves a component like MaxRows – but you cannot use columns, as the limits affect the entire query and a column value belongs to a specific record.
Similar to sort, you must put complex expressions in parenthesis ().

Limitations​

When talking about what is permitted in a query (i.e. in the filter or when calculating a column value), we must differentiate between 2 areas:

  • the parts of a query that involve a column and are evaluated by the query engine – these have limits
  • the parts of a query that do not involve a column, which are evaluated beforehand – these are limitless
    • we call these parts "query parameters"

For instance, it's currently not allowed to use a record component in a filter, because sending an entire record table to the query engine would often be expensive and slow down your query. However, a record component can still be used in a query parameter – consider these two cases:

// not allowed
// a column value is used in Get(), thus the entire component is needed to evaluate the query
filter myRecordComponent.Get(.partNumber).Price < MaxPrice

// allowed
// everything to the right of (>) is a query parameter, as it doesn't involve a column value
// the query parameter is a simple number that can be calculated before the query itself is run
filter .price > myRecordComponent.Get(SelectedPartNumber).Price

The key takeaway is that you don't have to worry about limits in query parameters, only in code that directly involves column values. The system automatically tries to run as much code of the filter as possible before the query is started (i.e. turning those parts into parameters).
💡 If your query results in an error due to limits, try to restructure it so that the parts highlighted by the error do not depend on column values.

These types and features can currently be used in filters:

  • Number: all operators (>, >=, <, <=, =, <>, +, *, -, /, mod, ^) and functions (toText, round, floor, ceiling, truncate, atLeast, atMost)
  • Text: all operators (+, =, <>, ~=, *) and functions (length, contains, skip, take, replace, toUpper, toLower, toNumber)
  • Logic: all operators (not, and, or, =, <>)
  • Lists: all functions (length, get, contains)
  • if/then/else expressions
  • variables
  • optional chaining operator ?.