X

The Oracle NoSQL Database Blog covers all things Oracle NoSQL Database. On-Prem, Cloud and more.

  • April 18, 2019

Working with Oracle NoSQL Database Aggregate Expressions

Vishal Settipalli
Principal Product Manager

Introduction

As an application developer building modern applications you will be constantly required to handle speed for both inserts and querying of data. Oracle NoSQL Database is a scalable, distributed NoSQL database, designed to provide highly reliable, flexible and available data management across a configurable set of machine. It is truly multi model with flexibility in defining your data models as Key-Value, strict table schema or JSON document structure. While modern applications need data model flexibility, the need for aggregate operations that can process records and return computed results simplifies application code to a great extent. Oracle NoSQL Database provides a rich set of aggregate operations that perform calculations on your data sets via the SQL like query language. The database provides a SQL like interface that can be used to query data from a flat relational data model, hierarchical typed data and schema-less JSON data models seamlessly. Queries can be executed on either the command line sql interface or using the Java API.

SQL like Query Language

SQL (Structured Query Language) is widely used in programming primarily for managing data held in data stores. It is particularly defined in the form of statements which are classified as DML (Data Manipulation Language), DDL (Data Definition Language) among others. To get a detailed introduction with examples on the SQL like query language for Oracle NoSQL Database see Getting Started with SQL for Oracle NoSQL Database.

As with the 18.3 version of Oracle NoSQL Database the SQL like query language includes support for non-updating queries (SELECT), updating queries (INSERT, DELETE) and DDL queries (CREATE, ALTER).

Currently Oracle NoSQL Database supports Atomic types (Integer, Long, Float, Double, Number, String, Boolean, Binary, Timestamp), Complex Types (Arrays, Map, Record) and JSON type of data. It also includes the ability to perform simple aggregates and perform join operations on Parent-Child tables.

In this post let us look at details of how using simple aggregates you can provide a real time or near real time representation of your table data.

Use Case - Customer 360

Let’s take a Cab Aggregator application (web/mobile) which allows a typical customer to book a cab using its service. This customer sets himself on the application by creating a demographic profile before he starts using the core service. A 360-degree customer view helps companies to get a complete view of customers buying and behavioral pattern by aggregating data from various touch points that a customer may use to purchase or receive a service. This requires identification of new ways to capture Customer’s data which could be structured (third party applications) or unstructured (social media channels) and combine them in a central location and analyze them.

Oracle NoSQL Database with its ability to be truly multi-model is a perfect fit to build and store your Customer 360 profile and run aggregate queries which can help to analyze and identify customer needs over and beyond serving their requests. Typical questions that can be answered using the aggregate operations are:

  1. How many movies has a customer booked while booking a cab to a multiplex cinema hall?
  2. How many metro train tickets has a customer booked while booking a cab to the nearest metro station?
  3. What is the total revenue generated by a customer?
  4. What is the minimum amount spent by a customer for a booked cab?
  5. What is the maximum amount spent by a customer for a booked cab?
  6. What is the average amount spent by a customer for a booked cab?

A list of all the supported Aggregate operations can be found here.

Let us see some examples of how aggregation queries in Oracle NoSQL Database can be used to analyze a customer’s behavior.

Examples:

Below are examples of queries that can be run on a typical Customer 360 profile for the Cab Aggregator Industry.

The table(s) used are:

DataLake

CREATE TABLE DataLake (ID INTEGER, profile JSON, PRIMARY KEY (ID))

DataLake.MetroBooking

CREATE TABLE DataLake.MetroBooking (MB_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000), metroBookingDetails JSON, PRIMARY KEY (MB_ID))

DataLake.MovieBooking

CREATE TABLE DataLake.MovieBooking (MV_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000), movieBookingDetails JSON, PRIMARY KEY (MV_ID))

NOTE:

The assumption made below – the Cab Aggregator mobile app includes Value Added Services as Customer Touch Points like Movie Booking and Train Ticket Booking while they book their cabs to reach a Multiplex Theater or the nearest Local Train station.

  1. Movies booked by User/Customer with ID 1 in the last 1 year (in the query i = each month of the year)

select count(*) as count from DataLake.MovieBooking dlm where month(cast(dlm.movieBookingDetails.createTime as Timestamp(0))) = "+i+" and dlm.ID=1

The query above uses the following constructs:

Aggregate function – count

Timestamp Function – month

Utility Function – cast (Timestamp is represented as String within a JSON)

  1. Train tickets booked by User/Customer with ID 1 in the last 1 year (in the query i = each month of the year)

select count(*) as count from DataLake.MetroBooking dlm where month(cast(dlm.metroBookingDetails.createTime as Timestamp(0))) = "+i+" and dlm.ID=1

The query above uses the following constructs:

Aggregate function – count

Timestamp Function – month

Utility Function – cast (Timestamp is represented as String within a JSON)

  1. Total number of Cabs booked by a User/Customer with ID 1

select size(dl.profile.customerRideDetails) as size from DataLake dl where cp.ID=1

The query above uses the following constructs:

Aggregate function – size

  1. Total revenue generated by a User/Customer with ID 1  in booking Metro train tickets

select sum(cast(dlm.metroBookingDetails.fare as INTEGER)) from DataLake.MetroBooking dlm where dlm.ID = 1

The query above uses the following constructs:

Aggregate function – sum

Utility Function – cast (Timestamp is represented as String within a JSON)

  1. Total revenue generated by User/Customer with ID 1 on cab rides booked.

select sum(dl.profile.customerRideDetails[].price) as sum from DataLake dl where cp.ID=1

The query above uses the following constructs:

Aggregate function – sum

Summary

  • Oracle NoSQL Database supports aggregate functions like sum, count, size, avg, max and min.
  • More details can be found in the official documentation –

Getting Started with SQL for Oracle NoSQL Database and SQL for NoSQL Specification.

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.