Skip to main content

Querying the Datastore

ActivePivot is mainly designed to query the multidimensional analytics cube. However, it is sometimes necessary to retrieve information directly from the datastore, either for a specific computation within an analytics query, or for other purposes (for example checking the existence of a given piece of data to confirm a specific data loading has occurred, or getting values for a specific set of facts...).

Client interaction with the Datastore occurs via the ActivePivot Queries API, which is fully multi-threaded with respect to:

  • concurrency between different user queries
  • internal concurrency within a single query

A configurable pool of threads is allocated to the query API (see Managing Transaction and Query Thread Pools).

The degree of parallelism depends on the locations asked in the query and the partitioning of the data: the more the data is spread amongst different partitions, the more parallelism will occur.

A query to the datastore can either give a condition which will limit the returned results, it can query without any condition at all, or it can query for a very specific key. In all cases the returned results have a subset of the total fields in the store, which is dictated by another option. Using this query can be done for one set of fields and a separate set of values can be returned.

A store can be queried in a number of different ways depending on what the objective of the calling code is:

  • if the query is a one-shot query, the QueryRunner is the way to go: it offers some sugar to very easily define a query and run it
  • if the query is going to be run multiple times, and maybe on different datastore versions, then creating a IRecordQuery and compiling it offers better performances
  • if moreover we know in advance the key of the result that is going to be returned by the query, then we can use getByKey queries.

Using the Query Builder​

The Query Runner offers a builder type pattern to create queries which can be run multiple times on a fixed version of the datastore. Using the query runner is useful for writing concise code.

IDictionaryCursor cursor = datastoreVersion.getQueryRunner()
        .forStore("StoreName")
        .withCondition(condition)
        .selecting("KeyFieldName", "NonKeyFieldName")
        .run();

In the above example, condition is an com.qfs.condition.ICondition. Conditions are easy to create using the helper class com.qfs.condition.impl.BaseConditions.

Here is an example where we want to query for records having a given field ("NonKeyFieldName") equal to "valueA" or "valueB":

ICondition condition = BaseConditions.In("NonKeyFieldName", "valueA", "valueB");

The ICursor that is returned by the query can be iterated over:

List<String> result = new ArrayList<>();
while (cursor.hasNext()) {
    cursor.next();
    IRecordReader reader = cursor.getRecord();
    result.add((String) reader.read("KeyFieldName"));
}

Manually creating a Query, and compiling it​

A query can be created manually which can then be run multiple times against one or several versions of a datastore. Creating a manual query gives the most flexibility over the when and how the query is run, and also gives the opportunity to create a prepared/compiled query that gives performance benefits. A manually built query always must have a valid condition otherwise it cannot be compiled properly internally, though this condition can be BaseConditions.TRUE which returns all results.

/**
 * Creates a record query.
 *
 * @param storeName the name of the base {@link IStore store} that the query is executed against
 * @param condition the {@link ICondition condition} associated with this query used to filter the rows
 *        returned by the query, pass the parameter {@link BaseConditions#TRUE} to return all rows in the data
 *        store
 * @param selectedFields the names of the columns returned by the query, they define the result's
 *        {@link IRecordFormat}
 */
public RecordQuery(String storeName, ICondition condition, String... selectedFields) {
    this(storeName, condition, Arrays.asList(selectedFields));
}

To get the benefits of a compiled query, we must compile it against a given version of the datastore: 

ICompiledQuery compiledQuery = datastoreVersion.getQueryManager().compile(recordQuery);

There are two kinds of conditions:

  • IConstantCondition: conditions whose operands must be given at construction time.
  • IDynamicCondition: parameterized conditions whose operands will be specified only when we execute the query. The idea is to compile the query once and execute many times with different values of operands.

A dynamic condition can be constructed using the parameterized method. Each parameter has a name and an index that allow to specify the parameterized values at query execution time. If names or indices are omitted while constructing the query, the missing names and indexes will be generated automatically at compile time.

Thus you can choose whether you prefer using names: 

BaseConditions.And(
        BaseConditions.Equal("field1").parametrized("parameter1"),
        BaseConditions.In("field2").parametrized("parameter2"));
or if you are more comfortable with indices: 
BaseConditions.And(
        BaseConditions.Equal("field1").parametrized(0),
        BaseConditions.In("field2").parametrized(1));

The parameterized values are specified while using the IQueryRunner builder to execute the query, specifying either the parameter names: 

/**
 * Defines the parameters by their names.
 *
 * @param parameters the map of parameters by name
 * @return the builder
 */
IHasRunnableQuery withParameters(Map<String, Object> parameters);
or their index: 
/**
 * Defines the parameters by their index.
 *
 * @param parameters the array of parameters
 * @return the builder
 */
IHasRunnableQuery withParameters(Object[] parameters);

For example, the query constructed with the above condition (parameterized by name) could be run using: 

Map<String, Object> map = new HashMap<>();
map.put("parameter1", "myValueForField1");
map.put("parameter2", Arrays.asList("value1", "value2"));
IDictionaryCursor cursor =
        datastoreVersion.getQueryRunner().forQuery(compiledQuery).withParameters(map).run();

The code is thread safe. Parallel execution of the same compile query is allowed with different values of the operands.

Query by key​

If the key of an object to query is known, then it is possible to do a very quick query which will return just that object. Get by key queries are much faster than record queries.

Get by key queries are constructed using the DatastoreQueryHelper, which contains a series of static methods that can aid in the querying of the datastores. Only one record is returned from a get by key query, unless no value is found, in which case null is returned.

/**
 * Retrieve the record from a store by giving its key. This is a convenience version of
 * {@link #getByKey(IDatastoreVersion, String, Object[], List)} that takes {@link String}...
 * {@code selectedFields} instead of {@link List}.
 *
 * @param datastoreVersion a version of the datastore containing the store to query.
 * @param storeName the name of the store to query.
 * @param key the tuple forming the key of the store. Components must be in the order of the key fields.
 * @param selectedFields the field to read from the matching record.
 * @see IRecordQuery#getSelectedFields()
 * @return the record with the given key, or null if no record was found.
 * @throws UnsupportedOperationException if the given {@code store} has no key field for the given
 *         {@code storeName}.
 */
public static final IRecordReader getByKey(
        final IDatastoreVersion datastoreVersion,
        final String storeName,
        final Object[] key,
        final String... selectedFields)
        throws UnsupportedOperationException

It is possible to construct a compiled query by key (using the DatastoreQueryHelper), and run it several times with different keys. Here is an example:

// Create the compiled query on a given datastore version
ICompiledGetByKey compiledQuery =
        DatastoreQueryHelper.createGetByKeyQuery(datastoreVersion, "myStoreName", "field1", "field2"); // the
                                                                                                        // selected
                                                                                                        // fields
// The compiled query can be executed multiple times with different keys
Object[] keyValue = new Object[] {"key1"};
IRecordReader recordResult1 = compiledQuery.runInVersion(datastoreVersion, keyValue);
keyValue[0] = "key2";
IRecordReader recordResult2 = compiledQuery.runInVersion(datastoreVersion, keyValue);

A query by key can also be executed against the current transaction rather than on a stable datastore version. In that case the compiled query must be created using ITransactionQueryRunner.createGetByKeyQuery and it can then be run several times using ICompiledGetByKey.runInTransaction.

A compiled get by key query can only be runInVersion if it was compiled on a stable datastore version, and can only be runInTransaction if it was compiled for that transaction.