Enriching File Fields by Adding Column Calculators
This page provides a description of how we can add a new column calculator. It features an example in which we populate a new field to a new store. To see how this store was populated, see Adding and Populating a New Store.
The techniques employed are generic examples that can be extended, adapted and repeated for any use case that is needed. In all cases, we make minimal changes to the Reference Implementation.
The examples outlined in this page will build on the example in Adding and Populating a New Store.
Step 1 - Datastore Modifications
Before we can load this new column into our cube, we need to make sure that our datastore has a field that can accept this column.
We can follow the Adding and Populating a New Store example to create a new store or modify an existing store.
Step 2 - Define column calculator
We want to define the logic of our column calculator.
Below we have an example of a simple column calculator that returns a value Negative when the Custom Projected value is less than 0, and Non-Negative otherwise. We will define our custom column calculator inside:
/frtb-application/src/main/java/com/activeviam/frtb/application/config/
public class CustomColumnCalculator implements IColumnCalculator<ILineReader> {
private final String columnName;
public CustomColumnCalculator(String columnName) {
this.columnName = columnName;
}
@Override
public String getColumnName() {
return this.columnName;
}
@Override
public Object compute(IColumnCalculationContext<ILineReader> context) {
if ((Double) context.getValue("CustomProjected") < 0) {
return "Negative";
}
return "Non-Negative";
}
}
Column Calculators can be used to add columns from simple operations across any number of columns that are present in your dataset. Below is a common use case; parsing data out of the file name. More metadata is available through the context.
final String filename=context.getContext().getCurrentFile().getName();
Step 3 - DLC Config
For this step, we will be using the Data Load Controller.
In the example below, we will override the topic description we have configured here.
We will wire in our existing topic description and add a column calculator into the channel.
Since we have given our topic description a lower order, it will fully replace any existing topic description with the same unique identifier (name and topic type).
Alternatively, we can create a named Custom Field Description and add it to the channel via YAML.
@Configuration
public class TopicDescriptionCustomisations {
public static final String CALCULATED_COLUMN_NAME = "calculatedColumn";
@Bean
@Order(1)
public CsvTopicDescription customTopicDescription(
@Qualifier("customProjectionTopicDescription") CsvTopicDescription customProjectionTopicDescription,
NamedEntityResolverService namedEntityResolverService
) {
return customProjectionTopicDescription.toBuilder()
.channel(ChannelDescription.builder(namedEntityResolverService.getTarget("CustomProjectionStore"))
.customField(AnonymousCustomFieldDescription.of(s -> new CustomColumnCalculator(CALCULATED_COLUMN_NAME)))
.build()
)
.build();
}
}
Step 4 - Ensure FrtbApplicationConfig picks up our modifications
For our customizations to be picked up, we must include all added Spring @Configuration classes in FrtbApplicationConfig located in /frtb-application/src/main/java/com/activeviam/frtb/application/config/.
When using DirectQuery
When using DirectQuery, column calculators do not apply. This is because the data in DirectQuery is loaded directly and there is no ETL (Edit Transform Load) layer. If you want to enrich your table with a new computed column, you will either have to pre-compute the values and add them on your remote table, or create a view that adds the extra field.
If you create a view, its name must be the same as the database table you are trying to enrich and the source table for the view should have a different name. This ensures that when the application starts, Atoti does not read from the incorrect table.