cube-js/cube · error · UserError

ClickHouse doesn't support pre-aggregations without indexes

Error message

ClickHouse doesn't support pre-aggregations without indexes

What it means

ClickHouse pre-aggregation tables are created as MergeTree tables whose ORDER BY key comes from the first defined index. Without any indexes there is no sort key, so ClickHouseQuery.preAggregationLoadSql throws this UserError before generating CREATE TABLE SQL.

Source

Thrown at packages/cubejs-schema-compiler/src/adapter/ClickHouseQuery.ts:237

      datesTo.push(to);
    });

    return `SELECT parseDateTimeBestEffort(arrayJoin(['${datesFrom.join('\',\'')}'])) as date_from, parseDateTimeBestEffort(arrayJoin(['${datesTo.join('\',\'')}'])) as date_to`;
  }

  public concatStringsSql(strings) {
    // eslint-disable-next-line prefer-template
    return 'toString(' + strings.join(') || toString(') + ')';
  }

  public unixTimestampSql() {
    return `toUnixTimestamp(${this.nowTimestampSql()})`;
  }

  public preAggregationLoadSql(cube, preAggregation, tableName) {
    const sqlAndParams = this.preAggregationSql(cube, preAggregation);
    if (!preAggregation.indexes) {
      throw new UserError('ClickHouse doesn\'t support pre-aggregations without indexes');
    }
    const firstIndexName = Object.keys(preAggregation.indexes)[0];
    const indexColumns = this.evaluateIndexColumns(cube, preAggregation.indexes[firstIndexName]);
    return [`CREATE TABLE ${tableName} ENGINE = MergeTree() ORDER BY (${indexColumns.join(', ')}) ${this.asSyntaxTable} ${sqlAndParams[0]}`, sqlAndParams[1]];
  }

  public countDistinctApprox(sql: string): string {
    return `uniq(${sql})`;
  }

  public createIndexSql(indexName, tableName, escapedColumns) {
    return `ALTER TABLE ${tableName} ADD INDEX ${indexName} (${escapedColumns.join(', ')}) TYPE minmax GRANULARITY 1`;
  }

  public dimensionColumns(cubeAlias) {
    // For the top-level SELECT statement, explicitly set the column alias.
    // Clickhouse sometimes includes the "q_0" prefix in the column name, and this
    // leads to errors during the result mapping.

View on GitHub (pinned to 7d981676b3)

Solutions

1. Add an `indexes` block to the pre-aggregation (e.g. `indexes: { main: { columns: [CUBE.dimensionOrMeasure] } }`).
2. Use an `indexColumns`-compatible primary index matching your common filter/groupBy columns.
3. If no index is desired, switch that rollup to a database that supports indexless pre-aggregations.

Example fix

// before
preAggregations: {
  main: {
    measures: [CUBE.count],
    timeDimension: CUBE.createdAt,
    granularity: `day`
  }
}
// after
preAggregations: {
  main: {
    measures: [CUBE.count],
    timeDimension: CUBE.createdAt,
    granularity: `day`,
    indexes: { mainIdx: { columns: [CUBE.createdAt] } }
  }
}

if (dbType === 'clickhouse' && preAggregation && !preAggregation.indexes) {
  throw new Error('ClickHouse pre-aggregation requires indexes');
}

try { await cube.refresh() } catch (e) { if (/pre-aggregations without indexes/.test(e.message)) { console.error('Add an indexes block to cube', e.message); } else throw e; }

When it happens

Trigger: Defining a pre-aggregation in a data model without an `indexes` property (or with empty indexes) while using the ClickHouse driver, then triggering pre-aggregation load/rollup.

Common situations: Copying pre-aggregation configs from Postgres/MySQL projects (where indexes are optional) into a ClickHouse project; forgetting to add an index after renaming one; schema generators that omit indexes.

Related errors

• Create table failed: ${e}
• Unknown pre-aggregation type '${preAggregation.type}' in '${
• compareDateRange can only exist for one timeDimension
• Unable to detect column types for pre-aggregation on empty v
• Connection check failed: ${errorMessage}

AI-assisted analysis of cube-js/cube@7d981676b3 (2026-09-02). Data as JSON: /api/errors/c95ce7e20712800e. Report an issue: GitHub.