DEV Community

Cover image for Day 24 of 100 Days of ClickHouse: Working with the ClickHouse HTTP API
Kanishga Subramani
Kanishga Subramani

Posted on

Day 24 of 100 Days of ClickHouse: Working with the ClickHouse HTTP API

#clickhouse #devops #dataengineering #database

When most people think about interacting with a database, they imagine using a client library, a graphical interface, or a command-line tool. While these are common approaches, ClickHouse also provides a built-in HTTP API that enables you to communicate with the database using standard HTTP requests.

This means that if your application, script, or service can send an HTTP request, it can interact with ClickHouse. Whether you're using Python, JavaScript, Go, Java, cURL, Postman, or even a simple shell script, the HTTP API provides a universal and lightweight way to execute queries, insert data, and manage your database.

In this article, we'll explore how the ClickHouse HTTP API works, how to use it effectively, and why it's such an important feature for modern applications.

What Is the ClickHouse HTTP API?

The ClickHouse HTTP API is a built-in interface that allows applications to communicate with ClickHouse over the HTTP protocol.

Instead of connecting through the native ClickHouse protocol, your application simply sends an HTTP request containing an SQL query, and ClickHouse executes it before returning the result.

By default, the HTTP API runs on port 8123 and supports both GET and POST requests.

Unlike the native protocol, the HTTP interface is:

  • Language independent
  • Easy to integrate with any application
  • Firewall friendly
  • Ideal for automation
  • Simple to test using browsers, cURL, or Postman

This flexibility makes it popular for scripting, automation, lightweight services, and REST-based applications.

How the HTTP API Works

The communication flow is straightforward:

Application
     │
     ▼
HTTP Request
     │
     ▼
ClickHouse Server
     │
     ▼
SQL Execution
     │
     ▼
HTTP Response
Enter fullscreen mode Exit fullscreen mode

The response can be returned in many formats including JSON, CSV, TSV, Parquet, Arrow, and more.

Default Ports

ClickHouse exposes multiple interfaces.

Interface Default Port
Native TCP 9000
HTTP API 8123

For HTTP-based communication, you'll primarily work with port 8123.

Prerequisites

Before following along, ensure you have:

  • A running ClickHouse server
  • cURL installed
  • Basic SQL knowledge
  • Access to port 8123

Step 1: Verify the HTTP API

The simplest way to verify that the HTTP API is available is by checking the built-in ping endpoint.

curl http://localhost:8123/ping
Enter fullscreen mode Exit fullscreen mode

Expected response:

Ok.
Enter fullscreen mode Exit fullscreen mode

If you receive this response, your HTTP interface is working correctly.

Step 2: Execute Your First Query

One of the easiest ways to query ClickHouse is by passing SQL as a URL parameter.

Using GET:

curl "http://localhost:8123/?query=SELECT+version()"
Enter fullscreen mode Exit fullscreen mode

Output:

25.3.1.1
Enter fullscreen mode Exit fullscreen mode

Although GET works well for simple queries, POST requests are generally preferred for larger SQL statements.

Using POST:

curl -X POST http://localhost:8123/ \
  --data-binary "SELECT version()"
Enter fullscreen mode Exit fullscreen mode

Output:

25.3.1.1
Enter fullscreen mode Exit fullscreen mode

POST requests are cleaner and avoid URL length limitations.

Step 3: Authentication

If authentication is enabled, ClickHouse supports several methods.

URL Parameters 

curl "http://localhost:8123/?user=default&password=yourpassword&query=SELECT+1"
Enter fullscreen mode Exit fullscreen mode

HTTP Basic Authentication 

curl -u default:yourpassword \
"http://localhost:8123/?query=SELECT+1"
Enter fullscreen mode Exit fullscreen mode

HTTP Headers (Recommended) 

curl http://localhost:8123/ \
-H "X-ClickHouse-User: default" \
-H "X-ClickHouse-Key: yourpassword" \
--data-binary "SELECT 1"
Enter fullscreen mode Exit fullscreen mode

Using request headers is recommended because credentials aren't exposed in URLs or logs.

Step 4: Selecting a Database

Queries execute against the default database unless another database is specified.

Specify a database using the request parameter:

curl -u default:yourpassword \
"http://localhost:8123/?database=my_database" \
--data-binary "SELECT count() FROM my_table"
Enter fullscreen mode Exit fullscreen mode

Or reference the database directly inside the SQL statement:

SELECT count()
FROM my_database.my_table;
Enter fullscreen mode Exit fullscreen mode

Step 5: Querying Data

Executing SELECT statements is straightforward.

curl -u default:yourpassword \
http://localhost:8123/ \
--data-binary "SELECT * FROM default.products LIMIT 5"
Enter fullscreen mode Exit fullscreen mode

By default, ClickHouse returns data in TabSeparated format.

Output Formats

One of the strengths of the HTTP API is its support for multiple output formats.

JSON 

SELECT *
FROM default.products
FORMAT JSON
Enter fullscreen mode Exit fullscreen mode

Useful for:

  • REST APIs
  • Web applications
  • JavaScript clients

CSV 

SELECT *
FROM default.products
FORMAT CSV
Enter fullscreen mode Exit fullscreen mode

Ideal for:

  • Excel
  • Data exports
  • ETL pipelines

JSONEachRow 

SELECT *
FROM default.products
FORMAT JSONEachRow
Enter fullscreen mode Exit fullscreen mode

Each row is returned as an individual JSON document, making it perfect for:

  • Streaming
  • Kafka
  • Log processing
  • Large datasets

Other Supported Formats

ClickHouse supports numerous formats including:

  • JSON
  • JSONCompact
  • JSONEachRow
  • CSV
  • TSV
  • Pretty
  • PrettyCompact
  • Parquet
  • Apache Arrow
  • TabSeparated

Choosing the appropriate format depends on how downstream systems consume the data.

Step 6: Inserting Data

The HTTP API also supports data ingestion.

Insert TabSeparated Data 

curl -u default:yourpassword \
"http://localhost:8123/?query=INSERT+INTO+default.products+FORMAT+TabSeparated" \
--data-binary $'5\tMechanical Keyboard\tAccessories\n6\tMonitor 4K\tElectronics\n'
Enter fullscreen mode Exit fullscreen mode

Insert CSV 

curl -u default:yourpassword \
"http://localhost:8123/?query=INSERT+INTO+default.products+FORMAT+CSV" \
--data-binary @products.csv
Enter fullscreen mode Exit fullscreen mode

Insert JSON 

curl -u default:yourpassword \
"http://localhost:8123/?query=INSERT+INTO+default.products+FORMAT+JSONEachRow" \
--data-binary '
{"product_id":7,"name":"Webcam HD","category":"Accessories"}
{"product_id":8,"name":"Desk Lamp","category":"Furniture"}
'
Enter fullscreen mode Exit fullscreen mode

This flexibility allows applications to ingest data using the format most convenient for them.

Step 7: Creating and Managing Tables

The HTTP API supports all standard Data Definition Language (DDL) operations.

Create a table:

CREATE TABLE default.logs
(
    server_id LowCardinality(String),
    level LowCardinality(String),
    message String,
    timestamp DateTime
)
ENGINE = MergeTree()
ORDER BY timestamp;
Enter fullscreen mode Exit fullscreen mode

Drop a table:

DROP TABLE IF EXISTS default.logs;
Enter fullscreen mode Exit fullscreen mode

Similarly, ALTER, TRUNCATE, OPTIMIZE, and other administrative statements are fully supported.

Useful HTTP Parameters

Several query parameters can simplify common tasks.

Parameter Purpose
query SQL query
user Username
password Password
database Database name
default_format Default response format
max_rows_to_read Scan limit
max_execution_time Query timeout
compress Enable response compression

These parameters help control execution behavior without modifying SQL itself.

Best Practices

To get the most out of the ClickHouse HTTP API:

  • Use POST requests for longer SQL statements.
  • Send credentials using HTTP headers instead of URLs.
  • Use JSONEachRow for streaming large datasets.
  • Set max_execution_time to prevent runaway queries.
  • Enable compression for large responses.
  • Always specify the database explicitly.
  • Use official ClickHouse client libraries in production when advanced features like connection pooling and automatic retries are required.

Following these practices results in more secure, reliable, and efficient integrations.

HTTP API vs Native Protocol

Feature HTTP API Native Protocol
Ease of Use ⭐⭐⭐⭐⭐ ⭐⭐⭐⭐
Performance ⭐⭐⭐⭐ ⭐⭐⭐⭐⭐
Driver Required No Yes
Universal Support Yes Driver Dependent
Automation Excellent Good
Bulk Inserts Good Excellent

The HTTP API is ideal for integrations, scripting, and lightweight services, while the native protocol remains the best option for performance-critical applications.

Real-World Use Cases

The ClickHouse HTTP API is commonly used for:

  • Building REST APIs
  • Internal dashboards
  • Automation scripts
  • Monitoring systems
  • CI/CD pipelines
  • Serverless applications
  • ETL workflows
  • Data export services
  • Microservices
  • Third-party integrations

Because HTTP is universally supported, integrating ClickHouse into existing systems becomes remarkably simple.

Final Thoughts

The ClickHouse HTTP API demonstrates how powerful database interactions can also be simple. By exposing SQL operations over standard HTTP, ClickHouse removes the dependency on specialized drivers and enables seamless integration with virtually any programming language or platform.

Whether you're executing queries, loading data, automating administrative tasks, or building cloud-native applications, the HTTP API offers a flexible and reliable interface that scales from quick experiments to production workloads.

As your ClickHouse deployments grow, understanding the HTTP API becomes increasingly valuable. It's a feature that bridges the gap between databases and modern software architectures, making automation, integration, and analytics significantly easier.

In the next article of this 100 Days of ClickHouse series, we'll continue exploring another powerful capability that helps you build faster, more scalable analytical systems.

Top comments (0)