# How to connect ## SQL API [ClickHouse](https://clickhouse.com/) 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](mailto:support@koinju.io?subject=SQL%20API%20credentials%20request\&body=Hi%2C%20I%20would%20like%20to%20get%20an%20API%20key%20to%20access%20the%20Koinju%20Market%20Data%20REST%20API) 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` ```python import clickhouse_connect conn = clickhouse_connect.get_client( host="", port=8443, username="", 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 ```log 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. {% hint style="info" %} 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). {% endhint %} Type equivalence between clickhouse and python : ## Rust Install [clickhouse-rs](https://github.com/suharev7/clickhouse-rs) and other dependecies ```sh [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 ```rust 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(":8443") .with_user("") .with_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::().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]\() ``` 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]\() 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]\( ) ) or if speed is a priority simply using float. The connector will accept both values for field structs represented as decimals in the database. ```go 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{":9440"}, Auth: clickhouse.Auth{ Database: "public_data", Username: "", 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: ### Other methods Using ClickHouse CLI Client\ doc : \ \ Using BI and visualization tools\ doc : \ \ Using programming language clients and various third-party services\ doc : \ \ **Data formats :**\ \ Along with getting the SQL result directly, ClickHouse also supports exporting data in various formats, like CSV, parquet etc.\ doc : ## REST API The REST API uses a two-tier access model: | Endpoints | Authentication | Free | Developer | Professional | Business | | ------------------ | ---------------------------- | --------------------- | -------------------- | --------------------- | ---------------------- | | `/market/*` | None (public) | 10 req/s | 10 req/s | 10 req/s | 10 req/s | | `/ohlcv`, `/trade` | API key (`x-api-key` header) | 100 req/day, 1K items | 1K req/day, 1K items | 10K req/day, 1K items | 50K req/day, 10K items | See [Pricing](https://docs.koinju.io/pricing) for full tier details. ### Public Endpoints Market listing endpoints are open — no authentication required. #### curl {% code overflow="wrap" %} ```bash curl -s 'https://api.koinju.io/market/spot' ``` {% endcode %} #### python ```python import requests response = requests.get('https://api.koinju.io/market/spot') ``` ### Private Endpoints The `/ohlcv` and `/trade` endpoints require an API key passed in the `x-api-key` header. [Contact Koinju](mailto:contact@koinju.io?subject=Rest%20API%20key%20request\&body=Hi%2C%20I%20would%20like%20to%20get%20an%20API%20key%20to%20access%20the%20Koinju%20Market%20Data%20REST%20API) to get your API key. #### curl {% code overflow="wrap" %} ```bash curl -s -H 'x-api-key: YOUR_API_KEY' \ 'https://api.koinju.io/ohlcv?exchange=deribit&market=BTC-PERPETUAL&candle_duration_in_minutes=60&start_datetime=2026-03-01T00:00:00Z&end_datetime=2026-03-01T12:00:00Z' ``` {% endcode %} #### python ```python import requests headers = {'x-api-key': 'YOUR_API_KEY'} response = requests.get( 'https://api.koinju.io/ohlcv', headers=headers, params={ 'exchange': 'deribit', 'market': 'BTC-PERPETUAL', 'candle_duration_in_minutes': 60, 'start_datetime': '2026-03-01T00:00:00Z', 'end_datetime': '2026-03-01T12:00:00Z', }, ) ``` Part of the documentation is generated from OpenAPI, the spec file is [available here](https://openapi.gitbook.com/o/dUnaFW0Jh2YT6mdGcdaQ/spec/rest-market-data-api.yaml).