This section explains how to get started using DirectQuery to connect to an external Database. For an overview of DirectQuery, please see the DirectQuery Overview section in the User Guide.Documentation Index
Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
Use this file to discover all available pages before exploring further.
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 or by exposing 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 thedirect-query Maven Profile to add the frtb-directquery
module into the classpath. This will need to be completed when building the JAR file.
application.yaml
The application.yaml file contains specific properties for enabling and connecting to a remote Database.| Property | Default Value | Description |
|---|---|---|
starter.deployment.type | in-memory | in-memory / direct-query / query-node selects the source of data |
directquery.database.type | N/A | Defines the Database to connect to |
Supported directquery.database.type
- “
clickhouse” for a ClickHouse database. - “
databricks” for a Databricks database. - “
mssql” for a Microsoft SQL database. - “
snowflake” for a Snowflake database.
Dates to Include Configuration
Thedirectquery.dates-filter.dates-to-include property is used to specify the dates to include in the DirectQuery data node. This property is required when
running DirectQuery.
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 |
Limitations
Given that the data nodes are distributed by AsOfDate, no two data nodes can contain the same Partition - meaning that one AsOfDate cannot be present in another node. So 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 as well as 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 will consist of the StandardisedApproachCube query node and the CombinedCube query node.The query node cannot have cubes that are not available in the data nodes.
| Property | Value | Description |
|---|---|---|
starter.deployment.type | query-node | Activates only the query cubes. |
starter.deployment.transport | netty | Turns on Netty messaging to communicate with nodes in other JVMs. |
dlc.enabled | false | The DLC must be disabled for query nodes as there is no Datastore. |
activeviam.distribution.gossip.router.enable | true | Enables the distribution gossip to allow the Nodes to communicate with each other. |
activeviam.distribution.gossip.router.port | 16484 | The port on which the gossip host will be started. |
ima.drc.disable | true | IMADRCCube cube must be disabled as it is not currently supported on DirectQuery. As an alternative to ima.drc.disable, you can use ima.disable to disable all IMA cubes. |
ima.plat-backtesting.disable | true | PLCube must be disabled as it is not currently supported on DirectQuery. |
ima.stress-calibration.disable | true | StressCalibrationCube must be disabled as it is not currently supported on DirectQuery. |
JVM With In-Memory Data Node
This JVM will contain the in-memory data node. Start the JVM with the following Spring and Atoti Server properties, either in a property file or as command-line arguments:| Property | Value | Description |
|---|---|---|
starter.deployment.type | direct-query | In memory cubes. |
starter.deployment.transport | netty | Turns on Netty messaging to communicate with nodes in other JVMs. |
dlc.enabled | true | We need the DLC to populate some of our in-memory datastores. |
ima.drc.disable | true | IMADRCCube cube must be disabled as it is not currently supported on DirectQuery. |
ima.plat-backtesting.disable | true | PLCube must be disabled as it is not currently supported on DirectQuery. |
ima.stress-calibration.disable | true | StressCalibrationCube must be disabled as it is not currently supported on DirectQuery. |
content-service.db.url | jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1 | The ContentServer will be managed by the query node, so we specify to use an in-memory ContentServer for this data node. |
content-service.db.hibernate.hbm2ddl.auto | create | Since we are using an in-memory ContentServer, we will allow Atoti Server to automatically generate the correct ContentServer. |
server.port | 8082 | The exact value does not matter. The port must simply be unique from the query node and other data nodes. |
directquery.database.type | snowflake or clickhouse etc | The type of Database to connect to. |
directquery.database.database_type.XXX | Various Database-specific properties | Any other Database-specific properties required. |
directquery.dates-filter.dates-to-exclude | Dates loaded in-memory | The AsOfDates that were included in the in-memory data node. When using distribution, two nodes of the same cube cannot contain the same date. |
Single JVM
can be run purely on DirectQuery data in a single JVM. By running in a single JVM, the application will only contain DirectQuery data. The configuration properties are as follows:| Property | Value | Description |
|---|---|---|
starter.deployment.type | direct-query | DirectQuery cubes. |
starter.deployment.transport | local | Local messages to feed the combined cube. |
ima.drc.disable | true | IMADRCCube cube must be disabled as it is not currently supported on DirectQuery. |
ima.plat-backtesting.disable | true | PLCube must be disabled as it is not currently supported on DirectQuery. |
ima.stress-calibration.disable | true | StressCalibrationCube must be disabled as it is not currently supported on DirectQuery. |
directquery.database.type | database_type | The type of Database to connect to. |
directquery.database.database_type.XXX | Various Database-specific properties | Any other Database-specific properties required. |
directquery.dates-filter | Filter the dates in the cube | Can limit the application to a subset of dates. If left blank, all dates will be include in the application. |
directquery.dates-filter.dates-to-exclude | Any optional dates to exclude | The provided dates will be excluded from the cube. |
directquery.dates-filter.dates-to-include | Limits dates to include | Only the provided dates will be included in the cube. |
Single JVM Limitations
Running with a single JVM means running purely on DirectQuery. This will come at the compromise of query performance of Trade-level queries, while also not having access to tools available exclusively in-memory, 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.