Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

filesystem

View as MarkdownEdit this pageReport an issueSet up docs MCP
Not supported in ClickHouse Cloud

Recursively iterates a directory and returns a table with file metadata (paths, sizes, types, permissions, modification times) and, optionally, file contents.

In clickhouse-server mode, the path must be within the user_files_path directory. Symlinks inside user_files_path that point outside of it are followed, but only entries whose path (through the symlink) starts with user_files_path are returned.

In clickhouse-local mode, there are no path restrictions.

Syntax

filesystem([path])

Arguments

Parameter Description
path The directory to list. Can be an absolute path (must be inside user_files_path in server mode) or a path relative to user_files_path. If empty or omitted, defaults to user_files_path.

Returned columns

Column Type Description
path String Directory containing the entry (does not include the file/directory name itself).
name String File or directory name (the last component of the path).
file String (ALIAS of name) Alias for the name column.
type Enum8 File type: 'none', 'not_found', 'regular', 'directory', 'symlink', 'block', 'character', 'fifo', 'socket', 'unknown'.
size Nullable(UInt64) File size in bytes (for regular files). NULL for non-regular files (directories, symlinks, etc.) and on error.
depth UInt16 Recursion depth. 0 for the queried directory itself and its immediate children, 1 for entries one level deeper, and so on.
modification_time Nullable(DateTime64(6)) Last modification time with microsecond precision. NULL on error.
is_symlink Bool Whether the entry is a symbolic link.
content Nullable(String) File contents (for regular files). NULL for non-regular files (directories, symlinks, etc.). Read errors raise an exception. Reading this column triggers actual file I/O, so omit it if not needed.
owner_read Bool Owner has read permission.
owner_write Bool Owner has write permission.
owner_exec Bool Owner has execute permission.
group_read Bool Group has read permission.
group_write Bool Group has write permission.
group_exec Bool Group has execute permission.
others_read Bool Others have read permission.
others_write Bool Others have write permission.
others_exec Bool Others have execute permission.
set_gid Bool Set-GID bit.
set_uid Bool Set-UID bit.
sticky_bit Bool Sticky bit.

Only columns actually used in the query are computed, so selecting a subset of columns (especially omitting content) is efficient.

Examples

List files in user_files

SELECT name, type, size, depth
FROM filesystem()
ORDER BY name;

Find large files

SELECT path, name, size
FROM filesystem()
WHERE type = 'regular' AND size > 1000000
ORDER BY size DESC;

Read file contents

SELECT name, content
FROM filesystem('my_directory')
WHERE name LIKE '%.csv';

List only immediate children

SELECT name, type
FROM filesystem('my_directory')
WHERE depth = 0;
Navigation