2.2. Quasar OLAP Interface#
2.2.1. Introduction#
Attention
The Quasar OLAP interface requires QuasarDB 3.14.2 or later and is in beta.
Run analytical workloads and complex analytical SQL queries on QuasarDB with the Quasar OLAP interface. The interface effectively transforms your Quasar cluster into a fully-featured data warehouse with low-latency data updates.
The Quasar cluster handles pruning, filtering, parallelism, and scaling (compute/storage pushdown), while the SQL front end leverages DuckDB. In other words, you keep DuckDB’s ergonomics and features with Quasar’s virtually unconstrained storage and stronger multithreading/scalability.
The OLAP interface ships as a REST server (SQL over HTTP and JSON/Arrow) and a standalone shell (interactive and batch) with identical semantics.
For a reference to the SQL supported by DuckDB check out their SQL reference.
Note
Our examples below are based on the Quasar OLAP shell, the REST server supports the exact same queries, but configuration is set for the REST instance.
2.2.2. Features#
Feature-rich SQL queries (see compatibility notes)
Immediately visible insertions and mutations without compromise
Horizontal scaling: unlimited Quasar OLAP instances can connect to the same QuasarDB cluster
Very high-performance analytical queries
Full support for Quasar security and authentication
Best of both worlds: support for composition of Quasar queries and regular SQL queries
Enhanced multithreading and parallelism (compared to a regular DuckDB instance)
2.2.3. Implementation notes#
Our OLAP interface is a DuckDB extension that:
Uses low-level Quasar APIs to push down filtering and partitioning to the Quasar cluster efficiently
Leverages Quasar internals to multithread queries further than what DuckDB would naturally do
Very efficiently transforms results, leveraging the similar column-oriented nature of Quasar and DuckDB
Heavily optimizes Quasar connectivity to avoid connection latency
Properly registers and exposes all authentication and security mechanisms
Feeds performance information for useful ANALYZE performance traces
2.2.4. Attaching to a Quasar cluster#
Quasar fully integrates with the secret manager, and although you don’t have to, we recommend you to use it:
CREATE OR REPLACE SECRET qdb_secret
(TYPE quasar,
HOST '127.0.0.1', -- replace with the host
PORT '2836', -- usualy this is the port
USER_NAME 'anon', -- only if auth/security is on
USER_PRIVATE_KEY 'the user key', -- only if auth/security is on
CLUSTER_PUBLIC_KEY 'the cluster key'); -- only if auth/security is on
To attach to a remote Quasar cluster:
ATTACH 'secret=qdb_secret' AS qdb (type quasar);
Note that if you don’t want to use a secret you can also do the following:
ATTACH 'host=127.0.0.1 port=2836' AS qdb (type quasar);
Once you’re connected you should be able to list your database connection:
> SHOW databases;
┌───────────────┐
│ database_name │
│ varchar │
├───────────────┤
│ memory │
│ qdb │
└───────────────┘
Once you successfully attached you can use the database:
> USE 'qdb';
> SHOW TABLES;
┌─────────┐
│ name │
│ varchar │
├─────────┤
│ t1 │
│ t2 │
└─────────┘
-- you can now run any SQL queries on the Quasar tables
-- you can also mix with tables from other databases
-- the sky's the limit!
2.2.4.1. Options#
- host#
The host to connect to. This option is not necessary if specified in the secret.
- port#
The port to connect to. This option is not necessary if specified in the secret.
- cluster_public_key#
The public key of the cluster to connect to. This option is not necessary if specified in the secret or if authentication is disabled (unsecured cluster).
- user_name#
The username to be used for authentication to the QuasarDB cluster. This option is not necessary if specified in the secret or if authentication is disabled (unsecured cluster).
- user_private_key#
The private key of the user. This option is not necessary if specified in the secret or if authentication is disabled (unsecured cluster).
- max_batch_load (5)#
The size of a unit of work used for multithreading. The default value is 5.
- threads (1)#
The number of threads used by the Quasar backend for the queries. The default value is 1 (single-thread). Note that even in single thread, parallelism occurs thanks to asynchronous I/O.
- timeout#
The network timeout, in milliseconds. Default is 60,000 ms (1 minute).
2.2.5. Accessing Quasar reserved tables and columns#
$ is reserved, thus if you try to run this:
SELECT $timestamp, a FROM t;
You’re going to get the following error:
Prepared statement parameters cannot be used directly
To use prepared statement parameters, use PREPARE to prepare a statement, followed by EXECUTE
The solutions is to double quote the column:
SELECT "$timestamp", a FROM t;
2.2.6. Additional functions#
In addition to full support for the DuckDB SQL engine, the following functions are provided
2.2.6.1. qdb_query_find(VARCHAR, VARCHAR)#
Runs a FIND query and returns the results as a table.
The first parameter is the database instance, the second parameter is the search string to run on the specified database instance.
It is recommended to escape the search string with $$ as it will very likely have quotes.
Example:
> CALL qdb_query_find('qdb', $$find(tag='t1')$$);
┌─────────┐
│ entries │
│ varchar │
├─────────┤
│ t │
└─────────┘
> SELECT * FROM qdb_query_find('qdb', $$find(tag='t1')$$);
┌─────────┐
│ entries │
│ varchar │
├─────────┤
│ t │
└─────────┘
2.2.6.2. qdb_query(VARCHAR, VARCHAR)#
Runs a QuasarDB query and returns the results as a table. This allows you to compose Quasar queries with regular queries. Certain native Quasar queries much faster, such as ASOF JOIN or interpolations.
The first parameter is the database instance, the second parameter is the query string to run on the specified database instance.
Example:
> CALL qdb_query('qdb', 'select * from apple in range(2001, +2d) LIMIT 10');
┌─────────────────────┬─────────┬────────┬────────┬────────┬────────┬────────┐
│ $timestamp │ $table │ open │ high │ low │ close │ volume │
│ timestamp_ns │ varchar │ double │ double │ double │ double │ int64 │
├─────────────────────┼─────────┼────────┼────────┼────────┼────────┼────────┤
│ 2001-01-02 09:30:00 │ apple │ 0.97 │ 0.97 │ 0.97 │ 0.97 │ 989800 │
│ 2001-01-02 09:31:00 │ apple │ 0.97 │ 0.98 │ 0.97 │ 0.97 │ 205800 │
│ 2001-01-02 09:32:00 │ apple │ 0.98 │ 0.98 │ 0.97 │ 0.98 │ 644000 │
│ 2001-01-02 09:33:00 │ apple │ 0.98 │ 0.98 │ 0.97 │ 0.98 │ 404600 │
│ 2001-01-02 09:34:00 │ apple │ 0.97 │ 0.98 │ 0.97 │ 0.97 │ 771400 │
│ 2001-01-02 09:35:00 │ apple │ 0.97 │ 0.97 │ 0.97 │ 0.97 │ 252000 │
│ 2001-01-02 09:36:00 │ apple │ 0.97 │ 0.97 │ 0.97 │ 0.97 │ 266000 │
│ 2001-01-02 09:37:00 │ apple │ 0.97 │ 0.98 │ 0.97 │ 0.97 │ 218400 │
│ 2001-01-02 09:38:00 │ apple │ 0.98 │ 0.98 │ 0.97 │ 0.98 │ 427000 │
│ 2001-01-02 09:39:00 │ apple │ 0.98 │ 0.98 │ 0.97 │ 0.97 │ 362600 │
├─────────────────────┴─────────┴────────┴────────┴────────┴────────┴────────┤
│ 10 rows 7 columns │
└────────────────────────────────────────────────────────────────────────────┘
> SELECT (open + close) / 2 AS avg FROM qdb_query('qdb', 'select * from apple in range(2001, +2d) LIMIT 10');
┌────────┐
│ avg │
│ double │
├────────┤
│ 0.97 │
│ 0.97 │
│ 0.98 │
│ 0.98 │
│ 0.97 │
│ 0.97 │
│ 0.97 │
│ 0.97 │
│ 0.98 │
│ 0.975 │
└────────┘