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

657aeeff3fc467567dacebf8a1ea0b23?s=47 Jun Ohtani
September 19, 2018
690

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

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

657aeeff3fc467567dacebf8a1ea0b23?s=128

Jun Ohtani

September 19, 2018
Tweet

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