OpenTick

OpenTick is a fast tick database for financial timeseries data, built on FoundationDB with simplified SQL layer.

Features:

Built-in price adjustment support
Nanosecond support
Python, C++ and Go SDK
Both sync and async query
Implicit SQL statement prepare
Permission Control, check Python API for related functions, turned off by default
Cache

Installation on Ubuntu

You need to use Go >=1.11 which has module support.

sudo apt install -y python
wget https://www.foundationdb.org/downloads/6.2.22/ubuntu/installers/foundationdb-server_6.2.22-1_amd64.deb
wget https://www.foundationdb.org/downloads/6.2.22/ubuntu/installers/foundationdb-clients_6.2.22-1_amd64.deb
sudo dpkg -i foundationdb-clients_6.2.22-1_amd64.deb foundationdb-server_6.2.22-1_amd64.deb
git clone https://github.com/opentradesolutions/opentick
make build
sudo apt install nodejs
sudo npm install -g pm2
pm2 start ./opentick

Note: FoundationDB runs in memory storage mode and only one process by default. You can change it to disk storage as belows:

user@host$ fdbcli
fdb> configure ssd

Fore more configuration on FoundationDB, please check FoundationDB Configuration

Usage

Python

pip install opentick

C++

Performance

100k ohlcv bar inserted in 1 second.

user@host:~/opentick/bindings/go$ go run test.go
2018/11/27 21:27:23 4.500470184s 5.500314708s 0 100000 all insert futures get done
2018/11/27 21:27:25 861.306778ms 1.139363333s 0 10 all batch insert futures get done
2018/11/27 21:27:26 805.542584ms 100000 retrieved with ranges
2018/11/27 21:27:27 1.782497936s 100000 retrieved with async
2018/11/27 21:27:29 1.424262818s 100000 retrieved with one sync

user@host:~/opentick/bindings/python$ ./test.py
2018-11-27 21:29:10.168138 0:00:00.200577 0:00:06.724991 0 100000 all insert futures get done
2018-11-27 21:29:12.192570 0:00:00.176540 0:00:00.959563 0 10 all batch insert futures get done
2018-11-27 21:29:13.460025 0:00:01.267462 100000 retrieved with ranges
2018-11-27 21:29:15.077686 0:00:01.617666 100000 retrieved with async
2018-11-27 21:29:16.777043 0:00:01.699361 100000 retrieved with one sync

user@host:~/opentick/bindings/cpp$ make test
21:33:19.231156889: 4.22207s 4.84954s 0 100000 all insert futures get done
21:33:20.172744180: 0.447708s 0.934337s 0 10 all batch insert futures get done
21:33:21.677161076: 1.49497s 100000 retrieved with async

Sample Code (C++)

Create database and table

auto conn = Connection::Create("127.0.0.1", 1116);
conn->Start();
conn->Execute("create database if not exists test");
conn->Use("test");
conn->Execute(R"(
      create table if not exists test(sec int, interval int, tm timestamp,
      open double, high double, low double, close double, v double, vwap
      double, primary key(sec, interval, tm))
)");

Execute

// opentick prepares the sql statement automatically, no need to prepare explicitly
auto fut = conn->ExecuteAsync(
          "select * from test where sec=1 and interval=?", Args{1}));
auto res = fut->Get(); // blocked wait until execution done
// Get last 2 rows ordering by primary key
auto res = conn->Execute(
        "select tm from test where sec=1 and interval=? limit -2", Args{1});

Insert

static const std::string kInsert =
    "insert into test(sec, interval, tm, open, high, low, close, vol, vwap) "
    "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
std::vector<Future> futs;
for (auto i = 0; i < 1000; ++i) {
  futs.push_back(conn->ExecuteAsync(kInsert, Args{1, 1, system_clock::now(), 2.2, 2.4, 2.1, 2.3, 1000000, 2.25}));
}
// wait for all insertion done
for (auto fut : futs) fut->Get();

Batch Insert

Argss argss;
for (auto i = 0; i < 1000; ++i) {
  argss.push_back(Args{1, i, system_clock::now(), 2.2, 2.4, 2.1, 2.3, 1000000, 2.25});
}
conn->BatchInsert(kInsert, argss);

Price Adjustments

auto res = conn->Execute(
        "select tm, adj(open), adj(high), adj(low), adj(close), adj(vol) from test where sec=1 and interval=? limit -2", Args{1});