Skip to content

Summary Tables

This feature was introduced in Hydrolix version 6.4.

A summary table built from a sharded parent table inherits sharding configuration from its parent. Each summary partition receives the shard key hash from the parent data it's built from, so the summary table always uses the parent's shard_key and shard_key_algo.

Requirements⚓︎

For queries on a summary table to prune by shard key:

  1. The parent table must be sharded.
  2. The summary SQL must output the parent's shard_key column, unaltered and with its original name.

If the summary SQL doesn't output the shard key column, the summary table still works, but sharding won't.

Don't change the shard key column⚓︎

Don't change the shard_key column in the summary SQL

Summary partitions use the parent's original shard key values. If the summary SQL changes the shard key column value but keeps its name, queries that filter on that column hash the changed value and can skip partitions that contain matching rows. To show a changed value, add it as a separate column in the summary SQL.

1
2
3
4
5
6
7
8
SELECT
    toStartOfMinute(timestamp) AS minute,
    tenant,
    upper(tenant) AS tenant_display,
    count() AS requests
FROM example.web_logs
GROUP BY minute, tenant, tenant_display
SETTINGS hdx_primary_key = 'minute'

1
2
3
4
5
6
7
8
SELECT
    toStartOfMinute(timestamp) AS minute,
    upper(tenant) AS tenant,  -- DANGER

    count() AS requests
FROM example.web_logs
GROUP BY minute, tenant
SETTINGS hdx_primary_key = 'minute'

Troubleshoot summary table sharding⚓︎

Hydrolix logs these messages when it loads a summary table and its sharding setup doesn't match what pruning needs.

Summary table declares a different shard key⚓︎

Hydrolix logs this warning when the summary table has its own shard_key setting that doesn't match the parent's, or when the parent isn't sharded. In the second case, the parent's key appears as <none>. Hydrolix ignores the summary table's setting and uses the parent's.

Shard Key Mismatch Warning
1
namespace='project.summary_table' declares shard_key='iso_code' but parent='project.raw_table' has shard_key='region'; using the parent's, which is what partitions are bucketed by

To resolve it, remove shard_key from the summary table's settings. Recall that the column used for shard_key can't be changed once set.

Summary query doesn't output the shard key⚓︎

Hydrolix logs this message at DEBUG level when the parent table is sharded but the summary SQL doesn't output the parent's shard key column under its original name. The summary table is still queryable, but sharding is disabled.

Shard Pruning Unavailable Debug Message
1
namespace='project.summary_table' parent shard_key='region' is not a non-aggregate output column of the summary query; shard pruning unavailable

To turn pruning on, add the shard key column, unaltered, to the summary SQL's SELECT and GROUP BY clauses.

Because this message only appears at DEBUG level, a missing shard key column is easy to overlook. Transforming the column while keeping its name doesn't log this message at all. See Don't change the shard key column.