External disks for storing data

Note

Cache configuration settings and cache query settings correspond to the latest ClickHouse version,
for earlier versions something might not be supported.

#### Cache system tables[​](#cache-system-tables-file-cache "Direct link to Cache system tables")

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Table Name Description Requirements|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `system.filesystem_cache` Displays the current state of the filesystem cache. None|  |  |  | | --- | --- | --- | | `system.filesystem_cache_log` Provides detailed cache usage statistics per query. Requires `enable_filesystem_cache_log = true` | | | | | | | | |

#### Cache commands[​](#cache-commands-file-cache "Direct link to Cache commands")

##### `SYSTEM CLEAR|DROP FILESYSTEM CACHE (<cache_name>) (ON CLUSTER)` -- `ON CLUSTER`[​](#system-clear-filesystem-cache-on-cluster "Direct link to system-clear-filesystem-cache-on-cluster")

This command is only supported when no `<cache_name>` is provided

##### `SHOW FILESYSTEM CACHES`[​](#show-filesystem-caches "Direct link to show-filesystem-caches")

Show a list of filesystem caches which were configured on the server.
(For versions less than or equal to `22.8` the command is named `SHOW CACHES`)

```
SHOW FILESYSTEM CACHES
```

```
┌─Caches────┐
│ s3_cache  │
└───────────┘
```

##### `DESCRIBE FILESYSTEM CACHE '<cache_name>'`[​](#describe-filesystem-cache "Direct link to describe-filesystem-cache")

Show cache configuration and some general statistics for a specific cache.
Cache name can be taken from `SHOW FILESYSTEM CACHES` command. (For versions less
than or equal to `22.8` the command is named `DESCRIBE CACHE`)

```
DESCRIBE FILESYSTEM CACHE 's3_cache'
```

```
┌────max_size─┬─max_elements─┬─max_file_segment_size─┬─boundary_alignment─┬─cache_on_write_operations─┬─cache_hits_threshold─┬─current_size─┬─current_elements─┬─path───────┬─background_download_threads─┬─enable_bypass_cache_with_threshold─┐
│ 10000000000 │      1048576 │             104857600 │            4194304 │                         1 │                    0 │         3276 │               54 │ /s3_cache/ │                           2 │                                  0 │
└─────────────┴──────────────┴───────────────────────┴────────────────────┴───────────────────────────┴──────────────────────┴──────────────┴──────────────────┴────────────┴─────────────────────────────┴────────────────────────────────────┘
```

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Cache current metrics Cache asynchronous metrics Cache profile events|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `FilesystemCacheSize` `FilesystemCacheBytes` `CachedReadBufferReadFromSourceBytes`, `CachedReadBufferReadFromCacheBytes`| `FilesystemCacheElements` `FilesystemCacheFiles` `CachedReadBufferReadFromSourceMicroseconds`, `CachedReadBufferReadFromCacheMicroseconds`| `CachedReadBufferCacheWriteBytes`, `CachedReadBufferCacheWriteMicroseconds`| `CachedWriteBufferCacheWriteBytes`, `CachedWriteBufferCacheWriteMicroseconds` | | | | | | | | | | | | | | |

### Using static Web storage (read-only)[​](#web-storage "Direct link to Using static Web storage (read-only)")

This is a read-only disk. Its data is only read and never modified. A new table
is loaded to this disk via `ATTACH TABLE` query (see example below). Local disk
is not actually used, each `SELECT` query will result in a `http` request to
fetch required data. All modification of the table data will result in an
exception, i.e. the following types of queries are not allowed: [`CREATE TABLE`](/docs/sql-reference/statements/create/table),
[`ALTER TABLE`](/docs/sql-reference/statements/alter), [`RENAME TABLE`](/docs/sql-reference/statements/rename#rename-table),
[`DETACH TABLE`](/docs/sql-reference/statements/detach) and [`TRUNCATE TABLE`](/docs/sql-reference/statements/truncate).
Web storage can be used for read-only purposes. An example use is for hosting
sample data, or for migrating data. There is a tool `clickhouse-static-files-uploader`,
which prepares a data directory for a given table (`SELECT data_paths FROM system.tables WHERE name = 'table_name'`).
For each table you need, you get a directory of files. These files can be uploaded
to, for example, a web server with static files. After this preparation,
you can load this table into any ClickHouse server via `DiskWeb`.

In this sample configuration:

- the disk is of type `web`
- the data is hosted at `http://nginx:80/test1/`
- a cache on local storage is used

```
<clickhouse>
    <storage_configuration>
        <disks>
            <web>
                <type>web</type>
                <endpoint>http://nginx:80/test1/</endpoint>
            </web>
            <cached_web>
                <type>cache</type>
                <disk>web</disk>
                <path>cached_web_cache/</path>
                <max_size>100000000</max_size>
            </cached_web>
        </disks>
        <policies>
            <web>
                <volumes>
                    <main>
                        <disk>web</disk>
                    </main>
                </volumes>
            </web>
            <cached_web>
                <volumes>
                    <main>
                        <disk>cached_web</disk>
                    </main>
                </volumes>
            </cached_web>
        </policies>
    </storage_configuration>
</clickhouse>
```

Tip

Storage can also be configured temporarily within a query, if a web dataset is
not expected to be used routinely, see [dynamic configuration](#dynamic-configuration) and skip
editing the configuration file.

A [demo dataset](https://github.com/ClickHouse/web-tables-demo) is hosted in GitHub. To prepare your own tables for web
storage see the tool [clickhouse-static-files-uploader](/docs/operations/utilities/static-files-disk-uploader)

In this `ATTACH TABLE` query the `UUID` provided matches the directory name of the data, and the endpoint is the URL for the raw GitHub content.

```
-- highlight-next-line
ATTACH TABLE uk_price_paid UUID 'cf712b4f-2ca8-435c-ac23-c4393efe52f7'
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4),
    is_new UInt8,
    duration Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (postcode1, postcode2, addr1, addr2)
  -- highlight-start
  SETTINGS disk = disk(
      type=web,
      endpoint='https://raw.githubusercontent.com/ClickHouse/web-tables-demo/main/web/'
      );
  -- highlight-end
```

A ready test case. You need to add this configuration to config:

```
<clickhouse>
    <storage_configuration>
        <disks>
            <web>
                <type>web</type>
                <endpoint>https://clickhouse-datasets.s3.yandex.net/disk-with-static-files-tests/test-hits/</endpoint>
            </web>
        </disks>
        <policies>
            <web>
                <volumes>
                    <main>
                        <disk>web</disk>
                    </main>
                </volumes>
            </web>
        </policies>
    </storage_configuration>
</clickhouse>
```

And then execute this query: