Skip to main content

Customize Table

DirectQuery allows to customize the table(s) retrieved from the remote database.

Typically, you would have discovered the table from the remote database, with a code similar to:

Table salesTable = session.discoverTable("TUTORIAL", "SALES");
Table productsTable = session.discoverTable("TUTORIAL", "PRODUCTS");

However, you might not be satisfied with the current data structure. Note that all the changes are done in the local data structure, and nothing is written in the remote database.

Customize a table​

Each table can be customized.

Rename the table​

For instance, you can rename the table:

salesTable = salesTable.renameLocally("Sales");
productsTable = productsTable.renameLocally("Products");

Select a subset of fields​

The remote table might also contain some fields which are not relevant for the application. It's possible to select the fields you want:

productsTable =
    productsTable.selectFields(
        List.of("PRODUCT_ID", "CATEGORY", "SIZE", "PURCHASE_PRICE", "BRAND"));

Set the keys​

It is also possible to set the key(s) when it is not set properly.

salesTable = salesTable.setKeyFields(List.of("SALE_ID"));

Set the clustering fields​

Clustering fields can also be set.

salesTable = salesTable.setClusteringFields(List.of("DATE"));

Customize a field​

Each field in a table can be customized as well. As a general note, do not forget to update the field in the table.

Rename the field​

The field can be renamed:

salesTable = salesTable.updateField("PRODUCT", f -> f.renameLocally("PRODUCT_ID"));

Change the type​

The type can also be updated:

productsTable =
    productsTable.updateField("PURCHASE_PRICE", f -> f.withType(StandardTypes.FLOAT));

Make field non nullable​

You can mark explicitly the field as non-nullable in your application:

final Field notYetNonNullableShopField = salesTable.getField("SHOP");
final IFieldType NonNullableType =
    FieldType.setNonNullable(notYetNonNullableShopField.getType());
final Field nonNullableShopField = notYetNonNullableShopField.withType(NonNullableType);
final Table updatedSalesTableWithNonNullableField =
    salesTable.updateField("SHOP", nonNullableShopField);

This only impacts the local model, not the external database. However, if possible, fields should be marked as non-nullable directly in the database, so the database could enforce it.

Use a custom type​

This is a bit more complex, and can be found in the dedicated guide.

Naming convention​

Instead of renaming the fields one by one it is possible to apply a naming convention to rename the table and the fields.

Some usual naming conventions can be found in the NamingConventions utility class.

For instance, it can be used to switch everything to lower case, upper case or capitalized:

productsTable = productsTable.applyNaming(NamingConventions.LOWER_CASE_NAMING_CONVENTION);

or to change several names at once based on a mapping:

salesTable =
    salesTable.applyNaming(
        NamingConventions.fromMapping(Map.of("DATE", "Time", "SALE_ID", "Id")));

For more custom renaming it is possible to implement a custom INamingConvention using NamingConventions.from.