BSONEachRow - ClickHouse Documentation

Input	Output	Alias
✔	✔

Description

The BSONEachRow format parses data as a sequence of Binary JSON (BSON) documents without any separator between them. Each row is formatted as a single document and each column is formatted as a single BSON document field with the column name as a key.

Data types matching

For output it uses the following correspondence between ClickHouse types and BSON types:

ClickHouse type	BSON Type
Bool	`\x08` boolean
Int8/UInt8/Enum8	`\x10` int32
Int16/UInt16/Enum16	`\x10` int32
Int32	`\x10` int32
UInt32	`\x12` int64
Int64/UInt64	`\x12` int64
Float32/Float64	`\x01` double
Date/Date32	`\x10` int32
DateTime	`\x12` int64
DateTime64	`\x09` datetime
Decimal32	`\x10` int32
Decimal64	`\x12` int64
Decimal128	`\x05` binary, `\x00` binary subtype, size = 16
Decimal256	`\x05` binary, `\x00` binary subtype, size = 32
Int128/UInt128	`\x05` binary, `\x00` binary subtype, size = 16
Int256/UInt256	`\x05` binary, `\x00` binary subtype, size = 32
String/FixedString	`\x05` binary, `\x00` binary subtype or \x02 string if setting output_format_bson_string_as_string is enabled
UUID	`\x05` binary, `\x04` uuid subtype, size = 16
Array	`\x04` array
Tuple	`\x04` array
Named Tuple	`\x03` document
Map	`\x03` document
IPv4	`\x10` int32
IPv6	`\x05` binary, `\x00` binary subtype

For input it uses the following correspondence between BSON types and ClickHouse types:

BSON Type	ClickHouse Type
`\x01` double	Float32/Float64
`\x02` string	String/FixedString
`\x03` document	Map/Named Tuple
`\x04` array	Array/Tuple
`\x05` binary, `\x00` binary subtype	String/FixedString/IPv6
`\x05` binary, `\x02` old binary subtype	String/FixedString
`\x05` binary, `\x03` old uuid subtype	UUID
`\x05` binary, `\x04` uuid subtype	UUID
`\x07` ObjectId	String/FixedString
`\x08` boolean	Bool
`\x09` datetime	DateTime64
`\x0A` null value	NULL
`\x0D` JavaScript code	String/FixedString
`\x0E` symbol	String/FixedString
`\x10` int32	Int32/UInt32/Decimal32/IPv4/Enum8/Enum16
`\x12` int64	Int64/UInt64/Decimal64/DateTime64

Other BSON types are not supported. Additionally, it performs conversion between different integer types. For example, it is possible to insert a BSON int32 value into ClickHouse as UInt8. Big integers and decimals such as Int128/UInt128/Int256/UInt256/Decimal128/Decimal256 can be parsed from a BSON Binary value with the \x00 binary subtype. In this case, the format will validate that the size of the binary data equals the size of the expected value.

This format does not work properly on Big-Endian platforms.

Example usage

Inserting data

Using a BSON file with the following data, named as football.bson:

    ┌───────date─┬─season─┬─home_team─────────────┬─away_team───────────┬─home_team_goals─┬─away_team_goals─┐
│ 2022-04-30 │   2021 │ Sutton United         │ Bradford City       │               1 │               4 │
│ 2022-04-30 │   2021 │ Swindon Town          │ Barrow              │               2 │               1 │
│ 2022-04-30 │   2021 │ Tranmere Rovers       │ Oldham Athletic     │               2 │               0 │
│ 2022-05-02 │   2021 │ Port Vale             │ Newport County      │               1 │               2 │
│ 2022-05-02 │   2021 │ Salford City          │ Mansfield Town      │               2 │               2 │
│ 2022-05-07 │   2021 │ Barrow                │ Northampton Town    │               1 │               3 │
│ 2022-05-07 │   2021 │ Bradford City         │ Carlisle United     │               2 │               0 │
│ 2022-05-07 │   2021 │ Bristol Rovers        │ Scunthorpe United   │               7 │               0 │
│ 2022-05-07 │   2021 │ Exeter City           │ Port Vale           │               0 │               1 │
│ 2022-05-07 │   2021 │ Harrogate Town A.F.C. │ Sutton United       │               0 │               2 │
│ 2022-05-07 │   2021 │ Hartlepool United     │ Colchester United   │               0 │               2 │
│ 2022-05-07 │   2021 │ Leyton Orient         │ Tranmere Rovers     │               0 │               1 │
│ 2022-05-07 │   2021 │ Mansfield Town        │ Forest Green Rovers │               2 │               2 │
│ 2022-05-07 │   2021 │ Newport County        │ Rochdale            │               0 │               2 │
│ 2022-05-07 │   2021 │ Oldham Athletic       │ Crawley Town        │               3 │               3 │
│ 2022-05-07 │   2021 │ Stevenage Borough     │ Salford City        │               4 │               2 │
│ 2022-05-07 │   2021 │ Walsall               │ Swindon Town        │               0 │               3 │
    └────────────┴────────┴───────────────────────┴─────────────────────┴─────────────────┴─────────────────┘

Insert the data:

INSERT INTO football FROM INFILE 'football.bson' FORMAT BSONEachRow;

Reading data

Read data using the BSONEachRow format:

SELECT *
FROM football INTO OUTFILE 'docs_data/bson/football.bson'
FORMAT BSONEachRow

BSON is a binary format that does not display in a human-readable form on the terminal. Use the INTO OUTFILE to output BSON files.

Format settings

Setting	Description	Default
`output_format_bson_string_as_string`	Use BSON String type instead of Binary for String columns.	`false`
`input_format_bson_skip_fields_with_unsupported_types_in_schema_inference`	Allow skipping columns with unsupported types while schema inference for format BSONEachRow.	`false`

​Description

​Data types matching

​Example usage

​Inserting data

​Reading data

​Format settings

Description

Data types matching

Example usage

Inserting data

Reading data

Format settings