clickhouse-local

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/utilities/clickhouse-local.md)

## When to use clickhouse-local vs. ClickHouse[​](#when-to-use-clickhouse-local-vs-clickhouse "Direct link to When to use clickhouse-local vs. ClickHouse")

`clickhouse-local` is an easy-to-use version of ClickHouse that is ideal for developers who need to perform fast processing on local and remote files using SQL without having to install a full database server. With `clickhouse-local`, developers can use SQL commands (using the [ClickHouse SQL dialect](/docs/sql-reference) directly from the command line, providing a simple and efficient way to access ClickHouse features without the need for a full ClickHouse installation. One of the main benefits of `clickhouse-local` is that it is already included when installing [clickhouse-client](/docs/operations/utilities/clickhouse-local). This means that developers can get started with `clickhouse-local` quickly, without the need for a complex installation process.

While `clickhouse-local` is a great tool for development and testing purposes, and for processing files, it is not suitable for serving end users or applications. In these scenarios, it is recommended to use the open-source [ClickHouse](/docs/install). ClickHouse is a powerful OLAP database that is designed to handle large-scale analytical workloads. It provides fast and efficient processing of complex queries on large datasets, making it ideal for use in production environments where high-performance is critical. Additionally, ClickHouse offers a wide range of features such as replication, sharding, and high availability, which are essential for scaling up to handle large datasets and serving applications. If you need to handle larger datasets or serve end users or applications, we recommend using open-source ClickHouse instead of `clickhouse-local`.

Please read the docs below that show example use cases for `clickhouse-local`, such as [querying local file](#query_data_in_file) or [reading a parquet file in S3](#query-data-in-a-parquet-file-in-aws-s3).

## Download clickhouse-local[​](#download-clickhouse-local "Direct link to Download clickhouse-local")

`clickhouse-local` is executed using the same `clickhouse` binary that runs the ClickHouse server and `clickhouse-client`. The easiest way to download the latest version is with the following command:

```
curl https://clickhouse.com/ | sh
```

Note

The binary you just downloaded can run all sorts of ClickHouse tools and utilities. If you want to run ClickHouse as a database server, check out the [Quick Start](/docs/get-started/quick-start).

## Query data in a file using SQL[​](#query_data_in_file "Direct link to Query data in a file using SQL")

A common use of `clickhouse-local` is to run ad-hoc queries on files: where you don't have to insert the data into a table. `clickhouse-local` can stream the data from a file into a temporary table and execute your SQL.

If the file is sitting on the same machine as `clickhouse-local`, you can simply specify the file to load. The following `reviews.tsv` file contains a sampling of Amazon product reviews:

```
./clickhouse local -q "SELECT * FROM 'reviews.tsv'"
```

This command is a shortcut of:

```
./clickhouse local -q "SELECT * FROM file('reviews.tsv')"
```

ClickHouse knows the file uses a tab-separated format from filename extension. If you need to explicitly specify the format, simply add one of the [many ClickHouse input formats](/docs/interfaces/formats):

```
./clickhouse local -q "SELECT * FROM file('reviews.tsv', 'TabSeparated')"
```

The `file` table function creates a table, and you can use `DESCRIBE` to see the inferred schema:

```
./clickhouse local -q "DESCRIBE file('reviews.tsv')"
```

Tip

You are allowed to use globs in file name (See [glob substitutions](/docs/sql-reference/table-functions/file#globs-in-path)).

Examples:

```
./clickhouse local -q "SELECT * FROM 'reviews*.jsonl'"
./clickhouse local -q "SELECT * FROM 'review_?.csv'"
./clickhouse local -q "SELECT * FROM 'review_{1..3}.csv'"
```

```
marketplace    Nullable(String)
customer_id    Nullable(Int64)
review_id    Nullable(String)
product_id    Nullable(String)
product_parent    Nullable(Int64)
product_title    Nullable(String)
product_category    Nullable(String)
star_rating    Nullable(Int64)
helpful_votes    Nullable(Int64)
total_votes    Nullable(Int64)
vine    Nullable(String)
verified_purchase    Nullable(String)
review_headline    Nullable(String)
review_body    Nullable(String)
review_date    Nullable(Date)
```

Let's find a product with the highest rating:

```
./clickhouse local -q "SELECT
    argMax(product_title,star_rating),
    max(star_rating)
FROM file('reviews.tsv')"
```

```
Monopoly Junior Board Game    5
```

## Query data in a Parquet file in AWS S3[​](#query-data-in-a-parquet-file-in-aws-s3 "Direct link to Query data in a Parquet file in AWS S3")

If you have a file in S3, use `clickhouse-local` and the `s3` table function to query the file in place (without inserting the data into a ClickHouse table). We have a file named `house_0.parquet` in a public bucket that contains home prices of property sold in the United Kingdom. Let's see how many rows it has:

```
./clickhouse local -q "
SELECT count()
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')"
```

The file has 2.7M rows:

```
2772030
```

It's always useful to see what the inferred schema that ClickHouse determines from the file:

```
./clickhouse local -q "DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')"
```

```
price    Nullable(Int64)
date    Nullable(UInt16)
postcode1    Nullable(String)
postcode2    Nullable(String)
type    Nullable(String)
is_new    Nullable(UInt8)
duration    Nullable(String)
addr1    Nullable(String)
addr2    Nullable(String)
street    Nullable(String)
locality    Nullable(String)
town    Nullable(String)
district    Nullable(String)
county    Nullable(String)
```

Let's see what the most expensive neighborhoods are:

```
./clickhouse local -q "
SELECT
    town,
    district,
    count() AS c,
    round(avg(price)) AS price,
    bar(price, 0, 5000000, 100)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')
GROUP BY
    town,
    district
HAVING c >= 100
ORDER BY price DESC
LIMIT 10"
```

```
LONDON    CITY OF LONDON    886    2271305    █████████████████████████████████████████████▍
LEATHERHEAD    ELMBRIDGE    206    1176680    ███████████████████████▌
LONDON    CITY OF WESTMINSTER    12577    1108221    ██████████████████████▏
LONDON    KENSINGTON AND CHELSEA    8728    1094496    █████████████████████▉
HYTHE    FOLKESTONE AND HYTHE    130    1023980    ████████████████████▍
CHALFONT ST GILES    CHILTERN    113    835754    ████████████████▋
AMERSHAM    BUCKINGHAMSHIRE    113    799596    ███████████████▉
VIRGINIA WATER    RUNNYMEDE    356    789301    ███████████████▊
BARNET    ENFIELD    282    740514    ██████████████▊
NORTHWOOD    THREE RIVERS    184    731609    ██████████████▋
```

Tip

When you are ready to insert your files into ClickHouse, startup a ClickHouse server and insert the results of your `file` and `s3` table functions into a `MergeTree` table. View the [Quick Start](/docs/get-started/quick-start) for more details.

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

You can use `clickhouse-local` for converting data between different formats. Example:

```
$ clickhouse-local --input-format JSONLines --output-format CSV --query "SELECT * FROM table" < data.json > data.csv
```

Formats are auto-detected from file extensions:

```
$ clickhouse-local --query "SELECT * FROM table" < data.json > data.csv
```

As a shortcut, you can write it using the `--copy` argument:

```
$ clickhouse-local --copy < data.json > data.csv
```

## Usage[​](#usage "Direct link to Usage")