ClickHouse is a columnar database. It is open-source, licensed under the Apache 2.0 license.
It can be queried with SQL.
chproxy
chproxy is a load-balancing reverse-proxy for ClickHouse.
Since ClickHouse uses HTTP for running queries, you can use any reverse proxy like nginx or HAProxy with it. But chproxy is made specifically for ClickHouse usage.
ClickHouse clusters
You can put ClickHouse servers into clusters to increase your data redundancy, amount of storage, or query performance.
If you are using replication or distributed DDLs, you will need to set up something that implements the ZooKeeper protocol. The best options for ClickHouse are ClickHouse Keeper or ZooKeeper.
Useful links
Interesting queries
Array transpose
create function leoArrayTranspose as (arr) ->
arrayMap(x -> arrayMap(y -> y[x], arr), arrayEnumerate(arr[1]));
select leoArrayTranspose([
[1, 2, 3],
[4, 5, 6],
[7, 8, 9]
])
-- Outputs:
-- [1, 4, 7],
-- [2, 5, 8],
-- [3, 6, 9](https://www.gkbrk.com/1-4-7-2-5-8-3-6-9)
Fast Geo bounding box filtering
WITH
-1.8822175 AS lon_0,
53.6256685 AS lat_0,
1000 AS radius_m,
radius_m / 110540 AS delta_lat,
radius_m / (111320 * cos(radians(LEAST(90, ABS(lat_0) + delta_lat)))) AS delta_lon,
lat_0 - delta_lat AS min_lat,
lat_0 + delta_lat AS max_lat,
lon_0 - delta_lon AS min_lon,
lon_0 + delta_lon AS max_lon
SELECT *
FROM leomap.osm_node
PREWHERE (longitude >= min_lon) AND (longitude <= max_lon) AND (latitude >= min_lat) AND (latitude <= max_lat)
WHERE geoDistance(lon_0, lat_0, longitude, latitude) < radius_m
