Downloaded 16 times
![9
[LIVE DEMO]
• Search for text in a single field
• Search for text in multiple fields
• Search for a phrase
https://github.com/ycombinator/es-enron](https://image.slidesharecdn.com/gwoelasticsearchforsqlusers1-160413130928/75/Elasticsearch-for-SQL-Users-9-2048.jpg)
![17
Relationships: Nested objects
PUT blog/author/1
{
"name": "John Doe",
"bio": "...",
"blog_posts": [
{
"title": "...",
"body": "..."
},
{
"title": "...",
"body": "..."
},
{
"title": "...",
"body": "..."
}
]
}
POST blog/author/_search
{
"query": {
"match": {
"name": "John"
}
}
}
QUERY](https://image.slidesharecdn.com/gwoelasticsearchforsqlusers1-160413130928/75/Elasticsearch-for-SQL-Users-17-2048.jpg)
Uploaded byGreat Wide Open
Elasticsearch for SQL Users
This document discusses using Elasticsearch for SQL users. It covers search queries, data modeling, and architecture approaches. The agenda includes search queries, data modeling, and architecture. A live demo shows searching a single field, multiple fields, and phrases. Data modeling discusses analyzing or not analyzing fields. Relationships can be modeled through application joins, data denormalization, nested objects, or parent-child documents. Architecture approaches include using triggers, asynchronous replication, and forked writes from applications with or without Logstash.
More Related Content
Similar to Elasticsearch for SQL Users
Elasticsearch for SQL Users
- 1. 1 Shaunak Kashyap Developer Advocateat Elastic @shaunak Elasticsearch for SQL users
- 2. 2 The Elastic Stack ElasticsearchStore,Index & Analyze KibanaUser Interface SecurityMonitoring AlertingPlugins Logstash BeatsIngest Elastic Cloud: Elasticsearch as a ServiceHosted Service
- 3.
- 4.
- 5. 5 Agenda Search queries Data modeling 1 2 3Architecture
- 6.
- 7. 7 CREATE TABLE IFNOT 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); curl -XPOST 'http://localhost:9200/enron' -d' { "mappings": { "email": { "properties": { "sender": { "type": "string", "index": "not_analyzed" }, "recipients": { "type": "string", "index": "not_analyzed" }, "cc": { "type": "string", "index": "not_analyzed" }, "bcc": { "type": "string", "index": "not_analyzed" }, "subject": { "type": "string", "analyzer": "english" }, "body": { "type": "string", "analyzer": "english" } } } } Schemas
- 8.
- 9. 9 [LIVE DEMO] • Searchfor text in a single field • Search for text in multiple fields • Search for a phrase https://github.com/ycombinator/es-enron
- 10. 10 Other Search Features StemmingSynonyms Did you mean? • Jump, jumped, jumping • Queen, monarch • Monetery => Monetary
- 11.
- 12. 12 To analyze ornot to analyze? PUT cities/city/1 { "city": "Atlanta", "population": 447841 } PUT cities/city/2 { "city": "New Albany", "population": 8829 } PUT cities/city/3 { "city": "New York", "population": 8406000 } POST cities/_search { "query": { "match": { "city": "New Albany" } } } QUERY + = ?
- 13. 13 To analyze ornot to analyze? PUT cities/city/1 { "city": "Atlanta", "population": 447841 } PUT cities/city/2 { "city": "New Albany", "population": 8829 } PUT cities/city/3 { "city": "New York", "population": 8406000 } Term Document IDs Albany 2 New 2,3 Atlanta 1 York 3
- 14. 14 To analyze ornot to analyze? PUT cities { "mappings": { "city": { "properties": { "city": { "type": "string", "index": "not_analyzed" } } } } } MAPPING Term Document IDs New Albany 2 New York 3 Atlanta 1
- 15. PUT blog/post/1 { "author_id": 1, "title":"...", "body": "..." } PUT blog/post/2 { "author_id": 1, "title": "...", "body": "..." } PUT blog/post/3 { "author_id": 1, "title": "...", "body": "..." } 15 Relationships: Application-side joins PUT blog/author/1 { "name": "John Doe", "bio": "..." } POST blog/author/_search { "query": { "match": { "name": "John" } } } QUERY 1 POST blog/post/_search { "query": { "match": { "author_id": <each id from query 1 result> } } } QUERY 2
- 16. PUT blog/post/1 { "author_name": "JohnDoe", "title": "...", "body": "..." } PUT blog/post/2 { "author_name": "John Doe", "title": "...", "body": "..." } 16 Relationships: Data denormalization POST blog/post/_search { "query": { "match": { "author_name": "John" } } } QUERY PUT blog/post/3 { "author_name": "John Doe", "title": "...", "body": "..." }
- 17. 17 Relationships: Nested objects PUTblog/author/1 { "name": "John Doe", "bio": "...", "blog_posts": [ { "title": "...", "body": "..." }, { "title": "...", "body": "..." }, { "title": "...", "body": "..." } ] } POST blog/author/_search { "query": { "match": { "name": "John" } } } QUERY
- 18. 18 Relationships: Parent-child documents PUTblog/author/1 { "name": "John Doe", "bio": "..." } POST blog/post/_search { "query": { "has_parent": { "type": "author", "query": { "match": { "name": "John" } } } QUERY PUT blog { "mappings": { "author": {}, "post": { "_parent": { "type": "author" } } } } PUT blog/post/1?parent=1 { "title": "...", "body": "..." } PUT blog/post/2?parent=1 { "title": "...", "body": "..." } PUT blog/post/3?parent=1 { "title": "...", "body": "..." }
- 19.
- 20. 20 RDBMS Triggers database byCreative Stall from the Noun Project 1 2
- 21. 21 Async replication toElasticsearch 1 2 3 ESSynchronizer flow by Yamini Ahluwalia from the Noun Project
- 22. 22 Async replication toElasticsearch with Logstash 1 2 3
- 23. 23 Forked writes fromapplication 1 2
- 24. 24 Forked writes fromapplication (more robust) 1 2 queue by Huu Nguyen from the Noun Project ESSynchronizer3 4
- 25. 25 Forked writes fromapplication (more robust with Logstash) 1 2 3 4
- 26.