Share
Embed
え?SQLで入門?する ElasticsearchとElastic Stack / Getting started Elastic Stack with SQL
db tech showcase 2018での発表資料です。
タイトルはだいぶ煽り気味で、Elastic Stackの入門の話がメインです。
Jun Ohtani
September 19, 2018
Transcript
54 slides
-
!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 ΞδΣϯμ • Elastic Stackͱʁ • SQLͱElasticsearch • σʔλొํ๏ •
ElasticsearchͷSQL • σϞ ˍ QA -
Metrics Logging APM Site Search Application Search Business Analytics Enterprise
Search Security Analytics Future ιϦϡʔγϣϯ Elastic Stack อଘɺݕࡧɺੳ ՄࢹԽɺཧ ΠϯδΣετ Kibana Elasticsearch Beats Logstash -
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 -
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 σϓϩΠ -
10 Beats ܰྔσʔλγούʔ ιʔε͔ΒσʔλΛసૹ సૹ͠Elasticsearchʹू มͱύʔεͷͨΊ Logstashʹసૹ Elastic Cloudʹసૹ Libbeat:
ΧελϜbeatsͷͨ ΊͷAPIϑϨʔϜϫʔΫ 30Ҏ্ͷίϛϡχςΟbeats -
The Beats family Heartbeat Uptime monitoring Filebeat Log files Winlogbeat
Windows Event Logs Packetbeat Network data +40 community Beats Metricbeat Metrics Auditbeat Audit data -
13 Logstash σʔλՃύΠϓϥΠϯ શͯͷܗࣜɺαΠζͱσʔλιʔ εͷೖ ύʔεͱಈతͳ σʔλม ͋ΒΏΔग़ྗʹ σʔλసૹ ҆શͰ҉߸Խ͞Εͨ
σʔλೖྗ ಠࣗͷύΠϓϥΠϯॲཧ ͷ࡞ 200Ҏ্ͷϓϥάΠϯ -
15 Elasticsearch Heart of the Elastic Stack ࢄܕɺεέʔϥϒϧ ߴՄ༻ੑ Ϛϧνςφϯτ
։ൃऀϑϨϯυϦʔ ϦΞϧλΠϜɺશจݕࡧ ΞάϦήʔγϣϯ -
17 Kibana Window into the Elastic Stack ՄࢹԽͱੳ ཧۭؒ ΧελϚΠζͱ
Ϩϙʔτͷڞ༗ άϥϑ୳ࡧ Elastic Stackͷ ηΩϡΞͳΞΫηεͱཧ ΧελϜAppsͷ࡞ -
!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 -
!20 ElasticsearchͷΫΤϦ͍͠ʁ • ༻ޠͷҧ͍ • ಠࣗQuery DSL • JSONܗࣜ •
Aggregation • AnalyzerͱసஔΠϯσοΫε • Mappings -
Analyzer !24 my favorite movie star wars "My favorite movie
is Star Wars!" • StandardɺSimpleɺKuromojiͳͲબࢶ͕๛ • AnalyzerͷΧελϚΠζՄೳ -
!25 Elasticsearch SQLͷҐஔ͚ • ANSI SQLͷαϒηοτͱͯ͠“Read-only” ΠϯλʔϑΣΠεΛఏڙ SHOWɺDESCRIBEɺSELECTʹߜͬͨఏڙ • ElasticsearchͷػೳΛੜ͔ͨ͠ΦϖϨʔλʔ
શจݕࡧػೳΛੜ͔͢QUERYͱMATCH • ܰྔͰ؆ܿͳ࣮ -
!27 σʔλͷొํ๏ • Kibanaͷαϯϓϧσʔλʢ6.4͔Βʣ • LogstashͰJDBC input • LogstashͰCSV •
FilebeatͰΞΫηεϩά • MetricbeatͰϝτϦοΫ • PacketbeatͰMySQL/PostgreSQLͷύέοτղੳ -
!40 ରϢʔβʔ σϕϩούʔ σʔλαΠΤϯςΟετ BIϢʔβʔ • RDBMSΛར༻͍ͯ͠Δ • ΞϓϦέʔγϣϯʹݕࡧػ ೳΛ࣮
• SQLΛར༻͍ͯ͠Δ • σʔλੳͷٕज़ͷͻͱͭ ͱͯ͠ • ODBC/JDBCͰDWHʹଓ • BIπʔϧɺදܭࢉιϑτΛ ௨ͯ͡ -
!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 ೖखํ๏ͱηοτΞοϓʢ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 Elasticsearch SQLͷҐஔ͚ • ANSI SQLͷαϒηοτͱͯ͠“Read-only” ΠϯλʔϑΣΠεΛఏڙ SHOWɺDESCRIBEɺSELECTʹߜͬͨఏڙ • ElasticsearchͷػೳΛੜ͔ͨ͠ΦϖϨʔλʔ
શจݕࡧػೳΛੜ͔͢QUERYͱMATCH • ܰྔͰ؆ܿͳ࣮ -
!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 σʔλλΠϓ 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 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 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 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> } -
SQLͷElasticsearchͰͷ෦ॲཧ Analysis Planning Execution Parsing SQL QUERY Unresolved AST Resolved/Logical
Plan Optimized Plan Physical Plan Client Results -
!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 ·ͱΊ • ElasticsearchͰSQLΛ༻Մೳʢ6.3Ҏ߱ʣ • SQLҰ෦ͷΈαϙʔτ • Read-Onlyͷػೳʴݶఆతͳ݅ࣜͳͲ • _xpack/sql/translate
APIͰQuery DSLʹม • ެࣜϦϑΝϨϯε͕ศར -
Thank you! • Web : https://www.elastic.co/jp/ • Forums : https://discuss.elastic.co/
• Twitter : @johtani -
!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