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
950
Confoo Montreal: Ingest node: enriching documents within Elasticsearch
elastic
16
930
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.4k
Other Decks in Programming
See All in Programming
“いい感じ“な定量評価を求めて - Four Keysとアウトカムの間の探求 -
nealle
0
230
Rubyでやりたい駆動開発 / Ruby driven development
chobishiba
1
510
Modern Angular with Signals and Signal Store:New Rules for Your Architecture @enterJS Advanced Angular Day 2025
manfredsteyer
PRO
0
160
PicoRuby on Rails
makicamel
2
110
すべてのコンテキストを、 ユーザー価値に変える
applism118
2
980
『自分のデータだけ見せたい!』を叶える──Laravel × Casbin で複雑権限をスッキリ解きほぐす 25 分
akitotsukahara
1
590
ニーリーにおけるプロダクトエンジニア
nealle
0
660
PHPでWebSocketサーバーを実装しよう2025
kubotak
0
240
ruby.wasmで多人数リアルタイム通信ゲームを作ろう
lnit
2
320
Blazing Fast UI Development with Compose Hot Reload (droidcon New York 2025)
zsmb
1
270
既存デザインを変更せずにタップ領域を広げる方法
tahia910
1
240
「Cursor/Devin全社導入の理想と現実」のその後
saitoryc
0
620
Featured
See All Featured
GitHub's CSS Performance
jonrohan
1031
460k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
45
7.5k
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
17
950
Fireside Chat
paigeccino
37
3.5k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
29
9.5k
Stop Working from a Prison Cell
hatefulcrawdad
270
20k
Java REST API Framework Comparison - PWX 2021
mraible
31
8.7k
The Straight Up "How To Draw Better" Workshop
denniskardys
234
140k
RailsConf 2023
tenderlove
30
1.1k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
32
2.4k
Side Projects
sachag
455
42k
Scaling GitHub
holman
459
140k
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/