file Table Function

**Example**

Query the total number of rows in files named `file000`, `file001`, ... , `file999`:

```
SELECT count(*) FROM file('big_dir/file{0..9}{0..9}{0..9}', 'CSV', 'name String, value UInt32');
```

**Example**

Query the total number of rows from all files inside directory `big_dir/` recursively:

```
SELECT count(*) FROM file('big_dir/**', 'CSV', 'name String, value UInt32');
```

**Example**

Query the total number of rows from all files `file002` inside any folder in directory `big_dir/` recursively:

```
SELECT count(*) FROM file('big_dir/**/file002', 'CSV', 'name String, value UInt32');
```

## Virtual Columns[​](#virtual-columns "Direct link to Virtual Columns")

- `_path` — Path to the file. Type: `LowCardinality(String)`.
- `_file` — Name of the file. Type: `LowCardinality(String)`.
- `_size` — Size of the file in bytes. Type: `Nullable(UInt64)`. If the file size is unknown, the value is `NULL`.
- `_time` — Last modified time of the file. Type: `Nullable(DateTime)`. If the time is unknown, the value is `NULL`.

## use\_hive\_partitioning setting[​](#hive-style-partitioning "Direct link to use_hive_partitioning setting")

When setting `use_hive_partitioning` is set to 1, ClickHouse will detect Hive-style partitioning in the path (`/name=value/`) and will allow to use partition columns as virtual columns in the query. These virtual columns will have the same names as in the partitioned path.

**Example**

Use virtual column, created with Hive-style partitioning

```
SELECT * FROM file('data/path/date=*/country=*/code=*/*.parquet') WHERE date > '2020-01-01' AND country = 'Netherlands' AND code = 42;
```

## Settings[​](#settings "Direct link to Settings")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Description|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [engine\_file\_empty\_if\_not\_exists](/docs/operations/settings/settings#engine_file_empty_if_not_exists) allows to select empty data from a file that doesn't exist. Disabled by default.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [engine\_file\_truncate\_on\_insert](/docs/operations/settings/settings#engine_file_truncate_on_insert) allows to truncate file before insert into it. Disabled by default.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [engine\_file\_allow\_create\_multiple\_files](/docs/operations/settings/settings#engine_file_allow_create_multiple_files) allows to create a new file on each insert if format has suffix. Disabled by default.|  |  |  |  | | --- | --- | --- | --- | | [engine\_file\_skip\_empty\_files](/docs/operations/settings/settings#engine_file_skip_empty_files) allows to skip empty files while reading. Disabled by default.|  |  | | --- | --- | | [storage\_file\_read\_method](/docs/operations/settings/settings#engine_file_empty_if_not_exists) method of reading data from storage file, one of: read, pread, mmap (only for clickhouse-local). Default value: `pread` for clickhouse-server, `mmap` for clickhouse-local. | | | | | | | | | | | |

## Related[​](#related "Direct link to Related")

- [Virtual columns](/docs/engines/table-engines#table_engines-virtual_columns)
- [Rename files after processing](/docs/operations/settings/settings#rename_files_after_processing)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Examples for Writing to a File](#examples-for-writing-to-a-file)
        - [Write to a TSV file](#write-to-a-tsv-file)- [Partitioned write to multiple TSV files](#partitioned-write-to-multiple-tsv-files)- [Examples for Reading from a File](#examples-for-reading-from-a-file)
          - [SELECT from a CSV file](#select-from-a-csv-file)- [Inserting data from a file into a table](#inserting-data-from-a-file-into-a-table)- [Globs in path](#globs-in-path)- [Examples](#examples)- [Virtual Columns](#virtual-columns)- [use\_hive\_partitioning setting](#hive-style-partitioning)- [Settings](#settings)- [Related](#related)

Was this page helpful?