FILE type

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.

The FILE type represents a reference to an unstructured file and its metadata. Use it to manage and process documents, images, and audio alongside structured data in Unity Catalog.

FILE type is supported for Delta Lake tables only.

Note

The FILE type isn't supported on serverless notebooks. It is supported on notebooks attached to serverless Databricks SQL warehouses.

Syntax

{ FILE MANAGED | FILE EXTERNAL }

Use FILE MANAGED or FILE EXTERNAL as a column type when you declare a table column with CREATE TABLE. You can't declare a table column as FILE without specifying either MANAGED or EXTERNAL. FILE is allowed only as a parameter or return type in a UDF, stored procedure, or when casting.

For a comparison of FILE EXTERNAL and FILE MANAGED, including architecture diagrams, see FILE EXTERNAL and FILE MANAGED.

Fields

A FILE value contains the following fields:

Field Type Description
uri STRING The URI of the file. This field can't be null.
offset BIGINT An offset into the file, in bytes.
size BIGINT The size of the file in bytes.
content_type STRING The MIME type of the file, when known.
checksum STRING An integrity token for the file's bytes, of the form <algorithm>:<digest>. For the recognized algorithms, see Checksums.

Limits

  • The FILE type doesn't guarantee ordering. You can't use a FILE column as a partitioning column, a clustering column, a MAP key, a join key, or a grouping expression. To group by a file, group by its uri field instead.

Literals

To create a FILE value, see the following functions:

To CAST a VARIANT or STRUCT value to FILE:

  • A STRUCT must have exactly these fields, with these names and types: struct<uri:string, offset:bigint, size:bigint, content_type:string, checksum:string>.
  • A VARIANT must include the uri field. All other fields are optional, and any additional keys are ignored.

Cast to FILE, not to FILE EXTERNAL or FILE MANAGED. The target column determines whether the resulting reference is external or managed. When you write an external reference to a FILE MANAGED column, Databricks ingests it into managed storage.

Notes

  • As a top-level table column, FILE must be declared as FILE MANAGED or FILE EXTERNAL. Use FILE in all other cases:
    • As a parameter or return type in SQL, Python, and Scala user-defined functions (UDFs) and in SQL stored procedures.
    • As the target type of a CAST from a VARIANT or STRUCT value.
    • Nested inside a STRUCT, an ARRAY, the value of a MAP, or a VARIANT. Only external files are supported inside a VARIANT.
  • To read file metadata, use the uri, offset, size, content_type, and checksum fields with dot notation, for example file_column.uri.
  • To extract structured content from a file, pass a FILE value to the ai_parse_document function function.

Examples

To declare a table with an external FILE column:

CREATE TABLE attachments (id BIGINT, attachment FILE EXTERNAL);

To populate it from files that already exist in a volume:

INSERT INTO attachments
  SELECT row_number() OVER (ORDER BY file.uri), file
  FROM read_files('/Volumes/my_catalog/my_schema/my_volume/', format => 'file');

To read file metadata using dot notation:

SELECT attachment.uri, attachment.size, attachment.checksum
  FROM attachments;

To construct a FILE value from a struct:

SELECT named_struct(
  'uri', '/Volumes/my_catalog/my_schema/my_volume/report.pdf',
  'offset', null,
  'size', CAST(19494 AS BIGINT),
  'content_type', 'application/pdf',
  'checksum', 'ETAG:v1')::FILE;
  • Last updated on