SQL Range Queries

Time is relative.

You don’t have to be Einstein to understand your time series data even though it comes in at high velocity and volume. Riak TS SQL range queries let you read and analyze this data quickly to gain valuable insights.

With Riak TS, once you’ve created your tables to match your data model and begin ingesting data, you will use SELECT statements with WHERE clauses to query and analyze your data.

These SQL range queries can include arithmetic and aggregations to make your analysis easier.

TIME SERIES DATA IS ANALYZED IN TIME RANGES

Co-locating your data and using SQL commands to create your tables are just the beginning for analyzing time series data. Once you have completed data modeling and created your tables, you’ll want to write you data and then read it using SQL range queries.

Riak TS SQL range queries consist of standard SELECT statements that allow you to specify a time range (using timestamp or date range) and subset of columns that you would like to return. These range queries require WHERE clauses and you can use standard logical operators (=, !=, >, <, <=, >= ) and boolean operators (AND and OR) to filter the results set.

SELECT statement using timestamps (as long integers):

SELECT * from WEATHER
WHERE city = 'Seattle' AND 
      location = 'Bellevue' AND
      time >= 1451606400000 AND
      time <= 1454284800000;

You can also use ISO 8601 date/time formats with INSERTand SELECTstatements. Additionally, you can use GROUP BY to group query results by one or more columns. GROUP BY is useful for aggregating an attribute of a device over time. The example below includes both a date/time format and GROUP BY command:

SELECT * from WEATHER
WHERE city = 'Seattle' AND 
      location = 'Bellevue' AND
      time >= ‘2016-01-01’ AND
      time <= ‘2016-02-01 00:00:00’ 
GROUP BY city;

The above SELECT statement assumes a range query on the table created below:

table = 'WEATHER'
fmt = """CREATE TABLE {table} (
city varchar not null,
location varchar not null,
time timestamp not null,
weather varchar not null,
temperature double,
PRIMARY KEY ((myfamily, myseries, quantum(time, 2, 'h')),
myfamily, myseries,, time))
"""
query = fmt.format(table=table)
ts_obj - client.ts_query(table,query)

BENEFITS OF SQL RANGE QUERIES

Riak TS with SQL range queries enables you to quickly analyze your time series data to make real-time decisions quickly and easily.

Make real-time decisions
Your application is collecting massive volumes of time series data, but actionable business insights require analysis. With Riak TS, you can use SQL range queries to quickly analyze your data across time, enabling you to make real-time decisions.

Improve customer experience
Whether you’re collecting millions or possibly billions of time series data points, your customers don’t have time to wait. To forecast weather, analyze financial market data, or respond to changes in IT infrastructure, your application must be fast. Riak TS enables you to execute SQL range queries to quickly read and analyze data across time to ensure your application meets your business needs.

Increase performance and scale
Fast data ingest is an important aspect of application performance, but you must also be able to conduct real-time analysis of your time series data. With Riak TS you get highly performant SQL range queries for fast data analysis.