July 3, 2019 By Josh Mintz 3 min read

As of last week, IBM Cloud SQL Query now supports a wide variety of time series functions as native components of the service.

The library, developed by IBM Research, joins our Geospatial functionality as fully supported by the IBM Cloud SQL Query service and team. 

SQL Query is a serverless, pay-per-query offering that can manipulate and analyze semi-structured and structured data in IBM Cloud Object Storage. Its new SQL-native time series support is industry-leading by its breadth of directly available capability inside a SQL engine. It significantly simplifies definition and execution of time series processing problems through a declarative language (SQL) instead of having to write hundreds of lines of custom code. SQL-native time series processing allows for very high productivity and achieves low time to value.

Key features of the time series functions

  • Full suite of SQL-style temporal joins on aperiodic and unaligned time series
  • Multi-typed and multi-time series support for numeric and categorical (string) data
  • Time Reference System (akin to Coordinate Reference Systems for geospatial) for handling timestamps at multiple granularities
  • Rich support for segmentation based on time, number of records, markers, and anchors
  • Built-in SQL constructs for interpolation, similarity, and forecasting

Why time series support? 

The growth of data volumes is dominated by machine-generated data, such as IoT sensor feeds, connected cars, or user behavior logs. All this data is time stamped by nature, which is also a key dimension for deriving valuable business insights.

Capturing and analyzing the state of a systems over time allows us to make more informed predictions about future events, observe real-time changes, and capture historical anomalies. Whether it be IoT devices, autonomous driving systems, or network performance, many of the systems and products we use today are constantly emitting time series data that can be used to optimize and improve performance, safety, and robustness. 

Coupled with the massive scale-out capabilities of SQL Query and IBM Cloud Object Storage, we can now provide unlimited retention and analytics on this at petabyte scale at an extremely low cost and barrier to entry. Does your team know SQL? Great, they can now tap into time series insights on the IBM Cloud

Adding sophisticated time series functionality to IBM Cloud SQL Query is an essential realization to our vision of building a cloud-native, serverless data lake for our clients. We aim to deliver a platform that makes data simple, allowing you to seamlessly store, manage, life-cycle, and analyze data and develop analytic solutions at scale. 

What does it do? 

Our time series functionality includes, but isn’t limited to, the following: 

  • Artifact creation
  • Exploding and flattening
  • Statistical insights 
  • Forecasting
  • Filtering
  • Temporal join and align
  • Interpolation 

Check out the full cadre of features and get going with sample queries in our UI.

Example of hourly segmentation of time series data

Benefits of using SQL Query’s time series support

Time series functions allow SQL Query to filter, cleanse, and analyze trillions of observational events per day at an order of magnitude less than a typical database. For example, storing 1 Terabyte of Parquet data and scanning all that data 100 times would only cost:

1,000 GB *.022/GB =$22/month 

(100TB/38)*$5/TB-Scanned = $13/month

Total = $35/month 

We recommend converting your data to Parquet using IBM Cloud SQL Query to dramatically decrease your total data scanned and improve the speed of your queries. For example, we’ve shown that converting to Parquet allowed us to scan 38x less data than a CSV required, which means that it’s 38x less expensive!  

Learn more about Data Lakes in the IBM Cloud

For Jupyter notebook users, we also have an in-depth tutorial of using this functionality for data science.

To get in touch with IBM Cloud about a time series use case, you can reach out to Josh Rosenkranz (jmrosenk@us.ibm.com) or myself at Joshua.Mintz@ibm.com.

More from Analytics

How Macmillan Publishers authored success using IBM Cognos Analytics

5 min read - Macmillan Publishers is a global publishing company and one of the “Big Five” English language publishers. If you're a reader, chances are good you've read a book from Macmillan. They published many perennial favorites including Kristin Hannah’s The Nightingale, Bill Martin’s Brown Bear, Brown Bear, what do you see? and some of the more recent bestsellers such as The Silent Patient by Alex Michaelides, Identity by Nora Roberts and Razorblade Tears by S. A. Cosby. It’s no wonder then that Macmillan…

Jabil is building reports with IBM Business Analytics Portfolio

3 min read - Jabil isn’t just a manufacturer, they are experts on global supply chain, logistics, automation, product design and engineering solutions. They are also interested and involved in the holistic application of emerging technologies like additive manufacturing, autonomous technologies, and artificial intelligence. They are a technologically motivated enterprise, so it’s no surprise that they would apply this forward-thinking view to their finance reporting as well. Jabil is a sizable operation with over 260,000 employees across 100 locations in 30 countries. The world's…

How a water technology company overcame massive data problems with ActionKPI and IBM

4 min read - Access to clean water is essential for the survival and growth of humans, animals and crops. Water technology companies worldwide provide innovative solutions to supply, conserve and protect water throughout the highly complex and technical water cycle of collection, treatment, distribution, reuse and disposal.  After a series of international acquisitions, a leading water technology company formed an Assessment Services division to provide water infrastructure services to their customers. Through the formation of this group, the Assessment Services division discovered multiple…

IBM Newsletters

Get our newsletters and topic updates that deliver the latest thought leadership and insights on emerging trends.
Subscribe now More newsletters