$30 off During Our Annual Pro Sale. View Details »

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

Jun Ohtani
September 19, 2018
790

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

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

Jun Ohtani

September 19, 2018
Tweet

More Decks by Jun Ohtani

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