Elasticsearch for SQL Users

Most developers are familiar with relational databases and its query language, SQL. We know how to model our data so it can fit the relational paradigm and we know how to query this relational data using SQL. Elasticsearch is a document store with its own query DSL. In this talk we will look at several well-understood concepts and SQL queries from the relational paradigm and map these to their Elasticsearch equivalents. If you are someone with a solid grasp of the relational world and looking to map your mental model to the document-oriented search world, this talk is for you!

Transcript

1. Elasticsearch for SQL Users Philipp Krenn̴̴̴̴̴̴@xeraa
2. Infrastructure | Developer Advocate
3. ViennaDB Papers We Love Vienna
4. Agenda Ecosystem Architecture Queries Schema Conclusion
5. Ecosystem
6. You Know, for Search
9. ELK Stack
14. Elastic Stack
17. Architecture
18. Apache Lucene Library Index, store, search
19. Elasticsearch Distribution REST Query DSL
20. Index Type Document
21. Cluster Index Shard & Replica
22. Topology
23. Write
24. Immutable Segments
25. Immutable Segments
26. Schema-Free Is a Lie! Dynamic mapping
27. CREATE TABLE IF NOT EXISTS emails ( sender VARCHAR(255) NOT NULL, recipients TEXT, cc TEXT, bcc TEXT, subject VARCHAR(1024), body MEDIUMTEXT, datetime DATETIME ); CREATE INDEX emails_sender ON emails(sender); CREATE FULLTEXT INDEX emails_subject ON emails(subject); CREATE FULLTEXT INDEX emails_body ON emails(body);
28. PUT /communication { "mappings": { "email": { "properties": { "sender": { "type": "keyword" }, "recipients": { "type": "keyword" }, "cc": { "type": "keyword" }, "bcc": { "type": "keyword" }, "subject": { "type": "text", "analyzer": "english" }, "body": { "type": "text", "analyzer": "english" } } } } }
29. Beware of the Reindex
30. Queries
31. Let's Add Some Data POST /communication/email { "sender": "david@elastic.co", "recipients": [ "philipp@elastic.co" ], "cc": [], "subject": "Elasticsearch is pretty cool", "body": "Hey Philipp, this is great stuff. Check it out!" }
32. Let's Add Some Data POST /communication/email { "sender": "philipp@elastic.co", "recipients": [ "shay@elastic.co" ], "cc": [ "david@elastic.co" ], "subject": "Thanks for creating Elasticsearch", "body": "David pointed me to your project. It's awesome" }
33. Let's Add Some Data POST /communication/email { "sender": "david@elastic.co", "recipients": [ "shay@elastic.co" ], "cc": [], "subject": "We should hire Philipp", "body": "He is really into the project and will do great things." }
34. Get All the Data POST /communication/_search { "query": { "match_all": { } } }
35. Find great POST /communication/_search { "query": { "match": { "body": "great" } } }
36. text vs keyword text (default) is analyzed keyword is not
37. Analyzed Text PUT /cities/city/1 { "city": "Sandusky", "population": 25340 } PUT /cities/city/2 { "city": "New Albany", "population": 8829 } PUT /cities/city/3 { "city": "New York", "population": 8406000 }
38. Analyzed Text Stop words, stemming, synonyms, fuzzying,...
39. Analyzed Text POST /cities/_search { "query": { "match": { "city": "New Albany" } } }
40. Not Analyzed Text DELETE /cities PUT /cities { "mappings": { "city": { "properties": { "city": { "type": "keyword" } } } } }
41. Not Analyzed Text POST /cities/_search { "query": { "match": { "city": "New Albany" } } }
42. Search for great stuff POST /communication/_search { "query": { "match": { "body": "great stuff" } } }
43. Search for the Phrase great stuff POST /communication/_search { "query": { "match_phrase": { "body": "great stuff" } } }
44. Multiple Fields POST /communication/_search { "query": { "multi_match": { "query": "Philipp", "fields": [ "subject", "body" ] } } }
45. Boosting Positive (>1) or negative (<1) POST /communication/_search { "query": { "multi_match": { "query": "Philipp", "fields": [ "subject^3", "body" ] } } }
46. Fuzziness POST /communication/_search { "query": { "match": { "body": { "query": "awsome", "fuzziness": 1 } } } }
47. Lots More Boolean (must, must_not, should, minimum_should_match) Highlighting Search and scroll Geo
48. (Pipelined) Aggregations Example: Group by day, sum, moving average
49. Schema
50. Application-Side Joins PUT /blog/author/1 { "name": "Philipp", "bio": "..." } PUT /blog/post/1 { "author_id": 1, "title": "...", "body": "..." } PUT /blog/post/2 { "author_id": 1, "title": "...", "body": "..." }
51. Application-Side Joins POST /blog/author/_search { "query": { "match": { "name": "Philipp" } } } POST /blog/post/_search { "query": { "match": { "author_id": <each id from query 1 result> } } }
52. Data Denormalization PUT /blog/post/1 { "author_name": "Philipp", "title": "...", "body": "..." } PUT /blog/post/2 { "author_name": "Philipp", "title": "...", "body": "..." }
53. Data Denormalization POST /blog/post/_search { "query": { "match": { "author_name": "Philipp" } } }
54. Nested Objects PUT /blog/author/1 { "name": "Philipp", "bio": "...", "blog_posts": [ { "title": "...", "body": "..." }, { "title": "...", "body": "..." }, { "title": "...", "body": "..." } ] }
55. Nested Objects POST /blog/author/_search { "query": { "match": { "name": "Philipp" } } }
56. Parent-Child Documents PUT /blog { "mappings": { "author": {}, "post": { "_parent": { "type": "author" } } } }
57. Parent-Child Documents PUT /blog/author/1 { "name": "Philipp", "bio": "..." } PUT /blog/post/1?parent=1 { "title": "...", "body": "..." } PUT /blog/post/2?parent=1 { "title": "...", "body": "..." }
58. Parent-Child Documents POST /blog/post/_search { "query": { "has_parent": { "type": "author", "query": { "match": { "name": "Philipp" } } }
59. Conclusion
60. Many Queries Search, boolean aggregate, geo,...
61. text vs keyword
62. Schema Application, denormalization, nesting, parent-child
63. Getting Data from Your RDBMS RDBMS trigger Logstash Forked writes from your application
64. Can I use it as a primary datastore? It depends https://www.elastic.co/guide/en/elasticsearch/ resiliency/current/index.html
65. How fast is it?
66. Datastore, logs, metrics, analytics,...
68. More https://www.elastic.co/training Amsterdam: Nov 21-24
69. Thanks! Questions? Philipp Krenn̴̴̴@xeraa PS: Stickers
70. Elk horn: https://www.theexplora.com/the-irish-elk- megaloceros-giganteus/