6.1. Managing tables with Tags#
At QuasarDB, we encourage the use of many different tables: it improves write and read speed, and in most cases also improves your compression ratio. It is not uncommon for users to use tens of thousands of tables (or in rare occasions even millions).
To make the management and querying of these tables work at scale, QuasarDB provides a tags abstraction on top the timeseries tables. This document outlines strategies for defining your tables using tags, and shows you how to use them in practice.
6.1.1. Defining your tables#
Before you can start tagging your tables, you need to figure out an appropriate way to split your data model into multiple tables, if possible. To do this, you need to determine the most appropriate partition identifier for your dataset: the best choice depends upon your dataset, but in most circumstances it is fairly straightforward to find out.
A good rule of thumb is to use the single key you most frequently query or group by, and use that as identifier for the tables.
Examples of schemes that work well are customer_id, stock_id or sensors_id. If you need help figuring out the best partitioning scheme, consult your solution architect.
6.1.2. Defining your tags#
It is a common misconception that tags operate on the level of rows, similar to how a secondary index would work: this is not the case. Tags operate solely on the level of tables, and are in place to make querying easier.
As such, your tagging strategy depends upon your query patterns: when you find yourself wanting to join certain groups of tables frequently, the chance is likely that these tables should share a common tag.
Example: tagging stock tables by exchange:
CREATE TABLE stocks.apple (open DOUBLE, close DOUBLE, high DOUBLE, low DOUBLE, volume INT64)
CREATE TABLE stocks.facebook (open DOUBLE, close DOUBLE, high DOUBLE, low DOUBLE, volume INT64)
CREATE TABLE stocks.google (open DOUBLE, close DOUBLE, high DOUBLE, low DOUBLE, volume INT64)
ATTACH_TAG stocks.apple nasdaq
ATTACH_TAG stocks.facebook nasdaq
ATTACH_TAG stocks.google nasdaq
You can attach multiple tags to the same table, so you can query it in multiple dimensions.
6.1.3. Querying using tags#
Since timeseries are stored as plain key/value objects under the hood, we are able to query them using the FIND instruction as well. Assuming you followed the stock exchange example from the previous paragraph, we can query these tables as follows:
FIND(tag='nasdaq')
1. stocks.apple
2. stocks.facebook
3. stocks.google
Because QuasarDB allows for expansion of the table selection using the FIND operator, we can take this one step further and query the contents of these tables:
SELECT * FROM FIND(tag='nasdaq')
timestamp timeseries open close high low volume
-----------------------------------------------------------------------------------------------------------------------------
2018-01-01T00:00:00.000000000Z stocks.apple 10.240000 10.170000 10.360000 10.070000 1027389
2018-01-01T00:00:00.000000000Z stocks.facebook 902.750000 909.510000 925.000000 890.120000 8437
2018-01-01T00:00:00.000000000Z stocks.google 25.210000 24.910000 27.010000 25.190000 32497