> ## 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. # Connecting to a database export const productName = "Atoti FRTB"; This section explains how to connect to a [natively supported Database](./index#natively-supported-databases). ## Overall Sequence To connect to a remote Database using DirectQuery you need to complete these steps: 1. Configure your remote Database in Atoti FRTB's expected [Database format](/database) 2. Set the Atoti FRTB configuration properties 3. 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](/database) documentation or by exposing Views on your Database in the same expected [Database](/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: ```properties theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} ima.drc.disable=true ima.plat-backtesting.disable=true ima.stress-calibration.disable=true ``` ##### Command-line argument: ```properties theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} -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. ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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.username` The username associated with the database. #### password Full property name: `directquery.database.clickhouse.password` The password associated with the username. #### port Full property name: `directquery.database.clickhouse.port` The port where the ClickHouse instance is located. #### hostName Full property name: `directquery.database.clickhouse.hostName` The name of the host where the ClickHouse application is running. #### database Full property name: `directquery.database.clickhouse.database` The name of the ClickHouse Database. #### schema Full property name: `directquery.database.clickhouse.schema` The name of the ClickHouse Schema to use. ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} 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.timeTravelPolicy` The Time Travel Policy in Databricks allows previous versions of the data tables to be queried. #### connectionString Full property name: `directquery.database.databricks.connectionString` This is the JDBC Driver connection string which points to the Databricks Database. To get Connection String Details, follow the instructions below: 1. Login to your Databricks workspace and go to you Databricks cluster under compute. 2. Click on Advanced Properties and click on the JDBC/ODBC Driver Tab. 3. 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](https://learn.microsoft.com/en-us/azure/databricks/integrations/jdbc-odbc-bi) Azure Databricks documentation. #### heavyLoadConnectionString Full property name: `directquery.database.databricks.heavyLoadConnectionString` The 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.database` The Databricks Database we will be connecting to. #### schema Full property name: `directquery.database.databricks.schema` The Schema within the specified Database to use. This Schema should match {productName}'s expected [Database Structure](../../../database/index) either by having the same Table structure or through the use of views. ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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. #### hostName 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.database` The Microsoft SQL Database we will be connecting to. #### schema Full property name: `directquery.database.mssql.schema` The Schema within the specified Database to use. This Schema should match {productName}'s expected [Database Structure](../../../database/index) either by having the same Table structure or through the use of views. ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 password: YOUR_PASSWORD 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.connectionString` This 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](https://docs.snowflake.com/en/user-guide/client-redirect.html#jdbc-driver) Snowflake documentation. #### role Full property name: `directquery.database.snowflake.role` The role you want to grant {productName} when it runs queries on the remote database. #### warehouse Full property name: `directquery.database.snowflake.warehouse` The warehouse is where our queries and aggregation will take place. You can view available warehouses through the [SHOW WAREHOUSES](https://docs.snowflake.com/en/sql-reference/sql/show-warehouses.) command. #### database Full property name: `directquery.database.snowflake.database` The Snowflake Database we will be connecting to. #### schema Full property name: `directquery.database.snowflake.schema` The Schema within the specified Database to use. This Schema should match {productName}'s expected [Database Structure](../../../database/index) either by having the same Table structure or through the use of views. ## 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](#horizontal-distribution)
Run purely on DirectQuery remote data In a [Single JVM](#single-jvm) on DirectQuery only
### Dates to Include Filter Configuration 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](/dev/dev-direct-query/customization-and-internals/dates-filter-bean) 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 consists of the StandardisedApproachCube query node and the CombinedCube query node. Start the JVM by specifying the following parameters to the {productName} application, either in a .properties file or through command-line arguments (add `-D` before each property). ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 {productName} application, either in a .properties file or through command-line arguments (add `-D` before each property). ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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). ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 ``` ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 ``` ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 ``` ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 password: database_password 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 we will only need to configure the [Database Properties](#database-configuration) and [Disable Unsupported IMA Cubes](#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: ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 ``` ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 ``` ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 ``` ```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}} # 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 password: database_password 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 By running with a single JVM we will be running purely on DirectQuery. This will come at the compromise of query performance of Trade level queries while also not having access to in-memory only tools such as Atoti WhatIf and Atoti SignOff. Running both an in-memory data node and DirectQuery data node under a single JVM is not currently supported. ## Reference Database {productName} ships with an example ClickHouse database that can be used for testing. This database runs inside a docker container. It is located within the directory: `frtb-directquery/src/test/resources/databases/clickhouse/clickhouse_docker_container/`. You can run the docker container using the utility scripts located in the directory: `/scripts/`. {productName} 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/`. {productName} ships with an example MSSQL database that can be used for testing. This database runs inside a docker container. It is located within the directory: `frtb-directquery/src/test/resources/databases/mssql/mssql_docker_container/`. You can run the docker container using the utility scripts located in the directory: `/scripts/`. {productName} 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/`.