In earlier blog posts we described what time series data is and key characteristics of a time series database. In this blog post, which originally appeared in The New Stack, Eric Hanson, principal product manager at SingleStore, shows you how to use SingleStore for time series applications.
At SingleStore we’ve seen strong interest in using our database for time series data. This is especially the case when an organization needs to accommodate the following: (1) a high rate of event ingestion, (2) low-latency queries, and (3) a high rate of concurrent queries.
In what follows, I show how SingleStore can be used as a powerful time-series database and illustrate this with simple queries and user-defined functions (UDFs) that show how to do time series-frequency conversion, smoothing, and more. I also cover how to load time series-data points fast, with no scale limits.
Note: This blog post was originally published in March 2019. It has been updated to reflect the new time series functions in SingleStoreDB Self-Managed 7.0. Please see the Addendum at the end of this article for specifics on using the information herein. – Ed.
Manipulating Time Series with SQL
Unlike most time series-specific databases, SingleStore supports standard SQL, including inner and outer joins, subqueries, common table expressions (CTEs), views, rich scalar functions for date and time manipulation, grouping, aggregation, and window functions. We support all the common SQL data types, including a datetime(6) type with microsecond accuracy that’s perfect as a time series timestamp.
A common type of time-series analysis in financial trading systems is to manipulate stock ticks. Here’s a simple example of using standard SQL to do this kind of calculation. We use a table with a time series of ticks for multiple stocks, and produce high, low, open, and close for each stock:
1CREATE TABLE tick(ts datetime(6), symbol varchar(5),2 price numeric(18,4));3INSERT INTO tick VALUES4 ('2019-02-18 10:55:36.179760', 'ABC', 100.00),5 ('2019-02-18 10:57:26.179761', 'ABC', 101.00),6 ('2019-02-18 10:59:16.178763', 'ABC', 102.50),7 ('2019-02-18 11:00:56.179769', 'ABC', 102.00),8 ('2019-02-18 11:01:37.179769', 'ABC', 103.00),9 ('2019-02-18 11:02:46.179769', 'ABC', 103.00),10 ('2019-02-18 11:02:59.179769', 'ABC', 102.60),11 ('2019-02-18 11:02:46.179769', 'XYZ', 103.00),12 ('2019-02-18 11:02:59.179769', 'XYZ', 102.60),13 ('2019-02-18 11:03:59.179769', 'XYZ', 102.50);
This query uses standard SQL window functions to produce high, low, open and close values for each symbol in the table, assuming that “ticks” contains data for the most recent trading day.
1WITH ranked AS2(SELECT symbol,3 RANK() OVER w as r,4 MIN(price) OVER w as min_pr,5 MAX(price) OVER w as max_pr,6 FIRST_VALUE(price) OVER w as first,7 LAST_VALUE(price) OVER w as last8 FROM tick9 WINDOW w AS (PARTITION BY symbol10 ORDER BY ts11 ROWS BETWEEN UNBOUNDED PRECEDING12 AND UNBOUNDED FOLLOWING))13 14SELECT symbol, min_pr, max_pr, first, last15FROM ranked16WHERE r = 1;
Results:
1+--------+----------+----------+----------+----------+2| symbol | min_pr | max_pr | first | last |3+--------+----------+----------+----------+----------+4| XYZ | 102.5000 | 103.0000 | 103.0000 | 102.5000 |5| ABC | 100.0000 | 103.0000 | 100.0000 | 102.6000 |6+--------+----------+----------+----------+----------+
Similar queries can be used to create “candlestick charts,” a popular report style for financial time series that looks like the image below. A candlestick chart shows open, high, low, and close prices for a security over successive time intervals:
For example, this query generates a table that can be directly converted to a candlestick chart over three-minute intervals:
1WITH ranked AS2 (SELECT symbol, ts,3 RANK() OVER w as r,4 MIN(price) OVER w as min_pr,5 MAX(price) OVER w as max_pr,6 FIRST_VALUE(price) OVER w as first,7 LAST_VALUE(price) OVER w as last8 9 FROM tick10 WINDOW w AS (PARTITION BY symbol, time_bucket('3 minute', ts)11 ORDER BY ts12 ROWS BETWEEN UNBOUNDED PRECEDING13 AND UNBOUNDED FOLLOWING))14 15SELECT symbol, time_bucket('3 minute', ts), min_pr, max_pr,16first, last17FROM ranked18WHERE r = 119ORDER BY 1, 2;
Results:
1+--------+-----------------------------+----------+----------+----------+----------+2| symbol | time_bucket('3 minute', ts) | min_pr | max_pr | first | last |3+--------+-----------------------------+----------+----------+----------+----------+4| ABC | 2019-02-18 10:54:00.000000 | 100.0000 | 100.0000 | 100.0000 | 100.0000 |5| ABC | 2019-02-18 10:57:00.000000 | 101.0000 | 102.5000 | 101.0000 | 102.5000 |6| ABC | 2019-02-18 11:00:00.000000 | 102.0000 | 103.0000 | 102.0000 | 102.6000 |7| XYZ | 2019-02-18 11:00:00.000000 | 102.6000 | 103.0000 | 103.0000 | 102.6000 |8| XYZ | 2019-02-18 11:03:00.000000 | 102.5000 | 102.5000 | 102.5000 | 102.5000 |9+--------+-----------------------------+----------+----------+----------+----------+
Smoothing is another common need in managing time series data. This query produces a smoothed sequence of prices for stock “ABC,” averaging the price over the last three ticks:
1SELECT symbol, ts, price,2AVG(price) OVER (ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS smoothed_price3FROM tick4WHERE symbol = 'ABC';
Results:
1+--------+----------------------------+----------+----------------+2| symbol | ts | price | smoothed_price |3+--------+----------------------------+----------+----------------+4| ABC | 2019-02-18 10:55:36.179760 | 100.0000 | 100.00000000 |5| ABC | 2019-02-18 10:57:26.179761 | 101.0000 | 100.50000000 |6| ABC | 2019-02-18 10:59:16.178763 | 102.5000 | 101.16666667 |7| ABC | 2019-02-18 11:00:56.179769 | 102.0000 | 101.37500000 |8| ABC | 2019-02-18 11:01:37.179769 | 103.0000 | 102.12500000 |9| ABC | 2019-02-18 11:02:46.179769 | 103.0000 | 102.62500000 |10| ABC | 2019-02-18 11:02:59.179769 | 102.6000 | 102.65000000 |11+--------+----------------------------+----------+----------------+
Using Extensibility to Increase the Power of SingleStore for Time Series
SingleStore supports extensibility with user-defined functions and stored procedures. SingleStore compiles UDFs and stored procedures to machine code for high performance.
I actually used SingleStore’s extensibility to create the time_bucket() function, shown in the Supplemental Material section below, which appeared in the previous section as a UDF. This function provides equivalent capability to similar functions in time-series-specific products. You can easily create a function or expression to bucket by time intervals, such as second, minute, hour, or day.
A common need with time-series data is to perform interpolation. For example, suppose you have a time series with points at random intervals that are 30 seconds apart on average. There may be some minutes with no data point. So, if you convert the raw (irregular) time-series data to a regular time series with a point a minute, there may be gaps.
If you want to provide output for plotting with no gaps, you need to interpolate the values for the gaps from the values before and after the gaps. It’s straightforward to implement a stored procedure in SingleStore by taking a query result and outputting a row set, with the gaps interpolated, into a temporary table.
This can then be sent back to the client application using the ECHO command. In addition, SingleStore supports user-defined aggregate functions. These functions can be used to implement useful time series operations, such as shorthand for getting the first and last values in a sequence without the need for specific window functions.
Consider this query to get the first value for stock ABC in each three minutes of trading, based on a user-defined aggregate function (UDAF) called FIRST():
1SELECT time_bucket('3 minute', ts), first(price, ts)2FROM tick3WHERE symbol = "ABC"4GROUP BY 15ORDER BY 1;
Results:
1+-----------------------------+------------------+2| time_bucket('3 minute', ts) | first(price, ts) |3+-----------------------------+------------------+4| 2019-02-18 10:54:00.000000 | 100.0000 |5| 2019-02-18 10:57:00.000000 | 101.0000 |6| 2019-02-18 11:00:00.000000 | 102.0000 |7+-----------------------------+------------------+
The implementations of the FIRST() UDAF, and the analogous LAST() UDAF, are shown in the Supplemental Material section below.
Time Series Compression and Life Cycle Management
SingleStore is adept at handling both bursty insert traffic for time series events and historical time series information where space savings are important. For bursty insert traffic, you can use a SingleStore rowstore table to hold time series events.
For larger and longer-lived sets of time series events, or older time series data sets that have aged and are unlikely to be updated anymore, the SingleStore columnstore is a great format. It compresses time-series data very effectively, with SingleStore supporting fast operations on compressed columnstore data. Moreover, columnstore data resides on disk, so main memory size is not a limit on how much data you can store.
Scalable Time Series Ingestion
When building a time series application, data can come at high rates from many sources. Sources include applications, file systems, AWS S3, Hadoop HDFS, Azure Blob stores, and Kafka queues. SingleStore can ingest data incredibly fast from all these sources.
SingleStore Pipelines are purpose-built for fast and easy loading of data streams from these sources, requiring no procedural coding to establish a fast flow of events into SingleStore.
SingleStore can ingest data at phenomenal data rates. In a recent test, I inserted 2,850,500 events per second directly from an application, with full transactional integrity and persistence, using a two-leaf SingleStore cluster. Each leaf ran on an Intel Xeon Platinum 28-core system.
Comparable or even better rates can be had using direct loading or Kafka pipelines. If you have to scale higher, just add more nodes — there’s no practical limit.
When General-Purpose SingleStore Is Right for Time Series
We’ve seen the market for time-series data management bifurcate into special-purpose products for time series, with their own special-purpose languages, and extended SQL systems that can interoperate with standard reporting and business intelligence tools that use SQL. SingleStore is in this second category.
SingleStore is right for time series applications that need rapid ingest, low-latency query, and high concurrency, without scale limits, and which benefit from SQL language features and SQL tool connectivity.
Many time-series-specific products have shortcomings when it comes to data management. Some lack scale-out, capping the size of problems they can tackle, or forcing application developers to build tortuous sharding logic into their code to split data across multiple instances, which costs precious dollars for labor that could better be invested into application business logic.
Other systems have interpreted query processors that can’t keep up with the latest query execution implementations as ours can. Some lack transaction processing integrity features common to SQL databases.
SingleStore lets time series application developers move forward confidently, knowing they won’t hit a scale wall, and they can use all their familiar tools — anything that can connect to a SQL database.
Summary
SingleStore is a strong platform for managing time series data. It supports the ability to load streams of events fast and conveniently, with unlimited scale. It supports full SQL that enables sophisticated querying using all the standard capabilities of SQL 92, plus the more recently added window function extensions.
SingleStore supports transactions, high rates of concurrent update and query, and high availability technologies that many developers need for all kinds of applications, including time series. And your favorite SQL-compatible tools, such as business intelligence (BI) tools, can connect to SingleStore. Users and developers – in areas such as real-time analytics, predictive analytics, machine learning, and AI – can use the SQL interfaces they’re familiar with, as described above. All of this and more makes SingleStore a strong platform for time series.
Download and use SingleStore for free today and try it on your time series data!
Addendum
In SingleStoreDB Self-Managed 7.0, we added TIME_BUCKET(), FIRST(), and LAST() as built-in functions. So, if you run the scripts above to create those functions on SingleStoreDB Self-Managed 7.0, you will get an error. We recommend that you simply use the built-in version of the functions in 7.0. See the documentation here: https://archived.docs.singlestore.com/v7.0/reference/sql-reference/time-series-functions/time-series-functions/
If you’d like to experiment with the user-defined versions, edit the scripts to rename the functions to TIME_BUCKET2(), FIRST2(), and LAST2(), or something similar, before creating them.
Supplemental Material 1/2: Full Text of time_bucket() Function
1-- Usage: time_bucket(interval_string, timestamp_value)2-- Examples: time_bucket('1 day', ts), time_bucket('5 seconds', ts)3 4DELIMITER //5CREATE OR REPLACE FUNCTION time_bucket(6 bucket_desc varchar(64) NOT NULL,7 ts datetime(6)) RETURNS datetime(6) NULL AS8DECLARE9 num_periods bigint = -1;10 second_part_offset int = -1;11 unit varchar(255) = NULL;12 num_str varchar(255) = NULL;13 unix_ts bigint;14 r datetime(6);15 days_since_epoch bigint;16BEGIN17 num_str = substring_index(bucket_desc, ' ', 1);18 num_periods = num_str :> bigint;19 unit = substr(bucket_desc, length(num_str) + 2, length(bucket_desc));20 IF unit = 'second' or unit = 'seconds' THEN21 unit = 'second';22 ELSIF unit = 'minute' or unit = 'minutes' THEN23 unit = 'minute';24 ELSIF unit = 'hour' or unit = 'hours' THEN25 unit = 'hour';26 ELSIF unit = 'day' or unit = 'days' THEN27 unit = 'day';28 ELSE29 raise user_exception(concat("Unknown time unit: ", unit));30 END IF;31 32 unix_ts = unix_timestamp(ts);33 34 IF unit = 'second' THEN35 r = from_unixtime(unix_ts - (unix_ts % num_periods));36 ELSIF unit = 'minute' THEN37 r = from_unixtime(unix_ts - (unix_ts % (num_periods * 60)));38 ELSIF unit = 'hour' THEN39 r = from_unixtime(unix_ts - (unix_ts % (num_periods * 60 * 60)));40 ELSIF unit = 'day' THEN41 unix_ts += 4 * 60 * 60; -- adjust to align day boundary42 days_since_epoch = unix_ts / (24 * 60 * 60);43 days_since_epoch = days_since_epoch - (days_since_epoch % num_periods);44 r = (from_unixtime(days_since_epoch * (24 * 60 * 60))) :> date;45 ELSE46 raise user_exception("Internal error -- bad time unit");47 END IF;48 49 RETURN r;50END;51//52DELIMITER ;
Supplemental Material 2/2: Full Text of first() and last() Aggregate Functions
The following UDAF returns the first value in a sequence, ordered by the second argument, a timestamp:
1-- Usage: first(value, timestamp_expr)2-- Example:3-- Get first value of x for each day from a time series in table4-- t(x, ts)5-- with timestamp ts.6--7-- SELECT ts :> date, first(x, ts) FROM t GROUP BY 1 ORDER BY 1;8 9DELIMITER //10CREATE OR REPLACE FUNCTION first_init() RETURNS RECORD(v TEXT, d datetime(6)) AS11 BEGIN12 RETURN ROW("_empty_set_", '9999-12-31 23:59:59.999999');13 END //14DELIMITER ;15 16DELIMITER //17CREATE OR REPLACE FUNCTION first_iter(state RECORD(v TEXT, d DATETIME(6)),18 v TEXT, d DATETIME(6))19 RETURNS RECORD(v TEXT, d DATETIME(6)) AS20 DECLARE21 nv TEXT;22 nd DATETIME(6);23 nr RECORD(v TEXT, d DATETIME(6));24 BEGIN25 -- if new timestamp is less than lowest before, update state26 IF state.d > d THEN27 nr.v = v;28 nr.d = d;29 RETURN nr;30 END IF;31 RETURN state;32 END //33DELIMITER ;34 35DELIMITER //36CREATE OR REPLACE FUNCTION first_merge(state1 RECORD(v TEXT, d DATETIME(6)),37 state2 RECORD(v TEXT, d DATETIME(6))) RETURNS RECORD(v TEXT, d DATETIME(6)) AS38 BEGIN39 IF state1.d < state2.d THEN40 RETURN state1;41 END IF;42 RETURN state2;43 END //44DELIMITER ;45 46DELIMITER //47CREATE OR REPLACE FUNCTION first_terminate(state RECORD(v TEXT, d DATETIME(6))) RETURNS TEXT AS48 BEGIN49 RETURN state.v;50 END //51DELIMITER ;52 53CREATE AGGREGATE first(TEXT, DATETIME(6)) RETURNS TEXT54 WITH STATE RECORD(v TEXT, d DATETIME(6))55 INITIALIZE WITH first_init56 ITERATE WITH first_iter57 MERGE WITH first_merge58 TERMINATE WITH first_terminate;
A LAST() UDAF that is analogous to FIRST(), but returns the final value in a sequence ordered by timestamp, is as follows:
1-- Usage: last(value, timestamp_expr)2-- Example:3-- Get last value of x for each day from a time series in table t4-- t(x, ts)5-- with timestamp column ts.6--7-- SELECT ts :> date, last(x, ts) FROM t GROUP BY 1 ORDER BY 1;8 9DELIMITER //10CREATE OR REPLACE FUNCTION last_init() RETURNS RECORD(v TEXT, d datetime(6)) AS11 BEGIN12 RETURN ROW("_empty_set_", '1000-01-01 00:00:00.000000');13 END //14DELIMITER ;15 16DELIMITER //17CREATE OR REPLACE FUNCTION last_iter(state RECORD(v TEXT, d DATETIME(6)),18 v TEXT, d DATETIME(6))19 RETURNS RECORD(v TEXT, d DATETIME(6)) AS20 DECLARE21 nv TEXT;22 nd DATETIME(6);23 nr RECORD(v TEXT, d DATETIME(6));24 BEGIN25 -- if new timestamp is greater than largest before, update state26 IF state.d < d THEN nr.v = v; nr.d = d; RETURN nr; END IF; RETURN state; END // DELIMITER ; DELIMITER // CREATE OR REPLACE FUNCTION last_merge(state1 RECORD(v TEXT, d DATETIME(6)), state2 RECORD(v TEXT, d DATETIME(6))) RETURNS RECORD(v TEXT, d DATETIME(6)) AS BEGIN IF state1.d > state2.d THEN27 RETURN state1;28 END IF;29 RETURN state2;30 END //31DELIMITER ;32 33DELIMITER //34CREATE OR REPLACE FUNCTION last_terminate(state RECORD(v TEXT, d DATETIME(6))) RETURNS TEXT AS35 BEGIN36 RETURN state.v;37 END //38DELIMITER ;39 40CREATE AGGREGATE last(TEXT, DATETIME(6)) RETURNS TEXT41 WITH STATE RECORD(v TEXT, d DATETIME(6))42 INITIALIZE WITH last_init43 ITERATE WITH last_iter44 MERGE WITH last_merge45 TERMINATE WITH last_terminate;
