SQL for NoSQL and how Apache Calcite can help FOSDEM 2017 Christian - - PowerPoint PPT Presentation

SQL for NoSQL and how Apache Calcite can help

FOSDEM 2017

Christian Tzolov

2

Engineer at Pivotal BigData, Hadoop, Spring Cloud Dataflow Apache Committer, PMC member Apache {Crunch, Geode, HAWQ, ...}

Disclaimer This talk expresses my personal opinions. It is not read or approved by Pivotal and does not necessarily reflect the views and opinions of Pivotal nor does it constitute any official communication of Pivotal. Pivotal does not support any of the code shared here.

blog.tzolov.net twitter.com/christzolov nl.linkedin.com/in/tzolov

3

“It will be interesting to see what happens if an established NoSQL database decides to implement a reasonably standard SQL; The only predictable outcome for such an eventuality is plenty

• f argument.”

2012, Martin Fowler, P.J.Sadalage, NoSQL Distilled

Data Big Bang

4

Why?

NoSQL Driving Forces

5

• Infrastructure Automation and Elasticity (Cloud

Computing)

• Rise of Internet Web, Mobile, IoT – Data Volume,

Velocity, Variety challenges

• Row-based Relational Model. Object-Relational

Impedance Mismatch

ACID & 2PC clash with Distributed architectures. CAP, PAXOS instead.. More convenient data models: Datastores, Key/Value, Graph, Columnar, Full-text Search, Schema-on-Load… Eliminate operational complexity and cost. Shift from Integration to application databases …

Data Big Bang Implications

6

• Over 150 commercial NoSQL and

BigData systems.

• Organizations will have to mix data

storage technologies!

• How to integrate such multitude of

data systems?

“Standard” Data Process/Query Language?

7

• Functional - Unified Programming

Model

• Apache {Beam, Spark, Flink,

Apex, Crunch}, Cascading

• Converging around Apache

Beam

• Declarative - SQL
• Adopted by many NoSQL

Vendors

• Most Hadoop tasks: Hive and

SQL-on-Hadoop

• Spark SQL - most used

production component for 2016

• Google F1

pcollection.apply(Read.from(”in.txt")) .apply(FlatMapElements.via((String word) -> asList(word.split("[^a-zA-Z']+"))) .apply(Filter.by((String word)->!word.isEmpty())) .apply(Count.<String>perElement()) SELECT b."totalPrice", c."firstName” FROM "BookOrder" as b INNER JOIN "Customer" as c ON b."customerNumber" = c."customerNumber” WHERE b."totalPrice" > 0;

Batch & Streaming, OLTP OLAP, EDW, Exploration

SQL for NoSQL?

8

• Extended Relational Algebra - already present in most NoSql data system
• Relational Expression Optimization – Desirable but hard to implement

Organization Data - Integrated View

9

Single Federated DB (M:1:N)

HAWQ FDBS NoSQL 1 PXF 1

Native API 1

Apache HAWQ

Optimizer, Columnar (HDFS)

Organization Data Tools

SQL/JDBC

NoSQL 1 PXF 2

Native API 2

NoSQL n PXF n

Native API n

…

Organization Data Tools

NoSQL 1 Calcite SQLAdapter 1

SQL/JDBC

NoSQL 2 Calcite SQLAdapter 2

SQL/JDBC

NoSQL n Calcite SQLAdapter n

SQL/JDBC

…

Direct (M:N)

https://issues.apache.org/jira/browse/HAWQ-1235

Single Federated Database

10

Federated External Tables with Apache HAWQ - MPP, Shared-Noting, SQL-

• n-Hadoop

CREATE EXTERNAL TABLE MyNoSQL ( customer_id TEXT, first_name TEXT, last_name TEXT, gender TEXT ) LOCATION ('pxf://MyNoSQL-URL>? FRAGMENTER=MyFragmenter& ACCESSOR=MyAccessor& RESOLVER=MyResolver&') FORMAT 'custom'(formatter='pxfwritable_import');

Apache Calcite?

Java framework that allows SQL interface and advanced query optimization, for virtually any data system

• Query Parser, Validator and Optimizer(s)
• JDBC drivers - local and remote
• Agnostic to data storage and processing

Calcite Application

12

• Apache Apex
• Apache Drill
• Apache Flink
• Apache Hive
• Apache Kylin
• Apache Phoenix
• Apache Samza
• Apache Storm
• Cascading
• Qubole Quark
• SQL-Gremlin

…

• Apache Geode

SQL Adapter Design Choices

13

SQL completeness vs. NoSql design integrity (simple) Predicate Pushdown: Scan, Filter, Projection

(complex) Custom Relational Rules and Operations: Sort, Join, GroupBy ... Catalog – namespaces accessed in queries Schema - collection of schemas and tables Table - single data set, collection of rows RelDataType – SQL fields types in a Table

• Move Computation to Data
• Data Type Conversion

Geode to Calcite Data Types Mapping

14

Geode Cache Region 1 Region K

Val Key v1 k1 v2 k2

… Calcite Schema Table 1 Table K

Col1 Col2 ColN V(M,1) RowM V(M,2) V(M,N) V(2,1) Row2 V(2,2) V(2,N) V(1,1) Row1 V(1,2) V(1,N)

… Regions are mapped into Tables Geode Cache is mapped into Calcite Schema Geode Key/Value is mapped into Table Row Create Column Types (RelDataType) from Geode Value class (JavaTypeFact ctoryImpl)

Geode Adapter - Overview

Geode A API a and nd O OQL SQL SQL/JDBC/ JDBC/ODBC ODBC Conv nvert S SQL r relationa nal expressions ns i int nto O OQL q queries Geode Adapter (Geode Client) Geode Server Geode Server Geode Server Data Data Data Push d down t n the r relationa nal expressions ns s supported b by G Geode OQL a and nd f falls b back ck t to t the C Calci cite Enu numerable A Adapter f for t the r rest Enumerable Adapter Apache Calcite Spring Data Geode Spring ng D Data A API f for int nteract cting ng w with G Geode Parse S SQL, co conv nverts i int nto relationa nal e expression a n and nd

• ptimizes

Simple SQ L Adapter

16 <<SchemaFactory>>

MySchemaFactory

+create(operands):Schema <<create>>

<<ScannableTable>>

MyTable

+getRowType(RelDataTypeFactor) +scan(ctx):Ennumerator<Object[]>

<<Schema>>

MySchema

+getTableMap():Map<String, Table>) <<on scan() create>>

<<Enummerator>>

MyEnummerator

+moveNext() +convert(Object):E

My NoSQL

<<create>> <<Get all Data>>

defaultSchema: 'MyNoSQL', schemas: [{ name: ’MyNoSQLAdapter, factory: MySchemaFactory’,

• perand: { myNoSqlUrl: …, }

}]

!connect jdbc:calcite:model=path-to-model.json

Returns an Enumeration

• ver the entire target

data store

Uses reflection to builds RelDataType from your value’s class type

Converts MyNoSQL value response into Calcite row data Defined in the Linq4j sub-project

ScannableTable, FilterableTable, ProjectableFilterableTable

Initialize Query

SELECT b."totalPrice” FROM "BookOrder" as b WHERE b."totalPrice" > 0;

Non-Relational Tables (Simple)

17

Scanned without intermediate relational expression.

• ScannableTable - can be scanned
• FilterableTable - can be scanned, applying supplied filter expressions
• ProjectableFilterableTable - can be scanned, applying supplied filter expressions

and projecting a given list of columns

Enumerable<Object[]> scan(DataContext root, List<RexNode> filters, int[] projects); Enumerable<Object[]> scan(DataContext root, List<RexNode> filters); Enumerable<Object[]> scan(DataContext root);

Calcite Ecosystem

18

Several “semi-independent” projects.

JDBC and Avatica Linq4j Expression Tree Enumerable Adapter Relational

• Relational Expressions
• Row Expression
• Optimization Rules
• Planner …

SQL Parser & AST

Port of LINQ (Language-Integrated Query) to Java. Local and Remote JDBC driver Converts SQL queries Into AST (SqlNode …)

3rd party Adapters

Method for translating executable code into data (LINQ/MSN port) Default (In-memory) Data Store Adapter implementation. Leverages Linq4j Relational Algebra, expression,

• ptimizations …

Interpreter

Complies Java code generated from linq4j “Expressions”. Part of the physical plan implementer

Calcite SQL Query Execution Flow

19

Enumerable Interpreter Prepare SQL, Relational, Planner Geode Adapter Binder JDBC Geode Cluster

1 2 3 4 5 6 7 7 7

• 2. Parse SQL, convert to rel.
• expressions. Va

Valid lidate te and Opti Optimi mize ze them

• 3. Start building a physical plan from

the relation expressions

• 4. Implement the Geode relations and

encode them as Expression t n tree

• 5. Pass the Expression tree to the

Interpreter to generate Java code

• 6. Generate and Compile a Binder

instance that on ‘bind()’ call runs Geodes’ query method

• 1. On new SQL query JD

JDBC BC delegates to Pr Prepar epare to prepare the query execution

• 7. JDBC uses the newly compiled

Binder to perform the query on the Geode Cluster

Calcite Framework Geode Adapter

2

Calcite Relational Expressions

20

RelNode Relationa nal expression TableScan Project Filter Aggregate Join Intersect Sort RexlNode Ro Row-level expressions

Project, Sort field fields s Filter, Join co cond nditions ns

Input Column Ref Literal Struct field access Function call Window expressions

*

RelTrait

*

Physica cal attribute

• f a relation

Calcite Relational Expressions

21

RelNode

+ register(RelOptPlander) + List<RelNode> getInputs();

RelOptPlanner

+findBestExp():RelNode

RexNode RelTrait Convention NONE

* *

EnumberableConvention RelOptRule

+ onMatch(call)

<<register>> <<create>>

MyDBConvention ConverterRule

+ RelNode convert(RelNode)

Converts from one calling convention to another

Convertor

Indicate that it converts a physical attribute only! <<rules>>

*

<<inputs>>

*

<<root>> Query optimizer: Transforms a relational expression according to a given set of rules and a cost model.

RelOptCluster

Rule transforms an expression into another. It has a list of Operands, which determine whether the rule can be applied to a particular section of the tree.

RelOptRuleOperand

*

<<fire criteria>> Calling convention used to represent a single data source. Inputs to a relational expression must be in the same convention

Calcite Adapter Implementation Patterns

22

MyAdapterRel

+ implement(implContext)

MyAdapterConvention Convention.Impl(“MyAdapter”)

Common interface for all MyAdapter Relation Expressions. Provides implementation callback method called as part of physical plan implementation

ImplContext

+ implParm1 + implParm2 …

RelNode MyAdapterTable

+ toRel(optTable) + asQueryable(provider,…)

MyAdapterQueryable

+ myQuery(params) : Enumetator

TranslatableTable

<<instance of>>

AbstractQueryableTable AbstractTableQueryable

<<create>> Can convert queries in Expression myQuer myQuery() implements the call to your DB It is called by the auto generated code. It must return an Enumberable instance

MyAdapterScan

+ register(planer) {

Registers all MyAdapter Rules

}

<<create>>

MyAdapterToEnumerableConvertorRule

• perands: (RelNode.class,

MyAdapterConvention, EnumerableConvention)

ConverterRue TableScan MyAdapterToEnumerableConvertor

+ implement(EnumerableRelImplementor) {

ctx = new MyAdapterRel.ImplContext() getImputs().implement(ctx) return BlockBuild.append( MY_QUERY_REF, Expressions.constant(ctx.implParms1), Expressions.constant(ctx.implParms2) …

EnumerableRel ConvertorImpl

<<create on match >>

MyAdapterProject MyAdapterFilter MyAdapterXXX RelOptRule MyAdapterProjectRu MyAdapterFilterRule MyAdapterXXXRule

<<create on match >> Recursively call the implement on each MyAdapter Relation Expression Encode the myQuery(params) call as Expressions

MY_QUERY_REF = Types.lookupMethod( MyAdapterQueryable.class, ”myQuery”, String.class String.class);

1 3 4 5 2 6 7 8 9 Calcite Framework MyAdapter components

Relational Algebra

23

Scan Scan Join Filter Project Customer [c] BookOrder [b] (on customerNumber) (b.totalPrice > 0) (c.firstName, b.totalPrice)

SELECT b."totalPrice", c."firstName” FROM "BookOrder" as b INNER JOIN "Customer" as c ON b."customerNumber" = c."customerNumber” WHERE b."totalPrice" > 0;

Scan Scan Join Project Customer [c] BookOrder [b] (on customerNumber) (totalPrice > 0) (c.firstName, b.totalPrice) Project (firstName, customerNumber) Filter (totalPrice, customerNumber) Project

• ptimize

Calcite with Geode - Without Implementation

24

SELECT b."totalPrice", c."firstName” FROM "BookOrder" as b INNER JOIN "Customer" as c ON b."customerNumber" = c."customerNumber” WHERE b."totalPrice" > 0;

Calcite with Geode – Scannable Table (Simple)

25

SELECT b."totalPrice", c."firstName” FROM "BookOrder" as b INNER JOIN "Customer" as c ON b."customerNumber" = c."customerNumber” WHERE b."totalPrice" > 0;

Calcite with Geode – Relational (Complex)

26

SELECT b."totalPrice", c."firstName” FROM "BookOrder" as b INNER JOIN "Customer" as c ON b."customerNumber" = c."customerNumber” WHERE b."totalPrice" > 0;

Calcite JDBC Connection

27

References

28

• Big Data is Four Different Problems, 2016, M.Stonebraker:

https://www.youtube.com/watch?v=S79-buNhdhI

• Turning Database Inside-Out, 2015 (M. Kleppmann)

https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza

• NoSQL Distilled, 2012 (Pramod J. Sadalage and M.Fowler)

https://martinfowler.com/books/nosql.html

• Architecture of a Database System, 2007 (J.M. Hellerstein, M. Stonebraker, J.

Hamilton)http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf

• ORCA: A Modular Query Optimizer Architecture for Big Data:

http://15721.courses.cs.cmu.edu/spring2016/papers/p337-soliman.pdf

• Apache Geode Project (2016) : http://geode.apache.org
• Geode Object Query Language (OQL) : http://bit.ly/2eKywgp
• Apache Calcite Project (2016) : https://calcite.apache.org
• Apache Geode Adapter for Apache Calcite: https://github.com/tzolov/calcite
• Relational Algebra Operations: https://www.coursera.org/learn/data-manipulation/lecture/

4JKs1/relational-algebra-operators-union-difference-selection

Thanks!

Apache Geode? “… in-memory, distributed database with strong consistency built to support low latency transactional applications at extreme scale”

Why Apache Geode?

31

5,700 train stations 4.5 million tickets per day 20 million daily users 1.4 billion page views per day 40,000 visits per second 7,000 stations 72,000 miles of track 23 million passengers daily 120,000 concurrent users 10,000 transactions per minute

https://pivotal.io/big-data/case-study/distributed-in-memory-data-management-solution https://pivotal.io/big-data/case-study/scaling-online-sales-for-the-largest-railway-in-the-world-china-railway-corporation

China Railway

Geode Features

32

• In-Memory Data Storage

– Over 100TB Memory – JVM Heap + Off Heap

• Any Data Format

– Key-Value/Object Store

• ACID and JTA Compliant

Transactions

• HA and Linear Scalability
• Strong Consistency
• Streaming and Event Processing

– Listeners – Distributed Functions – Continuous OQL Queries

• Multi-site / Inter-cluster
• Full Text Search (Lucene indexes)
• Embedded and Standalone
• Top Level Apache Project

Apache Geode Concepts

Cache Server (member) Cache Region 1 Region N

Val Ke y v1 k1 v2 k2

…

Cach che - In-memory collection

• f Regi

Regions Region - n - consistent, di distr stributed uted Ma Map (key-value), Partitioned or Replicated Cach cheServer – proce cess connected to the distributed system with created Cach che

Client Locator (member)

Client nt –read and modify the content of the distributed system Loca cator – tracks system members and provides membership information

… Listeners Functions

Funct nctions ns – distributed, concurrent data processing Listene ner – event handler. Registers for one or more events and notified when they occur

Geode Topology

Cache Server Cache Server Cache Server Cache Data Cache Data Cache Data

Peer-to-Peer

Cache Server Cache Server Cache Server Cache Data Cache Data Cache Data Client Local Cache

pool

Client-Server

Cache Server Cache Server Gateway Sender … Cache Server Gateway Receiver Cache Server Cache Server Cache Data Cache Data Cache Data Cache Data Gateway Receiver Cache Server … Gateway Sender Cache Server Cache Server Cache Data Cache Data Cache Data Cache Data

WAN Multi-site Boundary

Multi-Site