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 New Data Loading or Unloading Topics. The techniques employed are generic examples that can be extended, adapted and repeated for any use case that we need. In all cases, we make minimal changes to the Reference Implementation.

Step 1 - Define customizations to datastore

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.

To make modifications to our datastore, we will need to navigate to the addModifications() method inside the DatastoreCustomisationsConfig class.

frtb-starter/src/main/java/com/activeviam/frtb/starter/cfg/impl/

In this case we will be adding a field to a store that we created in Adding New Data Loading or Unloading Topics.

    IStoreDescription storeDescription=StoreDescription.builder()
		.withStoreName(CUSTOM_STORE)
		.withField(AS_OF_DATE,ILiteralType.LOCAL_DATE).asKeyField()
		.withField(RISK_CLASS,ILiteralType.STRING).asKeyField()
		.withField("CustomProjected",ILiteralType.DOUBLE)
		// we create the column which will be populated by our column calculator
		.withField(CALCULATED_COLUMN_NAME)
		.build();

		configurator.addStore(FRTBConstants.FRTB_SCHEMA,storeDescription);

To enrich the data of an existing store, we can add the field to that existing store:

configurator.appendField(EXISTING_STORE,new CustomField(CALCULATED_COLUMN_NAME,ILiteralType.STRING))

If we want to organize our customizations into a separate class for instance ClientCustomisations , we can create a new class as outlined here.

See Datastore Helper documentation for more information on potential datastore modifications.

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-starter/src/main/java/com/activeviam/frtb/starter/cfg/impl/

package com.activeviam.frtb.starter.cfg.impl;

import com.qfs.msg.IColumnCalculator;
import com.qfs.msg.csv.ILineReader;

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 - Include column calculator in our ChannelParameters

For our column calculator to be picked up, we need to include it in our ChannelParameters. If you are adding a column calculator to an existing store, ensure that you are overriding the appropriate ChannelParameters to include the new column calculator. For more information, see ChannelParameters Spring Beans.

public class CustomChannelParameters {

	@Autowired
	ASourceConfig sourceConfig;

	private static final String CUSTOM_STORE = "CustomStore";
	public static final String FILE_PATTERN_PROP = "custom.file-pattern";
	public static final String TOPIC_CUSTOM = "Custom_TOPIC";

	@SourceConfigChannelParametersBean
	public ACSVSourceConfig.ChannelParameters customStoreChannelParameter() {
		return sourceConfig.channelParametersBuilder(CUSTOM_STORE, TOPIC_CUSTOM, FILE_PATTERN_PROP)
				.calculators(List.of(new CustomColumnCalculator(ClientCustomisations.CALCULATED_COLUMN_NAME))).build();
	}
}

Step 4 - Ensure that FRTBConfig picks up our modifications

To ensure that our customisations are picked up, be sure to include CustomChannelParameters and ClientCustomisations classes in FRTBConfig located in /frtb-starter/src/main/java/com/activeviam/frtb/starter/cfg/impl/

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.

Suggested further reading

Adding New Cube Hierarchies

Adding New Cube Measures