Query an external database

Not available in the community edition: upgrade to use it.

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",
...     redacted_properties={
...         "private_key_file": os.environ["SNOWFLAKE_PRIVATE_KEY_FILE"],
...         "private_key_file_pwd": os.environ["SNOWFLAKE_PRIVATE_KEY_PWD"],
...     },
... )
>>> 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.

Releases and upgrades

Install and start

Guides

Query an external database

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

Connecting to the external database

Discovering the external tables

Building the data model

Creating the cube

Running a query

Refreshing the data

Going further