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 16, 2016
Technology
0
330
Elasticsearch for SQL Users
As presented at Great Wide Open 2016
Elastic Co
March 16, 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 Technology
See All in Technology
Github Copilot エージェントモードで試してみた
ochtum
0
130
AWS テクニカルサポートとエンドカスタマーの中間地点から見えるより良いサポートの活用方法
kazzpapa3
2
570
5min GuardDuty Extended Threat Detection EKS
takakuni
0
180
生成AI時代 文字コードを学ぶ意義を見出せるか?
hrsued
1
680
Claude Code Actionを使ったコード品質改善の取り組み
potix2
PRO
6
2.6k
KubeCon + CloudNativeCon Japan 2025 Recap
ren510dev
1
290
WordPressから ヘッドレスCMSへ! Storyblokへの移行プロセス
nyata
0
310
開発生産性を組織全体の「生産性」へ! 部門間連携の壁を越える実践的ステップ
sudo5in5k
0
220
マーケットプレイス版Oracle WebCenter Content For OCI
oracle4engineer
PRO
3
930
CursorによるPMO業務の代替 / Automating PMO Tasks with Cursor
motoyoshi_kakaku
2
660
PHPでWebブラウザのレンダリングエンジンを実装する
dip_tech
PRO
0
210
急成長を支える基盤作り〜地道な改善からコツコツと〜 #cre_meetup
stefafafan
0
150
Featured
See All Featured
Fashionably flexible responsive web design (full day workshop)
malarkey
407
66k
Facilitating Awesome Meetings
lara
54
6.4k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
161
15k
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.3k
Faster Mobile Websites
deanohume
307
31k
It's Worth the Effort
3n
185
28k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
8
680
Typedesign – Prime Four
hannesfritz
42
2.7k
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
3.9k
How to Think Like a Performance Engineer
csswizardry
24
1.7k
Balancing Empowerment & Direction
lara
1
390
Reflections from 52 weeks, 52 projects
jeffersonlam
351
20k
Transcript
1 Shaunak Kashyap Developer Advocate at Elastic @shaunak Elasticsearch for
SQL users
2 The Elastic Stack Elasticsearch Store, Index & Analyze Kibana
User Interface Security Monitoring Alerting Plugins Logstash Beats Ingest Elastic Cloud: Elasticsearch as a Service 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": "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 To analyze or not 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 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 Atlanta 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/