title_wip

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

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

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

[Apache Avro](https://avro.apache.org/) is a row-oriented serialization format that uses binary encoding for efficient data processing. The `Avro` format supports reading and writing [Avro data files](https://avro.apache.org/docs/++version++/specification/#object-container-files). This format expects self-describing messages with an embedded schema. If you're using Avro with a schema registry, refer to the [`AvroConfluent`](/docs/interfaces/formats/AvroConfluent) format.

## Data type mapping[​](#data-type-mapping "Direct link to Data type mapping")

The table below shows all data types supported by the Apache Avro format, and their corresponding ClickHouse [data types](/docs/sql-reference/data-types) in `INSERT` and `SELECT` queries.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Avro data type `INSERT` ClickHouse data type Avro data type `SELECT`|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `boolean`, `int`, `long`, `float`, `double` [Int(8\16\32)](/docs/sql-reference/data-types/int-uint), [UInt(8\16\32)](/docs/sql-reference/data-types/int-uint) `int`| `boolean`, `int`, `long`, `float`, `double` [Int64](/docs/sql-reference/data-types/int-uint), [UInt64](/docs/sql-reference/data-types/int-uint) `long`| `boolean`, `int`, `long`, `float`, `double` [Float32](/docs/sql-reference/data-types/float) `float`| `boolean`, `int`, `long`, `float`, `double` [Float64](/docs/sql-reference/data-types/float) `double`| `bytes`, `string`, `fixed`, `enum` [String](/docs/sql-reference/data-types/string) `bytes` or `string` \*| `bytes`, `string`, `fixed` [FixedString(N)](/docs/sql-reference/data-types/fixedstring) `fixed(N)`| `enum` [Enum(8\16)](/docs/sql-reference/data-types/enum) `enum`| `array(T)` [Array(T)](/docs/sql-reference/data-types/array) `array(T)`| `map(V, K)` [Map(V, K)](/docs/sql-reference/data-types/map) `map(string, K)`| `union(null, T)`, `union(T, null)` [Nullable(T)](/docs/sql-reference/data-types/date) `union(null, T)`| `union(T1, T2, …)` \*\* [Variant(T1, T2, …)](/docs/sql-reference/data-types/variant) `union(T1, T2, …)` \*\*| `null` [Nullable(Nothing)](/docs/sql-reference/data-types/special-data-types/nothing) `null`| `int (date)` \*\*\* [Date](/docs/sql-reference/data-types/date), [Date32](/docs/sql-reference/data-types/date32) `int (date)` \*\*\*| `long (timestamp-millis)` \*\*\* [DateTime64(3)](/docs/sql-reference/data-types/datetime) `long (timestamp-millis)` \*\*\*| `long (timestamp-micros)` \*\*\* [DateTime64(6)](/docs/sql-reference/data-types/datetime) `long (timestamp-micros)` \*\*\*| `bytes (decimal)` \*\*\* [DateTime64(N)](/docs/sql-reference/data-types/datetime) `bytes (decimal)` \*\*\*| `int` [IPv4](/docs/sql-reference/data-types/ipv4) `int`| `fixed(16)` [IPv6](/docs/sql-reference/data-types/ipv6) `fixed(16)`| `bytes (decimal)` \*\*\* [Decimal(P, S)](/docs/sql-reference/data-types/decimal) `bytes (decimal)` \*\*\*| `string (uuid)` \*\*\* [UUID](/docs/sql-reference/data-types/uuid) `string (uuid)` \*\*\*| `fixed(16)` [Int128/UInt128](/docs/sql-reference/data-types/int-uint) `fixed(16)`| `fixed(32)` [Int256/UInt256](/docs/sql-reference/data-types/int-uint) `fixed(32)`| `record` [Tuple](/docs/sql-reference/data-types/tuple) `record` | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

\* `bytes` is default, controlled by setting [`output_format_avro_string_column_pattern`](/docs/operations/settings/formats#output_format_avro_string_column_pattern)

\*\* The [Variant type](/docs/sql-reference/data-types/variant) implicitly accepts `null` as a field value, so for example the Avro `union(T1, T2, null)` will be converted to `Variant(T1, T2)`.
As a result, when producing Avro from ClickHouse, we have to always include the `null` type to the Avro `union` type set as we don't know if any value is actually `null` during the schema inference.

\*\*\* [Avro logical types](https://avro.apache.org/docs/current/spec.html#Logical+Types)

Unsupported Avro logical data types:

- `time-millis`
- `time-micros`
- `duration`

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

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Description Default|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `input_format_avro_allow_missing_fields` Whether to use a default value instead of throwing an error when a field is not found in the schema. `0`| `input_format_avro_null_as_default` Whether to use a default value instead of throwing an error when inserting a `null` value into a non-nullable column. `0`| `output_format_avro_codec` Compression algorithm for Avro output files. Possible values: `null`, `deflate`, `snappy`, `zstd`. |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `output_format_avro_sync_interval` Sync marker frequency in Avro files (in bytes). `16384`| `output_format_avro_string_column_pattern` Regular expression to identify `String` columns for Avro string type mapping. By default, ClickHouse `String` columns are written as Avro `bytes` type. |  |  |  | | --- | --- | --- | | `output_format_avro_rows_in_file` Maximum number of rows per Avro output file. When this limit is reached, a new file is created (if the storage system supports file splitting). `1` | | | | | | | | | | | | | | | | | | | | |

## Examples[​](#examples "Direct link to Examples")

### Reading Avro data[​](#reading-avro-data "Direct link to Reading Avro data")

To read data from an Avro file into a ClickHouse table:

```
$ cat file.avro | clickhouse-client --query="INSERT INTO {some_table} FORMAT Avro"
```

The root schema of the ingested Avro file must be of type `record`.

To find the correspondence between table columns and fields of Avro schema, ClickHouse compares their names.
This comparison is case-sensitive and unused fields are skipped.

Data types of ClickHouse table columns can differ from the corresponding fields of the Avro data inserted. When inserting data, ClickHouse interprets data types according to the table above and then [casts](/docs/sql-reference/functions/type-conversion-functions#CAST) the data to the corresponding column type.