Connecting to a database

This section explains how to connect to a natively supported Database.

Overall sequence

To connect to a remote database using DirectQuery you need to complete these steps:

Configure your remote database in Atoti FRTB’s expected Database format
Set the Atoti FRTB configuration properties
Deploy Atoti FRTB in Horizontal Distribution

Database schema

Your remote Database must be configured in the same format as Atoti FRTB’s in-memory datastores. This means the same tables and field types will need to be replicated. You can do this by defining the tables as outlined in the Database documentation. Alternatively, you can expose views on your database in the same expected Database format.

All of the database tables must be present either as actual tables or views on your connected database.

Required DirectQuery properties

The following properties must be configured to get started with DirectQuery.

Maven profile

The frtb-directquery module is not included in the classpath by default. You will need to enable the direct-query Maven profile to add the frtb-directquery module into the classpath. This will need to be completed when building the JAR file.

Disable unsupported IMA cubes

For this preview implementation, only SA is supported when running with DirectQuery. To disable IMA, add the following property either to the frtb.properties file or as a command-line argument with the prefix -D as shown:

application.properties:

ima.drc.disable=true
ima.plat-backtesting.disable=true
ima.stress-calibration.disable=true

Command-line argument:

-Dima.drc.disable=true
-Dima.plat-backtesting.disable=true
-Dima.stress-calibration.disable=true

Database configuration

The application.yaml file contains specific properties for enabling and connecting to a remote database.

ClickHouse
Databricks
MSSQL
Snowflake

# Enable DirectQuery
starter:
  deployment:
    type: direct-query

# ClickHouse Database Connection Parameters
directquery:
  database:
    type: clickhouse
    clickhouse:
      username: CLICKHOUSE_USERNAME
      password: CLICKHOUSE_PASSWORD
      port: PORT
      hostName: HOST
      database: DATABASE
      schema: DATABASE_SCHEMA

The above properties populate the ClickhouseSpringProperties java class.

username

Full property name: directquery.database.clickhouse.usernameThe username associated with the database.

password

Full property name: directquery.database.clickhouse.passwordThe password associated with the username.

port

Full property name: directquery.database.clickhouse.portThe port where the ClickHouse instance is located.

hostName

Full property name: directquery.database.clickhouse.hostNameThe name of the host where the ClickHouse application is running.

database

Full property name: directquery.database.clickhouse.databaseThe name of the ClickHouse Database.

schema

Full property name: directquery.database.clickhouse.schemaThe name of the ClickHouse Schema to use.

starter:
  deployment:
    type: direct-query
# Databricks Database Connection Parameters
directquery:
  database:
    type: databricks
    databricks:
      timeTravelPolicy: DISABLED/STRICT/LAX/LIGHT
      connectionString: DATABRICKS_CONNECTION_STRING
      heavyLoadConnectionString: DATABRICKS_HEAVY_LOAD_CONNECTION_STRING
      database: DATABRICKS_DATABASE
      schema: DATABRICKS_SCHEMA

The above properties populate the DatabricksSpringProperties java class.

timeTravelPolicy

Full property name: directquery.database.databricks.timeTravelPolicyThe Time Travel Policy in Databricks allows previous versions of the data tables to be queried.

connectionString

Full property name: directquery.database.databricks.connectionStringThis is the JDBC Driver connection string which points to the Databricks Database. To get Connection String Details, follow the instructions below:

Log in to your Databricks workspace and go to your Databricks cluster under compute.
Click on Advanced Properties and click on the JDBC/ODBC Driver Tab.
Copy the JDBC string and replace the access token for the password.

For more details on how to find your JDBC Connection String, see the Databricks JDBC/ODBC Driver Azure Databricks documentation.

heavyLoadConnectionString

Full property name: directquery.database.databricks.heavyLoadConnectionStringThe heavy load connection string is for pre-populating the aggregate providers. If no heavy load connection string is provided, then the connection string will be used by default.

database

Full property name: directquery.database.databricks.databaseThe Databricks Database to connect to.

schema

Full property name: directquery.database.databricks.schemaThe Schema within the specified Database to use. This Schema should match Atoti FRTB‘s expected Database Structure either by having the same Table structure or through the use of views.

# Enable DirectQuery
starter:
  deployment:
    type: direct-query

# Microsoft SQL Database Connection Parameters
directquery:
  database:
    type: mssql
    mssql:
      username: YOUR_USERNAME
      password: YOUR_PASSWORD
      port: PORT_MSSQL_DATABASE_IS_ON
      hostName: HOST_OF_THE_MSSQL_DATABASE
      database: YOUR_DATABASE_NAME
      schema: YOUR_DATABASE_SCHEMA
      encrypt: true/false
      trustServerCertificate: true/false
      connectRetryCount: integer

The above properties populate the MSSQLSpringProperties java class.

port

The port of where the MSSQL database is exposed. For example, “1433”.

hostName

Hostname of where the MSSQL database is running. For example, when running locally we can specify “localhost”.

database

Full property name: directquery.database.mssql.databaseThe Microsoft SQL Database to connect to.

schema

Full property name: directquery.database.mssql.schemaThe Schema within the specified Database to use. This Schema should match Atoti FRTB‘s expected Database Structure either by having the same Table structure or through the use of views.

# Enable DirectQuery
starter:
  deployment:
    type: direct-query

# Snowflake Database Connection Parameters
directquery:
  database:
    type: snowflake
    snowflake:
      connectionString: YOUR_JDBC_SNOWFLAKE_CONNECTION_STRING
      username: YOUR_USERNAME
      additional-options:
        private-key-base64: YOUR_BASE64_ENCODED_PRIVATE_KEY
      role: ROLE_TO_USE
      warehouse: YOUR_WAREHOUSE
      database: YOUR_DATABASE
      schema: YOUR_SCHEMA

The above properties populate the SnowflakeSpringProperties java class.

connectionString

Full property name: directquery.database.snowflake.connectionStringThis is the JDBC Driver connection string which points to the Snowflake Database. for details on how to find your JDBC Connection String, see the JDBC Driver Snowflake documentation.

username

Full property name: directquery.database.snowflake.usernameThe username used to authenticate the connection to Snowflake.

additional-options

Full property name: directquery.database.snowflake.additional-optionsA map of additional connection options passed to the Snowflake driver, such as the key-pair authentication credentials.

private-key-base64

Full property name: directquery.database.snowflake.additional-options.private-key-base64The base64-encoded private key used for Snowflake key-pair authentication. Snowflake is deprecating single-factor password authentication, so Atoti FRTB authenticates to Snowflake with a key pair instead of a password. Provide the private key as a base64-encoded string.

role

Full property name: directquery.database.snowflake.roleThe role you want to grant Atoti FRTB when it runs queries on the remote database.

warehouse

Full property name: directquery.database.snowflake.warehouseThe warehouse is where queries and aggregation take place. You can view available warehouses through the SHOW WAREHOUSES command.

database

Full property name: directquery.database.snowflake.databaseThe Snowflake Database to connect to.

schema

Full property name: directquery.database.snowflake.schemaThe Schema within the specified Database to use. This Schema should match Atoti FRTB‘s expected Database Structure either by having the same Table structure or through the use of views.

Snowflake key-pair authentication

Snowflake is deprecating single-factor password authentication. Atoti FRTB therefore connects to Snowflake using key-pair authentication instead of a password. The base64-encoded private key is supplied through the directquery.database.snowflake.additional-options.private-key-base64 property.

Set up the key pair

Generate an unencrypted PKCS#8 RSA private key and its matching public key. The following commands create the rsa_key.p8 private key and the rsa_key.pub public key in the current directory.
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Register the public key on the Snowflake user. Open rsa_key.pub and copy the key value. Strip the PEM header, the PEM footer, and all line breaks from the copied value. Run the following statement under a role that can alter the user, such as SECURITYADMIN.
ALTER USER <SNOWFLAKE_USERNAME> SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0...';
Base64-encode the private key and expose it to Atoti FRTB. The following command encodes the entire rsa_key.p8 file. The resulting value feeds the directquery.database.snowflake.additional-options.private-key-base64 property.
export SNOWFLAKE_PRIVATE_KEY_BASE64=$(base64 < rsa_key.p8 | tr -d '\n')

Keep the private key secret. Provide it only through the SNOWFLAKE_PRIVATE_KEY_BASE64 environment variable or a secret store, never in source control. To rotate the key, generate a new key pair, register the new public key on the Snowflake user, then update the environment variable with the new base64-encoded private key.

Deployment options

We provide two main options to run with DirectQuery:

Option	How to run Atoti FRTB
Operate with some data loaded in-memory and the rest available through DirectQuery	In Horizontal Distribution with in-memory database
Run purely on DirectQuery remote data	In a Single JVM on DirectQuery only

Dates to include filter configuration

The data nodes are distributed by AsOfDate, so no two data nodes can contain the same partition. This means that one AsOfDate cannot be present in another node. To prevent any issues, the directQueryDatesToIncludeFilter bean is used to set which dates to include in the DirectQuery data node.

This property must be provided with the same values to both data nodes.

Horizontal distribution

In Horizontal Distribution you have access to the in-memory tools such as What-If, data updates and Sign-Off for the data loaded in-memory. You also have access to a large number of historical dates. When running in Horizontal Distribution you need to run three nodes:

Query node
In-memory data node
DirectQuery data node

JVM With Query Node

This JVM consists of the StandardisedApproachCube query node and the CombinedCube query node. Start the JVM by specifying the following parameters to the Atoti FRTB application, either in a .properties file or through command-line arguments (add -D before each property).

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a query node
starter:
  deployment:
    type: query-node
    transport: netty
activeviam:
  distribution:
    gossip:
      router:
        enable: true
        port: 16484

JVM With In-Memory Data Node

This JVM consists of the in-memory data node only. Start the JVM by specifying the following parameters to the Atoti FRTB application, either in a .properties file or through command-line arguments (add -D before each property).

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a query node and data node
starter:
  deployment:
    type: in-memory
    transport: netty

# Use a different port than the other JVMs
server:
  port: 8081

# Use an in-memory Content Server for this JVM
content-service:
  db:
    url: jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1
    hibernate:
      hbm2ddl:
        auto: create

JVM With DirectQuery Data Node

Once the JVM with the query and in-memory data node is running, you can start a second JVM with the DirectQuery data node with the following configuration properties either in a .properties file or through command-line arguments (add -D before each property).

ClickHouse
Databricks
MSSQL
Snowflake

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: netty

# Use a different port than the other JVMs
server:
  port: 8082

# Use an in-memory Content Server for this JVM
content-service:
  db:
    url: jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1
    hibernate:
      hbm2ddl:
        auto: create

# Enable and configure the ClickHouse DirectQuery Database
directquery:
  database:
    type: clickhouse
    clickhouse:
      username: database_username
      password: database_password
      port: port
      hostName: host_name
      database: database
      schema: database_schema

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: netty

# Use a different port than the other JVMs
server:
  port: 8082

# Use an in-memory Content Server for this JVM
content-service:
  db:
    url: jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1
    hibernate:
      hbm2ddl:
        auto: create

# Enable and configure the Databricks DirectQuery Database
directquery:
  database:
    type: databricks
    databricks:
      timeTravelPolicy: DISABLED/STRICT/LAX/LIGHT
      connectionString: databricks_connection_string
      heavyLoadingConnectionString: databricks_connection_string
      database: databricks_database
      schema: databricks_database_schema

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: netty

# Use a different port than the other JVMs
server:
  port: 8082

# Use an in-memory Content Server for this JVM
content-service:
  db:
    url: jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1
    hibernate:
      hbm2ddl:
        auto: create

# Enable and configure the Microsoft SQL DirectQuery Database
directquery:
  database:
    type: mssql
    mssql:
      username: YOUR_USERNAME
      password: YOUR_PASSWORD
      port: PORT_MSSQL_DATABASE_IS_ON
      hostName: HOST_OF_THE_MSSQL_DATABASE
      database: YOUR_DATABASE_NAME
      schema: YOUR_DATABASE_SCHEMA
      encrypt: true/false
      trustServerCertificate: true/false
      connectRetryCount: integer

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: netty

# Use a different port than the other JVMs
server:
  port: 8082

# Use an in-memory Content Server for this JVM
content-service:
  db:
    url: jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1
    hibernate:
      hbm2ddl:
        auto: create

# Enable and configure the Snowflake DirectQuery Database
directquery:
  database:
    type: snowflake
    snowflake:
      username: database_username
      additional-options:
        private-key-base64: base64_encoded_private_key
      role: role_app_will_use
      connectionString: connection_string
      warehouse: snowflake_warehouse
      database: snowflake_database
      schema: snowflake_database_schema
      arrayAggWrapperFunctionName: specific_array_aggregation_function

Single JVM

You can run a single JVM consisting of only DirectQuery. By running in a single JVM with DirectQuery only, you can now see the DirectQuery data in the StandardisedApproachCube.

Properties

To run the Single JVM node, only configure the Database Properties and Disable Unsupported IMA Cubes and run the application as normal. There are no distributed nodes to configure when running in a single JVM. Here is an example of the properties to use:

ClickHouse
Databricks
MSSQL
Snowflake

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: local

# Enable and configure the ClickHouse DirectQuery Database
directquery:
  database:
    type: clickhouse
    clickhouse:
      username: database_username
      password: database_password
      port: port
      hostName: host_name
      database: database
      schema: database_schema

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: local

# Enable and configure the Databricks DirectQuery Database
directquery:
  database:
    type: databricks
    databricks:
      timeTravelPolicy: time_travel_policy
      connectionString: connection_string
      heavyLoadingConnectionString: heavy_loading_connection_string
      database: databricks_database
      schema: databricks_schema

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: local

# Enable and configure the Microsoft SQL DirectQuery Database
directquery:
  database:
    type: mssql
    mssql:
      username: YOUR_USERNAME
      password: YOUR_PASSWORD
      port: PORT_MSSQL_DATABASE_IS_ON
      hostName: HOST_OF_THE_MSSQL_DATABASE
      database: YOUR_DATABASE_NAME
      schema: YOUR_DATABASE_SCHEMA
      encrypt: true/false
      trustServerCertificate: true/false
      connectRetryCount: integer

# Disable unsupported IMA cubes
ima:
  drc.disable: true
  plat-backtesting.disable: true
  stress-calibration.disable: true

# Set {productName} to run as a data node and to connect to the second JVM
starter:
  deployment:
    type: direct-query
    transport: local

# Enable and configure the Snowflake DirectQuery Database
directquery:
  database:
    type: snowflake
    snowflake:
      username: database_username
      additional-options:
        private-key-base64: base64_encoded_private_key
      role: role_app_will_use
      connectionString: connection_string
      warehouse: snowflake_warehouse
      database: snowflake_database
      schema: snowflake_database_schema
      arrayAggWrapperFunctionName: specific_array_aggregation_function

Single JVM limitations

Running with a single JVM runs purely on DirectQuery. This comes at the compromise of query performance of Trade level queries while also not having access to in-memory only tools such as Atoti What-If and Atoti Sign-Off.

Running both an in-memory data node and DirectQuery data node under a single JVM is not currently supported.

Reference database

ClickHouse
Databricks
MSSQL
Snowflake

Atoti FRTB ships with the SQL and data needed to initialize a ClickHouse database that can be used for testing. The table-creation script and reference data are located within the directory: frtb-application-tests/src/test/resources/db/liquibase/clickhouse/. The DirectQuery tests start the database in a Docker container through Testcontainers.

Atoti FRTB ships with the SQL and data needed to initialize a Databricks database that can be used for testing. The SQL scripts and reference data are located within the directory: frtb-directquery/src/test/resources/databases/databricks/.

Atoti FRTB ships with the SQL and data needed to initialize an MSSQL database that can be used for testing. The table-creation script and reference data are located within the directory: frtb-application-tests/src/test/resources/db/liquibase/mssql/. The DirectQuery tests start the database in a Docker container through Testcontainers.

Atoti FRTB ships with the SQL and data needed to initialize a Snowflake database that can be used for testing. The SQL scripts and reference data are located within the directory: frtb-directquery/src/test/resources/databases/snowflake/.

​Overall sequence

​Database schema

​Required DirectQuery properties

​Maven profile

​Disable unsupported IMA cubes

application.properties:

Command-line argument:

​Database configuration

​username

​password

​port

​hostName

​database

​schema

​timeTravelPolicy

​connectionString

​heavyLoadConnectionString

​database

​schema

​port

​hostName

​database

​schema

​connectionString

​username

​additional-options

​private-key-base64

​role

​warehouse

​database

​schema

​Snowflake key-pair authentication

​Set up the key pair

​Deployment options

​Dates to include filter configuration

​Horizontal distribution

​JVM With Query Node

​JVM With In-Memory Data Node

​JVM With DirectQuery Data Node

​Single JVM

​Properties

​Single JVM limitations

​Reference database

Overall sequence

Database schema

Required DirectQuery properties

Maven profile

Disable unsupported IMA cubes

Database configuration

username

password

port

hostName

database

schema

timeTravelPolicy

connectionString

heavyLoadConnectionString

database

schema

port

hostName

database

schema

connectionString

username

additional-options

private-key-base64

role

warehouse

database

schema

Snowflake key-pair authentication

Set up the key pair

Deployment options

Dates to include filter configuration

Horizontal distribution

JVM With Query Node

JVM With In-Memory Data Node

JVM With DirectQuery Data Node

Single JVM

Properties

Single JVM limitations

Reference database