VLDB 2014 Industrial Track Joins on Encoded and

VLDB 2014 Industrial Track Joins on Encoded and Partitioned Data Jae-Gil Lee 2* Gopi Attaluri 3 Ronald Barber 1 Naresh Chainani 3 Oliver Draese 3 Frederick Ho 5 Stratos Idreos 4* Min-Soo Kim 6* Sam Lightstone 3 Guy Lohman 1 Konstantinos Morfonios 8* Keshava Murthy 10* Ippokratis Pandis 7* Lin Qiao 9* Vijayshankar Raman 1 Vincent Kulandai Samy 3 Richard Sidle 1 Knut Stolze 3 Liping Zhang 3 1 IBM Almaden Research Center 2 KAIST, Korea 3 IBM Software Group 4 Harvard University 5 IBM Informix 6 DGIST, Korea 7 Cloudera 8 Oracle 9 Linked. In 10 Map. R * Work was done while the author was with IBM Almaden Research Center

Table of Contents Introduction Partitioning Column Domains Encoding Join Columns Encoding Non-Join Columns Experiment Results Conclusions 09/03/2014 2 Joins on Encoded and Partitioned Data

Blink Project Accelerator technology developed by IBM Almaden Research Center since 2007 Main features Storing a compressed copy of a (portion of a) data warehouse Exploiting (i) large main memories, (ii) commodity multi-core processors, and (iii) proprietary compression Improving the performance of typical business intelligence(BI) SQL queries by 10 to 100 times Not requiring the tuning of indexes, materialized views, etc. Products offered by IBM based upon. Blink Informix Warehouse. Accelerator: releasedon March 2011 IBM Smart Analytics Optimizer for DB 2 for z/OSV 1. 1 A predecessorto today’s IBM DB 2 Analytics Accelerator for DB 2 forz/OS 09/03/2014 3 Joins on Encoded and Partitioned Data

Informix Warehouse Accelerator(IWA) A main-memoryaccelerator to the disk-based Informix database server product, packaged asthe Informix Ultimate Warehouse Edition(IUWE) System Architecture 09/03/2014 Data Loading and Query Execution 4 Joins on Encoded and Partitioned Data

Main Features Related to Joins Performing joins directly on encoded data Join method: hash joins Encoding method: dictionary encoding Handling join columns encodeddifferently: encoding translation Partitioning a column to support incrementalupdates and achieve better compression: frequency partitioning Encoding non-join(payload) columns on the fly 09/03/2014 5 Joins on Encoded and Partitioned Data

Hash Joins Build phase Scan each dimension table, applying local predicates Hash to an empty bucket in the hash table Store the values of join columns as well as “payload” columns Probe phase Scan the fact table, applying local predicates Look up the hash table with the foreign key per dimension Retrieve the values of payload columns Example A simple join query between LINEITEM and ORDERS 09/03/2014 Dimension 6 Group by, Aggregation Look up the values of O_Order. Date Hash Table O_Order. Key Fact O_Order. Date σ(L_Order. Key IN …) σ(O_Order. Date …) σ(L_Ship. Date …) scan(ORDERS) scan(LINEITEM) Joins on Encoded and Partitioned Data

Dictionary Encoding A value of a column is replaced by an encoded value requiring only a few bits Example States California 10 bytes 000101 6 bits Alabama 000001 Alaska 000010 California 000101 Arizona 000011 000001 Arkansas 000100 California 000101 Arizona 000011 Colorado 000110 Arizona 000011 … … Alabama 09/03/2014 States Encoding 7 Dictionary Joins on Encoded and Partitioned Data

Table of Contents Introduction Partitioning Column Domains Encoding Join Columns Encoding Non-Join Columns Experiment Results Conclusions 09/03/2014 8 Joins on Encoded and Partitioned Data

Updates in Dictionary Encoding Option 1: leaving room for future values Downside : overestimation of the number of future values will waste bits; underestimation will require re-encoding all values to additional ones beyond the capacity Option 2: partitioning the domain and creating separate dictionaries for each partition our approach Upside: the impact of adding newvalues can be isolated from the dictionaries of any existing partitions New values are simply added to a partition that will be created on the fly, as values arrive We leave the values in that partitionunencoded 09/03/2014 9 Joins on Encoded and Partitioned Data

Frequency Partitioning Achieving better compression : approximate Huffman Defining fixed-lengthcodes within a partition China, USA: 1 bit EU: 5 bits Rest: 8 bits 1 M, 100 K, 10 K occurrences of each group Frequency partitioning= 1. 58 Mbits 8 bits for all countries= 8. 88 Mbits Sales vol prod origin China GER, USA FRA, … Rest origin Top 64 traded goods – 6 bit code Rest 09/03/2014 Column partitions 10 product Example Cell 1 Cell 3 Cell 4 Cell 2 Cell 5 Cell 6 Joins on Encoded and Partitioned Data

Catch-All Cell (1/2) 09/03/2014 11 Joins on Encoded and Partitioned Data

Catch-All Cell (2/2) Example Containing the 5 th and 6 th rows in unencoded form LINEITEM L_Order. Key Cell 0: K 0 X D 0 L_Order. Key L_Ship. Date 0 0 100 8/2/2010 0 1 200 9/4/2010 Cell 1: K 1 X D 0 Encoding 100 9/4/2010 0 1 300 8/2/2010 1 0 100 5/1/2010 Catch-All Cell unencodable 400 8/2/2010 100 5/1/2010 400 8/2/2010 Dictionary of LINEITEM 09/03/2014 L_Order. Key L_Ship. Date Partition K 0: 100 Partition K 1: 200 300 Partition D 0: 8/2/2010 9/4/2010 12 same value Joins on Encoded and Partitioned Data

Table of Contents Introduction Partitioning Column Domains Encoding Join Columns Encoding Non-Join Columns Experiment Results Conclusions 09/03/2014 13 Joins on Encoded and Partitioned Data

Joins on Encoded Values (1/2) 09/03/2014 Encoded using the same scheme 14 Joins on Encoded and Partitioned Data

Joins on Encoded Values(2/2) build probe Encoding Translation 09/03/2014 15 Joins on Encoded and Partitioned Data

Advantages of Per-Column. Encoding Better compression The ideal encoding for one column maynot be ideal for the other (see next page) Flexible reorganization Any tables sharing acommon dictionary are inextricably linked Ad hoc querying Which columns might be joined in a query maynot be known when the data is encoded 09/03/2014 16 Joins on Encoded and Partitioned Data

Better Compression of Skewed Data per-column per-domain 33~50% gain 21% gain 09/03/2014 17 Joins on Encoded and Partitioned Data

Encoding Translation Challenge Dealing with the multiple representations of the same valuecaused by the catch-all cell At least, one encoded and one unencoded Two variants DTRANS(Dimension TRANSlation) Resolving the multiple representationsin the dimension -table scan Reducing the overhead of the probe phase FTRANS(Fact TRANSlation) Resolving the multiple representationsduring the fact-table scan Reducing the overhead of the build phase 09/03/2014 18 Joins on Encoded and Partitioned Data

Encoding Translation: DTRANS 1 hash table per fact-table partition ORDERS Build Phase: O_Order. Key O_Order. Status 100 200 300 400 500 "S" "S" "R" HT[0] HT[1] HT[2] 0 0 1 100 200 300 400 Hash Tables Having all qualifying key values in unencoded form Partition 0 0 0 Partition 1 Probe Phase: Encodable Unencodable 09/03/2014 0 1 HT[0] HT[1] HT[2] 0 0 1 100 200 300 400 Direct Probes Catch-All Cell 100 400 Data Hash Tables 19 Joins on Encoded and Partitioned Data

Encoding Translation: FTRANS 1 hash table per fact-table partition ORDERS Build Phase: O_Order. Key O_Order. Status 100 200 300 400 500 "S" "S" "R" HT[0] HT[1] HT[2] 0 0 1 400 Hash Tables Having only unencodable key values Partition 0 0 0 Probe Phase: 09/03/2014 HT[1] HT[2] 0 0 1 400 0 1 Catch-All Cell 100 400 Data Encoding Encodable Unencodable Partition 1 HT[0] 0 Fail: 400 Hash Tables Testing encodability 20 Joins on Encoded and Partitioned Data

Table of Contents Introduction Partitioning Column Domains Encoding Join Columns Encoding Non-Join Columns Experiment Results Conclusions 09/03/2014 21 Joins on Encoded and Partitioned Data

On-the-Fly(OTF) Encoding (1/2) Reasons for encoding payloadcolumns The join key is usually just an integer, whereas the payloads are often wider strings higher impact of compression Benefits of the on-the-fly(OTF)encoding Updates: a mixture of encoded and unencoded payloads are hard to maintain using hash tables Expressions : the results of an expression, e. g. , MONTH(Ship. Date), can be encoded very compactly Correlation: correlated columns in a query, e. g. , City, State, ZIPCode, and Country, can be used to create a tighter code Predicates: local/join predicates will likely reduce the cardinality of each column, allowing a more compact representation 09/03/2014 22 Joins on Encoded and Partitioned Data

On-the-Fly(OTF) Encoding (2/2) Mechanism Use a mapping table that consists of a list of hash tables Return an index into the bucket where the value was inserted an OTF code The OTF code is not changed, even if the hash table isresized Example 600+1024+2048+40=3712 Size: 4096 Size: 600 Size: 1024 40 value Hash Tables Original Dictionary 09/03/2014 Size: 2048 23 Joins on Encoded and Partitioned Data

Table of Contents Introduction Partitioning Column Domains Encoding Join Columns Encoding Non-Join Columns Experiment Results Conclusions 09/03/2014 24 Joins on Encoded and Partitioned Data

Experimental Setting Five alternative configurations Name Description DTRANS Encoding translation during dimension query processing FTRANS Encoding translation during fact query processing DECODE Run-time decoding before joining 1 DICT Per-domain encoding, i. e. , using only one dictionary without encoding translation UNENCODED No encoding at all Data set and queries: a simplified TPC-H data set and queries Measure: time for (i) build phase, (ii) probe phase, and (iii) scan 09/03/2014 25 Joins on Encoded and Partitioned Data

Per-Domain vs. Per-Column DTRANS(per-column)outperforms: l DECODE in query performance l 1 DICT(per-domain)in compression ratio 09/03/2014 26 Joins on Encoded and Partitioned Data

When Does DTRANS Win? wall clock time (sec) DTRANS outperforms FTRANS when: l Dimension tables are small, OR l High ratio of rows are left unencoded Varying the dimension size 09/03/2014 Varying the ratio of unencoded rows 27 Joins on Encoded and Partitioned Data

Summary of the Results DTRANS or FTRANS outperform traditional DECODE for most cases by up to 40% of query performance DTRANS or FTRANS improve the compression ratio by atleast 16%( or up to 50% in skewed data), with negligible overheadin query processing, in comparison with having one dictionaryfor both join columns(1 DICT) DTRANS is preferred whendimension tables aresmall FTRANS is preferred whena fact table issmall or local predicates on a fact tableare very selective DTRANS is preferredwhen high ratio of unencoded rows 09/03/2014 28 Joins on Encoded and Partitioned Data

Table of Contents Introduction Partitioning Column Domains Encoding Join Columns Encoding Non-Join Columns Experiment Results Conclusions 09/03/2014 29 Joins on Encoded and Partitioned Data

Conclusions 09/03/2014 30 Joins on Encoded and Partitioned Data

Blink Refereed Publications Jae-Gil Lee et al. : Joins on Encoded and Partitioned Data. PVLDB 7(13): 1355 -1366 (2014) Vijayshankar Raman et al. : DB 2 with BLU Acceleration: So Much More than Just a Column Store. PVLDB 6(11): 1080 -1091 (2013) Lin Qiao, Vijayshankar Raman, Frederick Reiss, Peter J. Haas, Guy M. Lohman: Main-memory scan sharing for multi-core CPUs. PVLDB 1(1): 610 -621 (2008) Ryan Johnson, Vijayshankar Raman, Richard Sidle, Garret Swart: Row-wise parallel predicate evaluation. PVLDB 1(1): 622 -634 (2008) Vijayshankar Raman, Garret Swart, Lin Qiao, Frederick Reiss, Vijay Dialani, Donald Kossmann, Inderpal Narang, Richard Sidle: Constant-Time Query Processing. ICDE 2008: 60 -69 Allison L. Holloway, Vijayshankar Raman, Garret Swart, David J. De. Witt: How to barter bits for chronons: compression and bandwidth trade offs for database scans. SIGMOD Conference 2007: 389 -400 Vijayshankar Raman, Garret Swart: How to Wring a Table Dry: Entropy Compression of Relations and Querying of Compressed Relations. VLDB 2006: 858 -869 09/03/2014 31 Joins on Encoded and Partitioned Data

Thank You! Any Questions?