With the recent addition of Lightweight Deletes, read about the latest best practices for handling updates and deletes in ClickHouse.

large_Handling Updates.png

medium_Handling Updates.png

small_Handling Updates.png

thumbnail_Handling Updates.png

Handling Updates.png

Handling Updates and Deletes in ClickHouse

Discover how to combine aggregation functions with combinators, providing elegant solutions to specific data challenges. Empower your query capabilities.

large_Aggregate combinators v02.png

medium_Aggregate combinators v02.png

small_Aggregate combinators v02.png

thumbnail_Aggregate combinators v02.png

Aggregate combinators v02.png

Using Aggregate Combinators in ClickHouse

![Data Lifecycles.png](/uploads/Data_Lifecycles_bea551354f.png)

## Introduction

If the data that you are analyzing in ClickHouse grows over time you may want to plan to move, remove, or summarize older data on a schedule. Typically, your options here will depend on your data retention requirements and whether query SLAs vary depending on the data age. For example, while storing all dimensions at the highest available granularity on the latest data is usually required, it might be optional to store historical data with a lower level of detail.

Managing the data lifecycle can help optimize storage as well as improve query performance. ClickHouse has a simple but powerful data lifecycle management tool configured with the [`TTL`](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl) clause of DDL statements.

In this blog post, we explore the `TTL` clause and how this can be used to solve a number of data management tasks.

## Automatically delete expired data

Sometimes, it no longer makes sense to store older data and it can be removed from ClickHouse. This is usually called a retention policy. Data removal is done automatically in the background based on conditions in a `TTL` statement.

Suppose we have the `events` table, and we would like to delete all records older than one month:

```sql
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE
```

We add the `TTL` statement and use the `DELETE` operation with `time + INTERVAL 1 MONTH`. This will remove a record when its `time` column value is more than one month in the past.

**Note** that records removal is an asynchronous background process, and outdated records can still be available for some time.

Let's try to insert a couple of records, including an outdated one, into the table:

```sql
INSERT INTO events VALUES('error', now() - interval 2 month, 123), ('error', now(), 123);
```

We'll find both records available in the table right after insert:

```sql
SELECT * FROM events

┌─event─┬────────────────time─┬─value─┐
│ error │ 2022-11-24 09:34:44 │   123 │
│ error │ 2023-01-24 09:34:44 │   123 │
└───────┴─────────────────────┴───────┘
```

The outdated record will be removed in the background by an "off-schedule" merge which is scheduled periodically. Sometime later:

```sql
SELECT * FROM events
┌─event─┬────────────────time─┬─value─┐
│ error │ 2023-01-24 09:34:44 │   123 │
└───────┴─────────────────────┴───────┘
```

### Managing background removal

Background removal happens every 4 hours by default and can be controlled using the [`merge_with_ttl_timeout`](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#merge_with_ttl_timeout) table settings option:

```sql
CREATE TABLE events
...
TTL time + INTERVAL 1 MONTH DELETE
SETTINGS merge_with_ttl_timeout = 1200
```

Do not use values smaller than 300 seconds for this setting, as this can create I/O overhead and impact cluster performance. After some time, we can find that the outdated record no longer exists in our table:

```sql
SELECT * FROM events

┌─event─┬────────────────time─┬─value─┐
│ error │ 2023-01-24 09:34:44 │   123 │
└───────┴─────────────────────┴───────┘
```


### Filter which rows to delete

Let's assume we would like to delete only a specific type of record (e.g., where an `event` column value is `error`). We can additionally specify this in the `WHERE` clause of the `TTL` statement:

```sql
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE WHERE event = 'error'
```

Now, only outdated rows with the `event='error'` value will be removed:

```sql
INSERT INTO events VALUES('not_error', now() - interval 2 month, 123), ('error', now(), 123)
```

We can be sure the `not_error` record will not be removed (though it's older than one month):

```sql
SELECT * FROM events

┌─event─────┬────────────────time─┬─value─┐
│ error     │ 2023-01-24 09:48:05 │   123 │
│ not_error │ 2022-11-24 09:48:05 │   123 │
└───────────┴─────────────────────┴───────┘
```

### Multiple delete conditions

ClickHouse allows specifying multiple TTL statements. This allows us to be more flexible and specific about what to remove and when. Let's say we want to remove all non `error` events in 1 month and all errors after 6 months:

```sql
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error',
    time + INTERVAL 6 MONTH DELETE WHERE event = 'error'
```

We can have any number of rules configured for the `TTL` statement.

### Move data to historical tables

We can use [Materialized Views](https://clickhouse.com/blog/using-materialized-views-in-clickhouse) in combination with a `TTL` statement to address cases where we might want to move outdated data to another table before being removed from the main table.

Suppose we want to move `error` events to the table  `errors_history` before removing them from the `events` table. First, we create a target table for a materialized view, which is of the same structure as the `events` table:

```sql
CREATE TABLE errors_history (
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
```

**Note** that we can't use `CREATE TABLE errors_history AS events` as this will also copy the TTL expression, which we don't want to happen. Then we create the materialized view trigger to ingest data automatically into the `errors_history` table:


```sql
CREATE MATERIALIZED VIEW errors_history_mv TO errors_history AS
SELECT * FROM events WHERE event = 'error'
```

Now, when we insert data into the `events` table, `error` events are automatically inserted into the `errors_history` table as well. On the other hand, when the `TTL` procedure removes records from the `events` table, they remain in the `errors_history` table.

## Compacting historical data using aggregations

In many cases, we don't want to delete the data, but we can afford to reduce its level of detail in order to save resources. For example, let's consider a situation when we don't want to remove `error` events from our table. At the same time, we might not need the per-second details after one month, so we can leave daily aggregated numbers.

This can be implemented using a `GROUP BY ... SET` clause of the `TTL` statement:

```sql
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (toDate(time), event)
TTL time + INTERVAL 1 MONTH GROUP BY toDate(time), event SET value = SUM(value)
```

Several important points here:
* The `GROUP BY toDate(time), event` expression should be a prefix of the [primary key](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design), so we also changed `ORDER BY (toDate(time), event)`.
* The `SET value = SUM(value)` clause will set the `value` column to the sum of all values in each group (usual behavior for the `GROUP BY` clauses in queries).
* The `time` column value will be chosen randomly during grouping (as in the case of using [`any()` aggregate function](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/any/)).

Let's ensure the following data is inserted:

```sql
INSERT INTO events VALUES('error', now() - interval 2 month, 123),
                         ('error', now() - interval 2 month, 321);
```

After some time, when background merge happens, we can see our data is aggregated:

```sql
SELECT * FROM events
```
```results
┌─event─┬────────────────time─┬─value─┐
│ error │ 2022-11-24 12:36:23 │   444 │
└───────┴─────────────────────┴───────┘
```

## Changing compression

While it may not be feasible to remove or aggregate data, you may have more relaxed query SLAs for your historical data. To address this, we can consider using higher compression levels for older data to save more space. For example, we can ask ClickHouse to use [`LZ4HC`](https://clickhouse.com/docs/en/sql-reference/statements/create/table/#lz4hc) compression of the higher level for the data older than one month. We need to use the `RECOMPRESS` clause for this:

```sql
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (toDate(time), event)
TTL time + INTERVAL 1 MONTH RECOMPRESS CODEC(LZ4HC(10))
```

Note that recompressed data will take less space but will also take more time to compress, thus impacting insertion times.

## Column-level TTL

We can also manage the lifecycle of a single column using `TTL`. Suppose we have a `debug` column in our table that stores additional debug information (e.g., errors backtrace). This column is only useful for one week, during which it consumes a lot of space. We can ask ClickHouse to reset it to the default value after a week:

```sql
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64,
    `debug` String TTL time + INTERVAL 1 WEEK
)
ENGINE = MergeTree
ORDER BY (event, time)
```

Now let's insert an outdated record with a `debug` column value:

```sql
INSERT INTO events VALUES('error', now() - interval 1 month, 45, 'a lot of details');
```

ClickHouse will reset this `debug` column value to an empty string once the TTL processing is completed:

```sql
SELECT * FROM events

┌─event─┬────────────────time─┬─value─┬─debug─┐
│ error │ 2022-12-24 15:13:54 │    45 │       │
└───────┴─────────────────────┴───────┴───────┘
```

Note that ClickHouse will use the default value for the column `TTL`. So in case the `DEFAULT` expression is present, outdated columns will have this value assigned.

## Moving data between hot and cold storage

For local setups, consider using storage management for your data. Typically users need faster but smaller disks (called hot, e.g., SSD) and larger but slower ones (called cold, e.g., HDD or S3). ClickHouse allows setting data policy so that data is moved to a slower device once the faster device usage reaches a specific threshold. This storage policy is [configured](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-multiple-volumes_configure) in two steps - declaring a storage list and a policy.

Alternatively, consider using ClickHouse Cloud, which avoids this complexity by separating storage and compute using object storage. When coupled with intelligent caching for data queried more often, typically your newer "hot" data, tiered architectures are no longer required.

## Summary

ClickHouse provides powerful data lifecycles management tools to enable automatic removal, compaction, or movement between different storage types. Compacting and retention can be configured using the `TTL` statement at a table level. Use [disk policies](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-multiple-volumes) to manage storage or consider using [ClickHouse Cloud](https://clickhouse.com/cloud) as a reliable solution to scaling.

For more details on TTL, see our [recently published guide](https://clickhouse.com/docs/en/guides/developer/ttl/).


Master the art of data lifecycle management in ClickHouse with our comprehensive guide. Enhance your ClickHouse skills and learn to maximize data efficiency.

large_Data Lifecycles.png

medium_Data Lifecycles.png

small_Data Lifecycles.png

thumbnail_Data Lifecycles.png

Data Lifecycles.png

Using TTL to Manage Data Lifecycles in ClickHouse

![data-formats.png](/uploads/data_formats_48f2df5c4d.png)

## Introduction

Users new to ClickHouse are often surprised by the number of supported data formats, but sometimes need help identifying the best and easiest way to load their data. Since we believe loading data into your favorite OSS database should be as easy as possible, we have recently [enhanced our docs](https://clickhouse.com/docs/en/integrations/data-formats) to include examples for the most popular formats. This also includes some useful hints and tricks for more experienced users. 

This post provides a brief overview of ClickHouse's extensive support for different formats and how to load your local files.

## Importing and exporting data

ClickHouse supports most of the known text and binary data formats. This allows easy integration into almost any working data pipeline to leverage the benefits of ClickHouse.

### Standard text formats

CSV is one of the most popular formats to store data due to its simplicity. Importing and exporting CSV data is easy with the [CSV](https://clickhouse.com/docs/en/interfaces/formats/#csv) format:

```bash
clickhouse-client -q "INSERT INTO some_table FORMAT CSV" < data.csv
```

In many cases, CSV files are broken, poorly encoded, and have custom delimiters or even line separators. ClickHouse provides ways to handle any of these cases.

To process CSVs with custom delimiters (`;` in our example), we have to set the following option:

```sql
SET format_csv_delimiter = ';';
```

### Importing data from broken or custom CSV files

In cases where the CSV file is encoded in a non-standard way or just invalid, we can use the [CustomSeparated](https://clickhouse.com/docs/en/sql-reference/formats/#format-customseparated) format to customize escaping rules and delimiters:

```sql
SET format_custom_field_delimiter = '|';
SET format_custom_row_between_delimiter = ';';
SET format_custom_escaping_rule = 'JSON';
```

Here, we've used the JSON [escaping rule](https://clickhouse.com/docs/en/operations/settings/settings/#format_custom_escaping_rule), `|` as a custom value delimiter, and `;` as the line separator. After the settings are changed, we can continue with our import:

```sql
INSERT INTO some_table FROM INFILE 'custom.csv' FORMAT CustomSeparated
```

Another popular text data format is Tab Separated Values (TSV), which ClickHouse also supports with the [TabSeparated](https://clickhouse.com/docs/en/interfaces/formats/#tabseparated) format:


```sql
clickhouse-client -q "INSERT INTO some_table FORMAT TabSeparated" < 
data.tsv
```

Explore more of ClickHouse's capabilities for working with the family of CSV formats in our docs, including [rows skipping](https://clickhouse.com/docs/en/integrations/data-formats/csv-tsv/#skipping-lines-in-a-csv-file), controlling [Null values](https://clickhouse.com/docs/en/integrations/data-formats/csv-tsv#treating-null-values-in-csv-files), [automatic decompression](https://clickhouse.com/docs/en/integrations/data-formats/csv-tsv#working-with-large-csv-files), and more.

### JSON data

ClickHouse can work with almost any JSON data, be that an array of values, an object of objects, or separate JSON objects.

For example, a logging app can write logs as a JSON object per line (known as [NDJson](http://ndjson.org/)). This case can be addressed using the [JSONEachRow](https://clickhouse.com/docs/en/interfaces/formats/#jsoneachrow) format in ClickHouse:

```bash
clickhouse-client - q "INSERT INTO sometable FORMAT JSONEachRow" < 
access.log
```

Explore how JSON data of different forms can be [loaded to ClickHouse](https://clickhouse.com/docs/en/integrations/data-formats/json#importing-json-data) as well as [exported](https://clickhouse.com/docs/en/integrations/data-formats/json#exporting-json-data). It is worth mentioning that ClickHouse also supports the [BSON format](https://clickhouse.com/docs/en/integrations/data-formats/json#importing-and-exporting-bson) used by MongoDB.


## Regular expressions for custom text formats

Besides standard formats like CSV or JSON, ClickHouse also supports data import based on [regular expressions](https://clickhouse.com/docs/en/integrations/data-formats/templates-regexp#importing-data-based-on-regular-expressions). In this case, the [`Regexp`](https://clickhouse.com/docs/en/interfaces/formats/#data-format-regexp) format should be used together with the `format_regexp` option containing regular expression with capture groups (treated as table columns):

```sql
INSERT INTO some_log FROM INFILE 'custom.txt'
SETTINGS
  format_regexp = '([0-9]+?) \[(.+?)\] \- "(.+)"'
FORMAT Regexp
```

This query can be used to load the following example file:

```
121201 [notice] - "Started service"
121202 [error] - "Configuration file not found"
122203 [warning] - "Creating default configuration file"
```

Another option to process custom text formats is to use a [Template format](https://clickhouse.com/docs/en/integrations/data-formats/templates-regexp#importing-based-on-a-template). The Template format is even more powerful in terms of exporting data because it allows rendering query results into high-level formats, like [HTML](https://clickhouse.com/docs/en/integrations/data-formats/templates-regexp#exporting-to-html-files).

## Native and binary formats

ClickHouse has its own [native format](https://clickhouse.com/docs/en/integrations/data-formats/binary-native#exporting-in-a-native-clickhouse-format) that can be used to import and export data. It's more efficient than text formats regarding processing speed and space usage. The Native format is helpful for transferring data between ClickHouse servers when they don't have a direct connection with each other. For example, to transfer data from a ClickHouse server to ClickHouse Cloud:

```bash
clickhouse-client -q "SELECT * FROM some_table FORMAT Native" | \
clickhouse-client --host some.aws.clickhouse.cloud --secure \
--port 9440 --password 12345 \
-q "INSERT INTO some_table FORMAT Native"
```

[Binary formats](https://clickhouse.com/docs/en/integrations/data-formats/binary-native#exporting-to-rowbinary) are usually more efficient and safe than text formats but are limited in support. ClickHouse has the RowBinary format for general binary cases, and RawBLOB is used with (but is not limited to) files. Additionally, ClickHouse supports popular serialization formats like [Protocol Buffers](https://clickhouse.com/docs/en/integrations/data-formats/binary-native#protocol-buffers), [Cap’n Proto](https://clickhouse.com/docs/en/integrations/data-formats/binary-native#capn-proto) and [Message Pack](https://clickhouse.com/docs/en/integrations/data-formats/binary-native#messagepack).

## Parquet and other Apache formats

Apache has multiple data storage and serialization formats that are popular in Hadoop environments. ClickHouse can work with all of them, [including Parquet](https://clickhouse.com/docs/en/integrations/data-formats/parquet-arrow-avro-orc#working-with-parquet-data).

We can import data from a Parquet file:

```sql
clickhouse-client -q "INSERT INTO some_table FORMAT Parquet" < 
data.parquet
```

By using the [`file()`](https://clickhouse.com/docs/en/sql-reference/table-functions/file/) function and [`clickhouse-local`](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local), we can explore data before actually loading it into a table:

<pre class='code-with-play'>
<div class='code'>
SELECT *
FROM file('data.parquet')
LIMIT 3;

┌─path──────────────────────┬─date───────┬─hits─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │  241 │
│ Aegithina_tiphia          │ 2018-02-01 │   34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │    1 │
└───────────────────────────┴────────────┴──────┘
</div>
</pre>
<br />

We can also export data to a Parquet file using the client:

```sql
clickhouse-client -q "SELECT * FROM some_table FORMAT Parquet" > 
file.parquet
```

Find out more about other supported Apache formats, such as [Avro](https://clickhouse.com/docs/en/integrations/data-formats/parquet-arrow-avro-orc#importing-and-exporting-in-avro-format), [Arrow](https://clickhouse.com/docs/en/integrations/data-formats/parquet-arrow-avro-orc#working-with-arrow-format), and [ORC](https://clickhouse.com/docs/en/integrations/data-formats/parquet-arrow-avro-orc#importing-and-exporting-orc-data).

## SQL dumps

Though SQL dumps are inefficient in storing and transferring data, ClickHouse supports loading data from MySQL dumps and creating SQL dumps for Mysql, PostgreSQL, and other databases.

To create a SQL dump, the [`SQLInsert`](https://clickhouse.com/docs/en/interfaces/formats/#sqlinsert) format should be used:

```sql
SET output_format_sql_insert_table_name = 'a_table_name';
SET output_format_sql_insert_include_column_names = 0;
SELECT * FROM some_table
INTO OUTFILE 'dump.sql'
FORMAT SQLInsert;
```

This will create the `dump.sql` file with an SQL values dump in it. It will use `a_table_name` as a table name and skip columns declaration. It can then be fed to other DBMS:

```bash
psql < dump.sql
```

ClickHouse also supports importing data from MySQL dumps using the [`MySQLDump`](https://clickhouse.com/docs/en/interfaces/formats/#mysqldump) format:

```sql
cat mysql-dump.sql | clickhouse-client -q "INSERT INTO some_data FORMAT MySQLDump"
```

Learn more about [importing and exporting SQL data](https://clickhouse.com/docs/en/integrations/data-formats/sql) in ClickHouse.

## Null format for performance testing

There's also a special [Null](https://clickhouse.com/docs/en/interfaces/formats/#null) data format that will not print anything but wait for the query to execute:

```sql
SELECT *
FROM big_table
LIMIT 100000
FORMAT `Null`

0 rows in set. Elapsed: 1.112 sec. Processed 131.07 thousand rows, 167.57 MB (117.86 thousand rows/s., 150.68 MB/s.)
```

ClickHouse server still returns data to the client, but it's not printed. This makes the `Null` format useful for testing query performance which returns too much data to fit into the terminal.

## Prettifying command line

By default, ClickHouse uses the [PrettyCompact](https://clickhouse.com/docs/en/interfaces/formats/#prettycompact) format for the command line client. It outputs data in blocks sometimes (as soon as results are returned):

<pre class='code-with-play'>
<div class='code'>
┌─id──────────────────────────┬─gender─┬─birth_year─┐
│ B7mkoLYIZEdoPkfCRTKtYg_0000 │ female │ 1951       │
└─────────────────────────────┴────────┴────────────┘
┌─id──────────────────────────┬─gender─┬─birth_year─┐
│ nzHXUMnmjspwV4JxL-KqzQ_0000 │ female │ 1956       │
└─────────────────────────────┴────────┴────────────┘
┌─id──────────────────────────┬─gender─┬─birth_year─┐
│ 5cs05UbDttZBFBE6tPpjUg_0000 │ male   │ 1989       │
│ 5cs05UbDttZBFBE6tPpjUg_0000 │ male   │ 1989       │
└─────────────────────────────┴────────┴────────────┘
...
</div>
</pre>
<br />

We can use [PrettyCompactMonoBlock](https://clickhouse.com/docs/en/interfaces/formats/#prettycompactmonoblock) to ask ClickHouse to output results as a single table:

<pre class='code-with-play'>
<div class='code'>
SELECT * FROM some_table
FORMAT PrettyCompactMonoBlock;

┌─id──────────────────────────┬─gender─┬─birth_year─┐
│ B7mkoLYIZEdoPkfCRTKtYg_0000 │ female │ 1951       │
│ nzHXUMnmjspwV4JxL-KqzQ_0000 │ female │ 1956       │
│ 5cs05UbDttZBFBE6tPpjUg_0000 │ male   │ 1989       │
│ 5cs05UbDttZBFBE6tPpjUg_0000 │ male   │ 1989       │
│ mcXoSkxAk-1xGpSiqnCB1Q_0000 │ female │ 1961       │
└─────────────────────────────┴────────┴────────────┘
</div>
</pre>
<br />

Less compact but easier to perceive is the [Pretty](https://clickhouse.com/docs/en/interfaces/formats/#pretty) format (also [PrettyMonoBlock](https://clickhouse.com/docs/en/interfaces/formats/#prettymonoblock) is available for single table output):

<pre class='code-with-play'>
<div class='code'>
SELECT FROM some_table
FORMAT PrettyMonoBlock;

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ path                           ┃ hits ┃ bar(hits, 0, 1500, 25)  ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ Bangor_City_Forest             │   34 │ ▌                       │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Alireza_Afzal                  │   24 │ ▍                       │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Akhaura-Laksam-Chittagong_Line │   30 │ ▌                       │
├────────────────────────────────┼──────┼─────────────────────────┤
│ 1973_National_500              │   80 │ █▎                      │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Attachment                     │ 1356 │ ██████████████████████▌ │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Kellett_Strait                 │    5 │                         │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Ajarani_River                  │   30 │ ▌                       │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Akbarabad,_Khomeyn             │    8 │ ▏                       │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Adriaan_Theodoor_Peperzak      │   88 │ █▍                      │
├────────────────────────────────┼──────┼─────────────────────────┤
│ Alucita_dryogramma             │    1 │                         │
└────────────────────────────────┴──────┴─────────────────────────┘
</div>
</pre>
<br />

Finally, we can ask ClickHouse to get rid of the table grid with the [PrettySpace](https://clickhouse.com/docs/en/interfaces/formats/#prettyspace) format (and [PrettySpaceMonoBlock](https://clickhouse.com/docs/en/interfaces/formats/#prettyspacemonoblock)):

<pre class='code-with-play'>
<div class='code'>
SELECT FROM some_table
FORMAT PrettySpace;

 path                             hits   bar(hits, 0, 1500, 25)

 Bangor_City_Forest                 34   ▌                       
 Alireza_Afzal                      24   ▍                       
 Akhaura-Laksam-Chittagong_Line     30   ▌                       
 1973_National_500                  80   █▎                      
 Attachment                       1356   ██████████████████████▌
 Kellett_Strait                      5                           
 Ajarani_River                      30   ▌                       
 Akbarabad,_Khomeyn                  8   ▏                       
 Adriaan_Theodoor_Peperzak          88   █▍                      
 Alucita_dryogramma                  1       
</div>
</pre>
<br />

## Summary

ClickHouse provides tools for all imaginable formats - standard text, binary or custom ones. Explore in-depth data formats in the official documentation and [our new guides](https://clickhouse.com/docs/en/integrations/data-formats), which we plan to continue to enhance. Consider using [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local), which is a powerful portable tool to query, convert and transform data from local files.


Learn about the wide range of data formats supported by ClickHouse, for both input and output, and read more in our newly published guides. 

large_data-formats.png

medium_data-formats.png

small_data-formats.png

thumbnail_data-formats.png

data-formats.png

An Introduction to Data Formats in ClickHouse

ClickHouse でMaterialized Viewを使い、クエリパフォーマンスを向上させる方法や、データ管理の可能性を広げる方法について解説します。

large_materialized views blog.png

medium_materialized views blog.png

small_materialized views blog.png

thumbnail_materialized views blog.png

materialized views blog.png

ClickHouse で Materialized View を使う

Learn how to use Materialized Views in ClickHouse to improve query performance and extend data management capabilities.

Using Materialized Views in ClickHouse

Linux, macOS, FreeBSD

Denys Golotiuk

Blog posts by Denys Golotiuk