Skip to main content

Database Queries

This guide shows how to build and use the database queries.

Get-by-key queries​

Run a simple get-by-key query​

The get-by-key query retrieves the record corresponding to a key.
For example, given a table trades where rows are indexed by their trade id, the following retrieves the trader that made the trade 1023:

final GetByKeyQuery query =
    databaseVersion
        .getQueryManager()
        .getByKeyQuery()
        .forTable("trades")
        .withTableFields("trader")
        .toQuery();
final IRecordReader recordReader =
    databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1023).run();
if (recordReader != null) {
  final String trader = (String) recordReader.read(0);
}

If the table has multiple key fields (e.g. year, month, and day), key values can be passed either as variadic arguments or as a list:

databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1998, 11, 21).run();
databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(List.of(1998, 11, 21)).run();

The list of key fields for a given store is available via IDataTable#getKeyFieldNames(). Key values should be passed to withKey() in this exact order.

Compile once, run twice​

If a query will be run several times, it is better to compile it once:

final IPreparedGetByKeyQuery query =
    database
        .getQueryManager()
        .getByKeyQuery()
        .forTable("trader")
        .withTableFields("trader")
        .compile();

Then run it multiple times:

for (int i = 0; i < 10; i++) {
  databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(i).run();
}

Use a get-by-key query​

The get-by-key query returns the result as an IRecordReader. For example, the following service retrieves the trader name for a trade id:

private static class TraderNameService {
  IDatabase database;
  IPreparedGetByKeyQuery traderQuery;
  public TraderNameService(final IDatabase database) {
    this.database = database;
    this.traderQuery =
        database
            .getQueryManager()
            .getByKeyQuery()
            .forTable("trader")
            .withTableFields("trader")
            .compile();
  }
  public String getTraderName(final int tradeId) {
    final IRecordReader recordReader =
        database
            .getHead(IEpoch.MASTER_BRANCH_NAME)
            .getQueryRunner()
            .getByKeyQuery(this.traderQuery)
            .withKey(tradeId)
            .run();
    return (String) recordReader.read(0);
  }
}

Get-by-key and DirectQuery​

While Get-by-key queries performed on the datastore are very fast, they can take a long time on external databases, solely due to the networking time. As running too many get-by-key queries through DirectQuery can be a performance issue, there is a setting to control whether this is allowed. The possible behaviors are denying, warning, or allowing the query to run, and the default is to warn.

For standard size tables, it is recommended to load this table into an in-memory datastore table, as mentioned in the composite database documentation. Get-by-key queries would then be directed to the in-memory table, instead of the external database.

Configure this behavior in the database settings when creating the application.

final BigqueryDatabaseSettings databaseSettings =
    BigqueryDatabaseSettings.builder().getByKeyBehavior(GetByKeyBehavior.ALLOW).build();
final Application app =
    Application.builder(connector)
        .schema(schema)
        .managerDescription(managerDescription)
        .databaseSettings(databaseSettings)
        .build();

List queries​

Minimal list query​

The minimal list query requires a table name and retrieves all records:

final IPreparedListQuery query =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withoutCondition()
        .selectingAllTableFields()
        .compile();

To run the query, use the Query Runner of the database version:

final ICursor result =
    databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();

Add a condition​

The list query can be filtered using a condition builder.

The following retrieves the trades made by John:

final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .selectingAllTableFields()
        .compile();

Dynamic conditions​

The condition can be parameterized at query compilation. The parameter values are specified at query execution.

The following retrieves the trades made by Paul with that mechanism:

final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader")).as("parameter"))
        .selectingAllTableFields()
        .compile();
final ICursor result =
    databaseVersion
        .getQueryRunner()
        .listQuery(preparedListQuery)
        .withParameters(Map.of("parameter", "Paul"))
        .run();

Select fields​

If not all fields are useful, the fields returned by the query can be selected. There are three ways of doing so.

Aliased fields​

This is the generic approach. Fields returned by the query can be aliased, like in SQL queries:

final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withAliasedFields(
            AliasedField.create("alias1", FieldPath.of("tradeId")),
            AliasedField.create("alias2", FieldPath.of("ToProduct", "currency")))
        .compile();
final int alias1Index =
    preparedListQuery.getQueryResultFormat().getPlainRecordFormat().getFieldIndex("alias1");

All fields belong to the queried table​

If all the fields come from the queried table, there is a shorter method that will use the field name as an alias:

.withAliasedFields(
    AliasedField.create("tradeId", FieldPath.of("tradeId")),
    AliasedField.create("date", FieldPath.of("date")))

This can be replaced by:

.withTableFields("tradeId", "date")

Fields without an alias​

If only the field position in the results matters, and not the alias, the following is also possible:

final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withFieldsWithoutAlias(FieldPath.of("tradeId"), FieldPath.of("ToProduct", "currency"))
        .compile();

Add a timeout or a result limit​

For performance reasons, the query can be restricted by adding a result limit and/or a timeout. The timeout will cancel the query if reached and an exception will be thrown. The result limit sets the maximum number of rows that will be returned. Note that the rows returned when using a result limit are not deterministic: successive executions of the same query may return different rows, even if no transaction occurred in the database.

final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withTableFields("tradeId", "date")
        .withResultsLimit(1000)
        .compile();
final ICursor result =
    databaseVersion
        .getQueryRunner()
        .listQuery(preparedListQuery)
        .withoutParameters()
        .withTimeout(Duration.ofSeconds(10L))
        .run();

Add query tags​

Each query can have a map of tags to help monitor the application. For example, the following adds the name of the operation:

final IPreparedListQuery preparedListQuery =
    database
        .getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "Paul"))
        .withTableFields("tradeId", "date")
        .withQueryTags(Map.of("operation", "trade-inspection"))
        .compile();
final ICursor result =
    databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();

Use a list query​

The list query returns an ICursor that can be used to manipulate every record. It is important to read through the ICursor within a try-with-resources statement to ensure that all related resources are freed at the end of the manipulation.

For example, the following retrieves the sum of "value" by date:

public Map<Object, Double> getValueByDates() {
  final IPreparedListQuery query =
      database
          .getQueryManager()
          .listQuery()
          .forTable("trades")
          .withCondition(BaseConditions.in(FieldPath.of("City")).as("City"))
          .withTableFields("date", "value")
          .compile();
  final ICursor result =
      databaseVersion
          .getQueryRunner()
          .listQuery(query)
          .withParameters(Map.of("City", Set.of("Paris", "London")))
          .run();
  final Map<Object, Double> valuesByDates = new HashMap<>();
  try (result) {
    for (final IRecordReader r : result) {
      final Object date = r.read(0);
      final double value = r.readDouble(1);
      valuesByDates.merge(date, value, Double::sum);
    }
  }
  return valuesByDates;
}

Run with an acceptor​

By default, calling .run() blocks the calling thread until the full result is available as a single ICursor. An acceptor provides a way to process results as they are produced, partition by partition, without waiting for the entire result set.

This is useful when:

  • The result processing should happen in parallel.
  • Fine-grained control over how partition results are merged or consumed is needed.

The acceptor implements IPartitionedResultAcceptor. Its onResult(int partitionId, ICursor result) method is called once per partition as soon as that partition's results are ready.

caution

onResult may be called concurrently from different threads (one per partition). Implementations must be thread-safe.

The ICursor passed to onResult is closed after the call returns. To retain records beyond the scope of onResult, materialize them using one of these methods:

  • IRecordReader#copy() or IRecordReader#toTuple() for individual records.
  • ICursor#toRecords() or ICursor#toTuples() for all records at once.
final Map<Integer, List<Object[]>> resultsByPartition = new ConcurrentHashMap<>();
final AtomicReference<Throwable> error = new AtomicReference<>();
final ICompletionSync sync =
    databaseVersion
        .getQueryRunner()
        .listQuery(query)
        .withoutParameters()
        .withAcceptor(
            new IPartitionedResultAcceptor() {
              @Override
              public void onResult(final int partitionId, final ICursor result) {
                resultsByPartition.put(partitionId, result.toTuples());
              }
              @Override
              public void complete() {
                // Called once all partitions have been processed
              }
              @Override
              public void completeExceptionally(final Throwable ex) {
                error.compareAndSet(null, ex);
              }
            })
        .run();
sync.awaitCompletion(Duration.ofSeconds(10));
if (error.get() != null) {
  throw new ActiveViamRuntimeException("An error occurred in the acceptor.", error.get());
}

The run() method returns an ICompletionSync that can be used to wait for the query to finish.

Distinct queries​

The distinct query retrieves all distinct values for a field or a field combination from a record list. The record list can be filtered with specified conditions.

Its characteristics are very similar to list query ones. It returns an ICursor, and provides the following options:

  • Field aliasing
  • Limit
  • Timeout
  • Query tags

This sample query extracts all the counterparties John has traded with:

final IPreparedDistinctQuery preparedDistinctQuery =
    database
        .getQueryManager()
        .distinctQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withTableFields("counterParty")
        .withResultsLimit(1000)
        .withQueryTags(Map.of("operation", "trade-inspection"))
        .compile();
final ICursor result =
    databaseVersion
        .getQueryRunner()
        .distinctQuery(preparedDistinctQuery)
        .withoutParameters()
        .withTimeout(Duration.ofSeconds(10L))
        .run();

Note on joined projection fields​

Projection fields belonging to a table other than the base table are joined with a left join. This implies that default or null values can be present in the distinct result.