Connect API to kdb database

Question

simple question - I've successfully connected to the Coinbase API using Python 3.6 and receiving BTC buy/sell prices in my console.

I'd like to connect this to a kdb database and start creating a HDB of tick data but I'm a bit confused on how to structure this set-up, i.e., push the get requests to the database for storage. My python code looks like...

api_key = 'XXXXX'
api_secret = 'XXXXX'
from coinbase.wallet.client import Client
import time, requests


client = Client(api_key, api_secret)


starttime = time.time()
while True:
    buy_price = client.get_buy_price(currency_pair = 'BTC-USD')
    sell_price = client.get_sell_price(currency_pair = 'BTC-USD')
    time.sleep(10.0)
    print(buy_price)
    print(sell_price)
    print("=-=-=-=-=-=")

The console prints the feed, which looks like...

{
  "amount": "8034.79",
  "base": "BTC",
  "currency": "USD"
}
{
  "amount": "7875.67",
  "base": "BTC",
  "currency": "USD"
}
=-=-=-=-=-=
{
  "amount": "8034.80",
  "base": "BTC",
  "currency": "USD"
}
{
  "amount": "7875.97",
  "base": "BTC",
  "currency": "USD"
}
=-=-=-=-=-=

Any guidance in storing this data locally would be helpful. Please let me know if you need any additional info.

Thank you in advance!

user_5403209 · Answer

You can use PyQ:

>>> from pyq import q
>>> p = {
...   "amount": "8034.79",
...   "base": "BTC",
...   "currency": "USD"
... }
>>> q.set(':x', [p])
k('`:x')
>>> q.upsert(':x', p)
k('`:x')
>>> q.get(':x').show()
amount  base currency
---------------------
8034.79 BTC  USD
8034.79 BTC  USD

user_8694303 · Answer

For a simple API like the Coinbase one, you can actually download the data directly in KDB quite simply, using .Q.hg for downloading and .j.k for parsing the JSON. For example, here is a sample "feedhandler":

/q feed.q [host]:port[:user:pwd]
	 30000

h:hopen `$":",$[count .z.x;.z.x 0;":6000"]; //open handle to TP, use first arg or default to :6000

//define timer function to run every 30 seconds & retrieve prices, send to TP
.z.ts:{
  a:{@[(.j.k .Q.hg`$":https://api.coinbase.com/v2/prices/BTC-USD/",x)`data;`typ;:;x]}'[("buy";"sell";"spot")];
  t:select time:1#.z.N,sym:first `$(base,'currency),
           bid: "F"$first amount where typ like "sell",
           ask: "F"$first amount where typ like "buy" ,
           spot:"F"$first amount where typ like "spot"
  from a;
  h(`.u.upd;`btc;get first t);
 }

This script makes use of the KDB timer to run every 30 seconds (you can adjust the frequency in the first line - 30000 milliseconds currently) & download buy, sell & spot prices from Coinbase API, and send them to a tickerplant (where the port is specified as the first argument when loading the script, or defaults to port 6000). The resultant table looks like the following:

:3002>btc
time                          sym    bid     ask     spot
------------------------------------------------------------
2017.11.20D21:32:48.697085000 BTCUSD 8164.23 8329.17 8246.5
2017.11.20D21:33:20.376192000 BTCUSD 8164.27 8331.69 8246.5
2017.11.20D21:33:50.709364000 BTCUSD 8164.27 8331.69 8247.27
2017.11.20D21:34:21.544488000 BTCUSD 8166.71 8331.69 8249

The schema for tick.q (default name sym.q) should look like this:

btc:([]time:`timespan$(); sym:`g#`symbol$(); bid:`float$(); ask:`float$(); spot:`float$())

One thing to note is that as this API uses HTTPS you will need SSL certificates set up, which you do something like so (from code.kx.com):

$ curl https://curl.haxx.se/ca/cacert.pem > $HOME/certs/cabundle.pem
$ export SSL_CA_CERT_FILE=$HOME/certs/cabundle.pem

I have also made a version of this script that integrates with TorQ, the free KDB framework published by AquaQ Analytics (disclaimer: I work for AquaQ). That version is available here:

https://github.com/jonathonmcmurray/TorQ-Coinbase

This version includes a start script that downloads the necessary SSL certificates and sets SSL_CA_CERT_FILE as described above. This setup allows you to start up a set of processes (tickerplant, rdb, hdb etc.) for processing & storing the data returned from the feed.

Connect API to kdb database

2 Answers