Migration helper - Atoti Hub

The migration helper is a set of REST endpoints that automate the one-off task of seeding a DirectQuery database from an existing in-memory Atoti Market Risk deployment.

The migration helper requires an in-memory data cube. It reads rows directly from the in-memory IDatastore and uses the runtime IActivePivotManagerDescription to generate schema-compatible DDL. It is therefore only available when the application starts with starter.deployment.type=in-memory (the default). It is intentionally not registered when:

starter.deployment.type=direct-query: there is no in-memory dataset to extract.
starter.deployment.type=query-node: query-only nodes do not host a data cube.

Typical workflow

Start Atoti Market Risk in in-memory mode and load the dataset you want to migrate.
Call the migration helper endpoints to produce the DDL and CSV files (see below).
Apply the DDL to your target database and load the CSV files.
Restart Atoti Market Risk with the DirectQuery profile pointing at the now-populated database (see Getting started).

Endpoints

The controller is mounted at host:port/mr-application/migrationHelper/ and exposes:

Endpoint	Method	Purpose
`/migrationHelper/sqlSchema`	`GET`	Generates SQL DDL statements for the configured datastore schema. Query parameters: `database` (`SNOWFLAKE`, `MSSQL`, `DATABRICKS`, defaults to `SNOWFLAKE`), `databaseName`, `schema`.
`/migrationHelper/extract`	`POST`	Returns the DDL for a `DatabaseExtractionDTO` body. No CSVs are written.
`/migrationHelper/extractForDatabase/{database}`	`POST`	Full extraction: writes CSV files under the requested `outputDirectory` and returns the DDL.

Names are mapped through MrDirectQueryNameMapper, so tables and columns in the generated DDL follow the same convention as the runtime DirectQuery schema (e.g. TradePnLs → TRADEPNLS, not TRADE_PN_LS). The following stores are excluded from the generated schema by default (see MRMigrationHelperRestController.getDefaultExtractionBuilder()):

Parent-child stores: BookParentChild, CounterpartyParentChild, LegalEntityParentChild: these are used to populate their hierarchy stores and are not needed in the DQ schema.
Summary cube base stores: BaseStore, SensiBaseStore, PnLBaseStore: summary cubes are not supported under DirectQuery.
The What-If simulation persistence table.
Atoti Sign-Off companion stores for TradePnLs, TradeSensitivities, PnL.

To customise the set of exported stores, subclass MRMigrationHelperRestController and override getDefaultExtractionBuilder().

POST /extract and POST /extractForDatabase/{database} declare consumes = application/json and produces = text/plain;charset=UTF-8. REST clients must send Content-Type: application/json; calls without this header will fail with HTTP 415 Unsupported Media Type.

Offline DDL generation

GET /migrationHelper/sqlSchema returns the DDL without opening a database connection or extracting data. It is useful to hand the schema to a DBA, to review the generated SQL in pull requests, or to bootstrap an environment where the target database is not yet provisioned.

curl "http://localhost:10010/mr-application/migrationHelper/sqlSchema?database=SNOWFLAKE&databaseName=MR_DB&schema=PUBLIC"

If databaseName or schema are omitted, the controller falls back to the Spring properties directquery.database.<type>.database and directquery.database.<type>.schema (<type> being snowflake, mssql, or databricks), then to spring.application.name, and finally to the constants returned by getDatabaseName() / getSchemaName() on the controller. For Atoti Market Risk these resolve to MR_DATABASE and MR_SCHEMA (see MRMigrationHelperRestController). MSSQL’s schema property additionally has a built-in default of dbo; for Snowflake and Databricks, the corresponding Spring properties have no Java-side default: PUBLIC (Snowflake) and default (Databricks) are JDBC connection-level defaults applied by the database server, not Spring fallbacks.

Vector emulation modes

Atoti Market Risk contains four vector tables: TradePnLs, TradeSensitivities, SensiLadders, and MarketShifts. For each table you can choose how the migrator represents the vector fields in the target database:

Mode	Behavior	Use when
`ROW` (default for databases without native vectors)	behavior table cells reference ‘Behaviour’ header; flag the ‘autoRowSameTable’/‘vectorStoreBehaviours’ are code keys so leave them into a separate `_VECTOR` table joined on the base table’s primary key.	The base table contains many scalar attributes alongside the vector(s).
`ROW_SAME_TABLE`	Vector fields stay inline in the original table with an index column added. No separate vector table or join is created.	The table contains only key fields and vector fields (a separate vector table would be redundant).
`COLUMN`	Each vector is expanded into a fixed number of scalar columns. When generating DDL the emitter uses a `<fieldName>_<index>` naming scheme (e.g. `PnLVector` of size 500 becomes `PnLVector_0`, …, `PnLVector_499`); discovery is lax and accepts other layouts (any non-empty separator, any contiguous integer range), so an existing database does not have to match this scheme exactly.	Vector size is known and bounded, and you prefer columnar access patterns over a vector type or join.

Modes can be selected at runtime through the REST endpoint, or programmatically on the Migrator bean:

Per-store, via REST

POST /migrationHelper/extract with body:

{
  "databaseType": "SNOWFLAKE",
  "vectorStoreBehaviours": {
    "MarketShifts": "ROW_SAME_TABLE",
    "SensiLadders": "COLUMN"
  },
  "columnVectorSizes": {
    "SensiLadders": { "Sensitivities": 1000 }
  }
}

The autoRowSameTable flag opts every key-only-plus-vector table into ROW_SAME_TABLE automatically, useful when many of your stores fit that shape. Per-store entries in vectorStoreBehaviours always win over autoRowSameTable.

Per-store, on the `Migrator` bean

migrator.vectorsTableBehaviour(MARKET_SHIFT_STORE_NAME, VectorType.ROW_SAME_TABLE);
migrator.vectorsTableBehaviour(SENSI_LADDER_STORE,      VectorType.COLUMN);
// Or, in one call:
migrator.autoRowSameTable(true);

For COLUMN mode, schema discovery picks up the columns that actually exist in the live database and adjusts the in-memory schema accordingly, so existing tables continue to work regardless of the size configured here.The directquery.schema.default-vector-size property (default 250) and the columnVectorSizes extraction-DTO field are only consulted when discovery is skipped or cannot determine the size, typically during DDL generation via GET /migrationHelper/sqlSchema, where they decide how many indexed columns the freshly generated DDL contains. Once the DDL is applied and Atoti Market Risk starts against the populated database, discovery takes over and the property no longer has any effect on the column count.

To change the default used for DDL generation:

directquery:
  schema:
    default-vector-size: 500

Configuration properties

Property	Default	Description
`mr.migration.export.enabled`	`true`	Registers the `MRMigrationHelperRestController`. Set to `false` to remove the endpoints from deployments that do not need them.
`mr.migration.export.base-dir`	`java.io.tmpdir`	Allowed base directory for CSV exports. Any `outputDirectory` requested by `POST /migrationHelper/extract*` that resolves outside this path (after normalization) is rejected with a runtime exception.

Example:

mr:
  migration:
    export:
      enabled: true
      base-dir: /data/mr-exports

​Typical workflow

​Endpoints

​Offline DDL generation

​Vector emulation modes

​Per-store, via REST

​Per-store, on the Migrator bean

​Configuration properties

Typical workflow

Endpoints

Offline DDL generation

Vector emulation modes

Per-store, via REST

Per-store, on the `Migrator` bean

Configuration properties