ClickHouse Client

### Aliases[​](#cli_aliases "Direct link to Aliases")

You can use the following aliases from within the REPL:

- `\l` - SHOW DATABASES
- `\d` - SHOW TABLES
- `\c <DATABASE>` - USE DATABASE
- `.` - repeat the last query

### Keyboard shortcuts[​](#keyboard_shortcuts "Direct link to Keyboard shortcuts")

- `Alt (Option) + Shift + e` - open editor with the current query. It is possible to specify the editor to use with the environment variable `EDITOR`. By default, `vim` is used.
- `Alt (Option) + #` - comment line.
- `Ctrl + r` - fuzzy history search.

The full list with all available keyboard shortcuts is available at [replxx](https://github.com/AmokHuginnsson/replxx/blob/1f149bf/src/replxx_impl.cxx#L262).

Tip

To configure the correct work of the meta key (Option) on MacOS:

iTerm2: Go to Preferences -> Profile -> Keys -> Left Option key and click Esc+

## Batch mode[​](#batch-mode "Direct link to Batch mode")

### Using batch mode[​](#using-batch-mode "Direct link to Using batch mode")

Instead of using ClickHouse Client interactively, you can run it in batch mode.
In batch mode, ClickHouse executes a single query and exits immediately - there's no interactive prompt or loop.

You can specify a single query like this:

```
$ clickhouse-client "SELECT sum(number) FROM numbers(10)"
45
```

You can also use the `--query` command-line option:

```
$ clickhouse-client --query "SELECT uniq(number) FROM numbers(10)"
10
```

You can provide a query on `stdin`:

```
$ echo "SELECT avg(number) FROM numbers(10)" | clickhouse-client
4.5
```

Assuming the existence of a table `messages`, you can also insert data from the command line:

```
$ echo "Hello\nGoodbye" | clickhouse-client --query "INSERT INTO messages FORMAT CSV"
```

When `--query` is specified, any input is appended to the request after a line feed.

### Inserting a CSV file into a remote ClickHouse service[​](#cloud-example "Direct link to Inserting a CSV file into a remote ClickHouse service")

This example is inserting a sample dataset CSV file, `cell_towers.csv` into an existing table `cell_towers` in the `default` database:

```
clickhouse-client --host HOSTNAME.clickhouse.cloud \
  --port 9440 \
  --user default \
  --password PASSWORD \
  --query "INSERT INTO cell_towers FORMAT CSVWithNames" \
  < cell_towers.csv
```

### Examples of inserting data from the command line[​](#more-examples "Direct link to Examples of inserting data from the command line")

There are several ways to insert data from the command line.
The example below inserts two rows of CSV data into a ClickHouse table using batch mode:

```
echo -ne "1, 'some text', '2016-08-14 00:00:00'\n2, 'some more text', '2016-08-14 00:00:01'" | \
  clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV";
```

In the example below `cat <<_EOF` starts a heredoc that will read everything until it sees `_EOF` again, then outputs it:

```
cat <<_EOF | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV";
3, 'some text', '2016-08-14 00:00:00'
4, 'some more text', '2016-08-14 00:00:01'
_EOF
```

In the example below, the contents of file.csv are output to stdout using `cat`, and piped into `clickhouse-client` as input:

```
cat file.csv | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV";
```

In batch mode, the default data [format](/docs/interfaces/formats) is `TabSeparated`.
You can set the format in the `FORMAT` clause of the query as shown in the example above.

## Queries with parameters[​](#cli-queries-with-parameters "Direct link to Queries with parameters")

You can specify parameters in a query and pass values to it with command-line options.
This avoids formatting a query with specific dynamic values on the client side.
For example:

```
$ clickhouse-client --param_parName="[1, 2]" --query "SELECT {parName: Array(UInt16)}"
[1,2]
```

It is also possible to set parameters from within an [interactive session](#interactive-mode):

```
$ clickhouse-client
ClickHouse client version 25.X.X.XXX (official build).

#highlight-next-line
:) SET param_parName='[1, 2]';

SET param_parName = '[1, 2]'

Query id: 7ac1f84e-e89a-4eeb-a4bb-d24b8f9fd977

Ok.

0 rows in set. Elapsed: 0.000 sec.

#highlight-next-line
:) SELECT {parName:Array(UInt16)}

SELECT {parName:Array(UInt16)}

Query id: 0358a729-7bbe-4191-bb48-29b063c548a7

   ┌─_CAST([1, 2]⋯y(UInt16)')─┐
1. │ [1,2]                    │
   └──────────────────────────┘

1 row in set. Elapsed: 0.006 sec.
```

### Query syntax[​](#cli-queries-with-parameters-syntax "Direct link to Query syntax")

In the query, place the values that you want to fill using command-line parameters in braces in the following format:

```
{<name>:<data type>}
```

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Parameter Description|  |  |  |  | | --- | --- | --- | --- | | `name` Placeholder identifier. The corresponding command-line option is `--param_<name> = value`.| `data type` [Data type](/docs/sql-reference/data-types) of the parameter.   For example, a data structure like `(integer, ('string', integer))` can have the `Tuple(UInt8, Tuple(String, UInt8))` data type (you can also use other [integer](/docs/sql-reference/data-types/int-uint) types).   It is also possible to pass the table name, database name, and column names as parameters, in that case you would need to use `Identifier` as the data type. | | | | | |

### Examples[​](#cli-queries-with-parameters-examples "Direct link to Examples")

```
$ clickhouse-client --param_tuple_in_tuple="(10, ('dt', 10))" \
    --query "SELECT * FROM table WHERE val = {tuple_in_tuple:Tuple(UInt8, Tuple(String, UInt8))}"

$ clickhouse-client --param_tbl="numbers" --param_db="system" --param_col="number" --param_alias="top_ten" \
    --query "SELECT {col:Identifier} as {alias:Identifier} FROM {db:Identifier}.{tbl:Identifier} LIMIT 10"
```

## AI-powered SQL generation[​](#ai-sql-generation "Direct link to AI-powered SQL generation")

ClickHouse Client includes built-in AI assistance for generating SQL queries from natural language descriptions. This feature helps users write complex queries without deep SQL knowledge.

The AI assistance works out of the box if you have either `OPENAI_API_KEY` or `ANTHROPIC_API_KEY` environment variable set. For more advanced configuration, see the [Configuration](#ai-sql-generation-configuration) section.

### Usage[​](#ai-sql-generation-usage "Direct link to Usage")

To use AI SQL generation, prefix your natural language query with `??`:

```
:) ?? show all users who made purchases in the last 30 days
```

The AI will:

1. Explore your database schema automatically
2. Generate appropriate SQL based on the discovered tables and columns
3. Execute the generated query immediately

### Example[​](#ai-sql-generation-example "Direct link to Example")

```
:) ?? count orders by product category

Starting AI SQL generation with schema discovery...
──────────────────────────────────────────────────

🔍 list_databases
   ➜ system, default, sales_db

🔍 list_tables_in_database
   database: sales_db
   ➜ orders, products, categories

🔍 get_schema_for_table
   database: sales_db
   table: orders
   ➜ CREATE TABLE orders (order_id UInt64, product_id UInt64, quantity UInt32, ...)

✨ SQL query generated successfully!
──────────────────────────────────────────────────

SELECT
    c.name AS category,
    COUNT(DISTINCT o.order_id) AS order_count
FROM sales_db.orders o
JOIN sales_db.products p ON o.product_id = p.product_id
JOIN sales_db.categories c ON p.category_id = c.category_id
GROUP BY c.name
ORDER BY order_count DESC
```

### Configuration[​](#ai-sql-generation-configuration "Direct link to Configuration")

AI SQL generation requires configuring an AI provider in your ClickHouse Client configuration file. You can use either OpenAI, Anthropic, or any OpenAI-compatible API service.

#### Environment-based fallback[​](#ai-sql-generation-fallback "Direct link to Environment-based fallback")

If no AI configuration is specified in the config file, ClickHouse Client will automatically try to use environment variables: