Access Keys:
Skip to content (Access Key - 0)

GemFire Versions

About GemFire Enterprise 6.5

Getting Started

Documentation

Resources

Articles

Developer Notes

Tools & Libraries

Toggle Sidebar

Querying

GemFire Querying

GemFire Enterprise stores data in regions. A region is a key-value store where the value could be anything ranging from simple byte arrys to complex nested objects. For example, if a Trade region may contain Trade objects keyed by a TradeId. GemFire allows applications to query the collection of values stored in a region. This support is provided for replicated as well as partitioned regions. In the Trade region example, an application might want to get a list of all Trades where the price was greater than a certain value. With GemFire Enterprise querying, this would be easily accomplished by writing an SQL like query in the Object Query Language (OQL) and running it on the Trade region. The query semantics and syntax supported in GemFire are based on OQL (Object Query Language).The query language supports the complete ASCII and Unicode character set.
To improve the efficiency of query execution, GemFire Enterprise supports indexing, much like how it is done in a relational database. During query execution, the query engine uses available indexes on the data store resulting in reduced query processing time. In the following sections, we will explore the GemFire API for querying regions, touch upon the differences between OQL and SQL and provide guidelines on writing queries so that they are performant. We will look at indexing, as well as timing out expensive queries and releasing resources associated with such queries. Querying is an extremely powerful capability in GemFire and it is also one that requires a fair amount of tuning and capacity planning to enure that it does not bog down the system.

OQL

OQL (ODMG 3.0 Object Data Management Group).
It is an SQL-like language with extended functionality for querying complex objects, object attributes and methods.

The GemFire Enterprise supports a subset of OQL language. For supported OQL semantics in GemFire Enterprise please refer Language Grammar topic under 'Query Language Grammar and Reserved Words' section in this page.

Sample OQL query

SELECT DISTINCT * FROM /exampleRegion WHERE status = 'active'

This query selects distinct Objects from the /exampleRegion that satisfy the where clause condition "status = active"

NOTE: All the queries explained in this page are based on the Objects used in the Examples and Sample Queries section in this page.

OQL v/s SQL

As mentioned OQL is similar to SQL language. The general query syntax between the two looks very similar.

For example:
Consider a "portfolio" table having "id" and "status" as its columns. And same is represented in object form as Portfolio object having "id" and "status" as its attributes.

A simple SQL SELECT syntax will look like:

SELECT [projection-list] FROM [From clause] WHERE [Where clause]
-- SELECT id FROM portfolio WHERE status = 'active'

The OQL syntax looks very similar:

SELECT [projection-list] FROM [From clause] WHERE [Where clause]
-- SELECT ID FROM /exampleRegion WHERE status = 'active'
Where exampleRegion is the region data-store in GemFire cache containing Portfolio objects as its values.

In addition OQL allows querying an object which is nested, compared to sql that is used to query two dimensional relation data (columns and rows). Object data is generally nested and to access the nested data you drill down through the outer data layers.

Consider "Portfolio" having multiple "Positions". In database model its reflected as flat data structure in two different tables "portfolio" and "position" with relationship between them. In object model its represented as single "Portfolio" object having set of "Position" objects.

To get the market values for all positions of active portfolios:

The SQL will query two tables:
SELECT pos.mktValue FROM portfolio p, position pos WHERE p.status = 'active' AND p.id = pos.id

In OQL it will be querying a single region, by traversing from upper level to lower level.
SELECT pos.mktValue FROM /exampleRegion portfolio, positions.values pos WHERE portfolio.status = 'active'

OQL only refers to SELECT statements. SQL includes both DDL and DML statements.

Writing and Executing a query.

These steps provide very general guidance in writing your queries. As with database querying, OQL querying is highly flexible and data dependent. It requires a careful study of your data and experimentation with different ways of expressing your query conditions and with the use of indexing (explained in later section)

  1. Decide what information you want to query, and from where. You can query server data from a client or from local cache.
  2. Build your query string. For a SELECT statement:
    • Build your FROM clause to bring the data you need into scope for the rest of the query. Include object typing and iterator variables.
    • Build your WHERE conditions to properly filter the data.
    • Build your SELECT projection list.
    • Decide whether to use indexes to support the query.
  3. Write the code to run the query, and handle your query results.
  4. Add your indexes according to the guidelines, test performance, and tune accordingly.

Query String

A query string is a fully-formed OQL statement that can be passed to a query engine and executed against a data set. To build a query string, you combine supported keywords, expressions, and operators to create an expression that returns the information that you require.

A query string expression follows the rules specified by the query language grammar.

A query expression can contain:

  • Path expressions
  • Attribute names
  • Method invocations
  • Operators
  • Literals
  • Query parameters
  • The functions IS_DEFINED, IS_UNDEFINED, ELEMENT, NVL and TO_DATE
  • SELECT statements

None of these is required to make a a query string. For example, a SELECT statement is generally thought of as the starting point for a query, but it is not essential. In the example the expressions with the region is a valid query.
/exampleRegion.size > 100
returns true if the region /exampleRegion has more than 100 values in it.

Aliases and Synonyms

In the query string, the path expressions (regions and its objects) can be defined using an alias-name, this name can be used/referred in other places of the query.

SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = 'active'

p is used as an alias to /exampleRegion

Query API

The GemFire QueryService provides methods to create the Query object using which you can perform query related operations.

Please refer product javadocs for Query related apis.

Query package Javadocs
QueryService Javadocs

Getting QueryService

For client-to-server Query execution, get QueryService from Pool
getQueryService for client-to-server queries

For Query execution on local Cache, get QueryService from Cache
getQueryService for member queries

Sample Query code.

// Identify your query string.
String queryString = "SELECT DISTINCT * FROM /exampleRegion";

// Get QueryService from Cache.
QueryService queryService = cache.getQueryService();

// Create the Query Object.
Query query = queryService.newQuery(queryString);

// Execute Query locally. Returns results set.
SelectResults results = (SelectResults)query.execute();

// Find the Size of the ResultSet.
int size = results.size();

// Iterate through your ResultSet.
Portfolio p = (Portfolio)results.iterator().next(); /* Region containing Portfolio object. */

Query Parameter Execution

This is similar to sql prepared statements, where query parameter can be set during query execution. This allows user to build query once and execute multiple times by passing the query conditions during run time.

NOTE: This is not supported from Client-to-Server queries.

The Query parameters are identified by a dollar sign, $ , followed by a digit that represents the parameter's position in the parameter array passed to the execute method. Counting begins at 1, to $1 references the first bound attribute, $2 the second, and so on.
The Query interface provides an overloaded execute method that accepts parameters inside an Object array.

execute using parameters

The 0th element of the Object array is used for the first query parameter, and so on. If the parameter count or parameter types do not match the query specification, the execute method throws If you pass in the wrong number of parameters, the call throws a QueryParameterCountInvalidException . If a parameter object type is not compatible with what is expected, the call throws a TypeMismatchException .
In this example, the first parameter, the integer 2, is bound to the first element in the object array. The second parameter, active, is bound to the second element.

example:

// Identify your query string.
String queryString = SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = $1;

// Get QueryService from Cache.
QueryService queryService = cache.getQueryService();

// Create the Query Object.
Query query = queryService.newQuery(queryString);

// Set query parameters.
Object[] params = new Object[1];
params[0] = "active";

// Execute Query locally. Returns results set.
SelectResults results = (SelectResults)query.execute(params);

// Find the Size of the ResultSet.
int size = results.size();

Additionally the query engine supports setting region path as query parameter.

Example:
SELECT DISTINCT * FROM $1 p WHERE p.status = $1

To use a parameter in the FROM clause, the parameter reference must be bound to a collection. This query could be used on any collection by passing the collection in as a query parameter.

Select Statement Results (Result Set)

The result of a SELECT statement is either UNDEFINED or is a Collection that implements the SelectResults interface.
SelectResults API

The SelectResults returned from the SELECT statement is either:

  1. A collection of objects, returned for these two cases:
    1. When only one expression is specified by the projection list and that expression is not explicitly specified using the fieldname:expression syntax
    2. When the SELECT list is * and a single collection is specified in the FROM clause
  2. A collection of Struct s that contains the objects.

When a struct is returned, the name of each field in the struct is determined following this order of preference:

  1. If a field is specified explicitly using the fieldname:expression syntax, the fieldname is used.
  2. If the SELECT projection list is * and an explicit iterator expression is used in the FROM clause, the iterator variable name is used as the field name.
  3. If the field is associated with a region or attribute path, the last attribute name in the path is used.
  4. If names can not be decided based on these rules, arbitrary unique names are generated by the query processor.

Here are some examples of how projections and FROM clause expressions are applied:
SELECT DISTINCT * FROM /exampleRegion - Returns the Collection of portfolios (Region containing Portfolio as values).
SELECT DISTINCT secId FROM /exampleRegion, positions.values TYPE Position WHERE status = 'active' - Returns the Collection of secId s from the positions of active portfolios.
SELECT DISTINCT "type", positions FROM /exampleRegion WHERE status = 'active' - Returns a Collection of struct<type: String, positions: map> for the active portfolios. The second field of the struct is a Map ( jav.utils.Map ) object, which contains the positions map as the value.
SELECT DISTINCT * FROM /exampleRegion, positions.values TYPE Position WHERE status = 'active' - Returns a Collection of struct<portfolios: Portfolio, values: Position> for the active portfolios.
SELECT DISTINCT * FROM /exampleRegion portfolio, positions positions TYPE Position WHERE portfolio.status = 'active' - Returns a Collection of struct<pflo: Portfolio, posn: Position> for the active portfolios.

Limiting the number of results

The LIMIT keyword can be optionally placed at the end of the query string to limit the number of rows returned.
For example, this query returns at most 10 rows:
SELECT * FROM /exampleRegions LIMIT 10

If you use the limit keyword, you cannot also run operations on the query result set that do any kind of summary activities, as these would be meaningless.
For example, trying to run add or addAll on a SelectResult returned from a query with a LIMIT clause throws an exception.

Queryable Data

For the regions you query, the objects must be of the same type. Querying and indexing against non-homogeneous data causes exceptions. You can, however, use different subtypes. To ensure that your data remains consistent for your queries, use the key-constraint and value-constraint region attributes, which restrict the types of keys and values allowed in the region.

For the objects you query, implement the equals and hashCode methods. The methods must conform to the properties and behavior documented in the online Java API documentation for java.lang.Object . Inconsistent query results may occur if these methods are absent.

Data Visibility

The query engine resolves names and path expressions according to the name space that is currently in scope in the query.

The initial name space for any query is composed of:

  • Regions In the context of a query, the name of a region is specified by its full path starting with a forward slash ( / ) and delimited by the forward slash between region names.
  • Region querying attributes From a region path, you can access the Region object's public fields and methods, referred to in querying as the region's attributes.
  • Top-level region data You can access entry keys and entry data through the region path.
    1. /exampleRegion.keySet returns the Set of entry keys in the region
    2. /exampleRegion.entrySet returns the Set of Region.Entry objects
    3. /exampleRegion.values returns the Collection of entry values
    4. /exampleRegion returns the Collection of entry values

Drill Down

New name spaces are brought into scope based on the FROM clause in the SELECT statement. In this query, for example, the FROM expression evaluates to the Collection of entry values in /exampleRegions. The attributes of the values are added to the initial scope of the query and status is resolved within the new scope.
SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = 'active'

Each FROM clause expression must resolve to a collection of objects. The collection is available for iteration in the query expressions that follow. In the example above, the entry value collection is iterated by the WHERE clause, comparing the status field to the string 'active'. When a match is found, the value object is added to the return set.
In this query, the collection specified in the first FROM clause expression is used by the rest of the SELECT statement, including the second FROM clause expression.
SELECT DISTINCT * FROM /exampleRegion, positions.values positions WHERE positions.qty > 1000.00

Attribute visibility

You can access any object or object attribute that is available in the current scope of a query. In querying, an object's attribute is any identifier that can be mapped to a public field or method in the object. In the FROM specification, any object that is in scope is valid, so at the beginning of a query, all locally cached regions and their attributes are in scope.

For attribute Position.secId which is public and has getter method "getSecId()" the query looks like:
SELECT DISTINCT * FROM /exampleRegion p where p.position1.secId = '1'
SELECT DISTINCT * FROM /exampleRegion p where p.position1.SecId = '1'
SELECT DISTINCT * FROM /exampleRegion p where p.position1.getSecId() = '1'

The query engine tries to evaluate the value using the public field value, if public field is not found makes a get call using field name (having its first character uppercase).

Namescopes

It is sometimes necessary for an OQL query to refer to the class of an object. In cases where same class name reside in two different namescopes (packages); there also needs to be a means of referring to different classes having same name.
The IMPORT statement is used to establish a name for a class in a query.

IMPORT package.Position;
SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position WHERE positions.mktValue >= 25.00

Specifying Object Types

Specifying object type helps query engine to process the query at optimal speed. Apart from specifying the object types during configuration (using key-constraint and value-constraint) type can be explicitly specified in query string.

SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position WHERE positions.mktValue >= 25.00

Performance considerations

As with query processors that run against relational databases, the way a query is written can greatly affect execution performance. Among other things, whether indexes are used depends on how each query is stated. These are some of the things to consider when optimizing your GemFire queries for performance.

  • Indexes are not used in expressions that contain NOT, so in a WHERE clause of a query, qty >= 10 could have an index on qty applied for efficiency. However, NOT(qty < 10) could not have the index applied.
  • For AND operators, you may get better results if the conditions that use indexes and conditions that are more selective come before other conditions in the query.

Examples and Sample Queries

Running Examples

  • Copy the above source to appropriate java files.
    QueryServer.java - Source for Server
    QueryClient.java - Source for Client
    Portfolio.java - Region Object
    Position.java - Nested Object
  • Compile the files.
    Make sure to have gemfire.jar and antlr.jar in your classpath (along with above add "query" class package for running tests).
  • Open two console windows

Start the server in one console window
Ex: java -classpath /product/lib/gemfire.jar:/product/lib/antlr.jar:/continuousQuery continuousQuery.QueryServer

This will start QueryServer, execute sample query on local cache and prints the ResultSet size.
At the end asks to start client to demonstrate query execution from client.

Start QueryClient on another window.

Ex: java -classpath /product/lib/gemfire.jar:/product/lib/antlr.jar:/continuousQuery continuousQuery.QueryClient

This executes a sample query on Server region and prints the ResultSet size.

Indexing

The GemFire Enterprise query engine supports indexing. Using indexes a query performance can be significantly improved.
A query run without the aid of an index iterates through every object in the collection. If an index is available that matches part or all of the query specification, the query iterates only over the indexed set, thus reducing the query processing time.

When creating indexes, keep in mind that:

  • Indexes incur maintenance costs as they must be updated when the indexed data changes. An index that requires many updates and is not used very often may require more system resources than no index at all.
  • Indexes consume memory.
  • Indexes are not allowed on overflow regions. Indexes cannot be overflowed to disk.

Index Creation

Index can be created programmatically and using xml.

The GemFire QueryService provides methods to create, list and remove the index.

QueryService.createIndex()

Please refer QueryService API for all related APIs.

Index types

Functional

sorted index that allows the comparison of an attribute to a constant. The comparison can be performed using any of the relational operators, and is not restricted to attributes or attributes paths. A comparison can refer to a complex function with parameters, as long as the attribute, attribute path, or function evaluates to an object that supports the Comparable interface. Consequently, an index expression which evaluates to a Comparable object is valid. The following are examples of valid comparables:
java.util.Date object
java.lang.Integer Float
java.lang.String String
java.lang.Boolean Boolean
See the Sun Java API for information about the Comparable interface.

IndexType.FUNCTIONAL

Primary key

This makes the query service aware of the relationship between the values in the region and the keys in the region.

IndexType.PRIMARY_KEY

Note:
The primary key index is not sorted. Without sorting, you can only do equality tests. Other comparisons are not possible. To obtain a sorted index on your primary keys, create a functional index on the attribute used as the primary key.

Note:
The query service is not automatically aware of the relationship between the region values and keys. For this, you must create the primary key index.

The FROM clause for a primary key index must be just a region path. The indexed expression is an expression that, when applied to an entry value, produces the key. For example, if a region has Portfolios as the values and the keys are the id field of the Portfolios , the indexed expression is id.

Index Maintenance (Synchronous and Asynchronous)

Indexes are automatically kept current with the region data they reference. The region attribute IndexMaintenanceSynchronous specifies whether the region indexes are updated synchronously when a region is modified or asynchronously in a background thread. Asynchronous index maintenance batches up multiple updates to the same region key. The default mode is synchronous, since this provides the greatest consistency with region data.

AttributesFactory.setIndexMaintenanceSynchronous()

This declarative index creation sets the maintenance mode to asynchronous:

<region-attributes index-update-type="asynchronous">
</region-attributes>

Internal Index structure

RangeIndex and CompactRangeIndex are the two internal data structure that is used to maintain the indexes created on query-able objects.

CompactRangeIndex

A CompactRangeIndex is a range index that has simple data structures to minimize its footprint, at the expense of doing extra work at index maintenance.

It is selected as the index implementation when:
Index Maintenance is synchronous.
The indexed expression is a path expression
The from clause has only one iterator (this implies there is only one value in the index for each region entry) and it is directly on the region values (not supported with with keys, entries)

This index does not support the storage of projection attributes.
Currently this implementation only supports an index on a region path.

Examples when its not created:

createIndex("statusIndex", IndexType.FUNCTIONAL,"status","/portfolios, positions");
createIndex("secIdIndex", IndexType.FUNCTIONAL,"b.secId","/portfolios pf, pf.positions.values b");
createIndex("intFunctionIndex", IndexType.FUNCTIONAL,"intFunction(pf.getID)","/portfolios pf, pf.positions b");
createIndex("kIndex", IndexType.FUNCTIONAL, "pf","/portfolios.keys pf");

Index samples

// Primary key index. The field doesn't has to be present.
createIndex("pkidIndex", IndexType.PRIMARY_KEY, "p.pkid1", "/root/exampleRegion p");

createIndex("Index4", IndexType.PRIMARY_KEY,"ID","/portfolios");

// Simple index
createIndex("pkidIndex", IndexType.FUNCTIONAL, "p.pkid", "/root/exampleRegion p");

// On Set type
createIndex("setIndex", IndexType.FUNCTIONAL, "s", "/root/exampleRegion p, p.sp s");

// Positions is a map
createIndex("secIdIndex", IndexType.FUNCTIONAL,"b.secId","/portfolios pf, pf.positions.values b");

// Index expression as function.
createIndex("intFunctionIndex", IndexType.FUNCTIONAL,"intFunction(pf.getID)","/portfolios pf, pf.positions b");

// On Keys
createIndex("keyIndex", IndexType.FUNCTIONAL, "keys","/portfolios.keySet keys");  

createIndex("kIndex", IndexType.FUNCTIONAL, "pf","/portfolios.keys pf");

createIndex("keyIndex", IndexType.FUNCTIONAL, "ks.hashCode","/portfolios.keys ks"); 

createIndex("k1Index", IndexType.FUNCTIONAL, "key","/portfolios.entries");

// On Entry 
createIndex("entryIndex", IndexType.FUNCTIONAL, "value.getID()","/portfolios.entrySet pf");

// Misc.
createIndex("cIndex", IndexType.FUNCTIONAL,"pf.getCW(pf.ID)","/portfolios pf");

createIndex("funcReturnSecIdIndex", IndexType.FUNCTIONAL,"pf.funcReturnSecId(element(select distinct pos from /portfolios pf, pf.positions.values as pos where pos.sharesOutstanding = 5000))","/portfolios pf, pf.positions b");

createIndex("NVLIndex1",IndexType.FUNCTIONAL, "nvl(pf.position2, pf.position1).secId", "/portfolios pf");

Index Usage Guidelines

Optimizing your queries with indexes requires a cycle of careful planning, testing, and tuning. Poorly-defined indexes can degrade the performance of your queries instead of improving it. This section gives guidelines for index usage in the query service.

General indexing guidelines
  • In general an index will improve query performance if the FROM clauses of the query and index match exactly.
  • The query evaluation engine does not have a sophisticated cost-based optimizer. It has simple optimizer which selects best index (one) or multiple index based on index size and the operator which is getting evaluated.
Single region queries
  • Queries with one comparison operation may be improved with either a primary key or functional key index, depending on whether the attribute being compared is also the primary key.

SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.pkid = '123'

If pkid is the key in the /exampleRegion region, creating a primary key index on pkid is the best choice as a primary key index does not have maintenance overhead.
If pkid is not the key, a functional index on pkid should improve performance.

  • With multiple comparison operations, you can create a functional index on one or more of the attributes.
    Try the following:
  1. Create a single index on the condition you expect to have the smallest result set size. Check performance with this index.
  2. Keeping the first index, add an index on a second condition. Adding the second index may degrade performance. If it does, remove it and keep only the first index. The order of the two comparisons in the query can also impact performance. Generally speaking, in OQL queries, as in SQL queries, you should order your comparisons so the earlier ones give you the fewest results on which to run subsequent comparisons.

For this query, you would try a functional index on name, age, or on both:
SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.status = 'active' and portfolio.ID > 45

  • For queries with nested levels, you may get better performance by drilling into the lower levels in the index as well as in the query.

This query drills down one level:
SELECT DISTINCT * FROM /exampleRegion portfolio, portfolio.positions.values positions where positions.secId = 'AOL' and positions.MktValue > 1

Equi-join queries

Equi-join queries are queries in which two regions are joined through an equality condition in the WHERE clause.

Create a functional index for each side of the equi-join condition. The query engine can quickly evaluate the query's equi-join condition by iterating over the keys of the left-side and right-side indexes for an equality match.

NOTE: Equi-join queries require functional indexes. Primary key indexes are not applied to equi-join queries.

If there are additional, single-region queries in a query with an equi-join condition, create additional indexes for the single-region conditions only if you are able to create at least one such index for each region in the query. Any indexing on a subset of the regions in the query will degrade performance.For this example query:

Equi-join queries with more than two regions

Identify all equi-join conditions. Then, create as few functional indexes for the equi-join conditions as you can while still joining all regions. If there are equi-join conditions that redundantly join two regions - in order to more-finely filter the data, for example - creating redundant indexes for these joins will negatively impact performance. Create indexes only on one equi-join condition for each region pair.

Querying Partitioned Region

The basic unit of storage for a partitioned region is a bucket, which resides on a GemFire node and contains all the entries that map to a single hashcode. To query a partitioned region the system distributes the query to all buckets across all nodes, then merges the result sets and sends back the query results.

Query Restrictions

Partitioned region queries function the same as non-partitioned region queries, except for the restrictions listed in this section. Partitioned region queries that do not follow these guidelines generate an UnsupportedOperationException.

  • Joins and cross-region queries are not allowed - between separate partitioned regions, or between a partitioned region and any other region.
  • No region other than the partitioned region can be referenced in the query.
  • The query must be just a SELECT expression (as opposed to arbitrary OQL expressions), preceded by zero or more IMPORT statements.
  • The SELECT expression itself can be arbitrarily complex, including nested SELECT expressions, as long as only one partitioned region is referenced.
  • The partitioned region reference can only be in the first FROM clause iterator. Additional FROM clause iterators are allowed if they do not reference any regions (such as drilling down into the values in the partitioned region).
  • The first FROM clause iterator must contain only one reference to the partitioned region (the reference can be a parameter, such as $1).
  • The first FROM clause iterator cannot contain a subquery, but subqueries are allowed in additional FROM clause iterators.

Queries With Large Result Sets

The size of a query result set depends on the restrictiveness of the query and the size of the total data set. A partitioned region can hold much more data than other types of regions, so there is more potential for larger result sets on partitioned region queries. This could cause the member receiving the results to run out of memory if the result set is very large.

Query Extensions

Functions

The query language supports these functions:

  • ELEMENT(expr) - Extracts a single element from a collection or array. This function throws a FunctionDomainException if the argument is not a collection or array with exactly one element.
    example:
    ELEMENT(SELECT DISTINCT * FROM /exampleRegion WHERE id = 'XYZ-1').status = 'active'
  • IS_DEFINED(expr) - Returns TRUE if the expression does not evaluate to UNDEFINED.
  • IS_UNDEFINED(expr) - Returns TRUE if the expression evaluates to UNDEFINED. In most queries, undefined values are not included in the query results. The IS_UNDEFINED function allows undefined values to be included, so you can identify elements with undefined values.
    example:
    SELECT DISTINCT * FROM /exampleRegion p WHERE IS_UNDEFINED(p.status)
  • NVL(expr1, expr2) - Returns expr2 if expr1 is null. The expressions can be bind arguments, path expressions, or literals.

*TO_DATE (date_str, format_str) - Returns a Java Date class object. The arguments must be String s with date_str representing the date and format_str representing the format used by date_str . The format_str you provide is parsed using java.text.SimpleDateFormat .

Literals

These are the literal types that GemFire supports:

  • boolean - A boolean value, either TRUE or FALSE
  • integer and long - An integer literal is of type long if it is suffixed with the ASCII letter L. Otherwise it is of type int .
  • floating point - A floating-point literal is of type float if it is suffixed with an ASCII letter F. Otherwise its type is double . It can optionally be suffixed with an ASCII letter D. A double or floating point literal can optionally include an exponent suffix of E or e, followed by a signed or unsigned number.
  • string - String literals are delimited by single quotation marks. Embedded single-quotation marks are doubled. For example, the character string 'Hello' evaluates to the value Hello , while the character string 'He said, ''Hello''' evaluates to He said, 'Hello' . Embedded newlines are kept as part of the string literal.
  • char - A literal is of type char if it is a string literal prefixed by the keyword CHAR, otherwise it is of type string. The CHAR literal for the single-quotation mark character is CHAR '''' (four single quotation marks).
  • date - A java.sql.Date object that uses the JDBC format prefixed with the DATE keyword: DATE yyyy-mm-dd . In the Date , yyyy represents the year, mm represents the month, and dd represents the day. The year must be represented by four digits; a two-digit shorthand for the year is not allowed.
  • time - A java.sql.Time object that uses the JDBC format (based on a 24-hour clock) prefixed with the TIME keyword: TIME hh:mm:ss . In the Time , hh represents the hours, mm represents the minutes, and ss represents the seconds.
  • timestamp - A java.sql.Timestamp object that uses the JDBC format with a TIMESTAMP prefix: TIMESTAMP yyyy-mm-dd hh:mm:ss.fffffffff In the Timestamp , yyyy-mm-dd represents the date , hh:mm:ss represents the time , and fffffffff represents the fractional seconds (up to nine digits).
  • NIL --Equivalent alternative of NULL .
  • NULL --The same as null in Java.
  • UNDEFINED --A special literal that is a valid value for any data type. An UNDEFINED value is the result of accessing an attribute of a null-valued attribute. Note that if you access an attribute that has an explicit value of null, then it is not undefined. For example if a query accesses the attribute address.city and address is null, the result is undefined. If the query accesses address, then the result is not undefined, it is NULL .

Comparing values with java.util.Date
You can compare temporal literal values DATE , TIME , and TIMESTAMP with java.util.Date values. There is no literal for java.util.Date in the query language.

Using comments in query string

You can include comments in the query string. To insert a one-line comment, begin the line with two dashes - . To insert a multiple-line comment, or to embed a comment within a line, begin it with /* and end it with */ .

SELECT DISTINCT * FROM /exampleRegion WHERE status = 'active' - here is another comment

SELECT DISTINCT * FROM /exampleRegion /* here is a comment */ WHERE status = 'active'

Method Invocation

To use a method in a query, use the attribute name that maps to the public method you want to invoke.

SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.size >= 2 - maps to positions.size()

Methods declared to return void evaluate to null when invoked through the query processor.

Invoking methods without parameters

If you the attribute name maps to a public method that takes no parameters, just include the method name in the query string as an attribute. For example, emps.isEmpty is equivalent to emps.isEmpty() . Here the query invokes isEmpty on positions, and returns the set of all portfolios with no positions:

SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.isEmpty

Invoking methods with parameters

SELECT DISTINCT * FROM /exampleRegion p WHERE p.name.startsWith('Bo')

For overloaded methods, the query processor decides which method to call by matching the runtime argument types with the parameter types required by the method. If only one method's signature matches the parameters provided, it is invoked. The query processor uses runtime types to match method signatures.
If more than one method can be invoked, the query processor chooses the method whose parameter types are the most specific for the given arguments. For example, if an overloaded method includes versions with the same number of arguments, but one takes a Person type as an argument and the other takes an Employee type, derived from Person , Employee is the more specific object type. If the argument passed to the method is compatible with both types, the query processor uses the method with the Employee parameter type.
The query processor uses the runtime types of the parameters and the receiver to determine the proper method to invoke. Because runtime types are used, an argument with a null value has no typing information, and so can be matched with any object type parameter. When a null argument is used, if the query processor cannot determine the proper method to invoke based on the non-null arguments, it throws an AmbiguousNameException .

Operators

Comparison operators

compare two values and return the results, either TRUE or FALSE.

supported comparison operators:

= equal to
< less than
<> not equal to
<= less than or equal to
!= not equal to
> greater than
>= greater than or equal to

The equal and not equal operators have lower precedence than the other comparison operators. They can be used with null. To perform equality or inequality comparisons with UNDEFINED, use the IS_DEFINED and IS_UNDEFINED operators instead of these comparison operators.

Logical operators

he logical operators AND and OR allow you to create more complex expressions by combining expressions to produce a boolean result. When you combine two conditional expressions using the AND operator, both conditions must evaluate to true for the entire expression to be true. When you combine two conditional expressions using the OR operator, the expression evaluates to true if either one or both of the conditions are true. You can create complex expressions by combining multiple simple conditional expressions with AND and OR operators. When expressions use AND and OR operators, AND has higher precedence than OR.

Unary operators

Unary operators operate on a single value or expression, and have lower precedence than comparison operators in expressions. GemFire supports the unary operator NOT. NOT is the negation operator, which changes the value of the operand to its opposite. So if an expression evaluates to TRUE, NOT changes it to FALSE. The operand must be a boolean.

Map and index operators

Map and index operators access elements in key/value collections (such as maps and regions) and ordered collections (such as arrays, lists, and String s). The operator is represented by a set of square brackets "[ ]" immediately following the name of the collection. The mapping or indexing specification is provided inside these brackets.
Array, list, and String elements are accessed using an index value. Indexing starts from zero for the first element, 1 for the second element and so on. If myList is an array, list, or String and index is an expression that evaluates to a non-negative integer, then myList[index] represents the ( index +1)th element of myList . The elements of a String are the list of characters that make up the string.
Map and region values are accessed by key using the same syntax. The key can be any Object . For a Region , the map operator performs a non-distributed get in the local cache only - with no use of netSearch . So myRegion[keyExpression] is the equivalent of myRegion.getEntry(keyExpression).getValue .

Dot and forward slash operator

The dot operator '.' separates attribute names in a path expression, and specifies the navigation through object attributes. An alternate equivalent to the dot is the right arrow, " -> ". The forward slash is used to separate region names when navigating into subregions.

LIKE Predicate

GemFire offers limited support for the like predicate. It is expressed as:

WHERE x LIKE '<STRING>'

LIKE can be used to mean 'equals to':

SELECT DISTINCT * FROM /exampleRegion p where p.status LIKE 'active'

If you terminate the string with either wildcard character '%' or '*', it behaves like 'starts with'.

SELECT DISTINCT * FROM /exampleRegion p where p.status LIKE 'activ%'

The wild card can only be used at the end. If it is found anywhere else an exception is thrown. You can put escaped wild card characters anywhere in the string to represent the characters themselves. If an index is present the like predicate can use it.

IN Expression

The IN expression is a boolean indicating if one expression is present inside a collectin of expressions of compatible type. The determination is based on the expressions' equals semantics.
If e1 and e2 are expressions, e2 is a collection, and e1 is an object or a literal whose type is a subtype or the same type as the elements of e2 , then e1 IN e2 is an expression of type boolean.
The expression returns:

  • TRUE if e1 is not UNDEFINED and is contained in collection e2
  • FALSE if e1 is not UNDEFINED and is not contained in collection e2
  • UNDEFINED if e1 is UNDEFINED

For example, 2 IN SET(1, 2, 3) is TRUE.
Another example is when the collection you are querying into is defined by a subquery.

SELECT DISTINCT * FROM /exampleRegion p WHERE p.ID IN (SELECT p2.ID FROM /exampleRegion2 p2 WHERE p2.status = 'active')

The interior SELECT statement returns a collection of ids for all /exampleRegion2 entries whose status is active. The exterior SELECT iterates over /exampleRegion, comparing each entry's id with this collection. For each entry, if the IN expression returns TRUE, the associated name and address are added to the outer SELECT's collection.

Type Conversions

The GemFire query processor performs implicit conversions and promotions under certain cases in order to evaluate expressions that contain different types. The query processor performs binary numeric promotion, method invocation conversion, and temporal type conversion.

Binary numeric promotion

The query processor performs binary numeric promotion on the operands of the following operators:

  • Comparison operators <, <=, >, and >=
  • Equality operators = and <>
    Binary numeric promotion widens the operands in a numeric expression to the widest representation used by any of the operands. In each expression, the query processor applies the following rules in order until a conversion is made:
  1. If either operand is of type double , the other is converted to double
  2. If either operand is of type float , the other is converted to float
  3. If either operand is of type long , the other is converted to long
  4. Both operands are converted to type int char
Method invocation conversion

Method invocation conversion in the query language follows the same rules as Java method invocation conversion, except that the query language uses runtime types instead of compile time types, and handles null arguments differently than in Java.
One aspect of using runtime types is that an argument with a null value has no typing information, and so can be matched with any type parameter. When a null argument is used, if the query processor cannot determine the proper method to invoke based on the non-null arguments, it throws an AmbiguousNameException .

Temporal type conversion

The temporal types that the query language supports include the Java types java.util.Date , java.sql.Date , java.sql.Time , and java.sql.Timestamp , which are all treated the same and can be compared and used in indexes. When compared with each other, these types are all treated as nanosecond quantities.

Query Language Grammar and Reserved Words

Reserved Words

These words are reserved for the query language and may not be used as identifiers. The words with asterisk after them are not currently used by GemFire Enterprise, but are reserved for future implementation.

abs*
all*
and
andthen*
any*
array
as
asc*
avg*
bag*
boolean
by*
byte
char
collection
count*
date
declare*
define*
desc* 
dictionary
distinct
double
element
enum*
except*
exists*
false
first*
flatten*
float
for*
from*
group*
having*
import
in
int
intersect*
interval* 
is_defined
is_undefined
last*
like
limit
list*
listtoset*
long
map
max*
min*
mod*
nil
not
null
nvl
octet
or
order* 
orelse*
query*
select
set
short
some*
string
struct*
sum*
time
timestamp
to_date
true
type
undefine*
undefined
union*
unique*
where
To access any method, attribute, or named object that has the same name as a query language reserved word, enclose the name within double quotation marks.
Examples:
SELECT DISTINCT "type" FROM /root/portfolios WHERE status = 'active'
SELECT DISTINCT * FROM /region1 WHERE emps."select"() < 100000

Language Grammar

Notation used in the grammar: nA nonterminal symbol that has to appear at some place within the grammar on the left side of a rule. All nonterminal symbols have to be derived to be terminal symbols.t A terminal symbol (shown in italic bold).x yx followed by yx | yx or y(x | y)x or y[ x ]x or empty{ x }A possibly empty sequence of x.comment descriptive text
Grammar list:

symbol ::= expression
query_program ::= [ imports semicolon ] query [semicolon]
imports ::= import \{ semicolon import \}
import ::= qualifiedName [ AS identifier ]
query ::= selectExpr | expr
selectExpr ::= projectionAttributes fromClause [ whereClause ]
projectionAttributes ::= | projectionList
projectionList ::= projection \{ comma projection \}
projection ::= field | expr [ identifier ]
field ::= identifier colon expr
fromClause ::= iteratorDef \{ comma iteratorDef \}
iteratorDef ::= expr [ [ ] identifier ] [ identifier ] | identifier expr [ identifier ]
whereClause ::= expr
expr ::= castExpr
castExpr ::= orExpr | left_paren identifier right_paren castExpr
orExpr ::= andExpr \{ andExpr \}
andExpr ::= equalityExpr \{ equalityExpr \}
equalityExpr ::= relationalExpr \{ ( | | ) relationalExpr \}
relationalExpr ::= inExpr \{ ( | | | ) inExpr \}
inExpr ::= unaryExpr \{ unaryExpr \}
unaryExpr ::= [ ] unaryExpr
postfixExpr ::= primaryExpr \{ left_bracket expr right_bracket \}
| primaryExpr \{ dot identifier [ argList ] \}
argList ::= left_paren [ valueList ] right_paren
qualifiedName ::= identifier \{ dot identifier \}
primaryExpr ::= functionExpr
| identifier [ argList ]
| undefinedExpr
| collectionConstruction
| queryParam
| literal
| ( query )
| region_path
functionExpr ::= left_paren query right_paren
| left_paren query comma query right_paren
| left_paren query right_paren
undefinedExpr ::= left_paren query right_paren
| left_paren query right_paren
collectionConstruction ::= left_paren [ valueList ] right_paren
valueList ::= expr \{ comma expr \}
queryParam ::= integerLiteral
region_path ::= forward_slash region_name \{ forward_slash region_name \}
region_name ::= name_character \{ name_character \}
identifier ::= letter \{ name_character \}
literal ::= booleanLiteral
| integerLiteral
| longLiteral
| doubleLiteral
| floatLiteral
| charLiteral
| stringLiteral
| dateLiteral
| timeLiteral
| timestampLiteral
|
|
booleanLiteral ::= |
integerLiteral ::= [ dash ] digit \{ digit \}
longLiteral ::= integerLiteral
floatLiteral ::= [ dash ] digit \{ digit \} dot digit \{ digit \} [ ( | ) [ plus | dash ] digit \{ digit \} ]
doubleLiteral ::= [ dash ] digit \{ digit \} dot digit \{ digit \} [ ( | ) [ plus | dash ] digit \{ digit \} ] [ ]
charLiteral ::= single_quote character single_quote
stringLiteral ::= single_quote \{ character \} single_quote
dateLiteral ::= single_quote integerLiteral dash integerLiteral dash integerLiteral single_quote
timeLiteral ::= single_quote integerLiteral colon
integerLiteral colon integerLiteral single_quote
timestampLiteral ::= single_quote
integerLiteral dash integerLiteral dash integerLiteral integerLiteral colon
integerLiteral colon
digit \{ digit \} [ dot digit \{ digit \} ] single_quote
letter ::= any unicode letter
character ::= any unicode character except 0xFFFF
name_character ::= letter | digit | underscore
digit ::= any unicode digit 
The expressions in the following are all terminal characters:
dot ::= .
left_paren ::= (
right_paren ::= )
left_bracket ::= [
right_bracket ::= ]
single_quote ::= '
underscore ::= _
forward_slash ::= /
comma ::= ,
semicolon ::= ;
colon ::= :
dash ::= -
plus ::= +

Language Notes

  • Query language keywords such as SELECT, NULL, and DATE are case-insensitive. Identifiers such as attribute names, method names, and path expressions are case-sensitive.
  • Comment lines begin with - (double dash).
  • Comment blocks begin with /* and end with */.
  • String literals are delimited by single-quotes. Embedded single-quotes are doubled. Examples:
    'Hello' value = Hello
    'He said, ''Hello''' value = He said, 'Hello'
  • Character literals begin with the CHAR keyword followed by the character in single quotation marks. The single-quotation mark character itself is represented as CHAR '''' (with four single quotation marks).
  • In the TIMESTAMP literal, there is a maximum of nine digits after the decimal point.

Child Pages

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
Adaptavist Theme Builder (3.4.0-M2-conf210) Powered by Atlassian Confluence 2.10.3, the Enterprise Wiki.