Skip to main content

Categories: AI, Directory

LIST_FILES

Lists files recursively from a source directory, adhering to the COPY INTO security model. Returns structured information about each file for use with AI functions and data processing workflows.

Syntax​

LIST_FILES(source_path VARCHAR) → TABLE​

  • source_path: Path to the source directory to list files from. Must follow the format '@source_name/path/to/directory'. Adheres to COPY INTO security model.

Examples

Basic Usage: LIST_FILES returns a table with columns for source, path (relative without leading slash), size in bytes, and last_modified_time as Unix timestamp in milliseconds. 
SELECT source, path, size, last_modified_time FROM TABLE(LIST_FILES('@my_source/documents/reports'));

-- Sample output:
-- source      | path           | size  | last_modified_time
-- my_source   | report_q1.pdf  | 2048  | 1695123456789
-- my_source   | report_q2.pdf  | 3072  | 1695234567890
-- my_source   | summary.txt    | 512   | 1695345678901

File Filtering: Use WHERE clauses to filter by file extension, size, or other criteria. 
SELECT path, size / 1024 / 1024 AS size_mb FROM TABLE(LIST_FILES('@data_lake/customer_files')) WHERE path LIKE '%.pdf' AND size > 1000000;

-- Sample output:
-- path              | size_mb
-- contracts/big.pdf | 2.5
-- reports/annual.pdf| 1.8
-- manuals/guide.pdf | 3.2

AI Integration: LIST_FILES works seamlessly with AI functions. 
SELECT file['path'] AS recipe_file, AI_GENERATE('gpt.4o', ROW('Extract recipe details', file) WITH SCHEMA ROW(recipe_name VARCHAR, cuisine_type VARCHAR)) AS recipe_info FROM TABLE(LIST_FILES('@cookbooks/cookbook_recipes')) WHERE file['path'] LIKE '%.pdf';

-- Sample output:
-- recipe_file        | recipe_info
-- italian_recipes.pdf| {'recipe_name': 'Spaghetti Carbonara', 'cuisine_type': 'Italian'}
-- asian_cookbook.pdf | {'recipe_name': 'Pad Thai', 'cuisine_type': 'Thai'}
-- desserts_guide.pdf | {'recipe_name': 'Tiramisu', 'cuisine_type': 'Italian'}