Caching Remote Tables

This explains what a cached remote table is and how to configure a remote database table to be cached locally.

Overview

Caching a table locally takes a copy of a remote database table and stores it in an InMemory datastore store.

This is useful for tables where you want to perform GetByKey queries or datastore lookups. Essentially, if you need to retrieve specific facts (that is, rows) from the table, then it will be more performant to cache the table InMemory.

Out of the box, the FXRates table is cached locally. This allows us to perform GetByKey queries in PostProcessors without any performance penalty of having to query the remote database and send the results for a single value back to Atoti.

How it works

In the ADirectQueryDatabaseSchema we define tables that we want to cache locally. The FXRates is one of these by default. When the application starts we create an InMemory datastore store of the same name as the remote table. We then rename the remote table (the name Atoti uses to reference the remote table) to contain a suffix of “CACHE_SOURCE”. So our remote FXRates table will be renamed to “FXRates_CACHE_SOURCE” and we will have an InMemory datastore store “FXRates”. We then run a complete list query on the remote table “FXRates_CACHE_SOURCE” and write all the results into the InMemory “FXRates” store.

All existing references to the “FXRates” store (for datastore lookups or use in measures) within the application will query the InMemory store rather than the remote one.

This same process can be applied to any side store.

Defining Cached Table

To define a cached table we must register it as a table to cache InMemory and expose the table as a bean with a qualifier of SideStoresToCache.

Registering the Table to Cache

First we must define our table in our datastore description. See Create Datastore Definition on how to add new Tables/Stores to the application.

Once our store is defined in our datastore configuration, we can add this Table as a table we want to cache locally. This is done in an implementation of ADirectQueryDatabaseSchema. To add our Table we can simply extend one of the classes that extends ADirectQueryDatabaseSchema such as DirectQueryDatabaseConfig and override the getTablesToCacheLocally() method and mark our class as primary with the @Primary annotation.

We also need to mark our method as a Bean with the Qualifier set to SideStoresToCache. This will register these tables and our DirectQueryInitialDataLoadConfig will populate the InMemory stores with the contents of the remote tables.

Furthermore, when converting our datastore store description into a database Table description, we need to tell the migrator that this is a store to cache locally InMemory. We need to tell the migrator this is a table to cache locally, so it can set the database Table’s name to include “_CACHE_SOURCE” as well as mark the datastore store as an InMemory store to use.

This will leave us with our custom class defining the table to cache locally:

@Primary
public class CustomCachedTableClickHouseDatabaseConfig extends DirectQueryDatabaseConfig {

    @Bean
    @Qualifier("SideStoresToCache")
	@Override
	public List<Table> getTablesToCacheLocally(){
		// Get all default tables from the parent
		List<Table> existingTables = super.getTablesToCacheLocally();

		// Add the table we want to cache locally:
		existingTables.add(getMyCustomTable());

		return existingTables;
	}

    // Migrate from datastore description to database Table definition
	public Table getMyCustomTable(){
		return migrator.migrateToDirectQueryTable(
				getStoreDescriptionFor(
						"MyCustomTable"
				),
				true    // Specifying that this is a table to cache locally
		);
	}
}

Conclusion

Now you can query your cached table in measures just as you would with any other Atoti InMemory datastore store. We can validate that our table was cached by taking a look in the AdminUI. We should see that we have one store with the “_CACHE_SOURCE” suffix - this is the remote table. The InMemory table is the one without the suffix.

Here we can see the Table “MyCustomTable” as both an InMemory store (named “MyCustomTable”) and as a remote DirectQuery database Table (named “MyCustomTable_CACHE_SOURCE”). This can also be seen for the FXRates store. Both these stores will contain the same data.

Application Refresh

When the Application is Refreshed through the DirectQuery REST Service, the InMemory Stores will be completely re-populated with the content of the remote Tables.