Enable liquid clustering

Applies to: Red X icon SaaS connectors Green check icon Database connectors Red X icon Query-based connectors

Liquid clustering optimizes the data layout of a table based on its clustering columns, which improves query performance. This page describes how to enable liquid clustering on the destination streaming tables that a managed ingestion pipeline creates.

Because destination tables are streaming tables managed by the pipeline, you can't run ALTER TABLE ... CLUSTER BY on them directly. Instead, configure clustering in the pipeline's ingestion_definition using the table_configuration field.

You can enable liquid clustering in two ways:

Property Level Description
enable_auto_clustering Pipeline When set to true, automatic liquid clustering is enabled for all tables in the pipeline. Databricks selects and maintains the clustering keys based on your query workload.
clustering_columns Table Specify an explicit list of columns to cluster a specific destination table by. Set this in the table_configuration of an individual table.

Requirements

  • Automatic liquid clustering (enable_auto_clustering) requires Databricks Runtime 15.4 LTS or above, because intelligent key selection relies on metadata introduced in that version. Tables with liquid clustering enabled can be read or written from any Databricks Runtime version that supports liquid clustering (Databricks Runtime 13.3 LTS and above). For details about automatic key selection and version behavior, see Automatic liquid clustering.

Enable liquid clustering using the API

You can enable liquid clustering when you create or edit managed ingestion pipelines using Declarative Automation Bundles, notebooks, or the Databricks CLI.

The following example enables automatic liquid clustering at the pipeline level (enable_auto_clustering) and specifies explicit clustering_columns for the users_1 destination table:

Declarative Automation Bundles

resources:
  pipelines:
    pipeline_cdc:
      name: <pipeline-name>
      catalog: <destination-catalog>
      schema: <destination-schema>
      ingestion_definition:
        ingestion_gateway_id: <gateway-pipeline-id>
        table_configuration:
          enable_auto_clustering: true
        objects:
          - table:
              source_schema: <source-schema>
              source_table: users_1
              destination_catalog: <destination-catalog>
              destination_schema: <destination-schema>
              table_configuration:
                clustering_columns:
                  - user_id
                  - country
          - table:
              source_schema: <source-schema>
              source_table: calendar_test
              destination_catalog: <destination-catalog>
              destination_schema: <destination-schema>

Databricks notebook

pipeline_spec = """
{
  "ingestion_definition": {
    "ingestion_gateway_id": "<gateway-pipeline-id>",
    "table_configuration": {
      "enable_auto_clustering": true
    },
    "objects": [
      {
        "table": {
          "source_schema": "<source-schema>",
          "source_table": "users_1",
          "destination_catalog": "<destination-catalog>",
          "destination_schema": "<destination-schema>",
          "table_configuration": {
            "clustering_columns": ["user_id", "country"]
          }
        }
      },
      {
        "table": {
          "source_schema": "<source-schema>",
          "source_table": "calendar_test",
          "destination_catalog": "<destination-catalog>",
          "destination_schema": "<destination-schema>"
        }
      }
    ]
  }
}
"""

Databricks CLI

{
  "ingestion_definition": {
    "ingestion_gateway_id": "<gateway-pipeline-id>",
    "table_configuration": {
      "enable_auto_clustering": true
    },
    "objects": [
      {
        "table": {
          "source_schema": "<source-schema>",
          "source_table": "users_1",
          "destination_catalog": "<destination-catalog>",
          "destination_schema": "<destination-schema>",
          "table_configuration": {
            "clustering_columns": ["user_id", "country"]
          }
        }
      },
      {
        "table": {
          "source_schema": "<source-schema>",
          "source_table": "calendar_test",
          "destination_catalog": "<destination-catalog>",
          "destination_schema": "<destination-schema>"
        }
      }
    ]
  }
}

Limitations

  • Liquid clustering for managed ingestion pipelines is only supported for database connectors that use change data capture (CDC). SaaS connectors and query-based connectors are not yet supported.
  • You can't run ALTER TABLE ... CLUSTER BY on a destination table directly. Configure clustering through the pipeline's table_configuration.

Additional resources

  • Last updated on