ClickHouse Client

curl https://clickhouse.com/ | sh

sudo ./clickhouse install

$ clickhouse-client --host server

ClickHouse client version 24.12.2.29 (official build).
Connecting to server:9000 as user default.
Connected to ClickHouse server version 24.12.2.

:)

<config>
    <connections_credentials>
        <connection>
            <name>default</name>
            <hostname>hostname</hostname>
            <port>9440</port>
            <secure>1</secure>
            <user>default</user>
            <password>password</password>
            <!-- <history_file></history_file> -->
            <!-- <history_max_entries></history_max_entries> -->
            <!-- <accept-invalid-certificate>false</accept-invalid-certificate> -->
            <!-- <prompt></prompt> -->
        </connection>
    </connections_credentials>
</config>

clickhouse-client

ClickHouse client version 25.x.x.x
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.x.x.x

hostname :)

help domainWithoutWWW

help maxx_threads

:) /hepl

Exception on client:
Code: 36. DB::Exception: Unknown command `/hepl`. Maybe you meant: ['/help']. Type `/` at the beginning of the line to see all the commands. (BAD_ARGUMENTS)

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

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

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

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

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

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";

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

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

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

{<name>:<data type>}

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

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

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

# OpenAIを使用する場合
export OPENAI_API_KEY=your-openai-key
clickhouse-client

# Anthropicを使用する場合
export ANTHROPIC_API_KEY=your-anthropic-key
clickhouse-client

ai:
  provider: openai  # Use 'openai' for compatibility
  api_key: your-openrouter-api-key
  base_url: https://openrouter.ai/api/v1
  model: anthropic/claude-3.5-sonnet  # Use OpenRouter model naming

# Minimal config - uses environment variable for API key
ai:
  provider: openai  # Will use OPENAI_API_KEY env var

# No config at all - automatic fallback
# (Empty or no ai section - will try OPENAI_API_KEY then ANTHROPIC_API_KEY)

# Only override model - uses env var for API key
ai:
  provider: openai
  model: gpt-3.5-turbo

clickhouse:[//[user[:password]@][hosts_and_ports]][/database][?query_parameters]

clickhouse://[2001:db8::1234]

clickhouse-client clickhouse://localhost:9000 --query "SELECT 1"

clickhouse-client clickhouse://john:secret@127.0.0.1:9000

clickhouse-client clickhouse://[::1]:9000

clickhouse-client clickhouse://localhost:9000 '-m'

clickhouse-client clickhouse://default@localhost:9000

# 同等の表記:
clickhouse-client clickhouse://localhost:9000 --user default

clickhouse-client clickhouse://localhost:9000/my_database

# 同等の表記:
clickhouse-client clickhouse://localhost:9000 --database my_database

clickhouse-client clickhouse://localhost/my_database?s

# 同等の表記:
clickhouse-client clickhouse://localhost/my_database -s

clickhouse-client clickhouse:

clickhouse-client clickhouse://my_user@

# : と @ の間のパスワードを空にすると、接続開始前にユーザーへパスワードの入力を求めます。
clickhouse-client clickhouse://my_user:@

clickhouse-client clickhouse://some_user%40some_mail.com@localhost:9000

clickhouse-client clickhouse://192.168.1.15,192.168.1.25

Query id: 927f137d-00f1-4175-8914-0dd066365e96

<config>
  <query_id_formats>
    <speedscope>http://speedscope-host/#profileURL=qp%3Fid%3D{query_id}</speedscope>
  </query_id_formats>
</config>

speedscope:http://speedscope-host/#profileURL=qp%3Fid%3Dc8ecc783-e753-4b38-97f1-42cddfb98b7d

$ clickhouse-client --max_threads 1