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 New Cube Hierarchies

Adding New Cube Measures

Adding and Populating a New Store