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

# Using DirectQuery

<a id="module-docs.pages.using_directquery" />

<Note>
  This feature is not part of the community edition: it needs to be [unlocked](./unlocking_all_features).
</Note>

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

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> 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:

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> import os
```

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> from atoti_directquery_snowflake import ConnectionConfig
```

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> 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`](../api/atoti_directquery_clickhouse.connection_config#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:

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> # 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:

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> from atoti_directquery_snowflake import TableConfig
```

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> 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.

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> # Table definition in the external database:
>>> external_database.tables["TEST_RESOURCES", "TUTORIAL", "SALES"]  
```

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> # 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:

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> cube = session.create_cube(sales_table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
```

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> h["CATEGORY"] = [l["CATEGORY"], l["SUB_CATEGORY"]]
>>> del h["SUB_CATEGORY"]
```

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> m["Max Price"] = tt.agg.max(sales_table["UNIT_PRICE"])
```

## Running a query

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

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> 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:

```pycon theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
>>> # The method below is private and susceptible to change in patch releases.
>>> session._synchronize_with_external_database()
```

## Going further

See [DirectQuery](../getting_started/plugins#directquery) for the list of supported databases.