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_connectconn = 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
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).
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.
[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"] }
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);
}
