え?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
   

   View Slide

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
   

   View Slide

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

   View Slide

4. !4
   Elastic Stack
   

   View Slide

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

   View Slide

6. Metrics
   Logging
   APM
   Site

   Search
   Application
   Search
   Business

   Analytics
   Enterprise

   Search
   Security

   Analytics
   Future ιϦϡʔγϣϯ
   Elastic
   Stack
   อଘɺݕࡧɺ෼ੳ
   ՄࢹԽɺ؅ཧ
   ΠϯδΣετ
   Kibana
   Elasticsearch
   Beats Logstash
   

   View Slide

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
   

   View Slide

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
   σϓϩΠ
   

   View Slide

9. !9
   

   View Slide

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

    View Slide

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
    

    View Slide

12. !12
    

    View Slide

13. 13
    Logstash
    σʔλՃ޻ύΠϓϥΠϯ
    શͯͷܗࣜɺαΠζͱσʔλιʔ
    εͷ౤ೖ
    ύʔεͱಈతͳ
    σʔλม׵
    ͋ΒΏΔग़ྗʹ
    σʔλసૹ
    ҆શͰ҉߸Խ͞Εͨ

    σʔλೖྗ
    ಠࣗͷύΠϓϥΠϯॲཧ
    ͷ࡞੒
    200Ҏ্ͷϓϥάΠϯ
    

    View Slide

14. !14
    

    View Slide

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

    View Slide

16. !16
    

    View Slide

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

    View Slide

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
    

    View Slide

19. SQLͱElasticsearchʁ
    !19
    

    View Slide

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

    View Slide

21. !21
    ಠࣗΫΤϦDSL
    

    View Slide

22. !22
    QueryͱAggregation
    

    View Slide

23. సஔΠϯσοΫε
    !23
    

    View Slide

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

    View Slide

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

    View Slide

26. σʔλͷొ࿥ํ๏
    !26
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

31. !31
    JDBC Input
    

    View Slide

32. !32
    LogstashͰCSV
    Kibana
    Instances
    CSV

    File
    Elasticsearch
    Nodes
    Logstash
    Nodes
    

    View Slide

33. !33
    CSV filter
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

39. ElasticsearchͷSQL
    !39
    

    View Slide

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

    View Slide

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υϥΠόʔʢ։ൃதʣ
    

    View Slide

42. !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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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 ;
    column | type
    -----------------------+---------------
    @timestamp |TIMESTAMP
    @version |VARCHAR
    

    View Slide

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۟ʣ
    

    View Slide

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

    “fetch_size":
    }
    

    View Slide

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

    View Slide

50. σϞ
    !50
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide