FILE functions quickstart

Important

This feature is in Beta.

The FILE type represents unstructured files such as documents, images, and audio. Built-in SQL functions create, copy, and read files as FILE references.

This page summarizes these functions with reference examples for common operations, such as:

  • Listing files in a Unity Catalog volume as FILE references with list_files.
  • Reading file metadata, such as size and checksum (used to identify the file version).
  • Filtering files by their metadata.
  • Parsing document content with ai_parse_document.

For the type reference, see FILE type.

For a conceptual overview, see FILE type and unstructured data.

Requirements

  • These examples use the samples.sec.contracts dataset, a collection of SEC-filed contract PDFs available in all workspaces by default. To use your own files, point the path at a Unity Catalog volume that contains them.

Examples

List files and capture references

To list the files at a path and save the FILE references in a table, use list_files table-valued function:

CREATE TABLE documents AS
  SELECT path, size, modification_time, file
  FROM list_files('/Volumes/samples/sec/contracts/');

Read file metadata

To read the uri, size, content_type, and checksum fields of a FILE value, use dot notation:

SELECT file.uri, file.size, file.content_type, file.checksum
  FROM documents;

Filter files by metadata

To filter rows using file metadata, such as path and size:

SELECT file.uri
  FROM documents
  WHERE file.uri ILIKE '%.pdf'
    AND file.size > 100000;

Parse document content

To extract structured content from a document, pass a FILE value to ai_parse_document function:

SELECT file.uri, ai_parse_document(file) AS parsed
  FROM documents
  WHERE file.uri ILIKE '%.pdf';

Example notebook

The following notebook runs all of the preceding examples against the samples.sec.contracts dataset:

FILE functions quickstart notebook

Get notebook

Next steps

  • Last updated on