title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/formats/CapnProto.md)

Not supported in ClickHouse Cloud

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Input Output Alias|  |  |  | | --- | --- | --- | | ✔ ✔  | | | | | |

## Description[​](#description "Direct link to Description")

The `CapnProto` format is a binary message format similar to the [`Protocol Buffers`](https://developers.google.com/protocol-buffers/) format and [Thrift](https://en.wikipedia.org/wiki/Apache_Thrift), but not like [JSON](/docs/interfaces/formats/JSON) or [MessagePack](https://msgpack.org/).
CapnProto messages are strictly typed and not self-describing, meaning they need an external schema description. The schema is applied on the fly and cached for each query.

See also [Format Schema](/docs/interfaces/formats#formatschema).

## Data types matching[​](#data_types-matching-capnproto "Direct link to Data types matching")

The table below shows supported data types and how they match ClickHouse [data types](/docs/sql-reference/data-types) in `INSERT` and `SELECT` queries.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| CapnProto data type (`INSERT`) ClickHouse data type CapnProto data type (`SELECT`)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `UINT8`, `BOOL` [UInt8](/docs/sql-reference/data-types/int-uint) `UINT8`| `INT8` [Int8](/docs/sql-reference/data-types/int-uint) `INT8`| `UINT16` [UInt16](/docs/sql-reference/data-types/int-uint), [Date](/docs/sql-reference/data-types/date) `UINT16`| `INT16` [Int16](/docs/sql-reference/data-types/int-uint) `INT16`| `UINT32` [UInt32](/docs/sql-reference/data-types/int-uint), [DateTime](/docs/sql-reference/data-types/datetime) `UINT32`| `INT32` [Int32](/docs/sql-reference/data-types/int-uint), [Decimal32](/docs/sql-reference/data-types/decimal) `INT32`| `UINT64` [UInt64](/docs/sql-reference/data-types/int-uint) `UINT64`| `INT64` [Int64](/docs/sql-reference/data-types/int-uint), [DateTime64](/docs/sql-reference/data-types/datetime), [Decimal64](/docs/sql-reference/data-types/decimal) `INT64`| `FLOAT32` [Float32](/docs/sql-reference/data-types/float) `FLOAT32`| `FLOAT64` [Float64](/docs/sql-reference/data-types/float) `FLOAT64`| `TEXT, DATA` [String](/docs/sql-reference/data-types/string), [FixedString](/docs/sql-reference/data-types/fixedstring) `TEXT, DATA`| `union(T, Void), union(Void, T)` [Nullable(T)](/docs/sql-reference/data-types/date) `union(T, Void), union(Void, T)`| `ENUM` [Enum(8/16)](/docs/sql-reference/data-types/enum) `ENUM`| `LIST` [Array](/docs/sql-reference/data-types/array) `LIST`| `STRUCT` [Tuple](/docs/sql-reference/data-types/tuple) `STRUCT`| `UINT32` [IPv4](/docs/sql-reference/data-types/ipv4) `UINT32`| `DATA` [IPv6](/docs/sql-reference/data-types/ipv6) `DATA`| `DATA` [Int128/UInt128/Int256/UInt256](/docs/sql-reference/data-types/int-uint) `DATA`| `DATA` [Decimal128/Decimal256](/docs/sql-reference/data-types/decimal) `DATA`| `STRUCT(entries LIST(STRUCT(key Key, value Value)))` [Map](/docs/sql-reference/data-types/map) `STRUCT(entries LIST(STRUCT(key Key, value Value)))` | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

- Integer types can be converted into each other during input/output.
- For working with `Enum` in CapnProto format use the [format\_capn\_proto\_enum\_comparising\_mode](/docs/operations/settings/formats#format_capn_proto_enum_comparising_mode) setting.
- Arrays can be nested and can have a value of the `Nullable` type as an argument. `Tuple` and `Map` types also can be nested.

## Example usage[​](#example-usage "Direct link to Example usage")

### Inserting and selecting data[​](#inserting-and-selecting-data-capnproto "Direct link to Inserting and selecting data")

You can insert CapnProto data from a file into ClickHouse table by the following command:

```
$ cat capnproto_messages.bin | clickhouse-client --query "INSERT INTO test.hits SETTINGS format_schema = 'schema:Message' FORMAT CapnProto"
```

Where the `schema.capnp` looks like this:

```
struct Message {
  SearchPhrase @0 :Text;
  c @1 :Uint64;
}
```

You can select data from a ClickHouse table and save them into some file in the `CapnProto` format using the following command:

```
$ clickhouse-client --query = "SELECT * FROM test.hits FORMAT CapnProto SETTINGS format_schema = 'schema:Message'"
```

### Using autogenerated schema[​](#using-autogenerated-capn-proto-schema "Direct link to Using autogenerated schema")

If you don't have an external `CapnProto` schema for your data, you can still output/input data in `CapnProto` format using autogenerated schema.

For example:

```
SELECT * FROM test.hits 
FORMAT CapnProto 
SETTINGS format_capn_proto_use_autogenerated_schema=1
```

In this case, ClickHouse will autogenerate CapnProto schema according to the table structure using function [structureToCapnProtoSchema](/docs/sql-reference/functions/other-functions#structureToCapnProtoSchema) and will use this schema to serialize data in CapnProto format.

You can also read CapnProto file with autogenerated schema (in this case the file must be created using the same schema):

```
$ cat hits.bin | clickhouse-client --query "INSERT INTO test.hits SETTINGS format_capn_proto_use_autogenerated_schema=1 FORMAT CapnProto"
```

## Format settings[​](#format-settings "Direct link to Format settings")

The setting [`format_capn_proto_use_autogenerated_schema`](/docs/operations/settings/formats#format_capn_proto_use_autogenerated_schema) is enabled by default and is applicable if [`format_schema`](/docs/interfaces/formats#formatschema) is not set.

You can also save the autogenerated schema to a file during input/output using setting [`output_format_schema`](/docs/operations/settings/formats#output_format_schema).

For example:

```
SELECT * FROM test.hits 
FORMAT CapnProto 
SETTINGS 
    format_capn_proto_use_autogenerated_schema=1,
    output_format_schema='path/to/schema/schema.capnp'
```

In this case, the autogenerated `CapnProto` schema will be saved in file `path/to/schema/schema.capnp`.

- [Description](#description)- [Data types matching](#data_types-matching-capnproto)- [Example usage](#example-usage)
      - [Inserting and selecting data](#inserting-and-selecting-data-capnproto)- [Using autogenerated schema](#using-autogenerated-capn-proto-schema)- [Format settings](#format-settings)

Was this page helpful?