> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Using native and binary formats in ClickHouse

> Page describing how to use native and binary formats in ClickHouse

export const CloudNotSupportedBadge = () => {
  return <div className="cloudNotSupportedBadge">
            <div className="cloudNotSupportedIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.5" d="M6.33366 12.6666L12.3739 12.6667C13.6593 12.6667 14.7073 11.6187 14.7073 10.3334C14.7073 9.04804 13.6593 8.00003 12.3739 8.00003C12.3739 8.00003 12.3337 7.66659 12.0003 7.33325M10.667 5.33322C8.00033 2.33325 4.45395 4.78537 4.14195 6.68203C2.55728 6.7627 1.29395 8.06203 1.29395 9.6667C1.29395 11.3234 2.66699 12.6666 4.00033 12.6666" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.5" d="M2.66699 14L12.0003 4.66663" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>

        </div>
            Not supported in ClickHouse Cloud
        </div>;
};

ClickHouse supports multiple binary formats, which result in better performance and space efficiency. Binary formats are also safe in character encoding since data is saved in a binary form.

We're going to use some\_data [table](/assets/some_data.sql) and [data](/assets/some_data.tsv) for demonstration, feel free to reproduce that on your ClickHouse instance.

<h2 id="exporting-in-a-native-clickhouse-format">
  Exporting in a Native ClickHouse format
</h2>

The most efficient data format to export and import data between ClickHouse nodes is [Native](/reference/formats/Native) format. Exporting is done using `INTO OUTFILE` clause:

```sql theme={null}
SELECT * FROM some_data
INTO OUTFILE 'data.clickhouse' FORMAT Native
```

This will create [data.clickhouse](/assets/data.clickhouse) file in a native format.

<h3 id="importing-from-a-native-format">
  Importing from a Native format
</h3>

To import data, we can use [file()](/reference/functions/table-functions/file) for smaller files or exploration purposes:

```sql theme={null}
DESCRIBE file('data.clickhouse', Native);
```

```response theme={null}
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ String │              │                    │         │                  │                │
│ month │ Date   │              │                    │         │                  │                │
│ hits  │ UInt32 │              │                    │         │                  │                │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

<Tip>
  When using the `file()` function, with ClickHouse Cloud you will need to run the commands in `clickhouse client` on the machine where the file resides. Another option is to use [`clickhouse-local`](/concepts/features/tools-and-utilities/clickhouse-local) to explore files locally.
</Tip>

In production, we use `FROM INFILE` to import data:

```sql theme={null}
INSERT INTO sometable
FROM INFILE 'data.clickhouse'
FORMAT Native
```

<h3 id="native-format-compression">
  Native format compression
</h3>

We can also enable compression while exporting data to Native format (as well as most other formats) using a `COMPRESSION` clause:

```sql theme={null}
SELECT * FROM some_data
INTO OUTFILE 'data.clickhouse'
COMPRESSION 'lz4'
FORMAT Native
```

We've used LZ4 compression for export. We'll have to specify it while importing data:

```sql theme={null}
INSERT INTO sometable
FROM INFILE 'data.clickhouse'
COMPRESSION 'lz4'
FORMAT Native
```

<h2 id="exporting-to-rowbinary">
  Exporting to RowBinary
</h2>

Another binary format supported is [RowBinary](/reference/formats/RowBinary/RowBinary), which allows importing and exporting data in binary-represented rows:

```sql theme={null}
SELECT * FROM some_data
INTO OUTFILE 'data.binary' FORMAT RowBinary
```

This will generate [data.binary](/assets/data.binary) file in a binary rows format.

<h3 id="exploring-rowbinary-files">
  Exploring RowBinary files
</h3>

Automatic schema inference isn't supported for this format, so to explore before loading, we have to define schema explicitly:

```sql theme={null}
SELECT *
FROM file('data.binary', RowBinary, 'path String, month Date, hits UInt32')
LIMIT 5
```

```response theme={null}
┌─path───────────────────────────┬──────month─┬─hits─┐
│ Bangor_City_Forest             │ 2015-07-01 │   34 │
│ Alireza_Afzal                  │ 2017-02-01 │   24 │
│ Akhaura-Laksam-Chittagong_Line │ 2015-09-01 │   30 │
│ 1973_National_500              │ 2017-10-01 │   80 │
│ Attachment                     │ 2017-09-01 │ 1356 │
└────────────────────────────────┴────────────┴──────┘
```

Consider using [RowBinaryWithNames](/reference/formats/RowBinary/RowBinaryWithNames), which also adds a header row with a columns list. [RowBinaryWithNamesAndTypes](/reference/formats/RowBinary/RowBinaryWithNamesAndTypes) will also add an additional header row with column types.

<h3 id="importing-from-rowbinary-files">
  Importing from RowBinary files
</h3>

To load data from a RowBinary file, we can use a `FROM INFILE` clause:

```sql theme={null}
INSERT INTO sometable
FROM INFILE 'data.binary'
FORMAT RowBinary
```

<h2 id="importing-single-binary-value-using-rawblob">
  Importing single binary value using RawBLOB
</h2>

Suppose we want to read an entire binary file and save it into a field in a table.
This is the case when the [RawBLOB format](/reference/formats/RawBLOB) can be used. This format can be directly used with a single-column table only:

```sql theme={null}
CREATE TABLE images(data String) ENGINE = Memory
```

Let's save an image file to the `images` table:

```bash theme={null}
cat image.jpg | clickhouse-client -q "INSERT INTO images FORMAT RawBLOB"
```

We can check the `data` field length which will be equal to the original file size:

```sql theme={null}
SELECT length(data) FROM images
```

```response theme={null}
┌─length(data)─┐
│         6121 │
└──────────────┘
```

<h3 id="exporting-rawblob-data">
  Exporting RawBLOB data
</h3>

This format can also be used to export data using an `INTO OUTFILE` clause:

```sql theme={null}
SELECT * FROM images LIMIT 1
INTO OUTFILE 'out.jpg'
FORMAT RawBLOB
```

Note that we had to use `LIMIT 1` because exporting more than a single value will create a corrupted file.

<h2 id="messagepack">
  MessagePack
</h2>

ClickHouse supports importing and exporting to [MessagePack](https://msgpack.org/) using the [MsgPack](/reference/formats/MsgPack). To export to MessagePack format:

```sql theme={null}
SELECT *
FROM some_data
INTO OUTFILE 'data.msgpk'
FORMAT MsgPack
```

To import data from a [MessagePack file](/assets/data.msgpk):

```sql theme={null}
INSERT INTO sometable
FROM INFILE 'data.msgpk'
FORMAT MsgPack
```

<h2 id="protocol-buffers">
  Protocol Buffers
</h2>

To work with [Protocol Buffers](/reference/formats/Protobuf/Protobuf) we first need to define a [schema file](/assets/schema.proto):

```protobuf theme={null}
syntax = "proto3";

message MessageType {
  string path = 1;
  date month = 2;
  uint32 hits = 3;
};
```

Path to this schema file (`schema.proto` in our case) is set in a `format_schema` settings option for the [Protobuf](/reference/formats/Protobuf/Protobuf) format:

```sql theme={null}
SELECT * FROM some_data
INTO OUTFILE 'proto.bin'
FORMAT Protobuf
SETTINGS format_schema = 'schema:MessageType'
```

This saves data to the [proto.bin](/assets/proto.bin) file. ClickHouse also supports importing Protobuf data as well as nested messages. Consider using [ProtobufSingle](/reference/formats/Protobuf/ProtobufSingle) to work with a single Protocol Buffer message (length delimiters will be omitted in this case).

<h2 id="capn-proto">
  Cap'n Proto
</h2>

Another popular binary serialization format supported by ClickHouse is [Cap'n Proto](https://capnproto.org/). Similarly to `Protobuf` format, we have to define a schema file ([`schema.capnp`](/assets/schema.capnp)) in our example:

```response theme={null}
@0xec8ff1a10aa10dbe;

struct PathStats {
  path @0 :Text;
  month @1 :UInt32;
  hits @2 :UInt32;
}
```

Now we can import and export using [CapnProto](/reference/formats/CapnProto) format and this schema:

```sql theme={null}
SELECT
    path,
    CAST(month, 'UInt32') AS month,
    hits
FROM some_data
INTO OUTFILE 'capnp.bin'
FORMAT CapnProto
SETTINGS format_schema = 'schema:PathStats'
```

Note that we had to cast the `Date` column as `UInt32` to [match corresponding types](/reference/formats/CapnProto#data_types-matching-capnproto).

<h2 id="other-formats">
  Other formats
</h2>

ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:

* [CSV and TSV formats](/guides/clickhouse/data-formats/csv-tsv)
* [Parquet](/guides/clickhouse/data-formats/parquet)
* [JSON formats](/guides/clickhouse/data-formats/json/intro)
* [Regex and templates](/guides/clickhouse/data-formats/templates-regex)
* **Native and binary formats**
* [SQL formats](/guides/clickhouse/data-formats/sql)

And also check [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) - a portable full-featured tool to work on local/remote files without starting ClickHouse server.