Upgrade to Pro — share decks privately, control downloads, hide ads and more …

え?SQLで入門?する
 ElasticsearchとElastic Stack / Gett...

Jun Ohtani
September 19, 2018
930

え?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. !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
  2. Metrics Logging APM Site
 Search Application Search Business
 Analytics Enterprise


    Search Security
 Analytics Future ιϦϡʔγϣϯ Elastic Stack อଘɺݕࡧɺ෼ੳ ՄࢹԽɺ؅ཧ ΠϯδΣετ Kibana Elasticsearch Beats Logstash
  3. 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
  4. 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 σϓϩΠ
  5. !9

  6. The Beats family Heartbeat Uptime monitoring Filebeat Log files Winlogbeat

    Windows Event Logs Packetbeat Network data +40 community Beats Metricbeat Metrics Auditbeat Audit data
  7. !12

  8. !14

  9. 15 Elasticsearch Heart of the Elastic Stack ෼ࢄܕɺεέʔϥϒϧ ߴՄ༻ੑ Ϛϧνςφϯτ

    ։ൃऀϑϨϯυϦʔ ϦΞϧλΠϜɺશจݕࡧ ΞάϦήʔγϣϯ
  10. !16

  11. 17 Kibana Window into the Elastic Stack ՄࢹԽͱ෼ੳ ஍ཧۭؒ ΧελϚΠζͱ

    Ϩϙʔτͷڞ༗ άϥϑ୳ࡧ Elastic Stack΁ͷ ηΩϡΞͳΞΫηεͱ؅ཧ ΧελϜAppsͷ࡞੒
  12. !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
  13. !20 ElasticsearchͷΫΤϦ͸೉͍͠ʁ • ༻ޠͷҧ͍ • ಠࣗQuery DSL • JSONܗࣜ •

    Aggregation • AnalyzerͱసஔΠϯσοΫε • Mappings
  14. Analyzer !24 my favorite movie star wars "My favorite movie

    is Star Wars!" • StandardɺSimpleɺKuromojiͳͲબ୒ࢶ͕๛෋ • AnalyzerͷΧελϚΠζ΋Մೳ
  15. !27 σʔλͷొ࿥ํ๏ • Kibanaͷαϯϓϧσʔλʢ6.4͔Βʣ • LogstashͰJDBC input • LogstashͰCSV •

    FilebeatͰΞΫηεϩά • MetricbeatͰϝτϦοΫ • PacketbeatͰMySQL/PostgreSQLͷύέοτղੳ
  16. !40 ର৅Ϣʔβʔ σϕϩούʔ σʔλαΠΤϯςΟετ BIϢʔβʔ • RDBMSΛར༻͍ͯ͠Δ • ΞϓϦέʔγϣϯʹݕࡧػ ೳΛ࣮૷

    • SQLΛར༻͍ͯ͠Δ • σʔλ෼ੳͷٕज़ͷͻͱͭ ͱͯ͠ • ODBC/JDBCͰDWHʹ઀ଓ • BIπʔϧɺදܭࢉιϑτΛ ௨ͯ͡
  17. !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υϥΠόʔʢ։ൃதʣ
  18. !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
  19. !44 Ϛοϐϯάίϯηϓτ SQL Elasticsearch આ໌ ΧϥϜʢྻʣ ϑΟʔϧυ ElasticsearchͰ͸഑ྻ΍ΦϒδΣΫτͷՄೳੑ΋͋Δ ͕ɺϕετͳϨεϙϯεΛࢼΈΔ ϩʔʢߦʣ

    υΩϡϝϯτ ElasticsearchͷυΩϡϝϯτ͸ϑϨΩγϒϧ ςʔϒϧʢදʣ ΠϯσοΫε FROM۟Ͱ͸ΤΠϦΞε΍ϫΠϧυΧʔυ͕औΕΔ εΩʔϚ ҉໧త ܕͱϚϧνϑΟʔϧυ͸҉໧తʹม׵͞ΕΔ Χλϩάɺσʔλϕʔε Ϋϥελʔ ͻͱͭͷElasticsearchΫϥελʔ͸ɺͻͱͭͷσʔλ ϕʔεͱͯ͠ΞΫηε͞ΕΔ Ϋϥελʔ Ϋϥελʔ ʢΫϩεΫϥελʔʣ ΫϥελʔؒΛ·͍ͨͩݕࡧ͸ΫϩεΫϥελʔݕࡧ Λ௨ͯ͡ߦΘΕΔ https://www.elastic.co/guide/en/elasticsearch/reference/6.3/_mapping_concepts_across_sql_and_elasticsearch.html
  20. !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
  21. !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
  22. !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۟ʣ
  23. !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> }
  24. !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
  25. !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