え?SQLで入門?する  ElasticsearchとElastic Stack / Getting started Elastic Stack with SQL

Slide 1

Slide 1 text

!1 Jun Ohtani Community Engineer @Elastic  Twitter: @johtani ͑ʁSQLͰೖ໳ʁ͢Δ  ElasticsearchͱElastic Stack

Slide 2

Slide 2 text

!2 about • Me, Jun Ohtani / Community Engineer ‒ lucene-gosenίϛολʔ ‒ σʔλ෼ੳج൫ߏஙೖ໳ ڞஶ ‒ http://blog.johtani.info  • Elastic, founded in 2012 ‒ Products: Elasticsearch, Logstash, Kibana, Beats   Elastic APM,   X-Pack, Elastic Cloud, Swiftype   Professional services: Support & development subscriptions  Trainings, Consulting, SaaS

Slide 3

Slide 3 text

!3 ΞδΣϯμ • Elastic Stackͱ͸ʁ • SQLͱElasticsearch • σʔλొ࿥ํ๏ • ElasticsearchͷSQL • σϞ ˍ QA

Slide 4

Slide 4 text

!4 Elastic Stack

Slide 5

Slide 5 text

Elastic Stack อଘɺݕࡧɺ෼ੳ Elasticsearch ՄࢹԽɺ؅ཧ Kibana Beats ΠϯδΣετ Logstash

Slide 6

Slide 6 text

Metrics Logging APM Site  Search Application Search Business  Analytics Enterprise  Search Security  Analytics Future ιϦϡʔγϣϯ Elastic Stack อଘɺݕࡧɺ෼ੳ ՄࢹԽɺ؅ཧ ΠϯδΣετ Kibana Elasticsearch Beats Logstash

Slide 7

Slide 7 text

Metrics Logging APM Site  Search App  Search Business  Analytics Enterprise  Search Security  Analytics Future ιϦϡʔγϣϯ SaaS Elastic Cloud Self Managed Elastic Cloud  Enterprise Standalone σϓϩΠ Elastic Stack อଘɺݕࡧɺ෼ੳ ՄࢹԽɺ؅ཧ ΠϯδΣετ Kibana Elasticsearch Beats Logstash

Slide 8

Slide 8 text

Elastic Stack อଘɺݕࡧɺ෼ੳ Elasticsearch ՄࢹԽɺ؅ཧ Kibana Beats ΠϯδΣετ Logstash Metrics Logging APM Site  Search Application Search Business  Analytics Enterprise  Search Security  Analytics Future ιϦϡʔγϣϯ SaaS Elastic Cloud Self Managed Elastic Cloud  Enterprise Standalone σϓϩΠ

Slide 9

Slide 9 text

Slide 10

Slide 10 text

10 Beats ܰྔσʔλγούʔ ιʔε͔ΒσʔλΛసૹ సૹ͠Elasticsearchʹू໿ ม׵ͱύʔεͷͨΊ Logstashʹసૹ Elastic Cloudʹసૹ Libbeat: ΧελϜbeatsͷͨ ΊͷAPIϑϨʔϜϫʔΫ 30Ҏ্ͷίϛϡχςΟbeats

Slide 11

Slide 11 text

The Beats family Heartbeat Uptime monitoring Filebeat Log files Winlogbeat Windows Event Logs Packetbeat Network data +40 community Beats Metricbeat Metrics Auditbeat Audit data

Slide 12

Slide 12 text

!12

Slide 13

Slide 13 text

13 Logstash σʔλՃ޻ύΠϓϥΠϯ શͯͷܗࣜɺαΠζͱσʔλιʔ εͷ౤ೖ ύʔεͱಈతͳ σʔλม׵ ͋ΒΏΔग़ྗʹ σʔλసૹ ҆શͰ҉߸Խ͞Εͨ  σʔλೖྗ ಠࣗͷύΠϓϥΠϯॲཧ ͷ࡞੒ 200Ҏ্ͷϓϥάΠϯ

Slide 14

Slide 14 text

!14

Slide 15

Slide 15 text

15 Elasticsearch Heart of the Elastic Stack ෼ࢄܕɺεέʔϥϒϧ ߴՄ༻ੑ Ϛϧνςφϯτ ։ൃऀϑϨϯυϦʔ ϦΞϧλΠϜɺશจݕࡧ ΞάϦήʔγϣϯ

Slide 16

Slide 16 text

!16

Slide 17

Slide 17 text

17 Kibana Window into the Elastic Stack ՄࢹԽͱ෼ੳ ஍ཧۭؒ ΧελϚΠζͱ Ϩϙʔτͷڞ༗ άϥϑ୳ࡧ Elastic Stack΁ͷ ηΩϡΞͳΞΫηεͱ؅ཧ ΧελϜAppsͷ࡞੒

Slide 18

Slide 18 text

!18 Elastic Stackͷߏ੒ Beats Log Files Metrics Wire Data your{beat} Kibana Instances Kafka Distributed Message Queue Notification Queues Storage Metrics Data Store Web APIs Social Sensors Elasticsearch Nodes Logstash Nodes

Slide 19

Slide 19 text

SQLͱElasticsearchʁ !19

Slide 20

Slide 20 text

!20 ElasticsearchͷΫΤϦ͸೉͍͠ʁ • ༻ޠͷҧ͍ • ಠࣗQuery DSL • JSONܗࣜ • Aggregation • AnalyzerͱసஔΠϯσοΫε • Mappings

Slide 21

Slide 21 text

!21 ಠࣗΫΤϦDSL

Slide 22

Slide 22 text

!22 QueryͱAggregation

Slide 23

Slide 23 text

సஔΠϯσοΫε !23

Slide 24

Slide 24 text

Analyzer !24 my favorite movie star wars "My favorite movie is Star Wars!" • StandardɺSimpleɺKuromojiͳͲબ୒ࢶ͕๛෋ • AnalyzerͷΧελϚΠζ΋Մೳ

Slide 25

Slide 25 text

!25 Elasticsearch SQLͷҐஔ෇͚ • ANSI SQLͷαϒηοτͱͯ͠“Read-only” ΠϯλʔϑΣΠεΛఏڙ SHOWɺDESCRIBEɺSELECTʹߜͬͨఏڙ • ElasticsearchͷػೳΛੜ͔ͨ͠ΦϖϨʔλʔ શจݕࡧػೳΛੜ͔͢QUERYͱMATCH • ܰྔͰ؆ܿͳ࣮૷

Slide 26

Slide 26 text

σʔλͷొ࿥ํ๏ !26

Slide 27

Slide 27 text

!27 σʔλͷొ࿥ํ๏ • Kibanaͷαϯϓϧσʔλʢ6.4͔Βʣ • LogstashͰJDBC input • LogstashͰCSV • FilebeatͰΞΫηεϩά • MetricbeatͰϝτϦοΫ • PacketbeatͰMySQL/PostgreSQLͷύέοτղੳ

Slide 28

Slide 28 text

!28 Kibanaͷαϯϓϧσʔλʢ>= 6.4.0ʣ

Slide 29

Slide 29 text

!29 ϫϯΫϦοΫͰσʔλొ࿥

Slide 30

Slide 30 text

!30 LogstashͰJDBC Input Kibana Instances Data Store Elasticsearch Nodes Logstash Nodes

Slide 31

Slide 31 text

!31 JDBC Input

Slide 32

Slide 32 text

!32 LogstashͰCSV Kibana Instances CSV  File Elasticsearch Nodes Logstash Nodes

Slide 33

Slide 33 text

!33 CSV filter

Slide 34

Slide 34 text

!34 FilebeatͰΞΫηεϩά Beats Log Files Kibana Instances Elasticsearch Nodes

Slide 35

Slide 35 text

• 2ͭͷElasticsearchϓϥάΠϯΛΠϯετʔϧͯ͠ElasticsearchΛىಈ • Filebeatͷapache2ϞδϡʔϧΛ༗ޮԽ • modules.d/apache2.ymlʹΞΫηεϩάͷύεΛઃఆ • setupίϚϯυΛ࣮ߦ͔ͯ͠ΒFilebeatΛىಈ !35 FilebeatͰΞΫηεϩά

Slide 36

Slide 36 text

MetricbeatͰϝτϦοΫ Beats Metrics Kibana Instances Elasticsearch Nodes

Slide 37

Slide 37 text

• MetricbeatͷsystemϞδϡʔϧΛ༗ޮԽ • setupίϚϯυΛ࣮ߦ͔ͯ͠ΒFilebeatΛىಈ !37 MetricbeatͰϝτϦοΫ

Slide 38

Slide 38 text

!38 PacketbeatͰMySQLɺPostgreSQLͷύέοτղੳ Beats Wire Data Kibana Instances Elasticsearch Nodes

Slide 39

Slide 39 text

ElasticsearchͷSQL !39

Slide 40

Slide 40 text

!40 ର৅Ϣʔβʔ σϕϩούʔ σʔλαΠΤϯςΟετ BIϢʔβʔ • RDBMSΛར༻͍ͯ͠Δ • ΞϓϦέʔγϣϯʹݕࡧػ ೳΛ࣮૷ • SQLΛར༻͍ͯ͠Δ • σʔλ෼ੳͷٕज़ͷͻͱͭ ͱͯ͠ • ODBC/JDBCͰDWHʹ઀ଓ • BIπʔϧɺදܭࢉιϑτΛ ௨ͯ͡

Slide 41

Slide 41 text

!41 ΠϯλʔϑΣʔε REST API _xpack/sql ΤϯυϙΠϯτ SQLΛJSONυΩϡϝϯτͱͯ͠ड৴ɺ݁ՌΛฦ͢ _xpack/sql/translate APIΛ௨ͯ͡Query DSL΁ͷม׵ CLI bin/elasticsearch-sqli-cli ίϚϯυ ಠཱͯ͠ಈ࡞͕Մೳ JDBC ElasticsearchͷJDBCυϥΠόʔ PlatinumαϒεΫϦϓγϣϯ͕ඞཁ ODBC ElasticsearchͷODBCυϥΠόʔʢ։ൃதʣ

Slide 42

Slide 42 text

!42 ೖखํ๏ͱηοτΞοϓʢ6.3Ҏ߱ͷόʔδϣϯ͕ඞཁʣ ΦϯϓϨɾϓϥΠϕʔτΫϥ΢υ ϚωʔδυαʔϏε REST API xpack.sql.enabled=true (default) Elasticsearch ServiceͰ༗ޮ CLI ੡඼ͷZIPɺRPMɺDEBͳͲʹಉࠝ bin/elasticsearch-sql-cli ίϚϯυ elasticsearch-sql-cli-.jarΛ ผ్ೖख JDBC ౰ࣾ΢ΣϒαΠτΑΓμ΢ϯϩʔυʢPlatinumαϒεΫϦϓγϣϯ͕ඞཁʣ https://www.elastic.co/downloads/jdbc-client ͦΕͧΕElastic LicenseʹΑΓ഑෍  https://github.com/elastic/elasticsearch/blob/6.3/licenses/ELASTIC-LICENSE.txt

Slide 43

Slide 43 text

!43 Elasticsearch SQLͷҐஔ෇͚ • ANSI SQLͷαϒηοτͱͯ͠“Read-only” ΠϯλʔϑΣΠεΛఏڙ SHOWɺDESCRIBEɺSELECTʹߜͬͨఏڙ • ElasticsearchͷػೳΛੜ͔ͨ͠ΦϖϨʔλʔ શจݕࡧػೳΛੜ͔͢QUERYͱMATCH • ܰྔͰ؆ܿͳ࣮૷

Slide 44

Slide 44 text

!44 Ϛοϐϯάίϯηϓτ SQL Elasticsearch આ໌ ΧϥϜʢྻʣ ϑΟʔϧυ ElasticsearchͰ͸഑ྻ΍ΦϒδΣΫτͷՄೳੑ΋͋Δ ͕ɺϕετͳϨεϙϯεΛࢼΈΔ ϩʔʢߦʣ υΩϡϝϯτ ElasticsearchͷυΩϡϝϯτ͸ϑϨΩγϒϧ ςʔϒϧʢදʣ ΠϯσοΫε FROM۟Ͱ͸ΤΠϦΞε΍ϫΠϧυΧʔυ͕औΕΔ εΩʔϚ ҉໧త ܕͱϚϧνϑΟʔϧυ͸҉໧తʹม׵͞ΕΔ Χλϩάɺσʔλϕʔε Ϋϥελʔ ͻͱͭͷElasticsearchΫϥελʔ͸ɺͻͱͭͷσʔλ ϕʔεͱͯ͠ΞΫηε͞ΕΔ Ϋϥελʔ Ϋϥελʔ ʢΫϩεΫϥελʔʣ ΫϥελʔؒΛ·͍ͨͩݕࡧ͸ΫϩεΫϥελʔݕࡧ Λ௨ͯ͡ߦΘΕΔ https://www.elastic.co/guide/en/elasticsearch/reference/6.3/_mapping_concepts_across_sql_and_elasticsearch.html

Slide 45

Slide 45 text

!45 σʔλλΠϓ SQL Elasticsearch null null long long ੔਺Λදͦ͢ͷ΄͔ͷܕ΋͋Γ float half_float, scaled_float ͦͷ΄͔ͷුಈখ਺఺ܕ΋͋Γ varchar keyword, text ҉໧తʹϚϧνϑΟʔϧυʹରԠ timestamp date struct object, nested ElasticsearchʹΑΔੵۃతͳ֎෦݁߹త ల։͕ߦΘΕΔ͜ͱ͕͋Δ https://www.elastic.co/guide/en/elasticsearch/reference/6.3/sql-data-types.html

Slide 46

Slide 46 text

Slide 47

Slide 47 text

!47 SELECTจ SELECT select_expr [, ...] [ FROM table_name ] [ WHERE condition ] [ GROUP BY grouping_element ] [ HAVING condition] [ ORDER BY expression [ ASC | DESC ] [, ...] ] [ LIMIT [ count ] ]; QUERY(query, query_string_parameter, ...) MATCH(fields, query, match_parameter, ...) શจݕࡧʢWHERE۟ʣ

Slide 48

Slide 48 text

!48 REST API • format=jsonͷ৔߹ʹ͸ɺ”cursor”Λ௨ͯ͡εΫϩʔϧ͕Մೳ • _xpack/sql/translate APIͰɺQuery DSLʹม׵ POST _xpack/sql?format=[json|txt|csv|yaml|smile] { "query": “”,  “fetch_size": }

Slide 49

Slide 49 text

SQLͷElasticsearchͰͷ಺෦ॲཧ Analysis Planning Execution Parsing SQL QUERY Unresolved AST Resolved/Logical Plan Optimized Plan Physical Plan Client Results

Slide 50

Slide 50 text

σϞ !50

Slide 51

Slide 51 text

!51 ৘ใιʔε An Introduction to Elasticsearch SQL with Practical Examples - Part 1, 2 https://www.elastic.co/blog/an-introduction-to-elasticsearch- sql-with-practical-examples-part-1 https://demo.elastic.co Elasticsearch ެࣜυΩϡϝϯτ https://www.elastic.co/guide/en/elasticsearch/reference/ current/xpack-sql.html

Slide 52

Slide 52 text

!52 ·ͱΊ • ElasticsearchͰSQLΛ࢖༻Մೳʢ6.3Ҏ߱ʣ • SQL͸Ұ෦ͷΈαϙʔτ • Read-Onlyͷػೳʴݶఆతͳ৚݅ࣜͳͲ • _xpack/sql/translate APIͰQuery DSLʹม׵ • ެࣜϦϑΝϨϯε͕ศར

Slide 53

Slide 53 text

Thank you! ● Web : https://www.elastic.co/jp/ ● Forums : https://discuss.elastic.co/ ● Twitter : @johtani

Slide 54

Slide 54 text

!54 Elasticsearch SQL SELECT Yes DESCRIBE Yes TRANSLATE/EXPLAIN Yes* INSERT/DELETE No CREATE/DROP No UPDATE/ALTER No MERGE No DECLARE LOCAL TEMPORARY TABLE No SET TRANSACTION No GRANT/REVOKE No WHERE Yes ORDER BY Yes EXTRACT Yes MAX, MIN, AVG, ... Yes SCORE Yes* KURTOSIS Yes* MINUTE_OF_HOUR, DAY_OF_WEEK Yes* JOIN No UNION No INTERSECT No Focus first on the things Elasticsearch does well: ● Scale: SQL query 1 billion docs? Sure! ● Full-text: it’s awful in most SQL engines. Synonyms, stemming, etc ● Relevance: ORDER BY SCORE()! ● Geo: Coming soon… Most databases are terrible and/or $$$$$$ with geo (Oracle) Skip the things we don’t do: ● Joins ● Transactions