Aggregate Tables

DirectQuery is using the external database to feed some aggregate providers and answer some queries. This can result in some large queries with heavy load on the database, and if the same cube is started multiple times, the same query can be repeated. Aggregate tables are a way to cache some aggregated data in the external database to make some queries faster and cheaper.

How does it work

Aggregate Tables are tables added in the external database as a table fed from aggregated data. These tables typically denormalize fields from joined tables into group-by columns, but can also preserve joins to keep the aggregate table smaller while still accessing fields from other tables at query time. Whenever possible, DirectQuery will query the Aggregate Table instead of the base Schema for a more optimized query: For instance, consider a Sales table containing sales such as this one:

Id	Date	Product	Buyer	Quantity	Price
1	2023-01-01	P1	Buyer_A	100.0	15.5
2	2023-01-01	P1	Buyer_B	100.0	16.5
3	2023-01-01	P2	Buyer_A	100.0	30
4	2023-01-01	P2	Buyer_B	100.0	31
5	2023-01-02	P1	Buyer_A	100.0	15.5
6	2023-01-02	P1	Buyer_B	100.0	15.5
7	2023-01-02	P1	Buyer_A	100.0	18
8	2023-01-03	P1	Buyer_B	100.0	18
9	2023-01-03	P2	Buyer_A	100.0	35
10	2023-01-03	P2	Buyer_B	100.0	40

It is possible to aggregate the sum of the Quantity per Date and Product in the external database into an other table agg_sales:

date	product	sum_of_quantity	count_of_rows
2023-01-01	P1	200.0	2
2023-01-01	P2	200.0	2
2023-01-02	P1	300.0	3
2023-01-03	P1	100.0	1
2023-01-03	P2	200.0	2

If this table is added as an Aggregate Table in DirectQuery, it will be used whenever possible instead of the original Sales table. For instance when asking for the SUM(Quantity) per Product in the Sales table, DirectQuery will retrieve the SUM(sum_of_quantity) per product in agg_sales. As the agg_sales is smaller and already aggregated the result will be faster to compute and will potentially avoid a huge scan of the database, saving both time and compute costs.

Use cases

There are a few classic use cases for using an Aggregate Table:

Feeding an aggregate provider faster by pre-aggregating over the provider fields
Having an “external database aggregate provider”, i.e. being able to answer some queries from the pre-aggregated data in the external database but without having to store additional data in-memory.
Feeding some hierarchies faster

Supported aggregations

The following aggregation function can be used in an Aggregate Table:

Sum
Min
Max
Average
Sum product
Count
Long
Short
Gross
Square sum

Custom Aggregations

To use a user defined aggregation function (UDAF) in an aggregate table, map it with the generic MeasureMapping.agg(pluginKey, originFields, targetColumnNames), where pluginKey is the plugin key shared by the Copper UDAF and the DirectQuery UDAF. How DirectQuery uses the aggregate table depends on the granularity of the query:

When the query groups by all the group-by columns of the aggregate table, DirectQuery reads the rows of the table as they are. Any DirectQuery UDAF can be used.
When the query is at a coarser granularity, DirectQuery re-aggregates the table in the external database, which requires the DirectQuery UDAF to be re-aggregable. For instance, a sum product stored per date and product is re-aggregated with SUM to get the sum product per date. If the DirectQuery UDAF is not re-aggregable, these queries run on the base tables. No error is raised at start-up.

The re-aggregation function only runs in the external database. In memory, aggregate providers and query execution merge aggregates with the merge function of the Copper UDAF. A re-aggregable UDAF is stored in a single column of the aggregate table. For instance, this ClickHouse table stores the sum product UDAF per date and product:

CREATE TABLE IF NOT EXISTS
  TUTORIAL.sales_by_date_and_product
(
  `date` Date32 NOT NULL,
  `product` String NOT NULL,
  `my_sum_product` Float64 NOT NULL,
  `contributors_count` UInt64 NOT NULL
)
ENGINE = MergeTree()
ORDER BY (date, product);
INSERT INTO TUTORIAL.sales_by_date_and_product
  SELECT
    T0.DATE AS `date`,
    T0.PRODUCT AS `product`,
    SUM(T0.QUANTITY * T0.UNIT_PRICE) AS `my_sum_product`,
    COUNT(*) AS `contributors_count`
  FROM
    TUTORIAL.SALES AS T0
  GROUP BY
    T0.DATE,
    T0.PRODUCT;

The aggregate table maps the UDAF through its plugin key, MY_SUM_PRODUCT:

final TableDescription salesByDateAndProductTable =
    discoverer.discoverTable(new ClickhouseTableId(DATABASE, "sales_by_date_and_product"));
final AggregateTableDescription aggregateTable =
    AggregateTableDescription.builder()
        .underlyingTable(salesByDateAndProductTable)
        .originBaseTableName(salesTable.getName())
        .withGroupByFieldsMapping(
            Map.of(FieldPath.of("DATE"), "date", FieldPath.of("PRODUCT"), "product"))
        .withMeasuresMappings(
            List.of(
                MeasureMapping.agg(
                    "MY_SUM_PRODUCT",
                    List.of(FieldPath.of("QUANTITY"), FieldPath.of("UNIT_PRICE")),
                    List.of("my_sum_product")),
                MeasureMapping.count("contributors_count")))
        .build();

Examples

Create a simple Aggregate Table to feed an aggregate provider

Let’s create a new table that pre-aggregates the quantity of the tutorial sales table per product. Unlike the agg_sales table above, this table has no date column: it answers the queries on the product only, which is the granularity of the aggregate provider defined below.

CREATE OR REPLACE TABLE `tutorial`.SALES_BY_PRODUCT (
  PRODUCT STRING NOT NULL,
  QUANTITY_SUM FLOAT64 NOT NULL,
  CONTRIBUTORS_COUNT INT64 NOT NULL
);