title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/data-formats/arrow-avro-orc.md)

Apache has released multiple data formats actively used in analytics environments, including the popular [Avro](https://avro.apache.org/), [Arrow](https://arrow.apache.org/), and [Orc](https://orc.apache.org/). ClickHouse supports importing and exporting data using any from that list.

## Importing and exporting in Avro format[​](#importing-and-exporting-in-avro-format "Direct link to Importing and exporting in Avro format")

ClickHouse supports reading and writing [Apache Avro](https://avro.apache.org/) data files, which are widely used in Hadoop systems.

To import from an [avro file](/docs/assets/files/data-a7ef352ad979099ce4ec4baf4fa54710.avro), we should use [Avro](/docs/interfaces/formats/Avro) format in the `INSERT` statement:

```
INSERT INTO sometable
FROM INFILE 'data.avro'
FORMAT Avro
```

With the [file()](/docs/sql-reference/functions/files#file) function, we can also explore Avro files before actually importing data:

```
SELECT path, hits
FROM file('data.avro', Avro)
ORDER BY hits DESC
LIMIT 5;
```

```
┌─path────────────┬──hits─┐
│ Amy_Poehler     │ 62732 │
│ Adam_Goldberg   │ 42338 │
│ Aaron_Spelling  │ 25128 │
│ Absence_seizure │ 18152 │
│ Ammon_Bundy     │ 11890 │
└─────────────────┴───────┘
```

To export to Avro file:

```
SELECT * FROM sometable
INTO OUTFILE 'export.avro'
FORMAT Avro;
```

### Avro and ClickHouse data types[​](#avro-and-clickhouse-data-types "Direct link to Avro and ClickHouse data types")

Consider [data types matching](/docs/interfaces/formats/Avro#data-type-mapping) when importing or exporting Avro files. Use explicit type casting to convert when loading data from Avro files:

```
SELECT
    date,
    toDate(date)
FROM file('data.avro', Avro)
LIMIT 3;
```

```
┌──date─┬─toDate(date)─┐
│ 16556 │   2015-05-01 │
│ 16556 │   2015-05-01 │
│ 16556 │   2015-05-01 │
└───────┴──────────────┘
```

### Avro messages in Kafka[​](#avro-messages-in-kafka "Direct link to Avro messages in Kafka")

When Kafka messages use Avro format, ClickHouse can read such streams using [AvroConfluent](/docs/interfaces/formats/AvroConfluent) format and [Kafka](/docs/engines/table-engines/integrations/kafka) engine:

```
CREATE TABLE some_topic_stream
(
    field1 UInt32,
    field2 String
)
ENGINE = Kafka() SETTINGS
kafka_broker_list = 'localhost',
kafka_topic_list = 'some_topic',
kafka_group_name = 'some_group',
kafka_format = 'AvroConfluent';
```

## Working with Arrow format[​](#working-with-arrow-format "Direct link to Working with Arrow format")

Another columnar format is [Apache Arrow](https://arrow.apache.org/), also supported by ClickHouse for import and export. To import data from an [Arrow file](/docs/assets/files/data-e34efb7c41390a44e4db06cf14dd539b.arrow), we use the [Arrow](/docs/interfaces/formats/Arrow) format:

```
INSERT INTO sometable
FROM INFILE 'data.arrow'
FORMAT Arrow
```

Exporting to Arrow file works the same way:

```
SELECT * FROM sometable
INTO OUTFILE 'export.arrow'
FORMAT Arrow
```

Also, check [data types matching](/docs/interfaces/formats/Arrow#data-types-matching) to know if any should be converted manually.

### Arrow data streaming[​](#arrow-data-streaming "Direct link to Arrow data streaming")

The [ArrowStream](/docs/interfaces/formats/ArrowStream) format can be used to work with Arrow streaming (used for in-memory processing). ClickHouse can read and write Arrow streams.

To demonstrate how ClickHouse can stream Arrow data, let's pipe it to the following python script (it reads input stream in Arrow streaming format and outputs the result as a Pandas table):

```
import sys, pyarrow as pa

with pa.ipc.open_stream(sys.stdin.buffer) as reader:
  print(reader.read_pandas())
```

Now we can stream data from ClickHouse by piping its output to the script:

```
clickhouse-client -q "SELECT path, hits FROM some_data LIMIT 3 FORMAT ArrowStream" | python3 arrow.py
```

```
                           path  hits
0       b'Akiba_Hebrew_Academy'   241
1           b'Aegithina_tiphia'    34
2  b'1971-72_Utah_Stars_season'     1
```

ClickHouse can read Arrow streams as well using the same ArrowStream format:

```
arrow-stream | clickhouse-client -q "INSERT INTO sometable FORMAT ArrowStream"
```

We've used `arrow-stream` as a possible source of Arrow streaming data.

## Importing and exporting ORC data[​](#importing-and-exporting-orc-data "Direct link to Importing and exporting ORC data")

[Apache ORC](https://orc.apache.org/) format is a columnar storage format typically used for Hadoop. ClickHouse supports importing as well as exporting [Orc data](/docs/assets/files/data-9ff869bfbcdf168bdfe4eb08f7a8fd50.orc) using [ORC format](/docs/interfaces/formats/ORC):

```
SELECT *
FROM sometable
INTO OUTFILE 'data.orc'
FORMAT ORC;

INSERT INTO sometable
FROM INFILE 'data.orc'
FORMAT ORC;
```

Also, check [data types matching](/docs/interfaces/formats/ORC) as well as [additional settings](/docs/interfaces/formats/Parquet#format-settings) to tune export and import.

## Further reading[​](#further-reading "Direct link to Further reading")

ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:

- [CSV and TSV formats](/docs/integrations/data-formats/csv-tsv)
- [JSON formats](/docs/integrations/data-formats/json/overview)
- [Regex and templates](/docs/integrations/data-formats/templates-regexp)
- [Native and binary formats](/docs/integrations/data-formats/binary-native)
- [SQL formats](/docs/integrations/data-formats/sql)

And also check [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) - a portable full-featured tool to work on local/remote files without the need for Clickhouse server.

- [Importing and exporting in Avro format](#importing-and-exporting-in-avro-format)
  - [Avro and ClickHouse data types](#avro-and-clickhouse-data-types)- [Avro messages in Kafka](#avro-messages-in-kafka)- [Working with Arrow format](#working-with-arrow-format)
    - [Arrow data streaming](#arrow-data-streaming)- [Importing and exporting ORC data](#importing-and-exporting-orc-data)- [Further reading](#further-reading)

Was this page helpful?