Setting query timeouts

DirectQuery answers cube queries by generating SQL and running it against an external database. Long-running queries can incur significant costs and, if the database is shared between users, can lead to resource contention. To prevent this, you can set timeouts on queries. The application admin configures these limits; an individual query may request a shorter timeout, but never one that exceeds the configured limit. There are two distinct configuration objects:

XXXClientSettings (e.g. DatabricksClientSettings) configures how Atoti, acting as a client, interacts with the external database. The limits there apply in a general context (regular query, discovery query, time-travel discovery).
XXXDatabaseSettings (e.g. DatabricksDatabaseSettings) configures how the Atoti cube (via its database object) interacts with the external database. Cube related limits are defined there, such as the feeding query timeout.

Summary

Setting	Configured on	Default	Applies to
`externalDatabaseQueryTimeout`	`XXXClientSettings`	5 minutes	Default for all queries
`feedingQueryTimeout`	`XXXDatabaseSettings`	1 hour	Feeding queries (initial load and refresh)

What is `externalDatabaseQueryTimeout`?

externalDatabaseQueryTimeout caps the duration of non-feeding queries.

A cube (MDX) query propagates its own timeout down to the database queries it triggers. The effective timeout for a database query is the minimum of the cube query’s own timeout and externalDatabaseQueryTimeout.For example, if an MDX query is allowed 1 hour but externalDatabaseQueryTimeout is left at its 5-minute default, the generated database query still times out after 5 minutes. If your interactive queries legitimately need longer, raise both this limit and the MDX query timeout.

Raising externalDatabaseQueryTimeout allows queries to run longer on the external database. This can increase cost. The default of 5 minutes is conservative to protect against runaway queries.

Pass these client settings to the connector factory when creating the connection.

final BigqueryClientSettings clientSettings =
    BigqueryClientSettings.builder()
        .bigQuery(bigquery)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

final ClickhouseClientSettings clientSettings =
    ClickhouseClientSettings.builder()
        .propertiesV2(properties)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

final DatabricksClientSettings clientSettings =
    DatabricksClientSettings.builder()
        .connectionProperties(properties)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

final GenericJdbcClientSettings clientSettings =
    GenericJdbcClientSettings.builder()
        .properties(properties)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

final MsSqlClientSettings clientSettings =
    MsSqlClientSettings.builder()
        .dataSource(dataSource)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

final RedshiftClientSettings clientSettings =
    RedshiftClientSettings.builder()
        .properties(properties)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

final SnowflakeClientSettings clientSettings =
    SnowflakeClientSettings.builder()
        .properties(properties)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

final SynapseClientSettings clientSettings =
    SynapseClientSettings.builder()
        .dataSource(dataSource)
        .externalDatabaseQueryTimeout(Duration.ofMinutes(10))
        .build();

What is `feedingQueryTimeout`?

feedingQueryTimeout applies to feeding queries: the queries run during the initial load to feed the aggregate providers and hierarchies, and during refresh operations.

Raising feedingQueryTimeout allows feeding queries to run longer on the external database. This can increase cost, especially on large datasets.

final BigqueryDatabaseSettings databaseSettings =
    BigqueryDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

final ClickhouseDatabaseSettings databaseSettings =
    ClickhouseDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

final DatabricksDatabaseSettings databaseSettings =
    DatabricksDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

final GenericJdbcDatabaseSettings databaseSettings =
    GenericJdbcDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

final MsSqlDatabaseSettings databaseSettings =
    MsSqlDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

final RedshiftDatabaseSettings databaseSettings =
    RedshiftDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

final SnowflakeDatabaseSettings databaseSettings =
    SnowflakeDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

final SynapseDatabaseSettings databaseSettings =
    SynapseDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();

Apply the database settings when building the application:

final Application app =
    Application.builder(connector)
        .schema(schema)
        .managerDescription(managerDescription)
        .databaseSettings(databaseSettings)
        .build();

How to set limits on direct database queries

If you issue queries directly against the database through the database query API rather than through the cube, you can set a timeout and a result limit per individual query. See the database queries page, under “Add a timeout or a result limit”.

Time-travel discovery timeout

For databases that support time travel, DirectQuery also runs discovery queries to detect the latest modification timestamp on tables. You can set a specific timeout for these queries. See the Versioning page, under “Time travel discoveries”.

Releases and upgrades

Getting started

Concepts

Step 1: Bring your data into Atoti

Step 2: Design your Cube

Step 3: Build an Atoti application

Step 4: Query an Atoti application

Configure Atoti

Secure Atoti

Operate Atoti

Reference

Setting query timeouts

Summary

What is `externalDatabaseQueryTimeout`?

What is `feedingQueryTimeout`?

How to set limits on direct database queries

Time-travel discovery timeout

​Summary

​What is externalDatabaseQueryTimeout?

​What is feedingQueryTimeout?

​How to set limits on direct database queries

​Time-travel discovery timeout

Summary

What is `externalDatabaseQueryTimeout`?

What is `feedingQueryTimeout`?

How to set limits on direct database queries

Time-travel discovery timeout