sql on hadoop - 12th swiss big data user group meeting, 3rd of july, 2014, eth zurich

© 2013 IBM Corporation1

SQL on Hadoop - 12th Swiss Big Data User Group Meeting, 3rd of July, 2014, ETH Zurich

Romeo Kienzler

IBM Center of Excellence for Data Science, Cognitive Systems and BigData(A joint-venture between IBM Research Zurich and IBM Innovation Center DACH)

Source: http://www.kdnuggets.com/2012/04/data-science-history.jpg


DataScience at present● Tools (http://blog.revolutionanalytics.com/2014/01/in-data-scientist-survey-r-is-the-most-used-tool-other-than-databases.html)

● SQL (42%)● R (33%)● Python (26%)● Excel (25%)● Java, Ruby, C++ (17%)● SPSS, SAS (9%)

● Limitations (Single Node usage)● Main Memory● CPU <> Main Memory Bandwidth● CPU ● Storage <> Main Memory Bandwidth (either Single node or SAN)


Data Science on Hadoop

SQL (42%)

R (33%)

Python (26%)

Excel (25%)

Java, Ruby, C++ (17%)

SPSS, SAS (9%)

Data Science Hadoop


SQL on Hadoop● IBM BigSQL (ANSI 2011 compliant, part of IBM BigInsights)● HIVE, Presto● Cloudera Impala ● Lingual● Shark● ...

SQL Hadoop


Two types of SQL Engines● Type I

● Compiler and Optimizer SQL->MapReduce● Type II

● Brings own distributed execution engine on Data Nodes● Brings own Task Scheduler

● The Hadoop SQL Ecosystem is evolving very fast


Hive● Runs on top of MapReduce● → Type I

Source: http://cdn.venublog.com/wp-content/uploads/2013/07/hive-1.jpg


Lingual● ANSI SQL Layer on top of Cascading● Cascading

● Java API do express DAG● Runs on top of MapReduce● → Type I


Limits of MapReduce● Disk writes between Map and Reduce● Slow for computations which depend on previously computed values● JOINs are very slow and difficult to implement

● Only sequential data access● Only tuple-wise data access● Map-Side joins have sort and size constraints● Reduce-Side joins require secondary sorting of values● …

● ...


Impala (Type II)

http://blog.cloudera.com/blog/wp-content/uploads/2012/10/impala.png


Presto (Type II)

https://www.facebook.com/notes/facebook-engineering/presto-interacting-with-petabytes-of-data-at-facebook/10151786197628920


Spark / Shark (Type II)

Source: http://bighadoop.files.wordpress.com/2014/04/spark-architecture.png


BigSQL V3.0 (Type II)

Like in Spark, MapReduce has been Kicked out :)(No JobTracker, No Task Tracker, But HDFS/GPFS remains)


BigSQL V3.0 – Architecture

Putting the story together….Big SQL shares a common SQL dialect with DB2Big SQL shares the same client drivers with DB2


BigSQL V3.0 – PerformanceQuery rewritesExhaustive query rewrite capabilitiesLeverages additional metadata such as constraints and nullability

OptimizationStatistics and heuristic driven query optimizationQuery optimizer based upon decades of IBM RDBMS experience

Tools and metricsHighly detailed explain plans and query diagnostic toolsExtensive number of available performance metrics

SELECT ITEM_DESC, SUM(QUANTITY_SOLD), AVG(PRICE), AVG(COST)

FROM PERIOD, DAILY_SALES, PRODUCT, STORE

WHERE

PERIOD.PERKEY=DAILY_SALES.PERKEY AND

PRODUCT.PRODKEY=DAILY_SALES.PRODKEY AND

STORE.STOREKEY=DAILY_SALES.STOREKEY AND

CALENDAR_DATE BETWEEN AND

'01/01/2012' AND '04/28/2012' AND

STORE_NUMBER='03' AND

CATEGORY=72

GROUP BY ITEM_DESC

Access plan generationQuery transformation

Dozens of query transformations

Hundreds or thousands of access plan options

Store

Product

Product Store

NLJOIN

Daily SalesNLJOIN

Period

NLJOIN

Product

NLJOIN

Daily Sales

NLJOIN

Period

NLJOIN

Store

HSJOIN

Daily Sales

HSJOIN

Period

HSJOIN

Product

StoreZZJOIN

Daily Sales

HSJOIN

Period


BigSQL V3.0 – PerformanceYou are substantially faster if you don't use MapReduce

IBM BigInsights v3.0, with Big SQL 3.0, is the only Hadoop distribution to successfully run ALL 99 TPC-DS queries and ALL 22 TPC-H queries without modification. Source: http://www.ibmbigdatahub.com/blog/big-deal-about-infosphere-biginsights-v30-big-sql


BigSQL V3.0 – Query Federation

Head Node

Big SQL

Compute Node

Task Tracker Data Node BigSQL

Compute Node

Task Tracker Data NodeBigSQL

Compute Node


Compute Node



BigSQL V1.0 – Demo (small)● 32 GB Data, ~650.000.000 rows (small, Innovation Center Zurich)● 3 TB Data, ~ 60.937.500.000 rows (middle, Innovation Center Zurich)● 0.7 PB Data, ~ 1.421875×10¹³ rows (large, Innovation Center Hursley)

● 32 GB Data, ~650.000.000 rows (small, Innovation Center Zurich)● 3 TB Data, ~ 60.937.500.000 rows (middle, Innovation Center Zurich)● 0.7 PB Data, ~ 1.421875×10¹³ rows (large, Innovation Center Hursley)


BigSQL V1.0 – Demo (small)CREATE EXTERNAL TABLE trace (

hour integer, employeeid integer,

departmentid integer, clientid integer,

date string, timestamp string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '/user/biadmin/32Gtest';


BigSQL V1.0 – Demo (small)


BigSQL V1.0 – Demo (small)[bivm.ibm.com][biadmin] 1> select count(*) from trace1;

+----------+

| |

+----------+

| 11416740 |

+----------+

1 row in results(first row: 39.78s; total: 39.78s)



select count(hour), hour from trace group by hour order by hour

30 rows in results(first row: 37.98s; total: 37.99s)



[bivm.ibm.com][biadmin] 1> select count(*) from trace1 t3 inner join trace2 t4 on t3.hour=t4.hour;

+--------+

| |

+--------+

| 477340 |

+--------+



BigSQL V3.0 – Demo (small)CREATE HADOOP TABLE trace3 (

hour int, employeeid int,

departmentid int,clientid int,

date varchar(30), timestamp varchar(30) )

row format delimited

fields terminated by '|'

stored as textfile;


BigSQL V3.0 – Demo (small)[bivm.ibm.com][biadmin] 1> select count(*) from trace3;

+----------+

| 1 |

+----------+

| 12014733 |

+----------+




[bivm.ibm.com][biadmin] 1> select count(*) from trace3 t3 inner join trace4 t4 on t3.hour=t4.hour;

+--------+

| 1 |

+--------+

| 504360 |

+--------+




[bivm.ibm.com][biadmin] 1> select count(hour), hour from trace3 group by hour order by hour;

29 rows in results(first row: 1.88s; total: 1.89s)


Questions?

http://www.ibm.com/software/data/bigdata/

BigInsights free VM and Installer for non-commercial use: ibm.co/quickstart

Twitter: @RomeoKienzler, @IBMEcosystem_DE, @IBM_ISV_Alps

http://www.ibm.com/software/data/bigdata/

sql on hadoop - 12th swiss big data user group meeting, 3rd of july, 2014, eth zurich

Data & Analytics