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.
- BigQuery
- ClickHouse
- Databricks
- MS SQL
- Redshift
- Snowflake
- Synapse
final Table salesTable = session.discoverTable(TableId.of(PROJECT_ID, "tutorial", "SALES"));
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
CustomShop.class,
CustomShop.getDefaultValue(),
(final Object value) -> new CustomShop(value.toString()));
final Table updatedSalesTable = salesTable.updateField("SHOP", updatedShopField);
final Table salesTable = session.discoverTable("TUTORIAL", "SALES");
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
CustomShop.class,
CustomShop.getDefaultValue(),
(final ClickHouseValue value) -> new CustomShop(value.asString()));
final Table updatedSalesTable = salesTable.updateField("SHOP", updatedShopField);
final Table salesTable =
session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
CustomShop.class,
CustomShop.getDefaultValue(),
(final Object value) -> new CustomShop((String) value));
final Table updatedSalesTable = salesTable.updateField("SHOP", updatedShopField);
final Table salesTable = session.discoverTable(new SqlTableId(DATABASE_NAME, SCHEMA_NAME, "SALES"));
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
CustomShop.class,
CustomShop.getDefaultValue(),
(final Object value) -> new CustomShop((String) value));
final Table updatedSalesTable = salesTable.updateField("SHOP", updatedShopField);
final Table salesTable =
session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
CustomShop.class,
CustomShop.getDefaultValue(),
(final Object value) -> new CustomShop((String) value));
final Table updatedSalesTable = salesTable.updateField("SHOP", updatedShopField);
final Table salesTable =
session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
CustomShop.class,
CustomShop.getDefaultValue(),
(final Object value) -> new CustomShop((String) value));
final Table updatedSalesTable = salesTable.updateField("SHOP", updatedShopField);
final Table salesTable = session.discoverTable(new SqlTableId(DATABASE_NAME, "TUTORIAL", "SALES"));
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
CustomShop.class,
CustomShop.getDefaultValue(),
(final Object value) -> new CustomShop((String) value));
final Table updatedSalesTable = salesTable.updateField("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:
- BigQuery
- ClickHouse
- Databricks
- MS SQL
- Redshift
- Snowflake
- Synapse
final Table productsTable = session.discoverTable(TableId.of(PROJECT_ID, "tutorial", "PRODUCTS"));
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField.withCustomTypeFromDouble(
CustomPrice.class,
CustomPrice.getDefaultValue(),
(final double value) -> new CustomPrice(value));
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);
Note that ClickHouse has no specific API, as the generic one already caters to primitives.
final Table productsTable = session.discoverTable("TUTORIAL", "PRODUCTS");
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField.withCustomType(
CustomPrice.class,
CustomPrice.getDefaultValue(),
(final ClickHouseValue value) -> new CustomPrice(value.asDouble()));
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);
final Table productsTable =
session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "PRODUCTS"));
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField
.withCustomTypeFromDouble(CustomPrice.class, CustomPrice.getDefaultValue(), CustomPrice::new);
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);
final Table productsTable = session.discoverTable(new SqlTableId(DATABASE_NAME, SCHEMA_NAME, "PRODUCTS"));
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField
.withCustomTypeFromDouble(CustomPrice.class, CustomPrice.getDefaultValue(), CustomPrice::new);
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);
final Table productsTable =
session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "PRODUCTS"));
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField
.withCustomTypeFromDouble(CustomPrice.class, CustomPrice.getDefaultValue(), CustomPrice::new);
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);
final Table productsTable =
session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "PRODUCTS"));
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField
.withCustomTypeFromDouble(CustomPrice.class, CustomPrice.getDefaultValue(), CustomPrice::new);
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);
final Table productsTable = session.discoverTable(new SqlTableId(DATABASE_NAME, "TUTORIAL", "PRODUCTS"));
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField
.withCustomTypeFromDouble(CustomPrice.class, CustomPrice.getDefaultValue(), CustomPrice::new);
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);
The schema and database are created as usual:
- BigQuery
- ClickHouse
- Databricks
- MS SQL
- Redshift
- Snowflake
- Synapse
final SqlJoin join = SqlJoin.builder()
.name("SALES_TO_PRODUCTS")
.fromTable("SALES")
.toTable("PRODUCTS")
.fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
.build();
final Schema schema = Schema.builder()
.withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
.build();
final SqlJoin join = SqlJoin.builder()
.name("SALES_TO_PRODUCTS")
.fromTable("SALES")
.toTable("PRODUCTS")
.fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
.build();
final Schema schema = Schema.builder()
.withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
.build();
final SqlJoin join = SqlJoin.builder()
.name("SALES_TO_PRODUCTS")
.fromTable("SALES")
.toTable("PRODUCTS")
.fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
.build();
final Schema schema = Schema.builder()
.withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
.build();
final SqlJoin join = SqlJoin.builder()
.name("SALES_TO_PRODUCTS")
.fromTable("SALES")
.toTable("PRODUCTS")
.fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
.build();
final Schema schema = Schema.builder()
.withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
.build();
final SqlJoin join = SqlJoin.builder()
.name("SALES_TO_PRODUCTS")
.fromTable("SALES")
.toTable("PRODUCTS")
.fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
.build();
final Schema schema = Schema.builder()
.withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
.build();
final SqlJoin join = SqlJoin.builder()
.name("SALES_TO_PRODUCTS")
.fromTable("SALES")
.toTable("PRODUCTS")
.fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
.build();
final Schema schema = Schema.builder()
.withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
.build();
final SqlJoin join = SqlJoin.builder()
.name("SALES_TO_PRODUCTS")
.fromTable("SALES")
.toTable("PRODUCTS")
.fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
.build();
final Schema schema = Schema.builder()
.withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
.build();
Run a query
We can run a simple query, to check it works:
- BigQuery
- ClickHouse
- Databricks
- MS SQL
- Redshift
- Snowflake
- Synapse
final Application app = session.applicationBuilder().schema(schema).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.hasNext()).isTrue();
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)
final Application app = session.applicationBuilder().schema(schema).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.hasNext()).isTrue();
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)
final Application app = session.applicationBuilder().schema(schema).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.hasNext()).isTrue();
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)
final Application app = session.applicationBuilder().schema(schema).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.hasNext()).isTrue();
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)
final Application app = session.applicationBuilder().schema(schema).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.hasNext()).isTrue();
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)
final Application app = session.applicationBuilder().schema(schema).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.hasNext()).isTrue();
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)
final Application app = session.applicationBuilder().schema(schema).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.hasNext()).isTrue();
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).