Creating Live QuestDB Crypto Charts

We are going to create a Pulse sql Dashboard to display live data from a QuestDB database.
QuestDB is an open-source "Columnar time-series database with high performance ingestion and SQL analytics".

Creating a new QuestDB Database - init and adding some data.
Adding a QuestDB Connection
Populating Data
Adding our First Table
- Adding a Time-Series Chart
User Interaction with Forms
Inserting more data to see a live chart update

Creating new QuestDB Database

QuestDB is an open-source "Columnar time-series database with high performance ingestion and SQL analytics". It's extremely small, fast and great at time-series queries. Download it then start questdb.exe up to give you this console:

Add QuestDB PostgreSQL Connection

Quest provides a number of APIs, REST / ILP - InfluxDB Line Protocol and PostgreSQL wire protocol.
For querying we are going to connect to the PostgreSQL on port 8812

Download Pulse
any platform is fine, then launch pulse.exe or pulse-log.sh. This will open the pulse UI in a web browser.
Click Connections to go to the connections screen.

To add a connection in Pulse goto Connections->"Add Data Connection" and fill in your details.
You can try clicking test to see if the connection works before adding it.

username: admin
password: quest
database: qdb
port: 8812
host: localhost

Run the test query "SHOW tables;" to check it works:

Populating the Tables

To populate our tables, we have created a java program that uses org.knowm.xchange to connect to a number of exchanges and forward data to QuestDB using ILP (InfluxDB Line Protocol). To run it yourself, download the jar below or see the source in our github repo. The periods "." are the feed waiting. The larger letters T/o are it sending Trades and Orders respectively.

Download cryptofeed.jar

When you run it, you should see output like this:

ryan@dev-server-5
$ java -jar cryptofeed.jar
SLF4J: No SLF4J providers were found.
SLF4J: Defaulting to no-operation (NOP) logger implementation
2023-02-14T14:05:30.134006Z I i.q.c.l.t.PlainTcpLineChannel Send buffer size change from 64512 to 131072
,bitfinex-BTC,.o-SENT
,bitfinex-ETH,.o,bitfinex-XRP,.o,bitfinex-ADA,.o2023-02-14T14:05:32.046677Z I i.q.c.l.t.PlainTcpLineChannel Send buffer size change from 64512 to 131072
,bitfinex-DOG,.o,bitfinex-ETH,.o,bitfinex-ETC,.o,bitfinex-XMR,.o,bitfinex-LIN,.o-SENT
,bitfinex-NEO,.o..........o.....o..........................o.............................o........................................o.............................................,bitstamp-BTC,.o,bitfinex-XDC,.o,bitstamp-ETH,.o.o...o...............o.......,bitstamp-XRP,..o.........T.
,bitstamp-ADA,.o.o-SENT
.o......o.,bitstamp-DOG,.o...o......o...........,bitstamp-ETH,.o....................,bitstamp-LIN,.o.o.o...,bitstamp-BCH,.oT.o....o....o,bitstamp-LTC,.o,bitfinex-VET,.o
.............o...................................o.........................,bitstamp-XLM,.o.............o....o................................................o....,bitstamp-EUR,.o....o....,bitstamp-GBP,.o..o........................................o.........................TT.............................................................................,bitstamp-BTC,.o-SENT
.-SENT
.........o....................................................o.......................................................................................................-SENT
.................................................TTTT.o..................................................................o............................................................................................-SENT
.................................................................................-SENT
........................................................................................................................................T.
TTTTTTTTTTT......o.o.o............................................o...............................................................................o.o.........o..................................................................o...........................................................o..............................................o.....-SENT
......................................o.o......................................................o.o...................................................o.................................................................o..............................o.....................................o..............................-SENT
.....................................................................................................................,bitstamp-ETH,.o-SENT
..........o..................o.........o.........o.................o-SENT
........................................................................o.o...........................................o..............................o........................................................o................................o................................................o.................................o........................o........................-SENT
......................................................................................................................................................-SENT
.....o......................................................o.o..........................

For those curious, the important loop of the java code is taking the incoming data and sending it as shown below. Notice we are flushing on a timer for efficiency purposes:

// CREATE TABLE IF NOT EXISTS trade(sym SYMBOL, price DOUBLE, amount DOUBLE, id LONG, etime TIMESTAMP, ts TIMESTAMPT) timestamp(ts);
private void sendTrade(String exchange, Trade trade) {
//		log.log(Level.INFO, "Trade: " + trade);
	System.out.print("T");
	// First get the data, in case gets break, we don't want to call sender and it break midway.
	String sym = trade.getInstrument().toString();
	String type = trade.getType().toString();
	double price = trade.getPrice().doubleValue();
	double amount = trade.getOriginalAmount().doubleValue();
	long id = -1;
	try {
		id = Long.parseLong(trade.getId());
	} catch(NumberFormatException pe) { }
	long etime = trade.getTimestamp().getTime();
	int v = r.nextInt(15);
	String account = v < 3 ? "ts"+v : ""; 
	
	synchronized(sender) {
		try {
			sender.table("trade")
				.symbol("sym", sym)
				.symbol("ex", exchange)
				.symbol("account", account)
				.symbol("type", type)
				.doubleColumn("price", price)
				.doubleColumn("amount", amount)
				.longColumn("id", id)
				.timestampColumn("etime", etime);
		} catch(Exception e) {
			log.log(Level.SEVERE, "Order Exception: " + e.getLocalizedMessage());
		} finally {
			sender.atNow();	
		}
		long now = System.currentTimeMillis();
		if((now - lastFlush) > TIMEWINDOW/2) {
			sender.flush();
			lastFlush = now;
			System.out.println(".");
		}
	}
}

Once data has been ticking a few seconds, go back to the editor and try this query: SELECT * FROM order LIMIT -10;

Adding our First Table

On the dashboards listing page, click add
Click on the newly created dashboard to go into it. Either click on edit or toggle design mode once in the dashboard.
On the component bar, click "Table" to add a grid.
Within the Editor
- Select the QuestDB server
- Enter the query:
```
select * from trade LIMIT -200;
```

Adding a Chart

The next SQL chart we want is a time-series. We use UNION to join the quotes + my trades + others trades. So that the bid/ask quotes are shown as lines, while the trades have triangle/circle shapes relative to the size of the trade:

SELECT * FROM (
(SELECT timestamp,bid,ask,
  NULL as mytrade_SD_CIRCLE,NULL as mytrade_SD_SIZE,
  NULL as trade_SD_TRIANGLE, NULL as trade_SD_SIZE
  FROM order WHERE sym LIKE 'BTC/USD' AND timestamp > timestamp_floor('m',dateadd('m', -1, now())))
UNION
(select timestamp,
  NULL as bid, NULL as ask,
  price as mytrade_SD_CIRCLE,4+11*log(2+amount) as mytrade_SD_SIZE,
  NULL as trade_SD_TRIANGLE, NULL as trade_SD_SIZE
  from trade WHERE sym LIKE 'BTC/USD' AND timestamp > timestamp_floor('m',dateadd('m', -1, now())) AND account like 'ts%')
UNION 
(select timestamp,
  NULL as bid, NULL as ask,
  NULL as mytrade_SD_CIRCLE,NULL as mytrade_SD_SIZE,
  price as trade_SD_TRIANGLE, 4+11*log(2+amount) as trade_SD_SIZE
  from trade WHERE sym LIKE 'BTC/USD' AND timestamp > timestamp_floor('m',dateadd('m', -1, now())) AND account NOT like 'ts%')
) ORDER BY timestamp ASC;

User Interaction with Forms

Forms allow user interaction by placing user input within the SQL queries. To create one:

Click "User Form" on the component bar to add that component.
Within the Editor, click "Add Component" to add a dropdown component.
Within the Component Editor, select List as the data source
Specify the list:
```
BTC/USD
ETH/USD
```
Click the dropdown button to test it.

Using Form Values in Charts

To use form values in charts, we use the key name surrounded by parentheses e.g. ((key1)).
The SQL code then changes from/to:

SELECT timestamp,bid,ask, FROM order WHERE sym LIKE 'BTC/USD' AND timestamp > timestamp_floor('m',dateadd('m', -1, now()))
SELECT timestamp,bid,ask, FROM order WHERE sym LIKE ((key1)) AND timestamp > timestamp_floor('m',dateadd('m', -1, now()));

Similarly update the other chart queries

Latest Prices asof 1/5/10 minutes:

So far QuestDB has been fast and easy to use but we haven't really taken advantage of it's features.
One of the coolest things in QuestDB is Latest On queries. If for example we wanted to show the:

Latest Price
Price 1 minute previously
Price 5 minutes previously

we can do this using "Latest On"

On the component bar, click "Table" to add a grid.
Within the Editor
- Select the QuestDB server
- Enter the query: SELECT time,CONCAT('<div class="cr-',LOWER(SUBSTRING(sym,1,3)),'"/></div>') AS img, sym,bid as price,min1 as min1_SD_PERCENT1,min5 as min5_SD_PERCENT1,min15 as min15_SD_PERCENT1, CASE WHEN min1 >= 0.001 THEN 'green' WHEN min1 <= -0.001 THEN 'red' ELSE '' END AS min1_SD_FG, CASE WHEN min5 >= 0.001 THEN 'green' WHEN min5 <= -0.001 THEN 'red' ELSE '' END AS min5_SD_FG, CASE WHEN min15 >= 0.001 THEN 'green' WHEN min15 <= -0.001 THEN 'red' ELSE '' END AS min15_SD_FG FROM (SELECT time,sym,bid,(bid-bid1)/bid as min1,(bid-bid5)/bid as min5,(bid-bid15)/bid as min15 FROM (SELECT to_str(timestamp,'HH:mm:ss.SSS') as time,sym,bid,ask,b.bid1,e.bid5,c.bid15 FROM order a LEFT JOIN (SELECT bid as bid1,sym as sym1 FROM order WHERE timestamp < (now() - 1*60000000L) LATEST ON timestamp PARTITION BY sym ORDER BY sym) b ON a.sym = b.sym1 LEFT JOIN (SELECT bid as bid5,sym as sym5 FROM order WHERE timestamp < (now() - 5*60000000L) LATEST ON timestamp PARTITION BY sym ORDER BY sym) e ON a.sym = e.sym5 LEFT JOIN (SELECT bid as bid15,sym as sym15 FROM order WHERE timestamp < (now() - 15*60000000L) LATEST ON timestamp PARTITION BY sym ORDER BY sym) c ON a.sym = c.sym15 LATEST ON timestamp PARTITION BY sym ORDER BY sym) d)

Well done, we've created our Crypto Dashboard.

I found QuestDB great to work with. It was small, simple very fast and just worked. In particular I loved the auto-creation of tables and the time-series based join. I did hit a few niggles and QuestDB has room to improve but on their slack channel, the QuestDB team quickly answered my queries and the development seems to be moving really fast.
In fact we added it to our top list of time-series databases.
Definitely a database to keep watching for future.

Thanks for watching our demo. Please download Pulse to give it a try for yourself.