DELETE_ORPHAN_FILES stored procedure

Orphaned data files are data files no longer referenced by an Iceberg table. Data files can be orphaned as a result of task or job failures, or through normal snapshot expiration when the expiration process fails to remove all data files no longer referenced by the remaining snapshots.

This procedure may take a significant amount of time to complete if it is run against a table with a lot of files, snapshots and directories. As a result, it is recommended you run this procedure infrequently.

The DELETE_ORPHAN_FILES stored procedure is used to delete orphaned data and metadata files no longer referenced by a table.

Authorization

EXECUTE is granted to the DASHDB_ENTERPRISE_ADMIN role.

Syntax

Read syntax diagramSkip visual syntax diagramDELETE_ORPHAN_FILES(schema_name ,table_name,older_than,delete_files,num_threads)

The schema is SYSHADOOP.

Description

schema_name
An input argument of type VARCHAR(128) containing the name of the schema. This is a required parameter which identifies the schema name. Although this can be specified using the named parameter syntax, it is not recommended.
table_name
An input argument of type VARCHAR(128) containing the name of the table. This is a required parameter which references the table which will be impacted by the procedure. Although this can be specified using the named parameter syntax, it is not recommended.
older_than
An input argument of type TIMESTAMP. Only orphaned files created before this timestamp will be removed. You must specify an older_than timestamp of at least one day ago. This parameter is optional, if not specified it is set to one day ago.
delete_files
An input argument of type boolean that indicates whether or not the files should be deleted. If set to false, the delete orphan files operation will return the list of files that are candidates for deletion. If set to true, the files are deleted. This parameter is optional, if not specified it is set to false.
num_threads
An input argument of type Integer that specifies the number of threads used to delete the files. This parameter is optional, if not specified it is set to three.

Output

Two result sets:

  • The first result set contains two columns named STATUS_CODE and STATUS_MESSAGE. These columns contain values indicating the success or failure of the procedure. If the procedure succeeds, the STATUS_CODE is set to 0 and the STATUS_MESSAGE includes information related to the procedure results. If the procedure failed, the STATUS_CODE is set to -1 and the STATUS_MESSAGE includes an explanation indicating why it failed.

  • A second result containing one or more rows is returned upon successful completion on the procedure. The rows contain a single column named ORPHAN_FILE_LOCATION with the file locations of the orphan files that are candidates for deletions or were deleted by the procedure.

    Table 1.
    Column Name Data Type Description
    ORPHAN_FILE_LOCATION VARCHAR(32672) Location of the orphan file that is a candidate for deletion or that was deleted by the procedure.

Example

CALL DELETE_ORPHAN_FILES('SAMPLE', 'EMPLOYEE', '2024-07-16-05.39.49')

  Result set 1

  --------------

  STATUS_CODE STATUS_MESSAGE                                                                                            

  ----------- ----------------------------------------------------------------------------------------------------------

            0 3 orphan file(s) found for SAMPLE.EMPLOYEE. To delete the file(s), set the DELETE_FILES parameter to true.

  1 record(s) selected.

  Result set 2

  --------------

  ORPHAN_FILE_LOCATION                                                    

  ------------------------------------------------------------------------

  s3a://default/employee/data/1475223065-0-20240717053238737-00001.parquet

  s3a://default/employee/data/1849780493-0-20240717053235621-00001.parquet

  s3a://default/employee/data/1922585285-0-20240717053237216-00001.parquet

  3 record(s) selected.

  Return Status = 0