trafodion – an enterprise class sql based on hadoop

HP © Copyright 2014 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice.

Trafodion Enterprise class Transactional SQL on Hadoop

by

Krishna Kumar, Architect

Karthikeyan Soundararajan, Architect

Open Source India 2014 – November 8th

HP © Copyright 2014 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice.2

Agenda

+

Motivation – Why?

Overview – What?

Use Cases – Where?

Architecture

Demo

Backup Slides

Open Source – How?


+MOTIVATION:Why Trafodion?


+

… and limitations

Query

Optimization

Data

Integrity

Workload

Management

Transaction Support

Real-time

Performance

Hadoop has strengths…

Social media Video Audio

EmailImagesTexts

Documents Mobile

Offline

Analytics

Data “Dumping”

Scalability

Replication/K-safety


+

Introducing Trafodion:

Transactional SQL on Hadoop

Full Hadoop

support

Scales like Hadoop Extends

HAVEn

HAVEn

Social media Video Audio

Email Texts Mobile

ImagesDocumentsTransactional

data

Adds enterprise-class

transactional and reporting

functionality with full SQL

Workload

Management

Transaction Support

Transaction Support

Real-time

Performance

Data

Integrity

Query

Optimization

Multi-

structured

Data


+

Overview:

What is Trafodion?


Trafodion - Introduction

Complete: Full-function ANSI SQL

Reuse existing SQL skills and improve developer productivity

Protected: Distributed ACID transactions

Guarantees data consistency across multiple rows, tables, SQL statements

Efficient: Optimized for low-latency read and write

transactions

Supports real-time transaction processing applications

Flexible: Schema flexibility and multi-structured data

Seamlessly integrates structured, unstructured, and semi-structured data

Interoperable: Standard ODBC/JDBC access

Works with existing tools and applications

Open: Hadoop and Linux distribution neutral

Easy to add to your existing infrastructure and no vendor lock-in

+

Transactional

SQL

Hadoop

Open source project to develop transactional SQL on HBase


Trafodion innovation built upon Hadoop stackLeverages Hadoop and

HBase for core modules

Maintains API

compatibility

Differentiation

• ANSI SQL via ODBC/JDBC

• Relational schema abstraction

• Distributed transaction protection

• Low latency reads and writes

• Automatic parallelism

Hadoop Trafodion

Client Application using

ODBC/JDBC on

Windows/Linux

HBase

Hive

HDFS

Zo

oke

ep

er SQL Compiler / Optimizer / Executor

Distributed Transaction Manager

Client Services for ODBC and JDBC

+


+

Use Cases:

Where Trafodion is

used?


Potential Use case Profile

• Online financial management

Finance

• Billing systems

• Provisioning systems

Telecom

• RFID tracking

Manufacturing

• Smart Metering

Energy

• Authorization and claims processing

Healthcare

• 911 Emergency System

Government

• Reservation systems

Transportation

• Online shopping

Consumer & Retail

Multi-structured data requirements

HBase – but through

SQL with standard

tools

Generates Revenue Touches the Customer Helps Run the Business

Experiencing scalability

or prohibitive licensing

issues


Some projects ongoing…

+

Aimed to continuously measure the performance of all SEM Campaigns. The Engine extracts information using Google Ad words API and provides a real time view of some of the key parameters like Quality Score (QS), Click through Rate (CTR), Impression Share (IS), Cost Per Click (CPC), Cost Per Acquisition (CPA), Cost Per Download (CPD), Average Position, and more, thereby enabling the Business & Marketing Functions to take real-time decisions. In the current scenario the TAT (Turnaround Time) is anywhere between few weeks to a month, which affects the campaign performance, leading to low conversions, poor lead quality & high marketing investments.

Enterprise Software company in China specializing in commercial fleet telemetry. Use telemetry from buses to make them safer and more efficient to operate. Currently monitor 65K buses. Steady growth, was 10K buses on Jan 2013

Telemetry data to be stored in Trafodion tables for short latency access. Periodic ETL to Vertica for analysis

Data ingest at about 10K – 30K rows per second. Concurrent access of data though relatively simple SELECT queries, at high concurrency, with sub-second response times

OSS allows business users and partners to track status of orders and run scheduled and ad-hoc reports. This application has a need to move to Hadoop to save on S/W license cost.

HP IceWall SSO is designed to adapt to various customers’ environments. It has a lot of parameters, templates and APIs in order to fulfill many kinds of customers’ requirements. And it is designed so that these customizations will not affect any future upgrades. HP IceWall SSO can flexibly connect using many types of authorization used in web applications. In particular it deals with 11 methods and 48 patterns of Form AuthN.

Its latest version, HP IceWall SSO 10.0, now provides support for new leading-edge technologies such as cloud and virtualization, and the IceWall SSO product line has been extended to include Windows support in addition to the existing HP-UX and Linux versions.


+

Open Source:

How

Trafodion is used?


Required SoftwareSoftware Version HBase Version

Linux 6.3,6.4 kernel

Zookeeper V3.4.5

Trafodion 0.9.0

One of the following Hadoop Distribution:

- Cloudera CDH 4.5 0.94.6

CDH 5.1.2 0.98

- Hortonworks HDP 1.3.3 0.94.6

HDP 2.1 0.98

- MapR M5 v3 0.94.13/0.94.17

PlatformsCloud

VM

Workstation

Cluster

Trafodion Installation Link -

https://wiki.trafodion.org/wiki/index.php/Installation

https://wiki.trafodion.org/wiki/index.php/Installation


Modern open source environment

Source code in GitHub

Build/test in OpenStack gerrit, zuul, jenkins

Defect tracking in LaunchPad

Documentation in MediaWiki

Following best practices of OpenStack project


Building an Open Community

Simple installation

MeritocracyShare your expertise: Developing, fixing defects,

testing, writing, translating and more

Seeking early adopters

Recruiting project contributors

Discover our capabilities: Download and install in

your Hadoop environment and take a test-drive

www.trafodion.orgEmail:

Generic Query- [email protected]

Future directions – [email protected]

(Hema Ramaswamy, HP Labs)

mailto:[email protected]

mailto:[email protected]


+High Level

Architecture


3-Layered High Level Architecture

Client

JDBC ODBC

User and ISV Operational

Applications

Driver

Hive

Native Hive Tables

Multi-Structured

Data Store

IntegrationHBase

Native HBase

Tables KVS,

Columnar

SQL

ESP

CMP Master

ESPDTM

WMS

Compiler and OptimizerWorkload Management(wip)

SQL Parallelism

Distributed Transaction

Management

. . . .

Database Connectivity

UDF

Communicate with

external processes

HBase

HDFS

Relational

Schema

Trafodion

Tables

Storage

Engines


Connectivity Architecture (T4)

ClientUser and ISV

Operational

Applications

ODBC/JDBC

Drivers

SQL

DCS

Master ZooKeeper

Master

ExecutorMaster

ExecutorMaster

Executor

Master

ExecutorMaster

ExecutorMaster

Executor

DCS

Server

Database

Connectivity

Services

. . .

.

Blue: control flow

Green: data flow

Connection

Mgmt

Process

Mgmt

DCS

Server


Process Architecture

Trafodion Node(DCS,EXE, ESP, CMP, DTM,

UDF, WMS)

VM or Physical Node

Hadoop

Data Node

HBase APIs

VM or Physical Node

TCP/IP

TCP/IP

…Trafodion

Metadata

Trafodion

Data

HBase Region

Server

Hive/HDFS

APIs

Hive

Data

HDFS

Data

Trafodion Node(DCS,EXE, ESP, CMP, DTM,

UDF, WMS)

Hadoop

Data Node

HBase Region

Server

Hive/HDFS

APIs

HDFS

Data

Hive

Data

Trafodion

Data

Trafodion

Metadata

HBase APIs

TCP/IP


Operational workload optimizations

• Key-based access with SQL “pushdown”

• Statistics based plan generation

• Query plan caching

• Data-flow, scheduler driven executor

• DOP optimization

• Parallelism without map-reduce

• Secondary index support

• Table structure optimizations

Data-flow, Scheduler-driven Salting of Row Keys

Optimized DOPStatistics Based Optimizations


+Demo


Enterprise-class SQL DBMS that uses native Hadoop formats (e.g., HBase, Hive, HDFS) for data storage

Full-functioned ANSI SQL language support

Standard ODBC/JDBC connectivity for Linux and Windows clients

Low latency reads and writes via compile time and run time optimizations

ACID distributed transaction protection over multiple stmts, tables, rows

Support for big data sets using parallel SQL optimizations

Retention of Hadoop benefits: reduced cost, scalability, elasticity and data redundancy

Support for structured, unstructured, semi-structured data and flexible schemas

Major Features - Recap


See for yourself…

Come discover and develop on Trafodion

www.trafodion.org

© Copyright 2014 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice.

Thank You

© Copyright 2014 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice.

Backup Slides


External Features


Trafodion DDL CREATE/DROP/ALTER statements

• Tables, views, indexes, columns

• Numeric, character, varchar, date, time, interval

• Unicode (UTF8, UCS2) and single byte (ISO8859-1) for user data

• UTF8 for metadata

• Salting (Table partitioning for uniform data access across disks)

LOBs

• BLOB/CLOB datatypes (wip)

Constraints

• RI, Foreign Key, Primary Key, Check

Security, Privileges

• Grant/Revoke, create/drop user

SPJ/UDF

• Java Stored Procedures

• User Defined Functions (wip)


Trafodion DML

Query statements

• SELECT, INSERT, UPDATE, DELETE, UPSERT and MERGE

Complex SQL operations

• JOIN (INNER, LEFT/RIGHT/FULL OUTER), UNION, WHERE, GROUP BY, HAVING, ORDER BY, SAMPLING, TRANSPOSE, GROUP PIVOT, etc.

Compile time and run time optimizations

• Cost-based query optimizer, MDAM, OR optimizations

• Correlated and nested subqueries

Cursor support (non-holdable)

SQL functions

• Aggregate, date/time, character, mathematical, OLAP, sequence, etc.


Miscellaneous

Utilities

• Update Statistics

• Explain

• Control Query Shape, Control Query Default

Transaction Control

• BEGIN WORK, COMMIT WORK, ROLLBACK WORK

• SET TRANSACTION

Oracle compatibility

• Based on internal POCs

• Syntax extensions (DUAL, ROWNUM, SYSDATE, NEXTVAL…)

• Functions (TO_CHAR/DATE, SEQUENCE, Incompatible operations, …)


Trafodion Interfaces

Product interfaces

• TrafCI

• sqlci

• Trafodion Query Workbench

• ODBC client app

• JDBC client app

• T4 Driver

• T2 Driver (wip)

• HP DSM (wip)

External Interfaces

• DBVisualizer

• SQuirrel


Multiple Storage Engines

Trafodion

• Uses hbase tables underneath

• native datatype storage format (ex: 2 bytes for a short)

• Encoded data for serialization

• column family and column name optimization

Native Hbase

• Cell format: One hbase cell per row output

• Rowwise format: All cells in one row

Hive

• External metadata from hive

• Text files: delimited data

• Sequence files: structured data

• ORC files: Optimized Row Columnar data (wip)


Interoperability

Trafodion, Hbase, Hive

• Syntax extension to identify tables

• Trafodion.sch.t_traf

• Hbase.”_CELL_”.t_hbase

• hive.hive.t_hive

JOINs, INSERT…SELECT

Transactional updates across storages

• Currently Trafodion and HBase


External integration

• Table-valued UDF

• Data Loader

• Data Extractor

• Other products through Table-valued UDF (wip)

WMS (Workload Management)

• Runtime stats data collection

• Repository updates (wip)

• Automated query control (wip)

• Resource control (wip)

• User control (wip)


DISTRIBUTED

TRANSACTION

MANAGEMENT

(DTM)


Overview

Single row consistency using underlying HBase

Global transactions across multiple tables/rows

Optimistic locking

• Conflict resolution at commit

• First commit gets through

HBase Trx EndPoint Coprocessors

HBase Write Ahead Log (HLOG) for audit logging


...

Node 1

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

Transaction

Manager

HBase trx Region Server

Node 2

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

Transaction

Manager

HBase trx Region Server

Node n

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

SQL Process

Transaction

Manager

Library

Resource

Manager

Library

Transaction

Manager

HBase trx EndPoint

Coprocessor

Distributed, Scalable Architecture

HBase trx EndPoint

Coprocessor

HBase trx EndPoint

Coprocessor


Performance Benchmarks


Configuration for Perf Runs

System under Test: Spinel ( Converted Gen8 Seaquest Cluster)

Spinel is not an optimal configuration for Trafodion/Hbase due to the single raided data

drive.

Nodes: 10; Memory: 128 Gibper node; Cores: 16 per node; OS:

RedhatLinux 6.3; Data Drives: 1 per node ( 3.4 TB Raid device)

Hadoop/Hbase: Cloudera 4.5.0; Hadoop 2.0.0; Hbase 0.94.6/0.98

Trafodion Version 0.8.3


YCSB (Yahoo Cloud Serving Benchmark)

Workload A: read/update ratio 50/50

Traf 0.9.0


DebitCredit

5 SQL statements per transaction

(3 update, 1 insert, 1 select)

Traf

0.9.0

Traf

0.9.0


WORKLOAD

OPTIMIZATIONS


Key-based access with SQL “pushdown”

Statistics based plan generation

Parallelism without map-reduce

DOP(Degree Of Parallelism) optimization

Adaptive segmentation

Query plan caching

Data-flow, scheduler driven executor

Secondary index support

Table structure optimizations


Optimized DOPStatistics Based

Optimizations


Data-flow, Scheduler-

driven

Salting of Row Keys


Real-Time Mixed Workload

Concurrent transactional OpenCart application

Queries against the same database

Query type: OLTP Insert, Deletes, Selects

Query monitoring


Operational Reporting Queries

Complex multi-table operations

Parallel query execution

Various query optimizations for complex queries

EXPLAIN: display query plan

EXECUTE: run query


Query: List of all products, some

product info, current specials, a

summary of their ratings and

reviews Nested Join for

keyed lookup

into Trafodion

Parallel scan larger

Trafodion tables

Cache of

previous

lookups into

Trafodion


Interoperability between Storages

Access multiple Hadoop Storages

Load/Extract data from Trafodion to/from Hive/HDFS

Parallel insert and select operations

Join tables from multiple storages

Without external federation

Storage access from within engine


Load data from Trafodion tables to Hive table with insert-select

statementSource data is detailed order

information obtained by joining multiple Trafodion tables

Parallel

Join

Trafodion tables acting

as source

Parallel insert into

Hive

Hive table is the target


Transactional Capabilities

Multiple rows

Multiple tables

Coordinated Commit and Rollback

Conflict resolution


TraFoto: Structured/Unstructured Integration

Access structured relational Trafodion table

Access unstructured native HBase table

JOINs and other operations between the tables

Transactional insert, update, deletes across both tables


UDFs: User Defined Functions

User written scalar and tabular functions

SQL syntax to access UDFs in queries

Parallel execution at runtime

trafodion – an enterprise class sql based on hadoop

Data & Analytics

hbase hp copyright

jdbc hp copyright

agenda hp copyright

hadoop stack leverages

sql statementsefficient

trafodion innovation

hadoop bykrishna kumar

existing tools