How to connect
SQL API
ClickHouse is an open-source column-oriented DBMS for online analytical processing (OLAP) that allows users to generate analytical reports using SQL queries in real-time.
The database can be interacted with either via its CLI client or connector available for all the mainstream languages.
Authentication
Please Contact Koinju to get the database url and your credentials, the following examples show how to authenticate yourself with them.
Python
Install clickhouse-connect
pip install clickhouse-connectBecause the connector allows to query directly into a pandas DataFrame, if this feature is desired pandas should be installed as well.
Get the last 20 trades for any instrument starting with BTC
import clickhouse_connect
conn = clickhouse_connect.get_client(
host="<provided_database_url>",
port=8443,
username="<username>",
password="<password>",
database="public_data",
)
df = conn.query_df(
"select * from trade where market like 'BTC%' and timestamp > toStartOfDay(now()) order by timestamp desc limit 20"
)
print(df.columns)
print(df[["exchange", "market", "timestamp", "price", "quantity", "side"]].head())Outputs
Index(['exchange', 'market', 'side', 'quantity', 'price', 'timestamp',
'hostname', 'trade_id', 'hash', 'ts_reception', 'fill_trade',
'created_at'],
dtype='object')
exchange market ... quantity side
0 binance BTCUSDT ... 0.00005000000000000000 buy
1 binance-usdm-future BTCUSDT ... 0.04900000000000000000 sell
2 coinbase BTC-USD ... 0.00001337000000000000 sell
3 binance BTCFDUSD ... 0.01893000000000000000 buy
4 binance BTCFDUSD ... 0.00036000000000000000 buy
[5 rows x 6 columns]The query includes an additional filter by timestamp to optimize speed. The trade table, over 40TB, contains all public trades across several exchanges. While the entire dataset is searchable, limiting the time frame ensures results return in milliseconds instead of seconds, especially if queries do not match existing indexes. More details on query optimization are provided for each endpoint and in a general overview.
Type equivalence between clickhouse and python : https://clickhouse.com/docs/integrations/python#read-format-options-python-types
Rust
Install clickhouse-rs and other dependecies
[dependencies]
clickhouse = { version = "0.13.3", features = ["rustls-tls", "time"] }
serde = { version = "1.0.219", features = ["derive"] }
tokio = { version = "1", features = ["full"] }
time = "0.3.41"
rust_decimal = { version = "1.37.1", features = ["serde-str"] }Then execute the following
use clickhouse::sql::Identifier;
use clickhouse::Client;
use clickhouse::Row;
use rust_decimal::Decimal;
use serde::{Deserialize, Deserializer};
use std::str::FromStr;
use time::OffsetDateTime;
#[derive(Row, Deserialize, Debug)]
struct Trade {
exchange: String,
market: String,
#[serde(with = "clickhouse::serde::time::datetime64::nanos")]
timestamp: OffsetDateTime,
price: Decimal,
quantity: Decimal,
side: String,
}
#[tokio::main]
async fn main() {
let client = Client::default()
// should include both protocol and port
.with_url("<provided_database_url>:8443")
.with_user("<username>")
.with_password("<password>")
.with_database("public_data");
let cursor = client
.query("select exchange ,market ,timestamp ,toString(price),toString(quantity), side from public_data.trade where market like 'BTC%' and timestamp > toStartOfDay(now()) order by timestamp desc limit 20")
.fetch_all::<Trade>().await.unwrap();
dbg!(&cursor);
}
Ouputs
[src/main.rs:31:5] &cursor = [
Trade {
exchange: "binance",
market: "BTCUSDT",
timestamp: 2025-06-09 14:18:17.242 +00:00:00,
price: 107414.47,
quantity: 0.001,
side: "buy",
},
Trade {
exchange: "binance",
market: "BTCUSDT",
timestamp: 2025-06-09 14:18:17.192 +00:00:00,
price: 107414.48,
quantity: 0.00009,
side: "sell",
},
....The conversion of the Decimal type into String in the query allows to decode the strings directly into Decimal type from rust_decimal crate.
Type equivalence between clickhouse and rust : https://clickhouse.com/docs/integrations/rust#data-types
Golang
Install [clickhouse-go](https://github.com/ClickHouse/clickhouse-go)
go get -u github.com/ClickHouse/clickhouse-go/v2By default all the decimal numbers in our tables are represented in [Decimal256 with a scale of 20](https://clickhouse.com/docs/sql-reference/data-types/decimal) as to not lose any precision from data recieved from the exchages.
If user value correctness the users can stick to decimal by installing and using this [decimal library](https://github.com/shopspring/decimal ) ) or if speed is a priority simply using float. The connector will accept both values for field structs represented as decimals in the database.
package main
import (
"context"
"crypto/tls"
"github.com/ClickHouse/clickhouse-go/v2"
"github.com/shopspring/decimal"
"time"
)
func main() {
ctx := context.Background()
conn, err := clickhouse.Open(&clickhouse.Options{
Addr: []string{"<provided_database_url>:9440"},
Auth: clickhouse.Auth{
Database: "public_data",
Username: "<user>",
Password: "<password>",
},
TLS: &tls.Config{},
Compression: &clickhouse.Compression{
Method: clickhouse.CompressionLZ4,
},
})
if err != nil {
panic(err)
}
var result []struct {
Exchange string `ch:"exchange"`
Market string `ch:"market"`
Timestamp time.Time `ch:"timestamp"`
Price decimal.Decimal `ch:"price"`
Quantity decimal.Decimal `ch:"quantity"`
Side string `ch:"side"`
}
err = conn.Select(ctx, &result,
"select exchange, market, timestamp, price, quantity , side from trade where market like 'BTC%' and timestamp > toStartOfDay(now()) order by timestamp desc limit 20");
if err != nil {
panic(err)
}
for _, row := range result[:5] {
println(row.Exchange, row.Market, row.Timestamp.String(), row.Price.String(), row.Quantity.String(), row.Side)
}
}Outputs
binance BTCUSDT 2025-06-07 15:41:19.39 +0000 UTC 105465 0.00044 buy
binance-usdm-future BTCUSDT 2025-06-07 15:41:19.365 +0000 UTC 105400.6 0.01 buy
binance-usdm-future BTCUSDT 2025-06-07 15:41:19.335 +0000 UTC 105400.6 0.008 buy
binance BTCUSDT 2025-06-07 15:41:19.282 +0000 UTC 105465 0.001 buy
binance BTCUSDT 2025-06-07 15:41:19.214 +0000 UTC 105465.01 0.001 sellType equivalence between clickhouse and go: https://clickhouse.com/docs/integrations/go#type-conversions
Other methods
Using ClickHouse CLI Client doc : https://clickhouse.com/docs/integrations/sql-clients/cli Using BI and visualization tools doc : https://clickhouse.com/docs/integrations/data-visualization Using programming language clients and various third-party services doc : https://clickhouse.com/docs/integrations Data formats : Along with getting the SQL result directly, ClickHouse also supports exporting data in various formats, like CSV, parquet etc. doc : https://clickhouse.com/docs/integrations/data-formats
REST API
Authentication
Contact Koinju to get your api keys then use http basic auth to authenticate your requests.
Part of the documentation are generated from OPEN API which spec file is avaialble here.
curl
curl -H "Content-Type: application/json" -s --user 'username:password' 'https://api.koinju.io/market/spot'
python
import requests
from requests.auth import HTTPBasicAuth
auth = HTTPBasicAuth('username', 'password')
response = requests.get(
url='https://api.koinju.io/market/spot,
auth=auth
)Last updated
Was this helpful?