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.
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
- ...
- BigQuery
- ClickHouse
- Databricks
- Generic JDBC
- MS SQL
- Redshift
- Snowflake
- Synapse
final BigqueryDatabaseSettings databaseSettings =
BigqueryDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();
final ClickhouseDatabaseSettings databaseSettings =
ClickhouseDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();
final DatabricksDatabaseSettings databaseSettings =
DatabricksDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();
final GenericJdbcDatabaseSettings databaseSettings =
GenericJdbcDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();
final MsSqlDatabaseSettings databaseSettings =
MsSqlDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();
final RedshiftDatabaseSettings databaseSettings =
RedshiftDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();
final SnowflakeDatabaseSettings databaseSettings =
SnowflakeDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();
final SynapseDatabaseSettings databaseSettings =
SynapseDatabaseSettings.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:
- BigQuery
- ClickHouse
- Databricks
- Generic JDBC
- MS SQL
- Redshift
- Snowflake
- Synapse
final BigqueryDatabaseSettings databaseSettings =
BigqueryDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();
final ClickhouseDatabaseSettings databaseSettings =
ClickhouseDatabaseSettings.builder()
.useClusteringFields(UseClusteringFields.ALWAYS)
.build();
final DatabricksDatabaseSettings databaseSettings =
DatabricksDatabaseSettings.builder()
.useClusteringFields(UseClusteringFields.ALWAYS)
.build();
final GenericJdbcDatabaseSettings databaseSettings =
GenericJdbcDatabaseSettings.builder()
.useClusteringFields(UseClusteringFields.ALWAYS)
.build();
final MsSqlDatabaseSettings databaseSettings =
MsSqlDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();
final RedshiftDatabaseSettings databaseSettings =
RedshiftDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();
final SnowflakeDatabaseSettings databaseSettings =
SnowflakeDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();
final SynapseDatabaseSettings databaseSettings =
SynapseDatabaseSettings.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_ID | DATE | SHOP | PRODUCT | QUANTITY | UNIT_PRICE |
---|---|---|---|---|---|
S0010 | 2022-01-31 | shop_2 | BED_2 | 3 | 150 |
... | ... | ... | ... | ... | ... |
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_ID | CATEGORY | SUB_CATEGORY | SIZE | PURCHASE_PRICE | COLOR | BRAND |
---|---|---|---|---|---|---|
BED_2 | Furniture | Bed | Single | 127 | red | Mega |
... | ... | ... | ... | ... | ... | ... |
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_ID | DATE | SHOP | PRODUCT | QUANTITY | UNIT_PRICE |
---|---|---|---|---|---|
S0010 | 2022-01-31 | shop_2 | BED_2 | 3 | 150 |
... | ... | ... | ... | ... | ... |
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.