Skip to main content

Database Queries

The guide will show you how to build and use the database queries.

Get-by-key queries​

Running 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, we can retrieve 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 var trader = recordReader.read(0);
}

If the table has multiple key fields (e.g. year, month, and day), we can pass key values 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();

You can find the list of key fields for a given store using IDataTable#getKeyFieldNames. Key values should be passed to withKey() in this exact order.

Compiling 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();
And run it multiple times: 
for (int i = 0; i < 10; i++) {
  databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(i).run();
}

Using a get-by-key query​

The get-by-key query returns the result as a RecordReader. For example, we can create a service to retrieve 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 to many get-by-key queries through DirectQuery can be a performance issue, there is a setting to control whether this is allowed or not. The possible behaviors are denying, warning, or allowing the query to run, and the default is to warn.

This setting can be set in the Database Setting 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 asks a table name and retrieve 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 QueryRunner of your database version:

databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();

Adding a condition​

The list query can be filtered using a condition builder.

Let's retrieve 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 the query compilation, and its parameter values and the parameter values specified at query execution.

Let's retrieve 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();
databaseVersion
    .getQueryRunner()
    .listQuery(preparedListQuery)
    .withParameters(Map.of("parameter", "Paul"))
    .run();

Selecting fields​

If not all fields are useful, we can select the fields returned by the query. There are 3 different ways of doing so.

Aliasing fields​

This is the generic approach. We can alias the fields returned by the query 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 var alias1Index =
    preparedListQuery.getQueryResultFormat().getPlainRecordFormat().getFieldIndex("alias1");

All fields belong to queried table​

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

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

This can be replaced by:

.withTableFields("tradeId", "date")

Fields without alias​

In case only the position (and not the alias) of the fields in the results matters, then 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();

Adding a timeout or a result limit​

For performance reasons, we can restrict the query 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 set the maximal number of rows that will be returned.

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();
databaseVersion
    .getQueryRunner()
    .listQuery(preparedListQuery)
    .withoutParameters()
    .withTimeout(Duration.ofSeconds(10L))
    .run();

Adding query tags​

Each query can have a map of tags to help monitoring the application. For example, we can add 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();
databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();

Using a list query​

The list query will return a ICursor that you can use 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, we can retrieve 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) {
    while (result.next()) {
      final Object date = result.getRecord().read(0);
      final double value = result.getRecord().readDouble(1);
      valuesByDates.merge(date, value, Double::sum);
    }
  }
  return valuesByDates;
}

Distinct queries​

The distinct query allows to retrieve all distinct values for a field or a field combination from a record list. Record list could be filtered with some specified conditions.

Its characteristics are very similar to list query ones. It returns a ICursor, and provides 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();
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.