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

db tech showcase 2018での発表資料です。
タイトルはだいぶ煽り気味で、Elastic Stackの入門の話がメインです。

Transcript

1. !1 Jun Ohtani Community Engineer @Elastic
 Twitter: @johtani ͑ʁSQLͰೖ໳ʁ͢Δ
 ElasticsearchͱElastic Stack
2. !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
3. !3 ΞδΣϯμ • Elastic Stackͱ͸ʁ • SQLͱElasticsearch • σʔλొ࿥ํ๏ • ElasticsearchͷSQL • σϞ ˍ QA
4. !4 Elastic Stack
5. Elastic Stack อଘɺݕࡧɺ෼ੳ Elasticsearch ՄࢹԽɺ؅ཧ Kibana Beats ΠϯδΣετ Logstash
6. Metrics Logging APM Site
 Search Application Search Business
 Analytics Enterprise
 Search Security
 Analytics Future ιϦϡʔγϣϯ Elastic Stack อଘɺݕࡧɺ෼ੳ ՄࢹԽɺ؅ཧ ΠϯδΣετ Kibana Elasticsearch Beats Logstash
7. 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
8. 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 σϓϩΠ
9. !9
10. 10 Beats ܰྔσʔλγούʔ ιʔε͔ΒσʔλΛసૹ సૹ͠Elasticsearchʹू໿ ม׵ͱύʔεͷͨΊ Logstashʹసૹ Elastic Cloudʹసૹ Libbeat: ΧελϜbeatsͷͨ ΊͷAPIϑϨʔϜϫʔΫ 30Ҏ্ͷίϛϡχςΟbeats
11. The Beats family Heartbeat Uptime monitoring Filebeat Log files Winlogbeat Windows Event Logs Packetbeat Network data +40 community Beats Metricbeat Metrics Auditbeat Audit data
12. !12
13. 13 Logstash σʔλՃ޻ύΠϓϥΠϯ શͯͷܗࣜɺαΠζͱσʔλιʔ εͷ౤ೖ ύʔεͱಈతͳ σʔλม׵ ͋ΒΏΔग़ྗʹ σʔλసૹ ҆શͰ҉߸Խ͞Εͨ
 σʔλೖྗ ಠࣗͷύΠϓϥΠϯॲཧ ͷ࡞੒ 200Ҏ্ͷϓϥάΠϯ
14. !14
15. 15 Elasticsearch Heart of the Elastic Stack ෼ࢄܕɺεέʔϥϒϧ ߴՄ༻ੑ Ϛϧνςφϯτ ։ൃऀϑϨϯυϦʔ ϦΞϧλΠϜɺશจݕࡧ ΞάϦήʔγϣϯ
16. !16
17. 17 Kibana Window into the Elastic Stack ՄࢹԽͱ෼ੳ ஍ཧۭؒ ΧελϚΠζͱ Ϩϙʔτͷڞ༗ άϥϑ୳ࡧ Elastic Stack΁ͷ ηΩϡΞͳΞΫηεͱ؅ཧ ΧελϜAppsͷ࡞੒
18. !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
19. SQLͱElasticsearchʁ !19
20. !20 ElasticsearchͷΫΤϦ͸೉͍͠ʁ • ༻ޠͷҧ͍ • ಠࣗQuery DSL • JSONܗࣜ • Aggregation • AnalyzerͱసஔΠϯσοΫε • Mappings
21. !21 ಠࣗΫΤϦDSL
22. !22 QueryͱAggregation
23. సஔΠϯσοΫε !23
24. Analyzer !24 my favorite movie star wars "My favorite movie is Star Wars!" • StandardɺSimpleɺKuromojiͳͲબ୒ࢶ͕๛෋ • AnalyzerͷΧελϚΠζ΋Մೳ
25. !25 Elasticsearch SQLͷҐஔ෇͚ • ANSI SQLͷαϒηοτͱͯ͠“Read-only” ΠϯλʔϑΣΠεΛఏڙ SHOWɺDESCRIBEɺSELECTʹߜͬͨఏڙ • ElasticsearchͷػೳΛੜ͔ͨ͠ΦϖϨʔλʔ શจݕࡧػೳΛੜ͔͢QUERYͱMATCH • ܰྔͰ؆ܿͳ࣮૷
26. σʔλͷొ࿥ํ๏ !26
27. !27 σʔλͷొ࿥ํ๏ • Kibanaͷαϯϓϧσʔλʢ6.4͔Βʣ • LogstashͰJDBC input • LogstashͰCSV • FilebeatͰΞΫηεϩά • MetricbeatͰϝτϦοΫ • PacketbeatͰMySQL/PostgreSQLͷύέοτղੳ
28. !28 Kibanaͷαϯϓϧσʔλʢ>= 6.4.0ʣ
29. !29 ϫϯΫϦοΫͰσʔλొ࿥
30. !30 LogstashͰJDBC Input Kibana Instances Data Store Elasticsearch Nodes Logstash Nodes
31. !31 JDBC Input
32. !32 LogstashͰCSV Kibana Instances CSV
 File Elasticsearch Nodes Logstash Nodes
33. !33 CSV filter
34. !34 FilebeatͰΞΫηεϩά Beats Log Files Kibana Instances Elasticsearch Nodes
35. • 2ͭͷElasticsearchϓϥάΠϯΛΠϯετʔϧͯ͠ElasticsearchΛىಈ • Filebeatͷapache2ϞδϡʔϧΛ༗ޮԽ • modules.d/apache2.ymlʹΞΫηεϩάͷύεΛઃఆ • setupίϚϯυΛ࣮ߦ͔ͯ͠ΒFilebeatΛىಈ !35 FilebeatͰΞΫηεϩά
36. MetricbeatͰϝτϦοΫ Beats Metrics Kibana Instances Elasticsearch Nodes
37. • MetricbeatͷsystemϞδϡʔϧΛ༗ޮԽ • setupίϚϯυΛ࣮ߦ͔ͯ͠ΒFilebeatΛىಈ !37 MetricbeatͰϝτϦοΫ
38. !38 PacketbeatͰMySQLɺPostgreSQLͷύέοτղੳ Beats Wire Data Kibana Instances Elasticsearch Nodes
39. ElasticsearchͷSQL !39
40. !40 ର৅Ϣʔβʔ σϕϩούʔ σʔλαΠΤϯςΟετ BIϢʔβʔ • RDBMSΛར༻͍ͯ͠Δ • ΞϓϦέʔγϣϯʹݕࡧػ ೳΛ࣮૷ • SQLΛར༻͍ͯ͠Δ • σʔλ෼ੳͷٕज़ͷͻͱͭ ͱͯ͠ • ODBC/JDBCͰDWHʹ઀ଓ • BIπʔϧɺදܭࢉιϑτΛ ௨ͯ͡
41. !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υϥΠόʔʢ։ൃதʣ
42. !42 ೖखํ๏ͱηοτΞοϓʢ6.3Ҏ߱ͷόʔδϣϯ͕ඞཁʣ ΦϯϓϨɾϓϥΠϕʔτΫϥ΢υ ϚωʔδυαʔϏε REST API xpack.sql.enabled=true (default) Elasticsearch ServiceͰ༗ޮ CLI ੡඼ͷZIPɺRPMɺDEBͳͲʹಉࠝ bin/elasticsearch-sql-cli ίϚϯυ elasticsearch-sql-cli-<version>.jarΛ ผ్ೖख JDBC ౰ࣾ΢ΣϒαΠτΑΓμ΢ϯϩʔυʢPlatinumαϒεΫϦϓγϣϯ͕ඞཁʣ https://www.elastic.co/downloads/jdbc-client ͦΕͧΕElastic LicenseʹΑΓ഑෍
 https://github.com/elastic/elasticsearch/blob/6.3/licenses/ELASTIC-LICENSE.txt
43. !43 Elasticsearch SQLͷҐஔ෇͚ • ANSI SQLͷαϒηοτͱͯ͠“Read-only” ΠϯλʔϑΣΠεΛఏڙ SHOWɺDESCRIBEɺSELECTʹߜͬͨఏڙ • ElasticsearchͷػೳΛੜ͔ͨ͠ΦϖϨʔλʔ શจݕࡧػೳΛੜ͔͢QUERYͱMATCH • ܰྔͰ؆ܿͳ࣮૷
44. !44 Ϛοϐϯάίϯηϓτ SQL Elasticsearch આ໌ ΧϥϜʢྻʣ ϑΟʔϧυ ElasticsearchͰ͸഑ྻ΍ΦϒδΣΫτͷՄೳੑ΋͋Δ ͕ɺϕετͳϨεϙϯεΛࢼΈΔ ϩʔʢߦʣ υΩϡϝϯτ ElasticsearchͷυΩϡϝϯτ͸ϑϨΩγϒϧ ςʔϒϧʢදʣ ΠϯσοΫε FROM۟Ͱ͸ΤΠϦΞε΍ϫΠϧυΧʔυ͕औΕΔ εΩʔϚ ҉໧త ܕͱϚϧνϑΟʔϧυ͸҉໧తʹม׵͞ΕΔ Χλϩάɺσʔλϕʔε Ϋϥελʔ ͻͱͭͷElasticsearchΫϥελʔ͸ɺͻͱͭͷσʔλ ϕʔεͱͯ͠ΞΫηε͞ΕΔ Ϋϥελʔ Ϋϥελʔ ʢΫϩεΫϥελʔʣ ΫϥελʔؒΛ·͍ͨͩݕࡧ͸ΫϩεΫϥελʔݕࡧ Λ௨ͯ͡ߦΘΕΔ https://www.elastic.co/guide/en/elasticsearch/reference/6.3/_mapping_concepts_across_sql_and_elasticsearch.html
45. !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
46. !46 SQLίϚϯυ sql> SHOW TABLES; name | type -------------------------------------------------------------------+--------------- .kibana |BASE TABLE .logstash |BASE TABLE sql> SHOW FUNCTIONS; name | type ----------------+--------------- AVG |AGGREGATE COUNT |AGGREGATE sql> DESCRIBE <TABLE NAME>; column | type -----------------------+--------------- @timestamp |TIMESTAMP @version |VARCHAR
47. !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۟ʣ
48. !48 REST API • format=jsonͷ৔߹ʹ͸ɺ”cursor”Λ௨ͯ͡εΫϩʔϧ͕Մೳ • _xpack/sql/translate APIͰɺQuery DSLʹม׵ POST _xpack/sql?format=[json|txt|csv|yaml|smile] { "query": “<sql_expression>”,
 “fetch_size": <number> }
49. SQLͷElasticsearchͰͷ಺෦ॲཧ Analysis Planning Execution Parsing SQL QUERY Unresolved AST Resolved/Logical Plan Optimized Plan Physical Plan Client Results
50. σϞ !50
51. !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
52. !52 ·ͱΊ • ElasticsearchͰSQLΛ࢖༻Մೳʢ6.3Ҏ߱ʣ • SQL͸Ұ෦ͷΈαϙʔτ • Read-Onlyͷػೳʴݶఆతͳ৚݅ࣜͳͲ • _xpack/sql/translate APIͰQuery DSLʹม׵ • ެࣜϦϑΝϨϯε͕ศར
53. Thank you! • Web : https://www.elastic.co/jp/ • Forums : https://discuss.elastic.co/ • Twitter : @johtani
54. !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