> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Guide to using clickhouse-local for processing data without a server # clickhouse-local

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](/reference/home) 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](/concepts/features/tools-and-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](/get-started/setup/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

`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: ```bash theme={null} curl https://clickhouse.com/ | sh ``` 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](/get-started/setup/install).

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: ```bash theme={null} ./clickhouse local -q "SELECT * FROM 'reviews.tsv'" ``` This command is a shortcut of: ```bash theme={null} ./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](/reference/formats/index): ```bash theme={null} ./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: ```bash theme={null} ./clickhouse local -q "DESCRIBE file('reviews.tsv')" ``` You are allowed to use globs in file name (See [glob substitutions](/reference/functions/table-functions/file#globs-in-path)). Examples: ```bash theme={null} ./clickhouse local -q "SELECT * FROM 'reviews*.jsonl'" ./clickhouse local -q "SELECT * FROM 'review_?.csv'" ./clickhouse local -q "SELECT * FROM 'review_{1..3}.csv'" ``` ```response theme={null} 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: ```bash theme={null} ./clickhouse local -q "SELECT argMax(product_title,star_rating), max(star_rating) FROM file('reviews.tsv')" ``` ```response theme={null} Monopoly Junior Board Game 5 ```

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: ```bash theme={null} ./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: ```response theme={null} 2772030 ``` It's always useful to see what the inferred schema that ClickHouse determines from the file: ```bash theme={null} ./clickhouse local -q "DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')" ``` ```response theme={null} 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: ```bash theme={null} ./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" ``` ```response theme={null} 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 ██████████████▋ ``` 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](/get-started/setup/install) for more details.

Format Conversions

You can use `clickhouse-local` for converting data between different formats. Example: ```bash theme={null} $ clickhouse-local --input-format JSONLines --output-format CSV --query "SELECT * FROM table" < data.json > data.csv ``` Formats are auto-detected from file extensions: ```bash theme={null} $ clickhouse-local --query "SELECT * FROM table" < data.json > data.csv ``` As a shortcut, you can write it using the `--copy` argument: ```bash theme={null} $ clickhouse-local --copy < data.json > data.csv ```

Usage

By default `clickhouse-local` has access to data of a ClickHouse server on the same host, and it does not depend on the server's configuration. It also supports loading server configuration using `--config-file` argument. For temporary data, a unique temporary data directory is created by default. Basic usage (Linux): ```bash theme={null} $ clickhouse-local --structure "table_structure" --input-format "format_of_incoming_data" --query "query" ``` Basic usage (Mac): ```bash theme={null} $ ./clickhouse local --structure "table_structure" --input-format "format_of_incoming_data" --query "query" ``` `clickhouse-local` is also supported on Windows through WSL2. Arguments: * `-S`, `--structure` — table structure for input data. * `--input-format` — input format, `TSV` by default. * `-F`, `--file` — path to data, `stdin` by default. * `-q`, `--query` — queries to execute with `;` as delimiter. `--query` can be specified multiple times, e.g. `--query "SELECT 1" --query "SELECT 2"`. Cannot be used simultaneously with `--queries-file`. * `--queries-file` - file path with queries to execute. `--queries-file` can be specified multiple times, e.g. `--query queries1.sql --query queries2.sql`. Cannot be used simultaneously with `--query`. * `--multiquery, -n` – If specified, multiple queries separated by semicolons can be listed after the `--query` option. For convenience, it is also possible to omit `--query` and pass the queries directly after `--multiquery`. * `-N`, `--table` — table name where to put output data, `table` by default. * `-f`, `--format`, `--output-format` — output format, `TSV` by default. * `-d`, `--database` — default database, `_local` by default. * `--stacktrace` — whether to dump debug output in case of exception. * `--echo [ ]` — print each query before execution. Takes an optional boolean value. Enabled by default in interactive mode and disabled in batch mode. Note: because `--echo` now takes an optional value, a positional query placed immediately after a bare `--echo` is consumed as its value; use `--echo --query "..."`, `--echo -q "..."`, `--echo=false`, or piped `stdin` instead. * `--echo-formatted [ ]` — format the echoed queries. Takes an optional boolean value. Enabled by default in interactive mode and disabled in batch mode. * `--echo-query-id [ ]` — print the `query_id` before execution. Takes an optional boolean value. Enabled by default in interactive mode and disabled in batch mode. * `--echo-query-separator ` — print this separator before the formatted echoed query (requires `--echo-formatted`), making it easier to tell the typed query apart from its reformatted echo. Empty by default (disabled). * `--highlight`, `--hilite` `` — toggle syntax highlighting of the command prompt and the echoed queries. Enabled by default. Highlighting is applied only when writing to a terminal. * `--hints ` — show as-you-type autocompletion hints (inline "ghost" text) for the best matching suggestion when the cursor is at the end of the input. Navigate the hints with Up/Down (or Ctrl-Up/Ctrl-Down); accept the inline hint with Tab or Right; `Enter` accepts a hint only after one has been explicitly selected and otherwise runs the query; `Tab` also opens the classic completion list. Requires `--highlight` (hints need color) and the suggestion machinery (so `--disable_suggestion` also turns them off). Enabled by default. * `--verbose` — more details on query execution. * `--logger.console` — Log to console. * `--logger.log` — Log file name. * `--logger.level` — Log level. * `--ignore-error` — do not stop processing if a query failed. * `-c`, `--config-file` — path to configuration file in same format as for ClickHouse server, by default the configuration empty. * `--no-system-tables` — do not attach system tables. * `--help` — arguments references for `clickhouse-local`. * `-V`, `--version` — print version information and exit. Also, there are arguments for each ClickHouse configuration variable which are more commonly used instead of `--config-file`.

Commands

LS Command

Lists all the files in the current working directory accessible to clickhouse-local. You can run it in interactive mode like: ```sql title="Query" theme={null} ClickHouse local version 26.3.1.1. :) ls SELECT _file AS file FROM file('*', 'One') ORDER BY file ASC ``` ```text title="Response" theme={null} ┌─file────────┐ │ file1.csv │ │ file2.json │ │ file3.xml │ └─────────────┘ ``` You can also run it as a query using the argument -q: ```sh theme={null} ./clickhouse-local -q ls ``` ```text title="Response" theme={null} file1.csv file2.json file3.xml ```

CLEAR command

Clears the terminal screen (similar to the `clear` command on Linux or Ctrl+L in many terminals). This is a client-side action: it is not sent to the SQL engine. In `clickhouse-local`, the meta-command is recognized in **interactive** mode and for **`-q`** and **`--queries-file`** input (same client path as `-q`, same idea as `ls`), so a bare `clear` does not produce an `UNKNOWN_IDENTIFIER` error. Remote **`clickhouse-client --queries-file`** is unchanged: file contents are executed as SQL only (no text-level meta-commands). In `clickhouse-client`, it is recognized only in **interactive** mode. With **`-q`** or query files, `clear` is still parsed as SQL, so automation keeps the previous error behavior instead of turning typos into a silent no-op. Supported forms: `clear`, `CLEAR`, `/clear` (optional trailing `;` is ignored). If standard output is not a terminal (for example, when piping output), the meta-command is accepted when recognized but does not emit control sequences. With `clickhouse-local` and `-q`: ```sh theme={null} ./clickhouse-local -q clear ```

Examples

```bash title="Query" theme={null} $ echo -e "1,2\n3,4" | clickhouse-local --structure "a Int64, b Int64" \ --input-format "CSV" --query "SELECT * FROM table" Read 2 rows, 32.00 B in 0.000 sec., 5182 rows/sec., 80.97 KiB/sec. 1 2 3 4 ``` Previous example is the same as: ```bash title="Query" theme={null} $ echo -e "1,2\n3,4" | clickhouse-local -n --query " CREATE TABLE table (a Int64, b Int64) ENGINE = File(CSV, stdin); SELECT a, b FROM table; DROP TABLE table;" Read 2 rows, 32.00 B in 0.000 sec., 4987 rows/sec., 77.93 KiB/sec. 1 2 3 4 ``` You don't have to use `stdin` or `--file` argument, and can open any number of files using the [`file` table function](/reference/functions/table-functions/file): ```bash title="Query" theme={null} $ echo 1 | tee 1.tsv 1 $ echo 2 | tee 2.tsv 2 $ clickhouse-local --query " select * from file('1.tsv', TSV, 'a int') t1 cross join file('2.tsv', TSV, 'b int') t2" 1 2 ``` Now let's output memory user for each Unix user: ```bash title="Query" theme={null} $ ps aux | tail -n +2 | awk '{ printf("%s\t%s\n", $1, $4) }' \ | clickhouse-local --structure "user String, mem Float64" \ --query "SELECT user, round(sum(mem), 2) as memTotal FROM table GROUP BY user ORDER BY memTotal DESC FORMAT Pretty" ``` ```text title="Response" theme={null} Read 186 rows, 4.15 KiB in 0.035 sec., 5302 rows/sec., 118.34 KiB/sec. ┏━━━━━━━━━━┳━━━━━━━━━━┓ ┃ user ┃ memTotal ┃ ┡━━━━━━━━━━╇━━━━━━━━━━┩ │ bayonet │ 113.5 │ ├──────────┼──────────┤ │ root │ 8.8 │ ├──────────┼──────────┤ ... ```

Starting TCP and HTTP Listeners

`clickhouse-local` can be transformed into a lightweight server that accepts TCP (native protocol) and HTTP connections. This is useful when you want to give other ClickHouse tools or applications access to the databases and tables of a running `clickhouse-local` instance. Note that each incoming connection gets a session of its own: temporary tables and session-level settings of the interactive `clickhouse-local` session are not visible to external connections. Use `SYSTEM START LISTEN` to open a listener and `SYSTEM STOP LISTEN` to close it: ```bash theme={null} clickhouse-local \ --listen_host 127.0.0.1 \ --tcp_port 9000 \ --http_port 8123 \ --query " SYSTEM START LISTEN TCP; SYSTEM START LISTEN HTTP; SELECT * FROM url('http://127.0.0.1:8123/?query=SELECT+42', LineAsString); SYSTEM STOP LISTEN TCP; SYSTEM STOP LISTEN HTTP; " ``` The `--listen_host`, `--tcp_port`, and `--http_port` options configure the bind address and ports. Default ports are `9000` for TCP and `8123` for HTTP. **Security** By default, `clickhouse-local` runs with the temporary users setup, so any listener it opens is unauthenticated. Bind to a loopback address (`127.0.0.1` or `::1`) unless you have explicitly configured users and access control by pointing the `users_config` setting at a custom `users.xml` (for example via `--config-file`). Listening on a non-loopback address without authentication exposes the data of the local instance to anyone who can reach the chosen port.

Related Content

* [Extracting, converting, and querying data in local files using clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) * [Getting Data Into ClickHouse - Part 1](https://clickhouse.com/blog/getting-data-into-clickhouse-part-1) * [Exploring massive, real-world data sets: 100+ Years of Weather Records in ClickHouse](https://clickhouse.com/blog/real-world-data-noaa-climate-data) * Blog: [Extracting, Converting, and Querying Data in Local Files using clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local)