title_wip

### CapnProto[​](#capnproto "Direct link to CapnProto")

In schema inference for CapnProto format ClickHouse uses the following type matches:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| CapnProto data type ClickHouse data type|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `Bool` [UInt8](/docs/sql-reference/data-types/int-uint)| `Int8` [Int8](/docs/sql-reference/data-types/int-uint)| `UInt8` [UInt8](/docs/sql-reference/data-types/int-uint)| `Int16` [Int16](/docs/sql-reference/data-types/int-uint)| `UInt16` [UInt16](/docs/sql-reference/data-types/int-uint)| `Int32` [Int32](/docs/sql-reference/data-types/int-uint)| `UInt32` [UInt32](/docs/sql-reference/data-types/int-uint)| `Int64` [Int64](/docs/sql-reference/data-types/int-uint)| `UInt64` [UInt64](/docs/sql-reference/data-types/int-uint)| `Float32` [Float32](/docs/sql-reference/data-types/float)| `Float64` [Float64](/docs/sql-reference/data-types/float)| `Text`, `Data` [String](/docs/sql-reference/data-types/string)| `enum` [Enum](/docs/sql-reference/data-types/enum)| `List` [Array](/docs/sql-reference/data-types/array)| `struct` [Tuple](/docs/sql-reference/data-types/tuple)| `union(T, Void)`, `union(Void, T)` [Nullable(T)](/docs/sql-reference/data-types/nullable) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

## Strong-typed binary formats[​](#strong-typed-binary-formats "Direct link to Strong-typed binary formats")

In such formats, each serialized value contains information about its type (and possibly about its name), but there is no information about the whole table.
In schema inference for such formats, ClickHouse reads data row by row (up to `input_format_max_rows_to_read_for_schema_inference` rows or `input_format_max_bytes_to_read_for_schema_inference` bytes) and extracts
the type (and possibly name) for each value from the data and then converts these types to ClickHouse types.

### MsgPack[​](#msgpack "Direct link to MsgPack")

In MsgPack format there is no delimiter between rows, to use schema inference for this format you should specify the number of columns in the table
using the setting `input_format_msgpack_number_of_columns`. ClickHouse uses the following type matches:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| MessagePack data type (`INSERT`) ClickHouse data type|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `int N`, `uint N`, `negative fixint`, `positive fixint` [Int64](/docs/sql-reference/data-types/int-uint)| `bool` [UInt8](/docs/sql-reference/data-types/int-uint)| `fixstr`, `str 8`, `str 16`, `str 32`, `bin 8`, `bin 16`, `bin 32` [String](/docs/sql-reference/data-types/string)| `float 32` [Float32](/docs/sql-reference/data-types/float)| `float 64` [Float64](/docs/sql-reference/data-types/float)| `uint 16` [Date](/docs/sql-reference/data-types/date)| `uint 32` [DateTime](/docs/sql-reference/data-types/datetime)| `uint 64` [DateTime64](/docs/sql-reference/data-types/datetime)| `fixarray`, `array 16`, `array 32` [Array](/docs/sql-reference/data-types/array)| `fixmap`, `map 16`, `map 32` [Map](/docs/sql-reference/data-types/map) | | | | | | | | | | | | | | | | | | | | | |

By default, all inferred types are inside `Nullable`, but it can be changed using the setting `schema_inference_make_columns_nullable`.

### BSONEachRow[​](#bsoneachrow "Direct link to BSONEachRow")

In BSONEachRow each row of data is presented as a BSON document. In schema inference ClickHouse reads BSON documents one by one and extracts
values, names, and types from the data and then transforms these types to ClickHouse types using the following type matches:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| BSON Type ClickHouse type|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\x08` boolean [Bool](/docs/sql-reference/data-types/boolean)| `\x10` int32 [Int32](/docs/sql-reference/data-types/int-uint)| `\x12` int64 [Int64](/docs/sql-reference/data-types/int-uint)| `\x01` double [Float64](/docs/sql-reference/data-types/float)| `\x09` datetime [DateTime64](/docs/sql-reference/data-types/datetime64)| `\x05` binary with`\x00` binary subtype, `\x02` string, `\x0E` symbol, `\x0D` JavaScript code [String](/docs/sql-reference/data-types/string)| `\x07` ObjectId, [FixedString(12)](/docs/sql-reference/data-types/fixedstring)| `\x05` binary with `\x04` uuid subtype, size = 16 [UUID](/docs/sql-reference/data-types/uuid)| `\x04` array [Array](/docs/sql-reference/data-types/array)/[Tuple](/docs/sql-reference/data-types/tuple) (if nested types are different)| `\x03` document [Named Tuple](/docs/sql-reference/data-types/tuple)/[Map](/docs/sql-reference/data-types/map) (with String keys) | | | | | | | | | | | | | | | | | | | | | |

By default, all inferred types are inside `Nullable`, but it can be changed using the setting `schema_inference_make_columns_nullable`.

## Formats with constant schema[​](#formats-with-constant-schema "Direct link to Formats with constant schema")

Data in such formats always have the same schema.

### LineAsString[​](#line-as-string "Direct link to LineAsString")

In this format, ClickHouse reads the whole line from the data into a single column with `String` data type. The inferred type for this format is always `String` and the column name is `line`.

**Example**

```
DESC format(LineAsString, 'Hello\nworld!')
```

```
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

### JSONAsString[​](#json-as-string "Direct link to JSONAsString")

In this format, ClickHouse reads the whole JSON object from the data into a single column with `String` data type. The inferred type for this format is always `String` and the column name is `json`.

**Example**

```
DESC format(JSONAsString, '{"x" : 42, "y" : "Hello, World!"}')
```

```
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

### JSONAsObject[​](#json-as-object "Direct link to JSONAsObject")

In this format, ClickHouse reads the whole JSON object from the data into a single column with `JSON` data type. Inferred type for this format is always `JSON` and the column name is `json`.

**Example**

```
DESC format(JSONAsObject, '{"x" : 42, "y" : "Hello, World!"}');
```

```
┌─name─┬─type─┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ JSON │              │                    │         │                  │                │
└──────┴──────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

## Schema inference modes[​](#schema-inference-modes "Direct link to Schema inference modes")