Secondary Indexing in Phoenix LA HBase User Group

Secondary Indexing in Phoenix LA HBase User Group – September 4, 2013 Jesse Yates HBase Committer Software Engineer

Agenda • About • Other Indexing Frameworks • Immutable Indexes • Mutable Indexes in Phoenix • Mutable Indexing Internals • Roadmap 2 LA HUG – Sept 2013 https: //www. madison. k 12. wi. us/calendars

About me • Developer at Salesforce – System of Record, Phoenix • Open Source – Phoenix – HBase – Accumulo 3 LA HUG – Sept 2013

Phoenix • Open Source – https: //github. com/forcedotcom/phoenix • “SQL-skin” on HBase – Everyone knows SQL! • JDBC Driver – Plug-and-play • Faster than HBase – in some cases 4 LA HUG – Sept 2013

Why Index? • HBase is only sorted on 1 “axis” • Great for search via a single pattern Example! 5 LA HUG – Sept 2013

Example name: type: subtype: date: major: minor: quantity: 6 LA HUG – Sept 2013

Secondary Indexes • Sort on ‘orthogonal’ axis • Save full-table scan • Expected database feature • Hard in HBase b/c of ACID considerations 7 LA HUG – Sept 2013

Agenda • About • Other Indexing Frameworks • Immutable Indexes • Mutable Indexes in Phoenix • Mutable Indexing Internals • Roadmap 8 LA HUG – Sept 2013

http: //www. wired. com/wiredenterprise/2011/10/microsoft-and-hadoop/ 9 LA HUG – Sept 2013

Other (Major) Indexing Frameworks • HBase SEP – Side-Effects Processor – Replication-based – https: //github. com/NGDATA/hbase-sep • Huawei – Server-local indexes – Buddy regions – https: //github. com/Huawei-Hadoop/hindex 10 LA HUG – Sept 2013

Agenda • About • Other Indexing Frameworks • Immutable Indexes • Mutable Indexes in Phoenix • Mutable Indexing Internals • Roadmap 11 LA HUG – Sept 2013

Immutable Indexes • Immutable Rows • Much easier to implement • Client-managed • Bulk-loadable 12 LA HUG – Sept 2013

Bulk Loading phoenix-hbase. blogspot. com 13 LA HUG – Sept 2013

Index Bulk Loading Identity Mapper Custom Phoenix Reducer HFile Output Format 14 LA HUG – Sept 2013

Index Bulk Loading Prepared. Statement statement = conn. prepare. Statement(dml. Statement); statement. execute(); String upsert. Stmt = "upsert into core. entity_history(organization_id, key_prefix, entity_history_id, created_by, created_date)n" + "values(? , ? , ? )"; statement = conn. prepare. Statement(upsert. Stmt); … //set values Iterator<Pair<byte[], List<Key. Value>>> data. Iterator = Phoenix. Runtime. get. Uncommitted. Data. Iterator(conn); 15 LA HUG – Sept 2013

Agenda • About • Other Indexing Frameworks • Immutable Indexes • Mutable Indexes in Phoenix • Mutable Indexing Internals • Roadmap 16 LA HUG – Sept 2013

The “fun” stuff… 17 LA HUG – Sept 2013

1. 5 years 18 LA HUG – Sept 2013

Mutable Indexes • Global Index • Change row state – Common use-case – “expected” implementation • Covered Columns 19 LA HUG – Sept 2013

Usage • Just SQL! • Baby name popularity • Mock demo 20 LA HUG – Sept 2013

Usage • Selects the most popular name for a given year SELECT name, occurrences FROM baby_names WHERE year=2012 LIMIT 1; • Selects the total occurrences of a given name across all years SELECT /*+ NO_INDEX */ name, sum(occurrences) FROM baby_names WHERE name='Jesse' GROUP BY name; • Selects the total occurrences of a given name across all years allowing an index to be used SELECT name, sum(occurrences) FROM baby_names WHERE name='Jesse' GROUP BY NAME; 21 LA HUG – Sept 2013

Usage • Update rows due to census inaccuracy – Will only work if the mutable indexing is working UPSERT INTO baby_names SELECT year, occurrences+3000, sex, name FROM baby_names WHERE name='Jesse'; • Selects the now updated data (from the index table) SELECT name, sum(occurrences) FROM baby_names WHERE name='Jesse' GROUP BY NAME; • Index table still used in scans EXPLAIN SELECT name, sum(occurrences) FROM baby_names WHERE name='Jesse' GROUP BY NAME; 22 LA HUG – Sept 2013

Agenda • About • Other Indexing Frameworks • Immutable Indexes • Mutable Indexes in Phoenix • Mutable Indexing Internals • Roadmap 23 LA HUG – Sept 2013

Internals • Index Management – Build index updates – Ensures index is ‘cleaned up’ • Recovery Mechanism – Ensures index updates are “ACID” 24 LA HUG – Sept 2013

“There is no magic” - Every programming hipster (chipster) 25 LA HUG – Sept 2013

Mutable Indexing: Standard Write Path Client HRegion. Coprocessor. Host WAL Region. Coprocessor. Host Mem. Store 26 LA HUG – Sept 2013

Mutable Indexing: Standard Write Path Client HRegion. Coprocessor. Host WAL Region. Coprocessor. Host Mem. Store 27 LA HUG – Sept 2013

Mutable Indexing Region Coprocessor Host Indexer Builder Codec WAL Updater WAL Region Coprocessor Host 28 Durable! Indexer LA HUG – Sept 2013 Index Table

Index Management • Lives within a Region. Coprocesor. Observer • Access to the local HRegion • Specifies the mutations to apply to the index tables public interface Index. Builder { public void setup(Region. Coprocessor. Environment env); public Map<Mutation, String> get. Index. Update(Put put); public Map<Mutation, String> get. Index. Update(Delete delete); } 29 LA HUG – Sept 2013

Why not write my own? • Managing Cleanup – Efficient point-in-time correctness – Performance tricks • Abstract access to HRegion – Minimal network hops • Sorting correctness – Phoenix typing ensures correct index sorting 30 LA HUG – Sept 2013

Example: Managing Cleanup • Updates can arrive out of order – Client-managed timestamps 31 ROW FAMILY QUALIFIER TS VALUE Row 1 Fam Qual 10 val 1 Row 1 Fam 2 Qual 2 12 val 2 Row 1 Fam Qual 13 val 3 LA HUG – Sept 2013

Example: Managing Cleanup Row 1 33 Fam Qual 11 val 4 ROW FAMILY QUALIFIER TS VALUE Row 1 Fam Qual 10 val 1 Row 1 Fam 2 Qual 2 12 val 2 Row 1 Fam Qual 13 val 3 LA HUG – Sept 2013

Example: Managing Cleanup 34 ROW FAMILY QUALIFIER TS VALUE Row 1 Fam Qual 10 val 1 Row 1 Fam Qual 11 val 4 Row 1 Fam 2 Qual 2 12 val 2 Row 1 Fam Qual 13 val 3 LA HUG – Sept 2013

Managing Cleanup • • • History “roll up” Out-of-order Updates Point-in-time correctness Multiple Timestamps per Mutation Delete vs. Delete. Column vs. Delete. Family Surprisingly hard! 37 LA HUG – Sept 2013

Phoenix Index Builder • Much simpler than full index management • Hides cleanup considerations • Abstracted access to local state public interface Index. Codec{ public void initialize(Region. Coprocessor. Environment env); public Iterable<Index. Update> get. Index. Deletes(Table. State state; public Iterable<Index. Update> get. Index. Upserts(Table. State state); } 38 LA HUG – Sept 2013

Phoenix Index Codec 39 LA HUG – Sept 2013

Dude, where’s my data? Ensuring Correctness 40 LA HUG – Sept 2013

HBase ACID • Does NOT give you: – Cross-row consistency – Cross-table consistency • Does give you: – Durable data on success – Visibility on success without partial rows 41 LA HUG – Sept 2013

Key Observation “Secondary indexing is inherently an easier problem than full transactions… secondary index updates are idempotent. ” - Lars Hofhansl 42 LA HUG – Sept 2013

Idempotent Index Updates • Doesn’t need full transactions • Replay as many times as needed • Can tolerate a little lag – As long as we get the order right 43 LA HUG – Sept 2013

Failure Recovery • Custom WALEdit. Codec – Encodes index updates – Supports compressed WAL • Custom WAL Reader – Replay index updates from WAL <property> <name>hbase. regionserver. wal. codec</name> <value>o. a. h. hbase. regionserver. wal. Indexed. WALEdit. Codec</value> </property> <name>hbase. regionserver. hlog. reader. impl</name> <value>o. a. h. hbase. regionserver. wal. Indexed. HLog. Reader</value> </property> 44 LA HUG – Sept 2013

Failure Situations • Any time before WAL, client replay • Any time after WAL, HBase replay • All-or-nothing 45 LA HUG – Sept 2013

Failure #1: Before WAL Client HRegion. Coprocessor. Host WAL Region. Coprocessor. Host Mem. Store 46 LA HUG – Sept 2013

Failure #1: Before WAL Client HRegion. Coprocessor. Host WAL Region. Coprocessor. Host Mem. Store 47 LA HUG – Sept 2013 No problem! No data is stored in the WAL, client just retries entire update.

Failure #2: After WAL Client HRegion. Coprocessor. Host WAL Region. Coprocessor. Host Mem. Store 48 LA HUG – Sept 2013

Failure #2: After WAL Client HRegion. Coprocessor. Host WAL Region. Coprocessor. Host Mem. Store 49 LA HUG – Sept 2013 WAL replayed via usual replay mechanisms

Agenda • About • Other Indexing Frameworks • Immutable Indexes • Mutable Indexes • Roadmap 50 LA HUG – Sept 2013

Roadmap • Next release of Phoenix • Performance testing • Increased adoption • Adding to HBase (? ) 51 LA HUG – Sept 2013

Open Source! • Main: https: //github. com/forcedotcom/phoenix • Indexing: https: //github. com/forcedotcom/phoenix/tree/mutable-si 52 LA HUG – Sept 2013

We’re Hiring! (obligatory hiring slide)

Questions? Comments? jyates@salesforce. com @jesse_yates