Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Elasticsearch for SQL Users
Search
Elastic Co
March 30, 2016
Programming
1
520
Elasticsearch for SQL Users
As given at Code PaLOUsa 2016
Elastic Co
March 30, 2016
Tweet
Share
More Decks by Elastic Co
See All by Elastic Co
Les Vendredis noirs : même pas peur ! - Breizhcamp
elastic
15
970
Confoo Montreal: Ingest node: enriching documents within Elasticsearch
elastic
16
940
Elastic{ON} 2018 - Sipping from the Firehose: Scalable Endpoint Data for Incident Response
elastic
6
4.3k
Elastic{ON} 2018 - A Security Analytics Platform for Today
elastic
3
11k
Elastic{ON} 2018 - The State of Geo in Elasticsearch
elastic
7
12k
Elastic{ON} 2018 - Reliable by design - Applying formal methods to distributed systems
elastic
5
4.8k
Elastic{ON} 2018 - Bigger, Faster, Stronger - Leveling Up Enterprise Logging
elastic
1
5k
Elastic{ON} 2018: Latest in Logstash
elastic
1
4.6k
Elastic{ON} 2018 - Lessons Learned from Workday's Search Application Journey from POC to Production
elastic
2
2.5k
Other Decks in Programming
See All in Programming
ソフトウェアテスト徹底指南書の紹介
goyoki
1
150
パッケージ設計の黒魔術/Kyoto.go#63
lufia
3
430
時間軸から考えるTerraformを使う理由と留意点
fufuhu
15
4.6k
MCPでVibe Working。そして、結局はContext Eng(略)/ Working with Vibe on MCP And Context Eng
rkaga
5
2.2k
私の後悔をAWS DMSで解決した話
hiramax
4
210
HTMLの品質ってなんだっけ? “HTMLクライテリア”の設計と実践
unachang113
4
2.7k
プロポーザル駆動学習 / Proposal-Driven Learning
mackey0225
2
1.2k
MCPとデザインシステムに立脚したデザインと実装の融合
yukukotani
4
1.4k
アルテニア コンサル/ITエンジニア向け 採用ピッチ資料
altenir
0
100
2025 年のコーディングエージェントの現在地とエンジニアの仕事の変化について
azukiazusa1
22
11k
Kiroで始めるAI-DLC
kaonash
2
580
The Past, Present, and Future of Enterprise Java
ivargrimstad
0
320
Featured
See All Featured
Side Projects
sachag
455
43k
How GitHub (no longer) Works
holman
315
140k
Java REST API Framework Comparison - PWX 2021
mraible
33
8.8k
What’s in a name? Adding method to the madness
productmarketing
PRO
23
3.7k
It's Worth the Effort
3n
187
28k
How STYLIGHT went responsive
nonsquared
100
5.8k
Embracing the Ebb and Flow
colly
87
4.8k
Git: the NoSQL Database
bkeepers
PRO
431
66k
BBQ
matthewcrist
89
9.8k
Writing Fast Ruby
sferik
628
62k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
30
9.6k
Transcript
1 Shaunak Kashyap Developer Advocate at Elastic @shaunak Elasticsearch for
SQL users
The Elastic Stack 2 Store, Index & Analyze User Interface
Plugins Ingest Hosted Service
3 Agenda Search queries Data modeling Architecture 1 2 3
2 4 Agenda Search queries Data modeling Architecture 1 3
5 Agenda Search queries Data modeling 1 2 3 Architecture
6 Search Queries https://www.flickr.com/photos/samhames/4422128094
7 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); 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 Loading the data
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
10 Other Search Features Stemming Synonyms Did you mean? •
Jump, jumped, jumping • Queen, monarch • Monetery => Monetary
11 Data Modeling https://www.flickr.com/photos/samhames/4422128094 https://www.flickr.com/photos/ericparker/7854157310
12 To analyze or not to analyze? PUT cities/city/1 {
"city": "Louisville", "population": 597337 } 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 To analyze or not to analyze? PUT cities/city/1 {
"city": "Louisville", "population": 597337 } 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 Louisville 1 York 3
14 To analyze or not to analyze? PUT cities {
"mappings": { "city": { "properties": { "city": { "type": "string", "index": "not_analyzed" } } } } } MAPPING Term Document IDs New Albany 2 New York 3 Louisville 1
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
PUT blog/post/1 { "author_name": "John Doe", "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 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
18 Relationships: Parent-child documents PUT blog/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 Architecture https://www.flickr.com/photos/samhames/4422128094 https://www.flickr.com/photos/haribote/4871284379/
20 RDBMS Triggers database by Creative Stall from the Noun
Project 1 2
21 Async replication to Elasticsearch 1 2 3 ESSynchronizer flow
by Yamini Ahluwalia from the Noun Project
22 Async replication to Elasticsearch with Logstash 1 2 3
23 Forked writes from application 1 2
24 Forked writes from application (more robust) 1 2 queue
by Huu Nguyen from the Noun Project ESSynchronizer 3 4
25 Forked writes from application (more robust with Logstash) 1
2 3 4
26 Questions? @shaunak https://www.flickr.com/photos/nicknormal/2245559230/