Hive CBO Accelerating Complex Queries John Pullokkaran 11192013

Hive CBO: Accelerating Complex Queries John Pullokkaran 11/19/2013 Page 1

CBO in Hive – Why? • Ease of Use • View Chaining • Ad hoc queries involving multiple views • Enables BI Tools front ending Hive © Hortonworks Inc. 2013 Page 2

CBO in Hive – How? Hive SQL Rewrite • Use Optiq CBO • Introduce transformation rules in Optiq • Convert Hive op tree to Optiq op tree • Optimize Optiq op tree • Convert optimized Optiq op tree back to Hive AST © Hortonworks Inc. 2013 Page 3

CBO in Hive – How? Optiq • Open source, Apache licensed, query execution framework implemented in Java • Used by: I. III. IV. Apache Cascade Apache Drill Lucid DB Sql. Stream • Based on Volcano paper & derived from Eigenbase Project • ~ 20 Man years of development • More than 50 optimization rules • Plan search space can be controlled © Hortonworks Inc. 2013 Page 4

CBO in Hive – How? Cost computation • Emphasis on latency reduction • Cost computation will be used for: Ø Join ordering Ø Join algorithm selection Ø Tez Vertex Boundary Selection • Shuffling cost is important • Cost formula uses, CPU, I/O and cardinality • I/O data size = Cardinality * avg size of tuple © Hortonworks Inc. 2013 Page 5

CBO in Hive – How? Cost computation Cont’d • I/O cost differentiates among: I. III. Network Local Disk HDFS • CPU cost and I/O Cost is normalized in to standard units of time • relation between CPU and various I/O cost is important © Hortonworks Inc. 2013 Page 6

CBO in Hive – How? Control Flow • “Semantic. Analyzer. analyze. Internal” calls into CBO optionally • CBO invocation conditional on lossless query tree translation. • All of the CBO code is contained in new packages • Optiq would pull in 3 new jars • Semantic. Analyzer. analyze. Internal” is called with CBO returned AST. © Hortonworks Inc. 2013 Page 7