prestodb/presto · error · PrestoException

HIVE_INVALID_METADATA

HIVE_INVALID_METADATA

Error message

bucketed table %s should not specify preferred_ordering_columns

What it means

A Hive table's storage may declare either bucketing (bucketed_by/sorted_by) or preferred ordering columns, not both. If decodePreferredOrderingColumnsFromStorage returns columns while the table also has a bucket property, getTableMetadata throws HIVE_INVALID_METADATA because the two ordering specifications conflict.

Source

Thrown at presto-hive/src/main/java/com/facebook/presto/hive/HiveMetadata.java:759

                .map(Column::getName)
                .collect(toList());
        if (!partitionedBy.isEmpty()) {
            properties.put(PARTITIONED_BY_PROPERTY, partitionedBy);
        }

        // Bucket properties
        Optional<HiveBucketProperty> bucketProperty = table.get().getStorage().getBucketProperty();
        table.get().getStorage().getBucketProperty().ifPresent(property -> {
            properties.put(BUCKET_COUNT_PROPERTY, property.getBucketCount());
            properties.put(BUCKETED_BY_PROPERTY, property.getBucketedBy());
            properties.put(SORTED_BY_PROPERTY, property.getSortedBy());
        });

        // Preferred ordering columns
        List<SortingColumn> preferredOrderingColumns = decodePreferredOrderingColumnsFromStorage(table.get().getStorage());
        if (!preferredOrderingColumns.isEmpty()) {
            if (bucketProperty.isPresent()) {
                throw new PrestoException(HIVE_INVALID_METADATA, format("bucketed table %s should not specify preferred_ordering_columns", tableName));
            }
            properties.put(PREFERRED_ORDERING_COLUMNS, preferredOrderingColumns);
        }

        // ORC format specific properties
        String orcBloomFilterColumns = table.get().getParameters().get(ORC_BLOOM_FILTER_COLUMNS_KEY);
        if (orcBloomFilterColumns != null) {
            properties.put(ORC_BLOOM_FILTER_COLUMNS, Splitter.on(COMMA).trimResults().omitEmptyStrings().splitToList(orcBloomFilterColumns));
        }
        String orcBloomFilterFfp = table.get().getParameters().get(ORC_BLOOM_FILTER_FPP_KEY);
        if (orcBloomFilterFfp != null) {
            properties.put(ORC_BLOOM_FILTER_FPP, Double.parseDouble(orcBloomFilterFfp));
        }

        // Avro specific property
        String avroSchemaUrl = table.get().getParameters().get(AVRO_SCHEMA_URL_KEY);
        if (avroSchemaUrl != null) {
            properties.put(AVRO_SCHEMA_URL, avroSchemaUrl);

View on GitHub (pinned to 55bb57d202)

Solutions

Recreate or ALTER the table to remove preferred_ordering_columns from its storage properties if bucketing is intended
Remove the bucketing property if ordering columns are intended instead
Fix the writing engine's table-property configuration so it emits only one of the two
Repair the metastore table properties directly (tblproperties/storage descriptor)

Example fix

-- before
CREATE TABLE t (...) WITH (bucketed_by = ARRAY['id'], preferred_ordering_columns = ARRAY['id']);
-- after
CREATE TABLE t (...) WITH (bucketed_by = ARRAY['id']);

Defensive patterns

Strategy: validation

Validate before calling

List<SortingColumn> ordering = decodePreferredOrderingColumnsFromStorage(table.getStorage());
if (!ordering.isEmpty() && getBucketProperty(table.getStorage()).isPresent()) {
    throw new IllegalStateException("Table cannot have both bucketing and preferred_ordering_columns");
}

Try / catch

try {
    // read table metadata
} catch (PrestoException e) {
    if (e.getErrorCode().getCode() == HIVE_INVALID_METADATA.toErrorCode().getCode()) {
        // inspect and fix tblproperties in the metastore
    } else throw e;
}

Prevention

Configure writer engines to emit either bucketing or preferred_ordering_columns, never both
Audit tblproperties on tables created by external engines before onboarding
Avoid hand-editing storage descriptor properties in the metastore

When it happens

Trigger: Reading metadata for a table whose SerDe/storage properties contain both bucketing information and a preferred_ordering_columns property.

Common situations: Tables written by engines (e.g. Spark/Trino CTAS) whose metadata emitter set both properties; hand-edited table properties; version upgrades where a writer started adding preferred_ordering_columns to legacy bucketed tables.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/e82f0f85e6eae836. Report an issue: GitHub.