DEV Community: Brian Thomas Smith

Monaco Editor, Prometheus & Alertmanager support in QuestDB

Brian Thomas Smith — Wed, 05 Jan 2022 15:25:14 +0000

QuestDB version 6.1.3 includes Prometheus Alertmanager support, new counters in the Prometheus endpoint for memory info, automatic query timeout, Monaco as the new SQL editor for QuestDB's web interface, and more UI additions. Here's a roundup of changes!

Monaco Editor from VS Code in QuestDB UI

The SQL editor in QuestDB's web console now includes the
Monaco Editor that powers VS Code. Upgrading the SQL editor to use the Monaco Editor brings with it many improvements and functionality that comes by default with VS Code, so now you get convenient features like bracket matching, find and replace-all, multiple cursor selection, and more right out of the box:

For more information on using the Monaco Editor in QuestDB, type F1 in QuestDB's web console, or refer to the official Monaco documentation.

Prometheus metrics

Prometheus is an open-source systems monitoring and alerting toolkit which collects and stores metrics as time series data. Prometheus collects small pieces of data about many components to help build a picture of the state and trajectory of a system. The scraped metrics are stored, and rules can be applied to aggregate and generate new metrics from existing data or generate alerts based on user-defined triggers.

QuestDB has a /metrics HTTP endpoint on port 9003 which provide counters in Prometheus format. Prometheus can be used to visualize and graph QuestDB metrics prefixed with questdb_:

For more information on configuring QuestDB and Prometheus to graph QuestDB metrics, see the Prometheus documentation for examples and hints for setup and configuration.

Prometheus Alertmanager

Release 6.1.3 introduces a new log writer for QuestDB that sends any message to Prometheus Alertmanager. To configure this writer, add it to the writers config alongside other log writers. Details on logging configuration can be found on the server configuration documentation.

Configuring that QuestDB should send alerts to Alertmanager alerting is done in QuestDB's log config with the address and port for Alertmanager:

# The writers to enable
writers=stdout,alert

# Prometheus Alertmanager
w.alert.class=io.questdb.log.LogAlertSocketWriter
w.alert.level=CRITICAL
w.alert.alertTargets=172.17.0.2:9093

For details on configuring QuestDB to send alerts to Alertmanager, see the Prometheus documentation for examples and guides for setup and configuration.

SQL syntax for bulk inserts

It's now possible to bulk insert vales into a table via SQL. This functionality follows the 'multirow' VALUES syntax used in PostgreSQL and acts as an accelerator when inserting data in bulk:

CREATE TABLE my_table(id SYMBOL index, val DOUBLE,ts TIMESTAMP)
  timestamp(ts);

INSERT INTO my_table
VALUES
    ('d1', 101.1, '2021-10-05T11:31:35.878Z'),
    ('d1', 101.2, '2021-10-05T12:31:35.878Z'),
    ('d2', 201.2, '2021-10-05T12:31:35.878Z'),
    ('d2', 201.3, '2021-10-05T13:31:35.878Z'),
    ('d2', 201.4, '2021-10-05T14:31:35.878Z');

Automatic SQL query timeout

Users can now define automatic timeouts for SQL queries via server configuration. This is set using the query.timeout.sec server configuration and is a global timeout in seconds used for long-running queries. For more
information on setting this parameter, see the server configuration documentation.

# Default is 60 sec
query.timeout.sec=10

Next up

The team have added Java 17 support and are working on a JIT (Just-in-time) compiler for filters which will massively improve performance of queries using WHERE clauses.

We hope you enjoyed the latest roundup, for the complete list of additions and fixes, see the release notes on GitHub.

Feel free to reach out to let us know how we're doing or just come by and say hello in our Slack Community.

QuestDB raises $12M Series A as the adoption of its time series database grows

Brian Thomas Smith — Thu, 04 Nov 2021 09:53:05 +0000

To continue development on QuestDB, the open source database for time series, we have raised a $12 million Series A investment led by 468 Capital, with participation from Uncorrelated Ventures and more than 15 enterprise open source founders and executives to grow the team, increase product development and accelerate adoption. In tandem with the funding round, Florian Leibert, co-founder of Mesosphere, joined QuestDB’s board of directors.

Funded by leaders in open source

The backing comes from innovators such as Tom Preston-Werner, GitHub co-founder, Sebastien Pahl, Docker co-founder, Alexis Ohanian, Reddit co-founder, Mirko Novakovic, Instana co-founder, Andrey Alekseev, NGINX co-founder and Tobi Knaup, D2iQ CEO, as well as Michael Li, VP Data at Coinbase and Grace Francisco, VP Developer Relations Strategy at Cisco.

The round of funding was also made possible with participation from Sumedh Pathak, Citus Data co-founder, James Hawkins, PostHog CEO, Paul Copplestone, Supabase CEO, and Dan Pinto, FingerprintJS CEO. QuestDB is now supported by investors who bring years of collective experience in open source and enterprise software domains and have invested personally in the expansion and success of the company at a phase of rapid growth.

The infusion of funds will support continued growth and drive product innovation, building up support and success functions to meet expanding user demand. These resources will enable us to continue powering high-performance applications that rely on robust, easy-to-use analytics. This round also allows the engineering team behind QuestDB to develop new features for a cloud-native database and launch a DBaaS platform that offers fully managed time series database solutions.

The fastest open source time series database

Developers rely on QuestDB for time series insights and real-time applications in FinTech, DevOps monitoring, asset tracking, Big Data, machine learning engines, digital factories, geospatial analysis, autonomous vehicles, and a growing list of use cases that depend on high-performance ingestion and queries over massive data sets in real-time. Collecting data quickly and efficiently allows for the storage of vast amounts of information without wasting compute resources; making sense of the collected data is a different challenge.

QuestDB allows developers to use SQL as a query language to answer questions about how their data looks without the barrier of entry that comes with proprietary query languages. Analyzing historical aspects of data allows users to understand changes over time, identify patterns, and make better decisions on future trends. The engineering team behind QuestDB has built a system that's easier to use and more powerful than open source alternatives by providing support for SQL, faster queries through massively parallelized execution via SIMD, and ingestion speeds that reach millions of rows per second.

Driven by product-led growth

The QuestDB team is dedicated to improving product functionality and enhancements, and regularly delivers features and stability improvements. In the last six months alone, the engineering team has delivered geospatial data support, out-of-order data ingestion, new SQL extensions and accelerators for working with time series data and time zones, along with AWS Marketplace offerings and DigitalOcean applications for flexible deployments. These features empower users to work with complex data sets, including spatial data and allow developers to quickly and easily build time series applications where their infrastructure operates.

Since QuestDB launched at Y Combinator one year ago, the company has been embraced by developers looking for high-ingestion and query speeds, and an open source alternative to proprietary solutions. Engineers continue to support QuestDB, which now consists of a community of more than 10,000 developers and is reflected in the DB-Engines ranking, which shows a 187% increase in popularity over the last 12 months. The adoption of QuestDB continues to expand with unique deployed instances growing 20% per month, and powering the infrastructure of industry leaders such as Airbus and Yahoo. The success of this funding round increases the company's valuation 10 times versus seed a year ago and will invigorate product and community development in preparation for upcoming launches.

What users say about QuestDB

"QuestDB is used at Airbus for real-time applications involving hundreds of millions of data points per day. For us, QuestDB is an outstanding solution that meets (and exceeds) our performance requirements." - Oliver Pfeiffer, Software Architect, Airbus
"We use QuestDB to monitor metrics for autoscaling decisions within our ML engine that provides search, recommendation, and personalization via models and aggregations on continuously changing data." - Jon Bratseth, VP Architect, Yahoo
"When we set out to design the next generation of our execution platform, one of our requirements was large-scale model calibration based on real-time market data streams. QuestDB allows us to derive quick insights on live and historical data that would not be achievable with other open source time series databases." - Jean-François Perreton, Head of Algo Quant, Kepler Cheuvreux

About QuestDB

QuestDB is an open source high-performance time series database built from the ground up to offer breakthrough performance for real-time analytics using SQL. The team behind QuestDB brings decades of experience and technical approaches from low-latency applications to leverage real-time data processing. QuestDB is a remote-first company and is backed by leading venture capital firms and Y Combinator. For more information, visit questdb.io, QuestDB on GitHub, or follow @QuestDB.

A low-code bitcoin ticker built with QuestDB and n8n.io

Brian Thomas Smith — Thu, 21 Jan 2021 14:01:08 +0000

We've had many predictions of the emerging trends of 2020. Three that ended up ringing very true were the popularity of low-code platforms, the rise of time-series databases, and a digital currency boom. This tutorial combines these three topics into one example workflow automation that stores and analyzes Bitcoin market prices in QuestDB with a workflow template to get up and running quickly.

Prerequisites

This tutorial will use the docker images for both QuestDB and n8n.io so users should ensure that they have the following installed and running on their system:

Docker Desktop

Confirm that this is correctly set up by requesting the version number:

docker --version
Docker version 20.10.2, build 2291f61

What is a low-code platform

low-code platforms allow for building applications without having to dig deep into code or technical implementation details. Most of the tools in this category use a visual editor and have drag-and-drop features to allow for quickly building systems that would otherwise require more intensive development resources. The main benefits are for users who either lack professional programming experience or don't want to invest time building applications from the ground up and manage rapidly-changing compatibility issues.

A great platform that I've been using recently is n8n.io, which offers a creative and efficient visual editor for process automation. Over 200 integrations supported out-of-the-box means you have a lot to choose from if you want to start streaming data into QuestDB from the supported nodes quickly.

This tutorial demonstrates how to use an n8n.io workflow that queries Bitcoin market prices via a REST API and uses QuestDB as a data store for the market prices as time series data.

Setup steps

The first step will be to get n8n.io up and running using docker:

docker run -it --rm \
  --name n8n \
  -p 5678:5678 \
  -v ~/.n8n:/home/node/.n8n \
  n8nio/n8n

This command mounts a volume with -v so that user configurations will be saved at ~/.n8n. QuestDB can be started with the following docker command:

docker run -p 9000:9000 -p 8812:8812 --name questdb-n8n questdb/questdb

By giving the container a name with --name questdb, we have an easy way to refer to the container created by run later on. If we want to re-use the same container (and its data!) after it has been stopped, we can use the following commands:

# bring the container up
docker start questdb-n8n
# shut the container down
docker stop questdb-n8n

Import an example workflow

To get started with our example workflow, navigate to http://localhost:5678/. In the left menu, click the greater-than icon > to expand the menu and reveal a navigation panel. To help us along with the setup steps, we have a template that can be imported directly from a URL.

Select Workflows -> import from URL and paste the following URL:

https://questdb-docs.s3-eu-west-1.amazonaws.com/QuestDB_BTC_Ticker.json

If imported successfully, the workflow will be generated automatically in the editor window:

Each of the nodes with their configuration can be inspected at any time by double-clicking and inspecting the Parameters and Settings panel. From left-to-right we have the following objects:

QuestDB node which executes an SQL statement to create our btc table
cron node which is set to a 1 minute interval
HTTP Request node which makes a GET request to a public API with latest BTC value in USD
Set node which selects the fields we are interested in from the HTTP response and assigns them the correct type
QuestDB node which inserts incoming values from the Set node into a btc table

Connecting to QuestDB

Before we activate the workflow, we have to provide connection credentials to QuestDB. In the left navigation menu, select Credentials -> New and choose type QuestDB.

The default configuration provides most of the information we need for a QuestDB instances running locally, but we will need to allow the QuestDB node access to these credentials in the Nodes with access section.

When correctly set up, the credentials configuration should look like the following:

Activating the workflow

To check if the workflow is correctly configured, click the Execute Node button on the first QuestDB node. On success, we should have a new table btc with a Timestamp and price column. Activating the workflow with the toggle in the top-right of the workflow editor will prompt you to save the workflow if you have not done so already. Give the workflow the name QuestDB BTC Ticker and click Save. If everything is set up correctly, we will have a success indicator on each of the nodes which are passing as expected and a notification window will display the status of the workflow:

We can confirm that we are writing data to QuestDB by navigating to http://localhost:9000/. The btc table is visible in the top-left Tables panel and we can query incoming requests by simply adding btc to the query editor and selecting Run. The data will be returned as a table by default, but can easily be graphed by selecting the
Chart button and specifying price as the series to visualize:

We can create some aggregate queries if we require specific data points or charts. The following SQL query allows for 5 minute aggregates of our incoming BTC pricing data:

SELECT count() as entries, Timestamp, avg(price)
FROM (btc timestamp(Timestamp))
SAMPLE BY 5m;

Next steps

In this article, we've covered how to get started with n8n.io, a low-code workflow automation platform to query a public REST API, stream data into QuestDB, and build some simple data visualizations with QuestDB's built-in
charting functionality.

Our example uses minimal information we need from the REST API, so future changes could be to have more fields or currencies being inserted into QuestDB to make more interesting comparisons across multiple tables. We are also using the cron node with an interval of 1 minute, so improvements would be to use a node that can provide much higher rates of ingestion to leverage the performance benefits of QuestDB.

For inspiration and troubleshooting workflow automations using n8n, take a look at the n8n community forum. If you like this content, we'd love to know your thoughts! Feel free to share your feedback or just come and say hello in the QuestDB Community Slack.