Introduction
In the world of data management and web services, creating and managing APIs can often be a complex and time-consuming task. However, with the right tools, this process can be significantly simplified. In this article, we will explore how to create APIs for fetching data from ClickHouse tables without writing any code and manage these APIs using APISIX. ClickHouse, a fast and open-source columnar database management system, provides an HTTP interface by default, enabling easy access to data. By integrating this with APISIX, an open-source API gateway, we can not only manage and log our APIs but also leverage a host of features provided by APISIX to enhance our API management capabilities.
Why ClickHouse and APISIX?
ClickHouse is renowned for its performance and efficiency in handling large volumes of data. Its built-in HTTP interface allows for straightforward data retrieval from tables, making it an excellent choice for building APIs.
APISIX, on the other hand, is a powerful API gateway that offers extensive features such as traffic management, logging, monitoring, and security, among others. Integrating ClickHouse with APISIX allows us to manage our APIs effectively without delving into the complexities of writing custom code.
Creating APIs in ClickHouse
ClickHouse’s HTTP interface enables us to interact with the database using simple HTTP requests. This interface supports various operations such as querying data, inserting records, and managing tables. To fetch data from a ClickHouse table, you can use a basic HTTP GET request:
GET http://<clickhouse-server>:8123/?query=SELECT+*+FROM+<table_name>
This simplicity makes ClickHouse an excellent candidate for API creation, as it eliminates the need for complex server-side logic. However, managing these APIs, especially in a production environment, requires additional capabilities such as authentication, rate limiting, and detailed logging.
Enhancing API Management with APISIX
APISIX comes into play by providing a robust platform for managing our ClickHouse APIs. By routing ClickHouse's HTTP requests through APISIX, we gain access to a plethora of features that enhance API management:
- Traffic Management: Control and optimize API traffic using load balancing and rate limiting.
- Security: Implement authentication and authorization mechanisms to secure your APIs.
- Logging and Monitoring: Gain insights into API usage patterns and performance through comprehensive logging and monitoring tools.
- Plugin System: Extend APISIX’s functionality using its rich plugin system, enabling custom behavior and integrations.
Setting Up the Integration
To run, follow these steps:
Clone the Repository
First, clone the repository to your local machine. This repository contains the necessary configurations for setting up ClickHouse and APISIX.
git clone https://github.com/ranjbaryshahab/clickhouse-apisix.git
cd clickhouse-apisix
Run Docker Compose
Start the services using Docker Compose. This command will set up ClickHouse and APISIX using predefined configurations.
docker-compose up -d
Connect to ClickHouse and Run the Following Commands
Create table
Create a table named users in the default database of ClickHouse. This table will store user data.
CREATE TABLE `default`.users (
id UInt64,
name String,
family String,
age UInt8
) ENGINE = MergeTree()
ORDER BY id;
Insert data into the table
Insert sample data into the users table. This data will be used for testing our API.
insert into users values(1, 'Shahab', 'Ranjbary',28);
insert into users values(2, 'Sepehr', 'Ranjbary',18);
insert into users values(3, 'John', 'Doe',28);
Create a Log Table for the API Calls
Create a log table in the apisix_log database. This table will store logs of API calls for monitoring and analysis.
CREATE database apisix_log;
CREATE TABLE apisix_log.users
(
`@timestamp` String,
`host` String,
`client_ip` String,
`consumer_name` String,
`route_id` String,
`route_name` String,
`request` String,
`upstream_status` String,
`status` UInt16,
`upstream_response_time` Float64,
`connection_time` Float64
)
ENGINE = MergeTree
PRIMARY KEY `@timestamp`
ORDER BY `@timestamp`
SETTINGS index_granularity = 8192
Create Users and Grant Access
Create users for APISIX and grant the necessary permissions to access the users table and insert logs into the apisix_log.users table.
CREATE USER apisix IDENTIFIED WITH plaintext_password BY 'apisix';
GRANT SHOW COLUMNS, SELECT on default.users TO apisix;
CREATE USER apisix_logger IDENTIFIED WITH plaintext_password BY 'apisix_logger';
GRANT SHOW COLUMNS, SELECT, INSERT on apisix_log.users TO apisix_logger;
Create a Consumer in APISIX
Create a consumer in APISIX with basic authentication. This consumer will be used to authenticate API requests.
curl http://localhost:9180/apisix/admin/consumers \
-H 'X-API-KEY: edd1c9f034335f136f87ad84b625c8f1' -X PUT -d '
{
"username": "ds_team",
"plugins": {
"basic-auth": {
"_meta": {
"disable": true
},
"password": "12345",
"username": "ds_team"
}
}
}'
Create a Route in APISIX
Create a route in APISIX to handle requests to the ClickHouse API. This route includes configurations for authentication, logging, and proxying requests to ClickHouse.
curl http://localhost:9180/apisix/admin/routes/1 \
-H 'X-API-KEY: edd1c9f034335f136f87ad84b625c8f1' -X PUT -d '
{
"uri": "/clickhouse-api*",
"name": "clickhouse",
"methods": [
"GET"
],
"plugins": {
"basic-auth": {
"disable": false
},
"clickhouse-logger": {
"database": "apisix_log",
"disable": false,
"endpoint_addr": "http://clickhouse:8123",
"log_format": {
"@timestamp": "$time_iso8601",
"client_ip": "$remote_addr",
"connection_time": "$connection_time",
"consumer_name": "$consumer_name",
"host": "$host",
"request": "$request",
"route_name": "$route_name",
"status": "$status",
"upstream_response_time": "$upstream_response_time",
"upstream_status": "$upstream_status"
},
"logtable": "users",
"password": "apisix_logger",
"user": "apisix_logger"
},
"proxy-rewrite": {
"headers": {
"Authorization": "",
"X-ClickHouse-Format": "JSON",
"X-ClickHouse-Key": "apisix",
"X-ClickHouse-User": "apisix"
},
"uri": "/?query=select%20name,family,age%20from%20default.users%20where%20age%20=%20%7Bage:UInt8%7D"
}
},
"upstream": {
"nodes": [
{
"host": "clickhouse",
"port": 8123,
"weight": 1
}
],
"timeout": {
"connect": 6,
"send": 6,
"read": 6
},
"type": "roundrobin",
"scheme": "http",
"pass_host": "pass",
"keepalive_pool": {
"idle_timeout": 60,
"requests": 1000,
"size": 320
}
},
"status": 1
}'
Now We Can Test It
Test the API by making a GET request to the ClickHouse API endpoint. This request fetches users with the age of 18.
curl --location --request GET 'http://localhost:9080/clickhouse-api/users?param_age=18' \
--header 'Authorization: Basic ZHNfdGVhbToxMjM0NQ=='
This API returns the users with the specified age. The result is:
{
"meta": [
{
"name": "name",
"type": "String"
},
{
"name": "family",
"type": "String"
},
{
"name": "age",
"type": "UInt8"
}
],
"data": [
{
"name": "Sepehr",
"family": "Ranjbary",
"age": 18
}
],
"rows": 1,
"statistics": {
"elapsed": 0.001827077,
"rows_read": 3,
"bytes_read": 35
}
}
You can also check the log table for verification:
SELECT * FROM apisix_log.users;
Conclusion
Integrating ClickHouse with APISIX allows you to create and manage APIs efficiently without writing any code. ClickHouse’s HTTP interface simplifies data retrieval, while APISIX enhances API management with features like traffic control, security, and logging. This setup is highly beneficial for production environments, where managing APIs with minimal code and maximum efficiency is crucial. By following the steps outlined in this guide, you can quickly set up and test APIs for your ClickHouse tables, ensuring robust and scalable API management.
Top comments (0)