Dremio Cloud
Dremio Software
 
On this page

    VACUUM TABLE

    When you are working with Apache Iceberg tables, you can remove snapshots that you no longer need and the files (data files, the manifest file, manifest list file, and partition stats files) that are associated only with them. See Expiring Snapshots of Apache Iceberg Tables for more information.

    Syntax 
    VACUUM TABLE <table_name> 
    [ EXPIRE SNAPSHOTS [older_than = <value>]  [retain_last = <value>] ]

    Parameters

    <table_name>

    String

    The path and name of the table to which the snapshots to expire belong.

    EXPIRE SNAPSHOTS [ older_than = <value> ] [ retain_last = <value> ]

    String

    Optional

    Options for specifying which snapshots to expire.

    • older_than
      Timestamp that marks the lower boundary of the snapshots to keep. Snapshots with timestamps earlier than the specified timestamp are deleted. The default timestamp is five days before the date and time at which the command is run.
    • retain_last
      Minimum number of snapshots to retain, starting with the current timestamp.

    Examples

    Example 1

    Remove snapshots older than specific day and time, but retain a minimum of 20 snapshots 
    VACUUM TABLE 's3.SF_Incidents2016' 
    EXPIRE SNAPSHOTS older_than=TIMESTAMP '2023-04-20 00:00:00.000' retain_last=20;

    Result

    The most recent twenty snapshots are retained. For example, suppose that eleven snapshots are created after the date and time represented by the timestamp. Eight snapshots older than the timestamp are retained, so as to retain the minimum amount of twenty. This image demonstrates the result of this example.

    Example 2

    Remove snapshots older than 5 days, but retain a minimum of 100 snapshots 
    VACUUM TABLE 's3.SF_Incidents2016' EXPIRE SNAPSHOTS retain_last=100;

    Result

    The most recent one hundred snapshots are retained, even among snapshots that are older than five days. After the 100 snapshots, any snapshots older than 5 days are deleted. For example, suppose that eleven snapshots were created in the past five days. When the command is run, the most recent one hundred snapshots are retained, even though the majority of them were created further back in the past than five days ago. This image demonstrates the result of this example.

    Example 3

    Remove snapshots older than 5 days, but retain a minimum of 1 snapshot 
    VACUUM TABLE 's3.SF_Incidents2016' EXPIRE SNAPSHOTS;

    Result

    The snapshots created within the last five days are retained. For example, suppose that eleven snapshots were created in the last five days. Only those snapshots would be retained. This image demonstrates the result of this example.