Using DirectQuery - Atoti Hub

This feature is not part of the community edition: it needs to be unlocked.

This will walk you through the basics of DirectQuery by creating an application on Snowflake. We’ll see how to:

Connect to an external database
Create a data model based on this external database
Use this data model in a cube

>>> import atoti as tt
>>> session = tt.Session.start()  

Connecting to the external database

Once we have a session, we can connect to the external database:

>>> import os

>>> from atoti_directquery_snowflake import ConnectionConfig

>>> connection_config = ConnectionConfig(
...     url=f"jdbc:snowflake://{os.environ['SNOWFLAKE_ACCOUNT_IDENTIFIER']}.snowflakecomputing.com/?user={os.environ['SNOWFLAKE_USERNAME']}&database=TEST_RESOURCES&schema=TESTS",
...     password=os.environ["SNOWFLAKE_PASSWORD"],
... )
>>> external_database = session.connect_to_external_database(connection_config)

The choice of the type of connection is what differs between the various supported databases. For example, you can switch to atoti_directquery_clickhouse.ConnectionConfig to connect to Clickhouse instead of Snowflake.

Discovering the external tables

We can list the various tables available on the Snowflake server:

>>> # The method below is private and susceptible to change in patch releases.
>>> external_database.tables._filter(schema_name="TUTORIAL")  

Building the data model

We can see the 2 tables of interest to us, add them to the session, and join them:

>>> from atoti_directquery_snowflake import TableConfig

>>> sales_table = session.add_external_table(
...     external_database.tables["TEST_RESOURCES", "TUTORIAL", "SALES"],
...     config=TableConfig(keys={"SALE_ID"}),
... )
>>> products_table = session.add_external_table(
...     external_database.tables["TEST_RESOURCES", "TUTORIAL", "PRODUCTS"],
...     config=TableConfig(keys={"PRODUCT_ID"}),
... )
>>> sales_table.join(
...     products_table, sales_table["PRODUCT"] == products_table["PRODUCT_ID"]
... )

Note that at any point, you can inspect the external table definition, as well as its definition as interpreted by Atoti.

>>> # Table definition in the external database:
>>> external_database.tables["TEST_RESOURCES", "TUTORIAL", "SALES"]  

>>> # Table definition in the Atoti session:
>>> sales_table  

Creating the cube

The cube, its hierarchies and measures can be defined as we would do on a session with an in-memory database:

>>> cube = session.create_cube(sales_table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures

>>> h["CATEGORY"] = [l["CATEGORY"], l["SUB_CATEGORY"]]
>>> del h["SUB_CATEGORY"]

>>> m["Max Price"] = tt.agg.max(sales_table["UNIT_PRICE"])

Running a query

Let’s make sure everything works, by running a query:

>>> cube.query(m["Max Price"], levels=[l["SHOP"]])
       Max Price
SHOP
shop_0    210.00
shop_1    300.00
shop_2    300.00
shop_3    210.00
shop_4    300.00
shop_5    150.00

Refreshing the data

If the external database receives updates, some elements retrieved by DirectQuery will be out-of-date. You can manually trigger a refresh of the session to restore synchronization:

>>> # The method below is private and susceptible to change in patch releases.
>>> session._synchronize_with_external_database()

Going further

See DirectQuery for the list of supported databases.

​Connecting to the external database

​Discovering the external tables

​Building the data model

​Creating the cube

​Running a query

​Refreshing the data

​Going further

Connecting to the external database

Discovering the external tables

Building the data model

Creating the cube

Running a query

Refreshing the data

Going further