Skip to main content

Clustering fields in DirectQuery

Feeding aggregate providers from an external database can result in very large queries to be run on this database. Some databases can have trouble processing such large queries. Clustering fields are a way to break down those queries in multiple parts when feeding Atoti Aggregate providers. You can extends the usage of the clustering fields beyond the feeding with the UseClusteringFields.ALWAYS database settings.

Clustering fields are set on a table on DirectQuery side. When feeding an Aggregate Provider, if the computed database query needs to access this table, the query will be split up according to the table clustering fields. If multiple tables are accessed by the query, the clustering fields from all tables are taken in account to split the query.

A query clustered by too many fields or by fields with a high cardinality can be divided in lots of sub queries. To avoid creating too many queries on the external database, DirectQuery limits the number of sub queries to 500.
This limit can be changed thanks to the parameter IDatabaseSettings#maxSubQueriesAllowedInMultiStepsQuery(), which can be set for your specific database in XXXDatabaseSettings, e.g.:

  • BigqueryDatabaseSettings
  • ClickhouseDatabaseSettings
  • DatabricksDatabaseSettings
  • SnowflakeDatabaseSettings
  • ...

final BigqueryDatabaseSettings databaseSettings =
    BigqueryDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

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

It is recommended to not increase it too much, as it can lead to contention on the external database.

If the cardinality of the clustering field (or the set of clustering fields) is lower than the limit, the query will be split up in as many queries as the cardinality of the clustering fields. If the cardinality of the clustering field (or the set of clustering fields) is higher than the limit, the same split will be performed but some queries will be batched together to stay within the limit.

By default, the clustering fields are only used for feeding queries as these are the ones using the most resources. This limits the number of queries sent to the external database as "normal" queries are not split up.

However it is possible to change this behavior in the database settings:

final BigqueryDatabaseSettings databaseSettings =
    BigqueryDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();

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

Examples​

Example with one table​

Consider the following table SALES.

SALE_IDDATESHOPPRODUCTQUANTITYUNIT_PRICE
S00102022-01-31shop_2BED_23150
..................

With this table clusterized by dates, the query sum quantities by product is split up in queries of the form sum quantities by product where date equals ....

Example with multiple tables​

Now consider this additional table PRODUCTS, storing all the products referenced in the SALES table.

PRODUCT_IDCATEGORYSUB_CATEGORYSIZEPURCHASE_PRICECOLORBRAND
BED_2FurnitureBedSingle127redMega
.....................

This table is clusterized by CATEGORY. With a query computing the sum of the margins by product, both the SALES and PRODUCTS table will be accessed, so the two clustering fields DATE and CATEGORY will be used to split the query up.

Example with one table and clustering field cardinality above the limit​

Consider the following table SALES.

SALE_IDDATESHOPPRODUCTQUANTITYUNIT_PRICE
S00102022-01-31shop_2BED_23150
..................

With this table clusterized by dates, the query sum quantities by product is split up in queries of the form sum quantities by product where date equals ....

However, as the cardinality of the clustering field DATE is above the limit, each subqueries will be performed with a condition on several dates, in order to stay within the limit of the number of subqueries.

Considerations​

Clustering fields and external database clustering​

DirectQuery clustering fields can be chosen independently of any database clustering.
Still it can be interesting to match DirectQuery tables clustering fields with the potential clustering already implemented in your external database. This results in better performances for the sub-queries generated.

Clustering fields and Aggregate Provider partitioning​

DirectQuery clustering fields can be chosen independently of any configured partitioning for Aggregate Providers. DirectQuery will repartition and reaggregate on the fly if needed.
Still it can be interesting to match DirectQuery tables clustering fields with the partitioning of the existing Aggregate Providers. This coherence results in better performances to avoid the cost of repartitioning.