copy_file function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18 LTS and above

Important

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Azure 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. Azure 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, Azure Databricks raises an error by default unless you set if_file_exists_mode to overwrite or skip.
  • If the source file doesn't exist, Azure 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 Azure Databricks.

Examples

To copy a file from one volume to another:

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:

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:

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:

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

Error: COPY_FILE_ERROR.FILE_NOT_EXISTS
  • Last updated on