FILE functions quickstart
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
FILEreferences withlist_files. - Reading file metadata, such as
sizeandchecksum(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.contractsdataset, 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: