Adding Columns to an Existing File and Store
This page provides a description of how to add a new column to an
existing file and load that column into the Atoti FRTB project. It features an
example in which we will add ‘TestField’ to the file
Trade\_Attributes.csv
.
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.
Editing the Data
For this example, we will add a column to the file
Trade_Attributes.csv
and load the data from this column into the
TradeMapping
store. In this case, our new column’s header is ‘TestField’,
and all data in this column is ‘TestData’. Below is a portion of the file.
AsOfDate | TradeId | Book | LegalEntity | Notional | Notional Ccy | PresentValue | PVCcy | ResidualRisk | ExoticUnderlying | OtherResidualRiskType | TradeDate | Sensitivity Scale Category | RRAO Category | TestField |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2018-09-26 | EQ_FUT_JBS S.A. c854d9d0 | EQ_LARG_EM | ActiveBank US | -71380331.05 | EUR | -4207218.46 | EUR | N | N | N | 2018-09-26 | TestData | ||
2018-09-26 | IRS_CNY_CPI c866a688 | CVA_EU_RATE | ActiveBank EU | 10938652.89 | EUR | -1619042.59 | EUR | N | N | N | 2018-09-26 | TestData | ||
2018-09-26 | EQ_SPOT_Sony c8791bf6 | EQ_WAREHOU | ActiveBank US | 35271290.98 | EUR | -2181687.99 | EUR | N | N | N | 2018-09-26 | TestData | ||
2018-09-26 | SWAPTION_JPY.IBOR6M c8896ec0 | CVA_AP_RATE | ActiveBank US | 52720813.95 | EUR | 10177813.4 | EUR | N | N | N | 2018-09-26 | TestData | ||
2018-09-26 | EQ_SWAP_Cr_dit Agricole c89b077a | CVA_HG_EQ | ActiveBank UK | -87503147.64 | EUR | 6911885.66 | EUR | N | N | N | 2018-09-26 | TestData | ||
2018-09-26 | COM_FUT_dry-bulk route c8ada754 | CM_PREC_EX | ActiveBank EU | -77905376.84 | EUR | -12973904.23 | EUR | N | N | N | 2018-09-26 | TestData | ||
2018-09-26 | IRS_DKK.IBOR6M c8bf1930 | CVA_EU_RATE | ActiveBank EU | 61826432.15 | EUR | -3652040.68 | EUR | N | N | N | 2018-09-26 | TestData |
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.
To add a new field to an existing datastore, we will create a DatastoreConfiguratorConsumer
bean that will add a field to the existing
DatastoreConfig.TRADE_MAPPING_STORE_NAME
store.
@Configuration
public static class DatastoreCustomisations {
/**
* Add our custom column to the {@link DatastoreConfig#TRADE_MAPPING_STORE_NAME} store.
*/
@Qualifier(SP_QUALIFIER__CUSTOMISATIONS)
@Bean
public DatastoreConfiguratorConsumer datastoreConfiguratorConsumer(){
// Add our custom Store & Reference into the datastore schema:
return datastoreConfigurator -> datastoreConfigurator
.appendField(DatastoreConfig.TRADE_MAPPING_STORE_NAME, new CustomField("TestField", ILiteralType.STRING));
}
}
See the Datastore Helper documentation for more information on potential datastore modifications.
Step 2 - DLC Config
For this step, we will be using the Data Load Controller.
Next, we may have to update the topic configuration to expect our new column.
If no parser.columns
are set for the Topic Description, the columns of the file are coming implicitly from the columns of the Atoti table.
If the columns of the file still match the columns of the Atoti table, no other configuration is required.
In the case of Trade_Attributes
, the columns are being set explicitly so we must add the new column to the Trade_Attributes
topic by including it in the
dlc.csv-topics.Trade_Attributes.parser.columns
section of the application-dlc-in-memory.yaml
file as shown below:
dlc:
csv:
topics:
Trade_Attributes:
parser:
columns:
- AsOfDate
- TradeId
- Book
- LegalEntity
- Notional
- NotionalCcy
- PresentValue
- PVCcy
- ResidualRisk
- ExoticUnderlying
- OtherResidualRiskType
- TradeDate
- SensitivityScaleCategory
- RRAOCategory
- Inclusion
- TestField
channels:
- target: tradeAttributesPublisher
If you would like to specify the column names in Java, or if you need to include additional processing logic to handle the new column, you can do so by defining DLC Description beans. See DLC - Configuration for details on how to customize different aspects of the data loading.
Step 3 - 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
With DirectQuery, modifying CSV files is not applicable as DirectQuery uses a remote database in place of loading data from CSV files. However, if you would like to add a column to an existing Table with DirectQuery, then see Modifying Datastores - when using DirectQuery.
Suggested further reading
Enriching File Fields by Adding Column Calculators
Adding and Populating a New Store