prestodb/presto · error · PrestoException
INVALID_TABLE_PROPERTY
INVALID_TABLE_PROPERTY
Error message
The property of %s is required for table engine %s
What it means
PrestoException (INVALID_TABLE_PROPERTY) thrown when creating a ClickHouse table with engine=MergeTree without the required order_by table property. ClickHouse's MergeTree family requires an ORDER BY expression; the connector enforces this at DDL time before issuing CREATE TABLE.
Source
Thrown at presto-clickhouse/src/main/java/com/facebook/presto/plugin/clickhouse/ClickHouseClient.java:773
String columnTypeMapping = toWriteMapping(column.getType());
if (column.isNullable()) {
builder.append("Nullable(").append(columnTypeMapping).append(")");
}
else {
builder.append(columnTypeMapping);
}
return builder.toString();
}
protected String createTableSql(RemoteTableName remoteTableName, List<String> columns, ConnectorTableMetadata tableMetadata)
{
ImmutableList.Builder<String> tableOptions = ImmutableList.builder();
Map<String, Object> tableProperties = tableMetadata.getProperties();
ClickHouseEngineType engine = ClickHouseTableProperties.getEngine(tableProperties);
tableOptions.add("ENGINE = " + engine.getEngineType());
if (engine == MERGETREE && formatProperty(ClickHouseTableProperties.getOrderBy(tableProperties)).equals(Optional.empty())) {
// order_by property is required
throw new PrestoException(INVALID_TABLE_PROPERTY,
format("The property of %s is required for table engine %s", ORDER_BY_PROPERTY, engine.getEngineType()));
}
formatProperty(ClickHouseTableProperties.getOrderBy(tableProperties)).ifPresent(value -> tableOptions.add("ORDER BY " + value));
formatProperty(ClickHouseTableProperties.getPrimaryKey(tableProperties)).ifPresent(value -> tableOptions.add("PRIMARY KEY " + value));
formatProperty(ClickHouseTableProperties.getPartitionBy(tableProperties)).ifPresent(value -> tableOptions.add("PARTITION BY " + value));
ClickHouseTableProperties.getSampleBy(tableProperties).ifPresent(value -> tableOptions.add("SAMPLE BY " + value));
return format("CREATE TABLE %s (%s) %s", quoted(remoteTableName), join(", ", columns), join(" ", tableOptions.build()));
}
/**
* format property to match ClickHouse create table statement
*
* @param properties property will be formatted
* @return formatted property
*/
private Optional<String> formatProperty(List<String> properties)
{View on GitHub (pinned to 55bb57d202)
Solutions
- Add order_by to the WITH clause: ENGINE=merge_tree, order_by='(...)' or a single column name.
- Choose a different engine (e.g. Log/TinyLog) if ordering is not needed.
- Include order_by in any CTAS/automation templates that create MergeTree tables.
- Also set primary_key consistent with the order_by prefix if desired.
Example fix
-- before CREATE TABLE clickhouse.analytics.events (ts DateTime, id bigint) WITH (engine = 'merge_tree'); -- after CREATE TABLE clickhouse.analytics.events (ts DateTime, id bigint) WITH (engine = 'merge_tree', order_by = ARRAY['ts'], primary_key = ARRAY['ts']);
Defensive patterns
Strategy: validation
Validate before calling
Map<String, Object> props = metadata.getProperties();
if ("merge_tree".equalsIgnoreCase(String.valueOf(props.getOrDefault("engine", "")))
&& !props.containsKey("order_by")) {
throw new IllegalArgumentException("engine merge_tree requires the order_by table property");
} Try / catch
try {
createTable(...);
} catch (PrestoException e) {
if (e.getMessage().contains("is required for table engine")) {
// add order_by to table properties and retry
}
throw e;
} Prevention
- Always include order_by in DDL templates for MergeTree tables.
- Centralize table-creation helpers that inject required properties.
- Review ClickHouseTableProperties docs before writing WITH clauses.
- Set primary_key to a prefix of order_by when applicable.
When it happens
Trigger: CREATE TABLE … ENGINE=MergeTree (or engine='merge_tree') with no order_by property in WITH (...); CTAS statements where table properties omit order_by.
Common situations: Migrating DDL from plain ClickHouse SQL where ORDER BY appears inline but the Presto connector requires it as a table property; copying example DDL that omitted WITH properties; forgetting order_by when defaulting engine to merge_tree.
Related errors
AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04).
Data as JSON: /api/errors/447211543bcb0143.
Report an issue: GitHub.