Elasticsearch for SQL users - OpenLate meetup July 2015

Transcript

1. ELASTICSEARCH FOR SQL USERS Shaunak Kashyap • @shaunak
2. WHY USE ELASTICSEARCH?
3. Full text search
4. Structured search
5. Faceted navigation
6. Analytics
7. BUT… WHAT IS IT? Elasticsearch is a distributed, open source search and analytics engine, designed for horizontal scalability, reliability, and easy management
8. CONCEPTS Document The unit of data fed into Elasticsearch, in JSON format Index A collection of documents stored in Elasticsearch Type The category of the document within an index { " f r o m " : " t o n y @ s t a r k . c o m " , " t o " : " h u l k @ a v e n g e r s . o r g " , " s u b j e c t " : " S c e p t e r g e m - > U l t r o n - W D Y T ? ? " , " b o d y " : " H e y g r e e n g u y , s o I h a v e t h i s i d e a . . . " }
9. CONCEPTS — COMPARISON RDBMS Elasticsearch Example Database Index e m a i l s Table Type m e s s a g e s Row Document (an email message) Column aka Field Field s e n d e r
10. CREATE AN EMAIL SQL I N S E R T I N T O e m a i l s . m e s s a g e s ( s e n d e r , r e c i p i e n t , s u b j e c t , b o d y ) V A L U E S ( " t o n y @ s t a r k . c o m " , " h u l k @ a v e n g e r s . o r g " , " S c e p t e r g e m - > U l t r o n - W D Y T ? ? " , " H e y g r e e n g u y , s o I h a v e t h i s i d e a . . . " ) ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s - d ' { " s e n d e r " : " t o n y @ s t a r k . c o m " , " r e c i p i e n t " : " h u l k @ a v e n g e r s . o r g " , " s u b j e c t " : " S c e p t e r g e m - > U l t r o n - W D Y T ? ? " , " b o d y " : " H e y g r e e n g u y , s o I h a v e t h i s i d e a . . . " } '
11. RETRIEVE AN EMAIL SQL S E L E C T * F R O M e m a i l s . m e s s a g e s W H E R E i d = : i d ELASTICSEARCH c u r l - X G E T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / : i d
12. UPDATE AN EMAIL SQL U P D A T E e m a i l s . m e s s a g e s S E T s u b j e c t = " T o p S e c r e t - C a l l m e ! ! ! " W H E R E i d = : i d ELASTICSEARCH — OPTION 1 c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / : i d / _ u p d a t e { " d o c " : { " s u b j e c t " : " T o p S e c r e t - C a l l m e ! ! ! " } } ELASTICSEARCH — OPTION 2 c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / : i d / _ u p d a t e { " s c r i p t " : " c t x . _ s o u r c e . s u b j e c t = ' T o p S e c r e t - C a l l m e ! ! ! ' " }
13. DELETE AN EMAIL SQL D E L E T E F R O M e m a i l s . m e s s a g e s W H E R E i d = : i d ELASTICSEARCH c u r l - X D E L E T E h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / : i d
15. SEARCH FOR A SINGLE TERM IN A SINGLE FIELD SQL S E L E C T * F R O M e m a i l s W H E R E b o d y L I K E " % h a v e % " ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / _ s e a r c h { " q u e r y " : { " m a t c h " : { " b o d y " : " h a v e " } } }
16. SEARCH FOR A MULTIPLE TERMS IN A SINGLE FIELD SQL S E L E C T * F R O M e m a i l s W H E R E b o d y L I K E " % g r e e n % " O R b o d y L I K E " % i d e a % " ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / _ s e a r c h { " q u e r y " : { " m a t c h " : { " b o d y " : " g r e e n i d e a " } } }
17. SEARCH FOR A PHRASE IN A SINGLE FIELD SQL S E L E C T * F R O M e m a i l s W H E R E b o d y L I K E " % h a v e t h i s i d e a % " ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / _ s e a r c h { " q u e r y " : { " m a t c h _ p h r a s e " : { " b o d y " : " h a v e t h i s i d e a " } } }
18. SEARCH SLOPPILY FOR A PHRASE IN A SINGLE FIELD SQL S E L E C T * F R O M e m a i l s W H E R E b o d y L I K E " % h a v e % i d e a % " ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / _ s e a r c h { " q u e r y " : { " m a t c h _ p h r a s e " : { " b o d y " : { " q u e r y " : " h a v e i d e a " , " s l o p " : 1 } } } }
19. SEARCH FOR TERMS ACROSS MULTIPLE FIELDS SQL S E L E C T * F R O M e m a i l s W H E R E s u b j e c t L I K E " % u l t r o n % " O R s u b j e c t L I K E " % i d e a % " O R b o d y L I K E " % u l t r o n % " O R b o d y L I K E " % i d e a % " ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / _ s e a r c h { " q u e r y " : { " m u l t i _ m a t c h " : { " f i e l d s " : [ " s u b j e c t " , " b o d y " ] , " q u e r y " : " u l t r o n i d e a " } } }
20. SEARCH FOR TERMS ACROSS ALL FIELDS SQL S E L E C T * F R O M e m a i l s W H E R E s e n d e r L I K E " % h u l k % " O R s e n d e r L I K E " % u l t r o n % " O R r e c i p i e n t L I K E " % h u l k % " O R r e c i p i e n t L I K E " % u l t r o n % " O R s u b j e c t L I K E " % h u l k % " O R s u b j e c t L I K E " % u l t r o n % " O R b o d y L I K E " % h u l k % " O R b o d y L I K E " % u l t r o n % " ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / e m a i l s / m e s s a g e s / _ s e a r c h { " q u e r y " : { " m a t c h " : { " _ a l l " : " h u l k u l t r o n " } } }
21. SEARCH FOR AN EXACT MATCH (FIELD = VALUE) SQL S E L E C T * F R O M c e n s u s . p e o p l e W H E R E c i t y = " S a n F r a n c i s c o " ; ELASTICSEARCH — TERM QUERY c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / c e n s u s / p e o p l e / _ s e a r c h { " q u e r y " : { " t e r m " : { " c i t y " : " S a n F r a n c i s c o " } } } ELASTICSEARCH — TERM FILTER c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / c e n s u s / p e o p l e / _ s e a r c h { " q u e r y " : { " f i l t e r e d " : { " f i l t e r " : { " t e r m " : { " c i t y " : " S a n F r a n c i s c o " } } } } }
22. AGGREGATIONS SQL S E L E C T c i t y , g e n d e r , A V G ( a g e ) F R O M c e n s u s . p e o p l e G R O U P B Y c i t y , g e n d e r ; ELASTICSEARCH c u r l - X P O S T h t t p : / / l o c a l h o s t : 9 2 0 0 / c e n s u s / p e o p l e / _ s e a r c h { " a g g r e g a t i o n s " : { " b y _ c i t y " : { " t e r m s " : { " f i e l d " : " c i t y " } , " a g g r e g a t i o n s " : { " b y _ g e n d e r " : { " t e r m s " : { " f i e l d " : " g e n d e r " } , " a g g r e g a t i o n s " : { " a v e r a g e _ a g e " : { " a v g " : { " f i e l d " : " a g e " } } } } }
23. THANK YOU https://discuss.elastic.co/