DEV Community

Cover image for Simplified API Creation and Management: ClickHouse to APISIX Integration Without Code
Shahab Ranjbary
Shahab Ranjbary

Posted on

Simplified API Creation and Management: ClickHouse to APISIX Integration Without Code

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>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Run Docker Compose

Start the services using Docker Compose. This command will set up ClickHouse and APISIX using predefined configurations.

docker-compose up -d
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"
    }
  }
}'
Enter fullscreen mode Exit fullscreen mode

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
}'
Enter fullscreen mode Exit fullscreen mode

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=='
Enter fullscreen mode Exit fullscreen mode

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
    }
}
Enter fullscreen mode Exit fullscreen mode

You can also check the log table for verification:

SELECT * FROM apisix_log.users;
Enter fullscreen mode Exit fullscreen mode

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)