FILE type

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.

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.

nota

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.

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_file function creates a FILE reference from a path.
• try_to_file function creates a FILE reference from a path, and returns NULL if the file doesn't exist.
• create_file function uploads content to a path and returns a FILE reference.
• copy_file function copies a file to a target path and returns a FILE reference.

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:

SQL

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

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

SQL

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:

SQL

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

To construct a FILE value from a struct:

SQL

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;

Related

• to_file function
• try_to_file function
• create_file function
• copy_file function
• try_copy_file function
• list_files table-valued function
• ai_parse_document function
• cast function
• try_cast function
• FILE type and unstructured data