Avro
| Input | Output | Alias |
|---|---|---|
| ✔ | ✔ |
Description
Apache Avro is a row-oriented data serialization framework developed within Apache’s Hadoop project.
ClickHouse's Avro format supports reading and writing Avro data files.
Data Types Matching
The table below shows all data types supported by the Apache Avro format, and their corresponding ClickHouse 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), UInt(8\16\32) | int |
boolean, int, long, float, double | Int64, UInt64 | long |
boolean, int, long, float, double | Float32 | float |
boolean, int, long, float, double | Float64 | double |
bytes, string, fixed, enum | String | bytes or string * |
bytes, string, fixed | FixedString(N) | fixed(N) |
enum | Enum(8\16) | enum |
array(T) | Array(T) | array(T) |
map(V, K) | Map(V, K) | map(string, K) |
union(null, T), union(T, null) | Nullable(T) | union(null, T) |
union(T1, T2, …) ** | Variant(T1, T2, …) | union(T1, T2, …) ** |
null | Nullable(Nothing) | null |
int (date) *** | Date, Date32 | int (date) *** |
long (timestamp-millis) *** | DateTime64(3) | long (timestamp-millis) *** |
long (timestamp-micros) *** | DateTime64(6) | long (timestamp-micros) *** |
bytes (decimal) *** | DateTime64(N) | bytes (decimal) *** |
int | IPv4 | int |
fixed(16) | IPv6 | fixed(16) |
bytes (decimal) *** | Decimal(P, S) | bytes (decimal) *** |
string (uuid) *** | UUID | string (uuid) *** |
fixed(16) | Int128/UInt128 | fixed(16) |
fixed(32) | Int256/UInt256 | fixed(32) |
record | Tuple | record |
* bytes is default, controlled by setting output_format_avro_string_column_pattern
** The Variant type 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.
Unsupported Avro logical data types:
time-millistime-microsduration
Example Usage
Inserting Data
To insert 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 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 is enabled, the default value will be used instead of throwing an error.
Selecting Data
To select data from a ClickHouse table into an Avro file:
$ 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_]
Output Avro file compression and sync interval can be configured with settings output_format_avro_codec and output_format_avro_sync_interval respectively.
Example Data
Using the ClickHouse DESCRIBE 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:
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 │ │ │ │ │ │
└────────────────────────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Format Settings
| Setting | Description | Default |
|---|---|---|
input_format_avro_allow_missing_fields | For Avro/AvroConfluent format: when field is not found in schema use default value instead of error | 0 |
input_format_avro_null_as_default | For Avro/AvroConfluent format: insert default in case of null and non Nullable column | 0 |
format_avro_schema_registry_url | For AvroConfluent format: Confluent Schema Registry URL. | |
output_format_avro_codec | Compression codec used for output. Possible values: 'null', 'deflate', 'snappy', 'zstd'. | |
output_format_avro_sync_interval | Sync interval in bytes. | 16384 |
output_format_avro_string_column_pattern | For Avro format: regexp of String columns to select as AVRO string. | |
output_format_avro_rows_in_file | Max rows in a file (if permitted by storage) | 1 |