copy_file function

Applies to: Databricks SQL Databricks Runtime 18 LTS and above

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

Copies a file to a destination path and returns a FILE reference to the copied file. Omit destination to copy the file into Unity Catalog-managed storage, which converts a FILE EXTERNAL reference to a FILE MANAGED reference.

Syntax

copy_file(file => file
  [, destination => destination ]
  [, if_file_exists_mode => mode ])

You can pass arguments positionally or by name. After you pass an argument by name, all following arguments must also be passed by name. For more information, see named parameter invocation.

Arguments

• file: A FILE value to copy.
• destination: An optional STRING with the full destination file path, not just a directory. When omitted, the file is copied into Unity Catalog-managed storage, converting a FILE EXTERNAL reference to a FILE MANAGED reference.
• if_file_exists_mode: An optional STRING that sets the behavior when a file already exists at the destination path. Applies only when using the destination argument. Accepted values (case insensitive) are:
  • 'error': Raises an error. This is the default value.
  • 'overwrite': Overwrites the existing file.
  • 'skip': Skips copying and returns a FILE reference to the existing file.

Returns

A FILE value that references the copied file.

Notes

• Omitting destination converts a FILE EXTERNAL reference to FILE MANAGED. Databricks also applies this conversion automatically when you insert a FILE EXTERNAL value into a FILE MANAGED column.
• If a file already exists at the destination path, Databricks raises an error by default unless you set if_file_exists_mode to overwrite or skip.
• If the source file doesn't exist, Databricks raises an error. To return NULL instead of raising an error in either case, use try_copy_file function.

Common error conditions

• COPY_FILE_ERROR.FILE_NOT_EXISTS
• COPY_FILE_ERROR.FILE_ALREADY_EXISTS
• COPY_FILE_AUTHORIZATION_ERROR.READ_UNAUTHORIZED
• COPY_FILE_AUTHORIZATION_ERROR.WRITE_UNAUTHORIZED

For more information, see Error conditions in Databricks.

Examples

To copy a file from one volume to another:

SQL

SELECT copy_file(
  to_file('/Volumes/source/data/input.csv'),
  destination => '/Volumes/target/data/output.csv'
);

To copy files to a target volume using a per-row file name:

SQL

SELECT copy_file(
  source_file,
  destination => '/Volumes/my_catalog/my_schema/my_volume/processed/' || file_name,
  if_file_exists_mode => 'skip'
)
FROM staging_files;

To overwrite the destination if a file already exists:

SQL

SELECT copy_file(
  to_file('/Volumes/source/reports/report.pdf'),
  destination => '/Volumes/archive/reports/report.pdf',
  if_file_exists_mode => 'overwrite'
);

If the source file doesn't exist, copy_file raises an error:

SQL

SELECT copy_file(deleted_file, destination => '/Volumes/archive/reports/report.pdf');

Text

Error: COPY_FILE_ERROR.FILE_NOT_EXISTS

Related functions

• to_file function
• try_to_file function
• create_file function
• try_copy_file function
• list_files table-valued function
• read_files table-valued function
• FILE type