optimizeTable

optimizeTable is a Change Type in the Liquibase Open Source Databricks extension that optimizes a table.

Uses

You can use this Change Type to optimize the layout of tables in your database. Optimization is particularly useful for large databases whose datasets have grown over time and may not be optimally organized. Optimization reorganizes the storage structure of your tables and compacts small files in the Databricks back-end in order to make future queries more efficient. You can also specify Z-Ordering, which reduces query scan times by physically co-locating related data.

Note: Rollback is not supported for optimizeTable because dynamic RESTORE statements can negatively impact the database.

Run optimizeTable

To run this Change Type, follow these steps:

  1. Add the Change Type to your changeset, as shown in the examples on this page.
  2. Specify any required attributes. Use the table on this page to see which ones your database requires.
  3. Deploy your changeset by running the update command:
    4. liquibase update

Available attributes

Tip: You must specify all top-level attributes marked as required. If you specify an optional attribute, you must also specify any nested attributes that it requires.

Name Type Description Requirement
tableName String Name of the table to optimize. Required
zOrderColumns String

Columns to Z-Order (collocate column information in the same set of files). Cannot be used on liquid clustered tables. Separate multiple values using commas.

Note: The effectiveness of the locality decreases with each additional column.

 Optional

Note: The WHERE SQL clause is not supported for the optimizeTable Change Type.

Examples

databaseChangeLog:
  - changeSet:
      id: 2
      author: your.name
      changes:
        - optimizeTable:
            tableName: test_optimize_table
            zOrderColumns: test_value_1,test_value_2
{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "2",
        "author": "your.name",
        "changes": [
          {
            "optimizeTable": {
              "tableName": "test_optimize_table",
              "zOrderColumns": "test_value_1,test_value_2"
            }
          }
        ]
      }
    }
  ]
}
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:databricks="http://www.liquibase.org/xml/ns/databricks"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/databricks
        http://www.liquibase.org/xml/ns/databricks/liquibase-databricks-latest.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet id="2" author="your.name">
        <databricks:optimizeTable tableName="test_optimize_table" zOrderColumns="test_value_1,test_value_2"/>
    </changeSet>

</databaseChangeLog>

Database support

This Change Type is only supported for Databricks. It does not support auto rollback.

Related links