Snowflake Database

This section explains how to connect to a Snowflake Database.

Overall Sequence

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

1. Configure your remote Database in Atoti Market Risk's expected Database format
2. Set Atoti Market Risk configuration properties
3. Deploy Atoti Market Risk in Horizontal Distribution

Database Schema

Required DirectQuery Properties

Disable Other Cubes

application.yaml:

configuration.cubes.enable.<cube>=false

Command-line argument:

-Dconfiguration.cubes.enable.<cube>=false

Snowflake Configuration

# Snowflake Database Connection Parameters
directquery:
  enabled: false
  database:
    type: snowflake
    snowflake:
      connectionString: YOUR_JDBC_SNOWFLAKE_CONNECTION_STRING
      username: YOUR_USERNAME
      password: YOUR_PASSWORD
      warehouse: YOUR_WAREHOUSE
      database: YOUR_DATABASE
      schema: YOUR_SCHEMA
      role: YOUR_ROLE

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 Snowflake documentation.

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 command.

database

Full property name: directquery.database.snowflake.database

The Snowflake Database you will be connecting to.

schema

Full property name: directquery.database.snowflake.schema

The Schema within the specified Database to use. This Schema should match Atoti Market Risk’s expected Database Structure either by having the same Table structure or through the use of views.

role

Full property name: directquery.database.snowflake.role

The Role with which the specified Database is used. This Role allows request and use of the specified Warehouse on the specified Schema.

Deployment Options

We provide two main options to run with DirectQuery:

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 two Nodes:

• Combined query and in-memory data node
• DirectQuery data node

initial-load.business-dates

For both Combined in-memory query node and DirectQuery data node, set the initial-load.business-dates property located inside mr.properties.

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 initial-load.business-dates property is used to set which dates to load into the in-memory data node, while also specifying to the DirectQuery data node which AsOfDates to exclude.

JVM With Query Node And In-Memory Data Node

This JVM consists of two distributed nodes; a query node and an in-memory data node.

Start the JVM by specifying the following parameters to the Atoti Market Risk application, either in a .properties file or through command-line arguments (add -D before each property).

See Activating and de-activating cubes for details about the enabling/disabling of cubes.

# Disable cubes
configuration.cubes.enable.<cube>: false

# Set MR to run this JVM with a query node and in-memory data node
spring.profiles.active=dist-data-node,dist-query-node

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).

# Set MR to run as a data node and to connect to the second JVM
spring.profiles.active=dist-data-node

# Use a different port from the second JVM
server.port=10011

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

# Disable cubes
configuration.cubes.enable.<cube>: false

# Enable and configure the Snowflake DirectQuery Database
directquery.enabled=true
directquery.database.type=database_type
directquery.database.snowflake.username=database_username
directquery.database.snowflake.password=database_password
directquery.database.snowflake.connectionString=connection_string
directquery.database.snowflake.warehouse=snowflake_warehouse
directquery.database.snowflake.database=snowflake_database
directquery.database.snowflake.schema=snowflake_database_schema

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 VaR/ES cube.

Properties

To run the Single JVM node you only need to configure the Snowflake Properties and disable 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:

See Activating and de-activating cubes for details about the enabling/disabling of cubes.

# Disable cubes
configuration.cubes.enable.<cube>: false

# Enable and configure the Snowflake DirectQuery Database
directquery.enabled=true
directquery.database.type=database_type
directquery.database.snowflake.username=database_username
directquery.database.snowflake.password=database_password
directquery.database.snowflake.connectionString=connection_string
directquery.database.snowflake.warehouse=snowflake_warehouse
directquery.database.snowflake.database=snowflake_database
directquery.database.snowflake.schema=snowflake_database_schema

Single JVM Limitations

By running DirectQuery with a single JVM you 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 WhatIf and SignOff.