Using native and binary formats in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/data-formats/binary.md)

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](/docs/assets/files/some_data-a632dc4a002ca619e7c3c4cbeedb744f.sql) and [data](/docs/assets/files/some_data-d46c76038ba8bd91e24d9ff9e4a8b8f9.tsv) for demonstration, feel free to reproduce that on your ClickHouse instance.

## Exporting in a Native ClickHouse format[​](#exporting-in-a-native-clickhouse-format "Direct link to Exporting in a Native ClickHouse format")

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

```
SELECT * FROM some_data
INTO OUTFILE 'data.clickhouse' FORMAT Native
```

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

### Importing from a Native format[​](#importing-from-a-native-format "Direct link to Importing from a Native format")

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

```
DESCRIBE file('data.clickhouse', Native);
```

```
┌─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`](/docs/operations/utilities/clickhouse-local) to explore files locally.

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

```
INSERT INTO sometable
FROM INFILE 'data.clickhouse'
FORMAT Native
```

### Native format compression[​](#native-format-compression "Direct link to Native format compression")

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

```
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:

```
INSERT INTO sometable
FROM INFILE 'data.clickhouse'
COMPRESSION 'lz4'
FORMAT Native
```

## Exporting to RowBinary[​](#exporting-to-rowbinary "Direct link to Exporting to RowBinary")

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

```
SELECT * FROM some_data
INTO OUTFILE 'data.binary' FORMAT RowBinary
```

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

### Exploring RowBinary files[​](#exploring-rowbinary-files "Direct link to Exploring RowBinary files")

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

```
SELECT *
FROM file('data.binary', RowBinary, 'path String, month Date, hits UInt32')
LIMIT 5
```

```
┌─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](/docs/interfaces/formats/RowBinaryWithNames), which also adds a header row with a columns list. [RowBinaryWithNamesAndTypes](/docs/interfaces/formats/RowBinaryWithNamesAndTypes) will also add an additional header row with column types.

### Importing from RowBinary files[​](#importing-from-rowbinary-files "Direct link to Importing from RowBinary files")

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

```
INSERT INTO sometable
FROM INFILE 'data.binary'
FORMAT RowBinary
```

## Importing single binary value using RawBLOB[​](#importing-single-binary-value-using-rawblob "Direct link to Importing single binary value using RawBLOB")

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](/docs/interfaces/formats/RawBLOB) can be used. This format can be directly used with a single-column table only:

```
CREATE TABLE images(data String) ENGINE = Memory
```

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

```
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:

```
SELECT length(data) FROM images
```

```
┌─length(data)─┐
│         6121 │
└──────────────┘
```

### Exporting RawBLOB data[​](#exporting-rawblob-data "Direct link to Exporting RawBLOB data")

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

```
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.

## MessagePack[​](#messagepack "Direct link to MessagePack")

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

```
SELECT *
FROM some_data
INTO OUTFILE 'data.msgpk'
FORMAT MsgPack
```

To import data from a [MessagePack file](/docs/assets/files/data-52382c91f64279d4cc08a203c8d033b7.msgpk):

```
INSERT INTO sometable
FROM INFILE 'data.msgpk'
FORMAT MsgPack
```

## Protocol Buffers[​](#protocol-buffers "Direct link to Protocol Buffers")

Not supported in ClickHouse Cloud

To work with [Protocol Buffers](/docs/interfaces/formats/Protobuf) we first need to define a [schema file](/docs/assets/files/schema-389da5b0925fda6e0f06c944f9427e3d.proto):

```
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](/docs/interfaces/formats/Protobuf) format:

```
SELECT * FROM some_data
INTO OUTFILE 'proto.bin'
FORMAT Protobuf
SETTINGS format_schema = 'schema:MessageType'
```

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

## Cap'n Proto[​](#capn-proto "Direct link to Cap'n Proto")

Not supported in ClickHouse Cloud

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`](/docs/assets/files/schema-eb595147f6142930f144316cbe1707e3.capnp)) in our example:

```
@0xec8ff1a10aa10dbe;

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

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

```
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](/docs/interfaces/formats/CapnProto#data_types-matching-capnproto).

## Other formats[​](#other-formats "Direct link to Other formats")