Create and customize a Table

DirectQuery allows to create the table in 2 ways: by discovering the table from the remote database or by creating the table manually.

Discover a table from the external database

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

TableDescription salesTable =
    discoverer.discoverTable(new ClickhouseTableId("TUTORIAL", "SALES"));
TableDescription productsTable =
    discoverer.discoverTable(new ClickhouseTableId("TUTORIAL", "PRODUCTS"));

This infers as much information as possible from the external database metadata, such as the fields, types, keys, etc.

However, you might not be satisfied with the current data structure and need to customize it further.

Manually create a table

It is possible to manually provide all the information about the external table so no discovery is done.

This is achieved by providing the external table to use, describing all the fields of the table and defining the key fields:

final FieldDescription saleIdField =
    FieldDescription.builder()
        .externalField("SALE_ID", false)
        .withSameLocalName()
        .type(StandardTypes.STRING)
        .dictionarized(true)
        .build();
final FieldDescription dateField =
    FieldDescription.builder()
        .externalField("DATE", false)
        .withSameLocalName()
        .type(StandardTypes.LOCAL_DATE)
        .dictionarized(true)
        .build();
final FieldDescription shopField =
    FieldDescription.builder()
        .externalField("SHOP", false)
        .withSameLocalName()
        .type(StandardTypes.STRING)
        .dictionarized(true)
        .build();
final FieldDescription productField =
    FieldDescription.builder()
        .externalField("PRODUCT", false)
        .withSameLocalName()
        .type(StandardTypes.STRING)
        .dictionarized(true)
        .build();
final FieldDescription quantityField =
    FieldDescription.builder()
        .externalField("QUANTITY", false)
        .withSameLocalName()
        .type(StandardTypes.NULLABLE_LONG)
        .build();
final FieldDescription unitPriceField =
    FieldDescription.builder()
        .externalField("UNIT_PRICE", false)
        .withSameLocalName()
        .type(StandardTypes.NULLABLE_DOUBLE)
        .build();
final TableDescription productsTable =
    TableDescription.builder()
        .externalTable(
            ExternalTable.fromTableId(
                new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES")))
        .withSameLocalName()
        .fields(
            List.of(
                saleIdField, dateField, shopField, productField, quantityField, unitPriceField))
        .keyFieldNames(List.of(saleIdField.getName()))
        .build();

Such table generally don't need customization as they can be defined directly as needed.

Customize a table

Each table can be customized. Note that all the changes are done in the local data structure, and nothing is written in the external database.

Rename the table

For instance, you can rename the table:

salesTable = salesTable.toBuilder().localName("Sales").build();
productsTable = productsTable.toBuilder().localName("Products").build();

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 =
    TableDescriptionUtils.selectFields(
        productsTable, 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.toBuilder().keyFieldNames(List.of("SALE_ID")).build();

Set the clustering fields

Clustering fields can also be set.

salesTable = salesTable.toBuilder().clusteringFieldNames(Set.of("SHOP")).build();

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 =
    TableDescriptionUtils.updateField(
        salesTable, "PRODUCT", f -> f.toBuilder().localName("PRODUCT_ID").build());

Change the type

The type can also be updated:

productsTable =
    TableDescriptionUtils.updateField(
        productsTable,
        "PURCHASE_PRICE",
        f -> FieldDescription.changeFieldType((FieldDescription) f, StandardTypes.FLOAT));

Make field non nullable

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

final FieldDescription notYetNonNullableShopField =
    (FieldDescription) salesTable.getField("SHOP");
final IFieldType nonNullableType =
    FieldType.setNonNullable(notYetNonNullableShopField.getType());
final AFieldDescription nonNullableShopField =
    FieldDescription.changeFieldType(notYetNonNullableShopField, nonNullableType);
final TableDescription updatedSalesTableWithNonNullableField =
    TableDescriptionUtils.updateField(salesTable, "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 =
    TableDescriptionUtils.applyNaming(
        productsTable, NamingConventions.LOWER_CASE_NAMING_CONVENTION);

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

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

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

Create a table from a SQL expression

It is possible to create a table based on a SQL expression. The expression must comply to the dialect of the used external database.
Here an example of a static table expression in Clickhouse dialect :

final ExternalTable.TableExpression externalTable =
    ExternalTable.fromSqlExpression(
        "(SELECT 'p1' AS PRODUCT, toDate('2021-07-19') AS DATE, 2.0 AS QUANTITY "
            + "UNION ALL SELECT 'p1', toDate('2021-07-20'), 3.0 "
            + "UNION ALL SELECT 'p1', toDate('2021-07-21'), 4.0) ");
final TableDescription table =
    TableDescription.builder()
        .externalTable(externalTable)
        .localName(CUSTOM_SALES_TABLE_NAME)
        .fields(
            List.of(
                FieldDescription.builder()
                    .externalField("PRODUCT", false)
                    .withSameLocalName()
                    .type(StandardTypes.STRING)
                    .dictionarized(false)
                    .build(),
                FieldDescription.builder()
                    .externalField("DATE", false)
                    .withSameLocalName()
                    .type(StandardTypes.LOCAL_DATE)
                    .dictionarized(false)
                    .build(),
                FieldDescription.builder()
                    .externalField("QUANTITY", false)
                    .withSameLocalName()
                    .type(StandardTypes.DOUBLE)
                    .dictionarized(false)
                    .build()))
        .build();

note

Such tables do not support time-travel (native or emulated).