Data / Sponsored

What MemSQL Can Do for Time-Series Applications

12 Mar 2019 11:14am, by

MemSQL sponsored this post.

At MemSQL we’ve seen strong interest in using our database for time series. This is especially the case when an organization experiences 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’ll show how MemSQL can be used as a powerful time-series database and illustrate this with simple queries and user-defined functions 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.

Manipulating Time Series with SQL

Unlike most time series-specific databases, MemSQL 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:

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.

Similar queries can be used to create “candlestick charts,” a popular report style for financial time series that looks like the image below to show 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:

Results:

Smoothing is another common need in managing-time series. This query produces a smoothed sequence of prices for stock “ABC,” averaging the price over the last three ticks:

Results:

Using Extensibility to Increase the Power of MemSQL for Time Series

Eric Hanson
Eric Hanson is a principal product manager at MemSQL, responsible for query processing, extensibility, and geospatial feature areas. He is a Ph.D. graduate of UC Berkeley, was a U.S. Air Force officer, a professor of computer science at the University of Florida and a principal program manager and developer for SQL Server at Microsoft.

MemSQL supports extensibility with user-defined scalar functions (UDF), aggregate functions and stored procedures (the MPSQL language). MemSQL compiles UDFs and stored procedures to machine code for high performance. I actually used extensibility to create the time_bucket() function (shown in the Appendix below) used in the previous section as a UDF; this provides equivalent capability to similar functions in other 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 MemSQL 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, MemSQL supports user-defined aggregate functions, which 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():

Results:

+-----------------------------+------------------+
| time_bucket('3 minute', ts) | first(price, ts) |
+-----------------------------+------------------+
| 2019-02-18 10:54:00.000000  | 100.0000         |
| 2019-02-18 10:57:00.000000  | 101.0000         |
| 2019-02-18 11:00:00.000000  | 102.0000         |
+-----------------------------+------------------+

The implementations of FIRST(), and the analogous LAST() UDAF are shown in the Supplemental Material section below.

Time Series Compression and Life Cycle Management

MemSQL 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 MemSQL 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, so they are not being updated anymore, the MemSQL columnstore is a great format because it compresses time-series data very effectively. Moreover, it 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, including applications, file systems, S3, HDFS, Azure Blob Stores and Kafka queues. MemSQL can ingest data incredibly fast from all these sources. MemSQL 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 MemSQL.   MemSQL 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 MemSQL cluster with each leaf running 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 MemSQL 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. MemSQL is in this second category. MemSQL 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, costing 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 like ours. Some lack transaction processing integrity features common to SQL databases. MemSQL 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

MemSQL 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. It 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 can connect to MemSQL. This and more makes MemSQL a strong platform for time series. Download MemSQL for free today and try it on your time series data!

Supplemental Material: Full Text of time_bucket() Function

A LAST() UDAF that is analogous to FIRST(), but returns the final value in a sequence ordered by timestamp, is as follows:

Feature image via Pixabay.

A newsletter digest of the week’s most important stories & analyses.

View / Add Comments

Please stay on topic and be respectful of others. Review our Terms of Use.