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-connect

Because 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.

All datetimes returned by koinju timezone aware and set to UTC. By default the connector will convert them to the user's timezone. So care should be applied if for some reasons the timezone awareness need to be dropped ( as for example when storing results in a excel spreadsheet).

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/v2

By 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 sell

Type 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
)