> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-sync-clickhouse-operator-docs-7e82242.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for the Avro format

# Avro

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

<h2 id="description">
  Description
</h2>

[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`](/core/reference/formats/Avro/AvroConfluent) format.

<h2 id="data-type-mapping">
  Data type mapping
</h2>

The table below shows all data types supported by the Apache Avro format, and their corresponding ClickHouse [data types](/core/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)](/core/reference/data-types/int-uint), [UInt(8\16\32)](/core/reference/data-types/int-uint) | `int`                            |
| `boolean`, `int`, `long`, `float`, `double` | [Int64](/core/reference/data-types/int-uint), [UInt64](/core/reference/data-types/int-uint)               | `long`                           |
| `boolean`, `int`, `long`, `float`, `double` | [Float32](/core/reference/data-types/float)                                                               | `float`                          |
| `boolean`, `int`, `long`, `float`, `double` | [Float64](/core/reference/data-types/float)                                                               | `double`                         |
| `bytes`, `string`, `fixed`, `enum`          | [String](/core/reference/data-types/string)                                                               | `bytes` or `string` \*           |
| `bytes`, `string`, `fixed`                  | [FixedString(N)](/core/reference/data-types/fixedstring)                                                  | `fixed(N)`                       |
| `enum`                                      | [Enum(8\16)](/core/reference/data-types/enum)                                                             | `enum`                           |
| `array(T)`                                  | [Array(T)](/core/reference/data-types/array)                                                              | `array(T)`                       |
| `map(V, K)`                                 | [Map(V, K)](/core/reference/data-types/map)                                                               | `map(string, K)`                 |
| `union(null, T)`, `union(T, null)`          | [Nullable(T)](/core/reference/data-types/date)                                                            | `union(null, T)`                 |
| `union(T1, T2, …)` \*\*                     | [Variant(T1, T2, …)](/core/reference/data-types/variant)                                                  | `union(T1, T2, …)` \*\*          |
| `null`                                      | [Nullable(Nothing)](/core/reference/data-types/special-data-types/nothing)                                | `null`                           |
| `int (date)` \*\*\*                         | [Date](/core/reference/data-types/date), [Date32](/core/reference/data-types/date32)                      | `int (date)` \*\*\*              |
| `long (timestamp-millis)` \*\*\*            | [DateTime64(3)](/core/reference/data-types/datetime)                                                      | `long (timestamp-millis)` \*\*\* |
| `long (timestamp-micros)` \*\*\*            | [DateTime64(6)](/core/reference/data-types/datetime)                                                      | `long (timestamp-micros)` \*\*\* |
| `bytes (decimal)`  \*\*\*                   | [DateTime64(N)](/core/reference/data-types/datetime)                                                      | `bytes (decimal)`  \*\*\*        |
| `int`                                       | [IPv4](/core/reference/data-types/ipv4)                                                                   | `int`                            |
| `fixed(16)`                                 | [IPv6](/core/reference/data-types/ipv6)                                                                   | `fixed(16)`                      |
| `bytes (decimal)` \*\*\*                    | [Decimal(P, S)](/core/reference/data-types/decimal)                                                       | `bytes (decimal)` \*\*\*         |
| `string (uuid)` \*\*\*                      | [UUID](/core/reference/data-types/uuid)                                                                   | `string (uuid)` \*\*\*           |
| `fixed(16)`                                 | [Int128/UInt128](/core/reference/data-types/int-uint)                                                     | `fixed(16)`                      |
| `fixed(32)`                                 | [Int256/UInt256](/core/reference/data-types/int-uint)                                                     | `fixed(32)`                      |
| `record`                                    | [Tuple](/core/reference/data-types/tuple)                                                                 | `record`                         |

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

\*\*  The [Variant type](/core/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`

<h2 id="format-settings">
  Format settings
</h2>

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

<h2 id="examples">
  Examples
</h2>

<h3 id="reading-avro-data">
  Reading Avro data
</h3>

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

```bash theme={null}
$ 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](/core/reference/functions/regular-functions/type-conversion-functions#CAST) the data to the corresponding column type.

While importing data, when a field is not found in the schema and setting [`input_format_avro_allow_missing_fields`](/core/reference/settings/formats#input_format_avro_allow_missing_fields) is enabled, the default value will be used instead of throwing an error.

<h3 id="writing-avro-data">
  Writing Avro data
</h3>

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

```bash theme={null}
$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Avro" > file.avro
```

Column names must:

* Start with `[A-Za-z_]`
* Be followed by only `[A-Za-z0-9_]`

The output compression and sync interval for Avro files can be configured using the [`output_format_avro_codec`](/core/reference/settings/formats#output_format_avro_codec) and [`output_format_avro_sync_interval`](/core/reference/settings/formats#output_format_avro_sync_interval) settings, respectively.

<h3 id="inferring-the-avro-schema">
  Inferring the Avro schema
</h3>

Using the ClickHouse [`DESCRIBE`](/core/reference/statements/describe-table) function, you can quickly view the inferred format of an Avro file like the following example.
This example includes the URL of a publicly accessible Avro file in the ClickHouse S3 public bucket:

```sql theme={null}
DESCRIBE url('https://clickhouse-public-datasets.s3.eu-central-1.amazonaws.com/hits.avro','Avro);

┌─name───────────────────────┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ WatchID                    │ Int64           │              │                    │         │                  │                │
│ JavaEnable                 │ Int32           │              │                    │         │                  │                │
│ Title                      │ String          │              │                    │         │                  │                │
│ GoodEvent                  │ Int32           │              │                    │         │                  │                │
│ EventTime                  │ Int32           │              │                    │         │                  │                │
│ EventDate                  │ Date32          │              │                    │         │                  │                │
│ CounterID                  │ Int32           │              │                    │         │                  │                │
│ ClientIP                   │ Int32           │              │                    │         │                  │                │
│ ClientIP6                  │ FixedString(16) │              │                    │         │                  │                │
│ RegionID                   │ Int32           │              │                    │         │                  │                │
...
│ IslandID                   │ FixedString(16) │              │                    │         │                  │                │
│ RequestNum                 │ Int32           │              │                    │         │                  │                │
│ RequestTry                 │ Int32           │              │                    │         │                  │                │
└────────────────────────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```