> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Working with Parquet in ClickHouse

> Page describing how to work with Parquet in ClickHouse

Parquet is an efficient file format to store data in a column-oriented way.
ClickHouse provides support for both reading and writing Parquet files.

<Tip>
  When you reference a file path in a query, where ClickHouse attempts to read from will depend on the variant of ClickHouse that you're using.

  If you're using [`clickhouse-local`](/docs/concepts/features/tools-and-utilities/clickhouse-local) it will read from a location relative to where you launched ClickHouse Local.
  If you're using ClickHouse Server or ClickHouse Cloud via `clickhouse client`, it will read from a location relative to the `/var/lib/clickhouse/user_files/` directory on the server.
</Tip>

<h2 id="importing-from-parquet">
  Importing from Parquet
</h2>

Before loading data, we can use [file()](/docs/reference/functions/regular-functions/files#file) function to explore an [example parquet file](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/data.parquet) structure:

```sql theme={null}
DESCRIBE TABLE file('data.parquet', Parquet);
```

We've used [Parquet](/docs/reference/formats/Parquet/Parquet) as a second argument, so ClickHouse knows the file format. This will print columns with the types:

```response theme={null}
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path │ Nullable(String) │              │                    │         │                  │                │
│ date │ Nullable(String) │              │                    │         │                  │                │
│ hits │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

We can also explore files before actually importing data using all power of SQL:

```sql theme={null}
SELECT *
FROM file('data.parquet', Parquet)
LIMIT 3;
```

```response theme={null}
┌─path──────────────────────┬─date───────┬─hits─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │  241 │
│ Aegithina_tiphia          │ 2018-02-01 │   34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │    1 │
└───────────────────────────┴────────────┴──────┘
```

<Tip>
  We can skip explicit format setting for `file()` and `INFILE`/`OUTFILE`.
  In that case, ClickHouse will automatically detect format based on file extension.
</Tip>

<h2 id="importing-to-an-existing-table">
  Importing to an existing table
</h2>

Let's create a table into which we'll import Parquet data:

```sql theme={null}
CREATE TABLE sometable
(
    `path` String,
    `date` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY (date, path);
```

Now we can import data using the `FROM INFILE` clause:

```sql theme={null}
INSERT INTO sometable
FROM INFILE 'data.parquet' FORMAT Parquet;

SELECT *
FROM sometable
LIMIT 5;
```

```response theme={null}
┌─path──────────────────────────┬───────date─┬─hits─┐
│ 1988_in_philosophy            │ 2015-05-01 │   70 │
│ 2004_Green_Bay_Packers_season │ 2015-05-01 │  970 │
│ 24_hours_of_lemans            │ 2015-05-01 │   37 │
│ 25604_Karlin                  │ 2015-05-01 │   20 │
│ ASCII_ART                     │ 2015-05-01 │    9 │
└───────────────────────────────┴────────────┴──────┘
```

Note how ClickHouse automatically converted Parquet strings (in the `date` column) to the `Date` type. This is because ClickHouse does a typecast automatically based on the types in the target table.

<h2 id="inserting-a-local-file-to-remote-server">
  Inserting a local file to remote server
</h2>

If you want to insert a local Parquet file to a remote ClickHouse server, you can do this by piping the contents of the file into `clickhouse-client`, as shown below:

```sql theme={null}
clickhouse client -q "INSERT INTO sometable FORMAT Parquet" < data.parquet
```

<h2 id="creating-new-tables-from-parquet-files">
  Creating new tables from Parquet files
</h2>

Since ClickHouse reads parquet file schema, we can create tables on the fly:

```sql theme={null}
CREATE TABLE imported_from_parquet
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM file('data.parquet', Parquet)
```

This will automatically create and populate a table from a given parquet file:

```sql theme={null}
DESCRIBE TABLE imported_from_parquet;
```

```response theme={null}
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path │ Nullable(String) │              │                    │         │                  │                │
│ date │ Nullable(String) │              │                    │         │                  │                │
│ hits │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

By default, ClickHouse is strict with column names, types, and values. But sometimes, we can skip nonexistent columns or unsupported values during import. This can be managed with [Parquet settings](/docs/reference/formats/Parquet/Parquet#format-settings).

<h2 id="exporting-to-parquet-format">
  Exporting to Parquet format
</h2>

<Tip>
  When using `INTO OUTFILE` with ClickHouse Cloud you will need to run the commands in `clickhouse client` on the machine where the file will be written to.
</Tip>

To export any table or query result to the Parquet file, we can use an `INTO OUTFILE` clause:

```sql theme={null}
SELECT *
FROM sometable
INTO OUTFILE 'export.parquet'
FORMAT Parquet
```

This will create the `export.parquet` file in a working directory.

<h2 id="clickhouse-and-parquet-data-types">
  ClickHouse and Parquet data types
</h2>

ClickHouse and Parquet data types are mostly identical but still [differ a bit](/docs/reference/formats/Parquet/Parquet#data-types-matching-parquet). For example, ClickHouse will export `DateTime` type as a Parquets' `int64`. If we then import that back to ClickHouse, we're going to see numbers ([time.parquet file](https://clickhouse-docs-assets.s3.us-east-1.amazonaws.com/time.parquet)):

```sql theme={null}
SELECT * FROM file('time.parquet', Parquet);
```

```response theme={null}
┌─n─┬───────time─┐
│ 0 │ 1673622611 │
│ 1 │ 1673622610 │
│ 2 │ 1673622609 │
│ 3 │ 1673622608 │
│ 4 │ 1673622607 │
└───┴────────────┘
```

In this case [type conversion](/docs/reference/functions/regular-functions/type-conversion-functions) can be used:

```sql theme={null}
SELECT
    n,
    toDateTime(time)                 <--- int to time
FROM file('time.parquet', Parquet);
```

```response theme={null}
┌─n─┬────toDateTime(time)─┐
│ 0 │ 2023-01-13 15:10:11 │
│ 1 │ 2023-01-13 15:10:10 │
│ 2 │ 2023-01-13 15:10:09 │
│ 3 │ 2023-01-13 15:10:08 │
│ 4 │ 2023-01-13 15:10:07 │
└───┴─────────────────────┘
```

<h2 id="further-reading">
  Further reading
</h2>

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/guides/clickhouse/data-formats/csv-tsv)
* [Avro, Arrow and ORC](/docs/guides/clickhouse/data-formats/arrow-avro-orc)
* [JSON formats](/docs/guides/clickhouse/data-formats/json/intro)
* [Regex and templates](/docs/guides/clickhouse/data-formats/templates-regex)
* [Native and binary formats](/docs/guides/clickhouse/data-formats/binary)
* [SQL formats](/docs/guides/clickhouse/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.