NoSQL, NewSQL and Multi-Model

Database Landscape

NewSQL
Hadoop/Spark
RDBMS

Operational Extreme Warehousing

NoSQL (Graph)
h

NoSQL (Document) NoSQL

(Key-Accessible) Time Series Databases

OO DBMS

Microsoft, AWS, Google, SAP, Oracle, Pivotal, Alibaba are placed in all segments where they have
offerings.
All other companies are positioned based on their primary capability.

2
MD&I 2020 Market View – Data & AI
NoSQL – Approach 1 – Niche (Specialty) Vendors

• A class of database management systems that depart from traditional RDBMSs

– Does not use SQL as the primary query language
– Is “schema-less”
Emergence of a growing number of non-
• No rigid schema enforced by the DBMS relational, distributed data stores for
– Programmer-friendly for adding fields to a document massive scale data

– Might not guarantee full ACID behavior

– Often has a distributed, fault-tolerant, elastic architecture
– Highly optimized for retrieve and append operations over great quantities of data

Now the challenge is high performance

integration of various data sources
containing important business data

3
NoSQL – Approach 1 – Niche (Specialty) Vendors

MongoDB Redis Elasticsearch PostgreSQL RethinkDB RabbitMQ etcd MySQL ScyllaDB

NoSQL Data Full-Text Extensible Scalable Asynchrono Key-Value Extensible Distributed

(BSON) Caching Search and Secure JSON us Database and Secure NoSQL
Document Key-Value Indexing Object Database Messaging for Relational data store
DB DB Engine Relational for Real- Layer Distributed Data Store
DB Time Apps DBs
NoSQL - Approach 2 – Multi-Modal database system

• Before Multi-modal there were two sub-optimal choices:

1. Shredding or decomposing
– Loses assets associated with native format dependencies
– Loses auditable assets like digital signature
– Creation of several relational tables as pointers
– Difficult to change original document
2. Large Object (BLOB, CLOB, Varchar) P.O. Number

– SLOW performance
» Search performance is slow (must parse at search time) POid CustId

987564331 A6789

» Retrieval of sub-documents is expensive …. …

» Update inside the document is slow

POid Par tNo Quantity

987564331 A54 12

– Indexing is inefficient (based on relative position)

987564331 985 1

…. …. ….

– Difficult to join with relational Custid Nam e Str eet City State Zip

A6789 John Sm ith Co 1234 W. Main St Toleodo OH 95141

– Costs get worse as document size increases … … … … … …

5
NoSQL - Approach 2 – Multi-modal database system

• With Multi-model sub-optimal issues are resolved

• Native storage of documents within their native format

•
P.O. Number
Ability to allow developers to access those native
documents with API / libraries of choice (NoSQL) POid CustId

987564331 A6789

…. …

POid Par tNo Quantity

• Indexing, security, ACID properties (NewSQL), 987564331

987564331
A54

985
12

recoverability, resilience, availability, etc. …. …. ….

Custid Nam e Str eet City State Zip

A6789 John Sm ith Co 1234 W. Main St Toleodo OH 95141

… … … … … …

6
XML
XML Schemas by Industry

Banking
ISO 15022, SPIFA, SPARCS, ect… Society for Worldwide Interbank Financial Telecom. (SWIFT)
IFX Standard Interactive Financial Exchange Forum, Inc.
OFX Standard Open Financial Exchange (OFX)
STEP2 Association of European Bankers
MISMO Standard Mortgage Industry Standards Maintenance Organization
Financial Markets
FIX protocol, FIXML FIX Protocol Ltd.
Message Standard for Post Trade & Pmts Society for Worldwide Interbank Financial Telecom. (SWIFT)
Market Data Definition Language (MDDL) FISD/SIAA - [Link]
Research Information ML (RIXML) [Link]
Financial Products ML (FPML) Int. Swaps & Derivatives Assoc. [Link]
Insurance
XML for P&C, Life Insurance, etc… ACORD
Chemical & Petroleum
Chem eStandards, CyberSecurity Chemical Industry Data Exchange (CIDX)
PIDX Standard American Petroleum Institute / Petroleum Industry Data Exchange
Healthcare
HL7 Standard Health Level 7
DICOM Standard Digital Imaging and Communication in Medicine
SNOMED Standard College of American Pathologists - Systemized Nomenclature of
Medicine Division
LOINC Standard The Regenstrief Institute
HIPAA Centers for Medicare & Medicaid Services
SCRIPT, Mfg. Rebate Stds. National Council For Prescription Drug Programs (NCPDP)
DoD XML, others Defense Industry Infrastructure-Common Operating Environment (DII-COE)
XML Schemas by Industry (contd.)
Life Sciences
numerous technical standards Global Grid Forum (GGF)
MIAME, MAGE, etc… Microarray Gene Expression Data Society (MGED)
LSID, others… Interoperable Informatics Infrastructure Consortium (I3C)
HL7 Standard Health Level 7
DICOM Standard Digital Imaging and Communication in Medicine
CDISC Standards for ODM, LAB, ADaM, etc.. Clinical Data Interchange Standards Consortium
Retail
UCCNET including EAN-UCC Uniform Code Council, Inc
many existing standards Global Commerce Initiative (GCI)
ePC Network & standards Auto-ID Center
ARTS XML for Retail (IXRETAIL) Association of Retail Technical Standards (ARTS)
Electronics
PIPs, RNIF, Business Directory, etc… Rosetta Net
Open Access Standards [Link]
Automotive
ebXML, and other B2B Standards Automotive Industry Action Group
STAR XML Standards for Technology in Automotive Retail
Telecommunications
eTOM, NGOSS, etc… Telemanagement Forum (TMF)
Parlay Specification The PARLAY Group
Energy & Utilities
IEC working group14, multiple standards International Electrotechnical Commission
CIM Distributed Management Taskforce ([Link])
Multispeak [Link]
Cross Industry
PDES/STEP ISO 13003 PEDS Inc
SMPI Standards Voluntary Interindustry Commerce Standards Association
Radio Frequency ID (RFID) EPCGlobal is a subsidiary of ECCnet
Db2 – XML Capabilities – Storage View

High cost development Streamlined development

Poor performance High performance

Or

Business data in XML form Business data in XML form

managed in relational database managed with Db2 pureXMLTM
Integration of XML & Relational Capabilities

 Native XML data type (server & client side)

 XML Capabilities in all Db2 components
 Applications combine XML & relational data

SERVER
CLIENT
SQL/X Relational
DB2 Storage:

DB2 Client / Interface Relational

Customer Client DB2
Application Engine
XQuery
XML XML
Interface

11
 Db2 – XML Capabilities – Retrieval View

SQL Person… "I see a XML Person… “I see a world

world class RDBMS that class XML repository that also
also supports XML" supports SQL"
XML

From a user and developer view – you have

natively stored XML – accessible using XQuery
XML – From a User perspective

• Data Definition
create table dept(deptID int, deptdoc xml);
• Indexing
Create index deptindex on dept(deptdoc) generate
key using xmlpattern ‘/Person/Dept’ as sql double;
• Insert
insert into dept(deptID, deptdoc) values (?,?)
• Retrieve
select deptID, deptdoc from dept
• Query
select deptID, xmlquery('$d/dept/name' passing
deptdoc as “d") from dept where deptID <>
“PR27”;

13
XML – From an XML Developer Perspective – The FLWOR Expression

• FOR: iterates through a sequence, bind variable to items

• LET: binds a variable to a sequence
• WHERE: eliminates items of the iteration
• ORDER: reorders items of the iteration
• RETURN: constructs query results
FOR $movie in xmlcolumn(‘[Link]’)
LET $actors := $movie//actor
WHERE $movie/duration > 90 <movie>
ORDER by $movie/@year <title>Chicago</title>
<actor>Renee Zellweger</actor>
RETURN <movie> <actor>Richard Gere</actor>
<actor>Catherine Zeta-Jones</actor>
{$movie/title, $actors} </movie>
</movie>
14
SQL/XML Functions
• Scalar functions
– XMLELEMENT - generates an XML element
– XMLATTRIBUTES - used within XMLELEMENT, specifies attributes
– XMLFOREST - produces a forest of XML elements from SQL values
– XMLCONCAT - concatenates a variable number of XML values
– XMLNAMESPACE – produces a namespace declarations in an XML
• Aggregate function
– XMLAGG - to group or aggregate XML data
• Cast functions
– XMLCAST - converts between XML data type and standard relational types
– XML2CLOB – converts XML data type to serialized XML as a char/varchar/clob
• Additional functions
– XMLPARSE – parses character/BLOB data, produces XML value
– XMLVALIDATE – validates XML value against an XML schema
– XMLEXISTS - determines if an XQuery returns a results (i.e. a sequence of one or more items)
– XMLQUERY - executes an XQuery and returns the result sequence
– XMLTABLE - executes an XQuery, returns the result sequence as a relational table (if possible)
– XMLSERIALIZE – converts XML data type to serialized XML as a char/varchar/clob/blob

15
SQL/XML: Choice of output formats

SELECT Available Functions:

XMLELEMENT(NAME "Department", XMLELEMENT
XMLATTRIBUTES ([Link] AS "name" ), XMLATTRIBUTES
XMLFOREST
XMLAGG( XMLELEMENT(NAME "emp", [Link]) ) XMLCONCAT
) AS "dept_list" XMLAGG
FROM employee e XML2CLOB
XMLNAMESPACES
WHERE ….. XMLCAST
GROUP BY [Link];
Start With Produce
dept_list
<Department name="A00">
<emp>CHRISTINE</emp>
firstname lastname dept
<emp>VINCENZO </emp>
SEAN LEE A00 <emp>SEAN</emp>
MICHAEL JOHNSON B01 </Department>
<Department name="B01">
VINCENZO BARELLI A00
<emp>MICHAEL</emp>
CHRISTINE SMITH A00 </Department>

16
 JSON

IBM Analytics
Db2 – JSON Support - Highlights

• Natively store JSON or BSON in Db2

– JSON (Java Script Object Notation)
– BSON (Binary version of JSON)

• JSON NoSQL support

– Through the Mongo API

• JSON SQL support

– Through JSON functions via SQL

• Indexing for performance:

• INDEX ON EXPRESSION allows for fast performing
queries against JSON data
Db2 – Mongo API Support

• Check out [Link]

1306nosqlforjson4/[Link] for details
20
JSON SQL functions - Proprietary

• Check out
[Link]
.doc/doc/[Link] for details

Insert, Retrieve, and Document Validation

– BSON2JSON – Convert BSON into JSON
– JSON2BSON – Convert JSON into BSON
– BSON_VALIDATE – Ensure LOB is valid BSON
Field Retrieval
– JSON_VAL – Extract data from JSON into SQL data types
– JSON_TYPE – Returns data type of a JSON field
Array Retrieval
– JSON_TABLE – Returns a table of values from a JSON array
– JSON_LEN – Returns #elements in a JSON array
– JSON_GET_POS_ARR_INDEX – Find a value within an
array
Document Update
– JSON_UPDATE – Update a field or document using set
syntax
21
JSON SQL functions – SQL:2016 Standards

• Check out
[Link]
.[Link]/doc/[Link] for details

Schema Name Comments

SYSIBM BSON_TO_JSON Convert BSON formatted document into JSON strings
SYSIBM JSON_TO_BSON Convert JSON strings into a BSON document format
SYSIBM JSON_ARRAY Creates JSON array from input key value pairs
SYSIBM JSON_OBJECT Creates JSON object from input key value pairs
SYSIBM JSON_VALUE Extract an SQL scalar value from a JSON object
SYSIBM JSON_QUERY Extract a JSON object from a JSON object
SYSIBM JSON_TABLE Creates relational output from a JSON object
SYSIBM JSON_EXISTS Determine whether a JSON object contains the desired JSON value
 Graph

IBM Analytics
 What are Property Graphs?

Another way to represent data

▪ Based on connections and relationships between

entities

▪ Has objects, called Vertices and relationships

between these objects, called Edges

▪ Each Vertex and edge have

▪ ID that’s unique across the graph
▪ Label representing the ‘type’ of object or
relationship
▪ Properties that further define the object or
relationship
 Mapping Graphs to relational tables

Works_At
ID Deptno Department
Works_at
1 D01
Startdate=… 3 Deptno= “D01”
Deptname= “Human Resources”
Enddate=…
2 D01
4
.. .. 5
1 Works_at
Startdate=..
Employee Enddate=…
Employee ID=1
Department First_name= “John”
ID First_name Last_nam DOB Email Deptno Deptname Last_name= “Doe” 2
e DOB= 01-03-1990
D01 Human Email=“…” Employee
1 John Doe … … Resources ID=2
2 Jane Doe … … First_name=“Jane”
D02 Sales
Last_name= “Doe”
.. .. .. .. DOB= 05-04-1975
Email=“[Link]@[Link]”

In the case of graph data, there is a clear

mapping between relational and graphs
which preserves all characteristics
Understanding how the Mapping Works

Vertex set contains the tables that will map to Vertices

Every Vertex table
▪ ID – one of more columns that uniquely identify the node
across the graph
▪ Label- can map to the table name or a column, to identify the
type of node this will be
▪ Set of columns that will make up the Properties

Edge set contains those tables that will map to

relationships
Every Edge table
▪ ID
▪ Label
▪ Set of columns that will make up the Properties
▪ Source and Destination vertex to identify the direction of
relationship
Technology behind Db2 Graph

▪ Plugin for Apache TinkerPop

▪ TinkerPop is an open-source graph analytics framework
and provides a way to interact with Graph
▪ Gremlin is the query language of Tinkerpop that
traverses the graph

▪ Exposes a Graph overlay mapping of existing relational

tables in Db2, through Db2 Graph

▪ Execute Gremlin queries on Db2 Graph

▪ Db2 Graph will fetch data from Db2, in real-time, using a JDBC
connection g.V().has('name','Scranton').out('Managed
by').values('name’)
▪ Uses several methods to translate Gremlin queries into SQL
statements for optimized query processing
Db2 – Graph Support

Check out these demos

[Link]

[Link]
Db2 – A Multi-Model – NoSQL and NewSQL Data Store

XML, JSON, Relational Graph

BSON

Support of hierarchical (tree structure) Support of traditional relational structured Support of graph data through native
documents in their native form data storage mechanisms

Enjoy the benefits of easily changeable Fully integrated with other data models Enjoy the benefits of a vertices and edge
schemas within documents through SQL and NoSQL queries based storage model for data

Fully integrated with other data models This is the foundation of Db2 and all Fully integrated with other data models
through SQL and NoSQL queries relational databases through SQL and NoSQL queries

Fast performance for search and retrieval NoSQL support through XQuery (XML) and Fast performance for search and retrieval
through indexing Mongo API (JSON) and Gremlin (Graph) through indexing

NoSQL support through XQuery (XML) and ACID support and enterprise capabilities are NoSQL support through Gremlin (Graph) and
Mongo API (JSON) built into foundation of Db2 Tinkerpop library integration

NewSQL (ACID) support through enterprise NewSQL (ACID) support through enterprise
capabilities of Db2 capabilities of Db2
 SQL and Machine Learning

IBM Analytics
 Doing AI with Database – Key Challenges

Large Data Volumes

Data Transfer Data Security/Lineage
Time Requirements
Development machine might not
Grows exponentially with be able to store and process large
data volume, and increases Confidential data (e.g. GDPR) may
data (e.g. 25 GB Pandas dataframe
“time to value” not be copied of the database
on a Dev Laptop)

30
Wait time
to load a
Pandas
Dataframe
from a
Remote
Database
ML Pipeline Time Breakdown

Visualizing Data
Other 13%
15%

Operationalizing
Model
12%

Acquiring and
Cleaning Data
40%

Model Building and

Evaluation
20%

Average time breakdown for a data scientist during a project - Based on a 2018 Kaggle Survey [Link]

32
 In-database machine learning refers to

What is In-DB the execution of the ML pipeline within

the database with zero or minimum
Machine Learning?
data copied to the client-side
Db2 – Integrated Machine Learning - Architecture

IBM Db2

1. ibm_db Python package

Client 2. Db2 ML Functions

+
3. Trained Model
4. Scoring Request
Client Data

5. Model Prediction

Client Data is never moved out of Db2!

Db2 – Integrated Machine Learning – The Value
Idea 1: In-Db2 Data Cleansing and Error Detection

▪ Poor data has a major negative impact on business.

25%
of the data record are inaccurate

[[Link] lytics/white-papers/the%20state%20of%20data%[Link]]

▪ Error detection is the natural first step in every data analysis pipeline.

Can Machine Learning Techniques help us detect and fix data errors at the Database?
36
Idea 2: A Python Library for exploring and manipulating data
in the virtual Db2 databases

Python for Db2 without a Python runtime on the server

Python command

SQLDataPrep

Results SQL command

Db2 Server
All platforms
Familiar open-source Python interface

SQLDataPrep Scikit-learn

Get Dataframe

Data Transformation

Pipeline Creation

38
 Idea 3: Accelerated and • Logistic Regression
Distributed Machine Learning
Algorithms in Db2 (Kaggle #1 most used)

21x Faster
Dataset: criteo-kaggle
Speed: Machine: 1x AC922
scikit-learn loss: 0.4558
Snap ML vs. Snap ML loss: 0.4558

scikit-learn

• Random Forest • Decision Tree (Kaggle

(Kaggle #2 most used) #2 most used)
400 677.5
60x 102x
361.8 9x 13x 1000
350
300
Training Time (s)

Training Time (s)

250
Dataset: SUSY 100 Dataset: higgs
Machine: x86, V100 Machine: x86, V100
200
scikit-learn acc: 0.7848 11.3 scikit-learn acc: 0.7030
150 Snap ML acc: 0.7945 6.7
10 Snap ML acc: 0.7030
100
39.5 27.9
50
0 1
scikit-learn Snap ML (CPU) Snap ML (GPU) scikit-learn Snap ML (CPU) Snap ML (GPU)
(CPU) (CPU)
SnapBoost API

XGBoost sklearn API SnapBoost API

from xgboost import XGBClassifier from pai4sk import BoostingMachine

booster = XGBClassifier(n_estimators=1000, booster = BoostingMachine(objective='logloss’,

max_depth=8, num_round=1000,
learning_rate=0.01 min_max_depth=8,
tree_method = 'gpu_hist’, max_max_depth=8,
n_jobs=8) learning_rate=0.01,
use_gpu=True,
n_threads=8)

# Train # Train
[Link](X_train, y_train) [Link](X_train, y_train)

# Inference # Inference
yhat_test = [Link](X_test, output_margin=True) yhat_test = [Link](X_test)
Db2 – Integrated Machine Learning

Check out this demo

[Link]
Db2 – A Multi-Model – NoSQL and NewSQL Data Store

XML, JSON, Relational Graph

BSON

Support of hierarchical (tree structure) Support of traditional relational structured Support of graph data through native
documents in their native form data storage mechanisms

Enjoy the benefits of easily changeable Fully integrated with other data models Enjoy the benefits of a vertices and edge
schemas within documents through SQL and NoSQL queries based storage model for data

Fully integrated with other data models This is the foundation of Db2 and all Fully integrated with other data models
through SQL and NoSQL queries relational databases through SQL and NoSQL queries

Fast performance for search and retrieval NoSQL support through XQuery (XML) and Fast performance for search and retrieval
through indexing Mongo API (JSON) and Gremlin (Graph) through indexing

NoSQL support through XQuery (XML) and ACID support and enterprise capabilities are NoSQL support through Gremlin (Graph) and
Mongo API (JSON) built into foundation of Db2 Tinkerpop library integration

NewSQL (ACID) support through enterprise NewSQL (ACID) support through enterprise
capabilities of Db2 capabilities of Db2

Leverage the joint benefits of multi-modal with integrated machine learning

routines to accelerate AI benefits with many models of data