Skip to main content

Using Custom Types with DirectQuery

When using DirectQuery, Atoti retrieves data from the remote database. DirectQuery has sensible defaults to read the different data types from the remote database into common data types. However, in some situations where the remote type is complex or one wishes to customize the resulting type, it is possible to adjust it.

This guide describes how to do this.

Define the Custom Type​

First, we define two custom types: they are simple wrappers around a String and a double.

@Data
public static class CustomShop {
  final String shopName;
  public static CustomShop getDefaultValue() {
    return new CustomShop("N/A");
  }
  @Override
  public String toString() {
    return "CustomShop(shopName=" + this.shopName + ")";
  }
}
@Data
public static class CustomPrice {
  final double price;
  public static CustomPrice getDefaultValue() {
    return new CustomPrice(0.0);
  }
  @Override
  public String toString() {
    return "CustomPrice(price=" + this.price + ")";
  }
}

Adjust the database schema​

We customize the field in the "SALES" table definition.

final TableDescription salesTable =
    discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "SALES"));
final FieldDescription shopField = (FieldDescription) salesTable.getField("SHOP");
final AFieldDescription updatedShopField =
    FieldDescription.changeFieldType(
        shopField,
        FieldType.builder()
            .dataClass(CustomShop.class)
            .defaultValue(CustomShop.getDefaultValue())
            .build(),
        CustomReaderDescription.fromObjectData(value -> new CustomShop(value.toString())));
final TableDescription updatedSalesTable =
    TableDescriptionUtils.updateField(salesTable, "SHOP", updatedShopField);

If the column contains a primitive types (such as int or double), it is more efficient to use a specific API.

We use such an API to customize the "PRODUCTS" table definition:

final TableDescription productsTable =
    discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "PRODUCTS"));
final FieldDescription priceField = (FieldDescription) productsTable.getField("PURCHASE_PRICE");
final AFieldDescription updatedPriceField =
    FieldDescription.changeFieldType(
        priceField,
        FieldType.builder()
            .dataClass(CustomPrice.class)
            .defaultValue(CustomPrice.getDefaultValue())
            .build(),
        CustomReaderDescription.fromDoubleData(CustomPrice::new));
final TableDescription updatedProductsTable =
    TableDescriptionUtils.updateField(productsTable, "PURCHASE_PRICE", updatedPriceField);

The schema and database are created as usual:

final JoinDescription join =
    JoinDescription.builder()
        .name("SALES_TO_PRODUCTS")
        .sourceTableName("SALES")
        .targetTableName("PRODUCTS")
        .fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
        .build();
final SchemaDescription schema =
    SchemaDescription.builder()
        .externalTables(List.of(updatedSalesTable, updatedProductsTable))
        .externalJoins(List.of(join))
        .build();

Run a query​

We can run a simple query, to check it works:

final Application app =
    Application.builder(connector)
        .schema(schema)
        .databaseSettings(BigqueryDatabaseSettings.defaults())
        .build();
final IDatabaseVersion databaseVersion = app.getDatabase().getHead(MASTER_BRANCH_NAME);
final ListQuery query =
    databaseVersion
        .getQueryManager()
        .listQuery()
        .forTable("SALES")
        .withCondition(BaseConditions.equal(FieldPath.of("SALE_ID"), "S0017"))
        .withAliasedFields(
            AliasedField.fromFieldName("SALE_ID"),
            AliasedField.fromFieldName("SHOP"),
            AliasedField.create(
                "PURCHASE_PRICE", FieldPath.of("SALES_TO_PRODUCTS", "PURCHASE_PRICE")))
        .toQuery();
final ICursor res = databaseVersion.getQueryRunner().listQuery(query).run();
assertThat(res.next()).isTrue();
final IRecordReader recordReader = res.getRecord();
System.out.println(recordReader.read(1)); // Printed CustomShop(shopName=shop_3)
System.out.println(recordReader.read(2)); // Printed CustomPrice(price=38.0)

Conclusion​

That's it. Every query on the 'SHOP' (resp. 'PURCHASE_PRICE') column will generate an Object of class CustomShop (resp. CustomPrice).