clickhouse-local

## 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")

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):

```
$ clickhouse-local --structure "table_structure" --input-format "format_of_incoming_data" --query "query"
```

Basic usage (Mac):

```
$ ./clickhouse local --structure "table_structure" --input-format "format_of_incoming_data" --query "query"
```

Note

`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 query before execution.
- `--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[​](#commands "Direct link to Commands")

### LS Command[​](#ls-command "Direct link to LS Command")

Lists all the files in the current working directory accessible to clickhouse-local.

You can run it in interactive mode like:

```
ClickHouse local version 26.3.1.1.

:) ls

SELECT _file AS file
FROM file('*', 'One')
ORDER BY file ASC
```

```
┌─file────────┐
│ file1.csv   │
│ file2.json  │
│ file3.xml   │
└─────────────┘
```

You can also run it as a query using the argument -q:

```
./clickhouse-local -q ls
```

```
file1.csv
file2.json
file3.xml
```

## Examples[​](#examples "Direct link to Examples")

```
$ 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:

```
$ 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](/docs/sql-reference/table-functions/file):

```
$ 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:

Query:

```
$ 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"
```

Result:

```
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 │
├──────────┼──────────┤
...
```

## Related Content[​](#related-content-1 "Direct link to 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)

- [When to use clickhouse-local vs. ClickHouse](#when-to-use-clickhouse-local-vs-clickhouse)- [Download clickhouse-local](#download-clickhouse-local)- [Query data in a file using SQL](#query_data_in_file)- [Query data in a Parquet file in AWS S3](#query-data-in-a-parquet-file-in-aws-s3)- [Format Conversions](#format-conversions)- [Usage](#usage)- [Commands](#commands)
              - [LS Command](#ls-command)- [Examples](#examples)- [Related Content](#related-content-1)

Was this page helpful?