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
〜『世界中の家族のこころのインフラ』を目指して”次の10年”へ〜 SREが導いたグローバルサービスの信頼性向上戦略とその舞台裏 / Towards the Next Decade: Enhancing Global Service Reliability
kohbis
1
280
FOSS4G 2025 KANSAI QGISで点群データをいろいろしてみた
kou_kita
0
400
AI専用のリンターを作る #yumemi_patch
bengo4com
6
4.3k
AI時代の開発生産性を加速させるアーキテクチャ設計
plaidtech
PRO
3
160
VS CodeとGitHub Copilotで爆速開発!アップデートの波に乗るおさらい会 / Rapid Development with VS Code and GitHub Copilot: Catch the Latest Wave
yamachu
2
140
american aa airlines®️ USA Contact Numbers: Complete 2025 Support Guide
aaguide
0
310
【LT会登壇資料】TROCCO新コネクタ「スマレジ」を活用した直営店データの分析
kazari0425
1
100
OSSのSNSツール「Misskey」をさわってみよう(右下ワイプで私のOSCの20年を振り返ります) / 20250705-osc2025-do
akkiesoft
0
170
Delta airlines Customer®️ USA Contact Numbers: Complete 2025 Support Guide
deltahelp
0
810
Lufthansa ®️ USA Contact Numbers: Complete 2025 Support Guide
lufthanahelpsupport
0
210
60以上のプロダクトを持つ組織における開発者体験向上への取り組み - チームAPIとBackstageで構築する組織の可視化基盤 - / sre next 2025 Efforts to Improve Developer Experience in an Organization with Over 60 Products
vtryo
2
300
Lakebaseを使ったAIエージェントを実装してみる
kameitomohiro
0
140
Featured
See All Featured
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
18
970
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
10
960
Why Our Code Smells
bkeepers
PRO
336
57k
Build your cross-platform service in a week with App Engine
jlugia
231
18k
Why You Should Never Use an ORM
jnunemaker
PRO
58
9.4k
Mobile First: as difficult as doing things right
swwweet
223
9.7k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
53
2.9k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
231
53k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
181
54k
KATA
mclloyd
30
14k
Build The Right Thing And Hit Your Dates
maggiecrowley
36
2.8k
Embracing the Ebb and Flow
colly
86
4.7k
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/