Share
Embed
Software stack for the visualization and analysis around Apache Solr with Parallel SQL
Software stack for the visualization and analysis around Apache Solr with Parallel SQL
Minoru Osuka
June 11, 2016
Transcript
34 slides
-
Software stack for the visualization and analysis around Apache Solr
with Parallel SQL [email protected] Some rights reserved by Sebastian Sikora -
Self-introduction େਢլ ູ Minoru OSUKA [email protected] Committer and PMC Member
on ManifoldCF Project at Apache Software Foundation http://manifoldcf.apache.org Contributor on Solr Project at Apache Software Foundation http://lucene.apache.org/solr/ Author ofʦվగ৽൛ʧApache Solr ೖ http://gihyo.jp/book/2014/978-4-7741-6163-1 Some rights reserved by QuinnDombrowski -
Agenda Parallel SQL SQL Request Handler JDBC SQL Syntax ETL
and Search The Elastic Stack Apache Family Visualizing Data Demonstration Some rights reserved by scui3asteveo -
Parallel SQL MapReduce-like shufflingɺ·ͨ JSON Facet API Λͬͯूܭɻ SQL
Prest SQL Parser Ͱ Solr Query ม ͞ΕɺStreaming API Λར༻ͯ͠σʔλͷݕࡧ Λߦ͏ɻ SQL Request Handler ͱ JDBC Driver Ͱఏڙɻ Some rights reserved by cogdogblog -
Parallel SQL SQL จ SolrCloud ͷෳͷϫʔΧʔϊʔυͰฒྻ࣮ߦ͢Δ Streaming Expression ʹίϯ ύΠϧ͞ΕΔɻ
SolrCloud ͷ Collection ϦϨʔγϣφϧͳςʔϒϧͱͯ͠நԽ͞ΕΔɻ WHERE ۟ Lucene / Solr ͷΫΤϦߏจΛαϙʔτɻ άϧʔϐϯάूܭͷΑ͏ͳଟ͘ͷΦϖϨʔγϣϯɺStreaming expressions ͷϦΞϧλ ΠϜ MapReduce ͷػೳΛར༻ͯ͠ɺࣗಈతʹฒྻԽ͞ΕΔɻ άϧʔϐϯά / ूܭύϑΥʔϚϯε͕Լ͢Δࣄ͕͋Δ͕ɺJSON Facet API ͰύϑΥʔ ϚϯεΛվળ͢Δࣄ͕Ͱ͖Δɻ ݱࡏ SolrCloud ϞʔυͷΈαϙʔτɻελϯυΞϩʔϯϞʔυͰະରԠɻ SQL ػೳݱࡏɺ࣮ݧతͰશͯͷ SQL ߏจ࣮͞Ε͍ͯͳ͍ɻ -
SQL Request Handler σϑΥϧτͰ SQL Λड͚͚Δɺ/sql ͱ͍͏໊ લͷϦΫΤετϋϯυϥʔΛఏڙɻ ϦΫΤετϋϯυϥʔʹରͯ͠ɺSQL จΛൃߦ
͢Δ͜ͱͰɺSolr ͷΠϯσοΫε͔Β݅ʹ Ϛον͢ΔυΩϡϝϯτ (σʔλ) Λநग़ɻ Some rights reserved by christiaan_008 -
SQL Request Handler • ϦΫΤεταϯϓϧ • Ϩεϙϯεαϯϓϧ $ curl --data-urlencode
\ 'stmt=SELECT to, count(*) FROM collection4 GROUP BY to ORDER BY count(*) desc LIMIT 10' \ http://localhost:8983/solr/collection4/sql?aggregationMode=facet {"result-set":{"docs":[ {"count(*)":9158,"to":"[email protected]"}, {"count(*)":6244,"to":"[email protected]"}, {"count(*)":5874,"to":"[email protected]"}, {"count(*)":5867,"to":"[email protected]"}, {"count(*)":5595,"to":"[email protected]"}, {"count(*)":4904,"to":"[email protected]"}, {"count(*)":4622,"to":"[email protected]"}, {"count(*)":3819,"to":"[email protected]"}, {"count(*)":3678,"to":"[email protected]"}, {"count(*)":3653,"to":"[email protected]"}, {"EOF":"true","RESPONSE_TIME":10}]} } -
JDBC Driver SQL αϙʔτʹ͍ɺैདྷ͔Β͋ΔΫϥΠ ΞϯτϥΠϒϥϦͷ SolrJ ( Java ΫϥΠΞϯ τϥΠϒϥϦ
) ʹɺJDBC υϥΠόʔ͕Έ ࠐ·ΕΔɻ JDBC υϥΠόʔܦ༝ͰɺSQL Request Handler Λར༻ͯ͠ɺSolr ͷΠϯσοΫε ΞΫηεՄೳɻ Some rights reserved by WashuOtaku -
JDBC Driver JDBC υϥΠόʔར༻࣌ʹҎԼͷϑΝΠϧΛ Java ΫϥεύεͷσΟϨΫτϦஔɻ JDBC υϥΠόʔΫϥε໊ org.apache.solr.client.solrj.io.sql.DriverImpl ଓจࣈྻ
jdbc:solr://<zk_connection_string>/?collection=<collection> $ find solr/solr-6.0.1/dist -name *.jar | grep -E "(/solr-solrj-.*\.jar)|(/solrj-lib/.*\.jar)" solr/solr-6.0.1/dist/solr-solrj-6.0.1.jar solr/solr-6.0.1/dist/solrj-lib/commons-io-2.4.jar solr/solr-6.0.1/dist/solrj-lib/httpclient-4.4.1.jar solr/solr-6.0.1/dist/solrj-lib/httpcore-4.4.1.jar solr/solr-6.0.1/dist/solrj-lib/httpmime-4.4.1.jar solr/solr-6.0.1/dist/solrj-lib/jcl-over-slf4j-1.7.7.jar solr/solr-6.0.1/dist/solrj-lib/noggit-0.6.jar solr/solr-6.0.1/dist/solrj-lib/slf4j-api-1.7.7.jar solr/solr-6.0.1/dist/solrj-lib/stax2-api-3.1.4.jar solr/solr-6.0.1/dist/solrj-lib/woodstox-core-asl-4.4.1.jar solr/solr-6.0.1/dist/solrj-lib/zookeeper-3.4.6.jar -
I Love Syntax Tee Shirt SQL Syntax SELECT จ •
WHERE ۟ • LIMIT ۟ • ORDER BY ۟ • DISTINCT ۟ • GROUP BY ۟ ౷ܭؔ countɺminɺmaxɺsumɺavg -
SQL Syntax fieldC ΛϑϨʔζ 'term1 term2' Ͱݕࡧ fieldC Λ 'term1'
ͱ 'term2' ͷ OR ݅Ͱݕࡧ fieldC ͕ 0 Ҏ্ɺ100 ҎԼͷ݅Ͱݕࡧ ( Solr ͷൣғݕࡧ ) ෳ݅ͷΈ߹Θͤ NOT ݕࡧ WHERE fieldC = 'term1 term2' WHERE fieldC = '(term1 term2)' WHERE fieldC = '[0 TO 100]' WHERE ((fieldC = 'term1' AND fieldA = 'term2') OR (fieldB = 'term3')) WHERE (fieldA = 'term1') AND NOT (fieldB = 'term2') -
Visualization and Analysis • Data Source ֎෦ͷσʔλͷೖखݩͱͳΔͷɻ σʔλϕεͳͲɻ • Data
Lake େྔͷσʔλΛѻ͏ͨΊͷετϨʔδɺ ϑΝΠϧஔ͖ͳͲɻ • Data Mart ༻్ɺతͳͲʹԠͯ͡ඞཁͳͷ͚ͩΛநग़ɺ ूܭ͠ɺར༻͍͢͠ܗʹ֨ೲͨ͠ͷɻ • User Interface σʔλʹରͯ͠ΞυϗοΫͳΫΤϦΛ༻͍ͯ ݕࡧɺநग़Λߦ͍ɺදάϥϑͳͲΛඳըɻ Some rights reserved by MMU Engage -
Data Lake Data Source Visualization and Analysis User Interface Data
Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream -
The Elastic Stack Logstash https://www.elastic.co/products/logstash ΠϕϯτϩάΛཧ͢ΔͨΊͷπʔϧɻ ҙͷγεςϜ͔Βϩά࣌ؒϕʔεͷ ΠϕϯτσʔλΛऔಘͰ͖Δɻ Elasticsearch https://www.elastic.co/products/elasticsearch
Lucene Λϕʔεʹͨ͠ɺॊೈͰڧྗͳ ΦʔϓϯιʔεͷࢄՄೳͳϦΞϧλΠϜ ݕࡧΤϯδϯɻ Kibana https://www.elastic.co/products/kibana Elasticsearch ΛόοΫΤϯυʹͨ͠ɺ μογϡϘʔυػೳΛͭσʔλ ͷՄࢹԽιϑτΣΞɻ Some rights reserved by paisleyorguk -
Apache Family Apache Flume http://flume.apache.org େྔͷσʔλΛޮతʹऩूɺू͓Αͼ Ҡಈͤ͞ΔͨΊͷࢄαʔϏεɻ Apache Solr http://lucene.apache.org/solr/
Apache Lucene ϓϩδΣΫτ͔Βੜͨ͠ɺ ߴͳΦʔϓϯιʔεͷݕࡧϓϥοτϑΥʔϜɻ Apache Zeppelin https://zeppelin.incubator.apache.org Web ϕʔεͷΠϯλϥΫςΟϒ UIɻ SQL Streaming ͷίϚϯυͷ݁ՌΛදʹܗ ͨ͠ΓɺάϥϑΛϓϩοτ͢Δ͜ͱ͕Ͱ͖Δɻ Some rights reserved by QuinnDombrowski -
Data Lake / Data Mart Data Source In The Case
of Solr User Interface Data Stream Banana / Silk SQuirreL SQL github.com mosuka/logstash-output-solr + github.com mosuka/fluent-plugin-output-solr + -
Apache Solr Some rights reserved by NASA Goddard Photo and
Video Data Lake Data Source User Interface Data Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream -
Apache Solr Solr Λ σʔλͷӬଓԽͷͨΊͷ Data Lakeɺ·ͨɺඞཁͳσʔλͷநग़ͷͨΊͷ Data Mart ͱͯ͠ར༻Λ͢Δɻ
Data-driven schemaless mode Λద༻ɺautoCommitɺsolftAutoCommit Λ༗ޮʹ͓ͯ͘͠ɻ add-unknown-fields-to-the-schema Update Request Processor Chain Ͱޡղऍ͞Εͯࠔ ΔϑΟʔϧυ͕͋Δ߹͋Β͔͡ΊɺSchema API ΛͬͯϑΟʔϧυఆٛΛߦ͏ɻ Solr 6.0.1 ʹɺγϯάϧγϟʔυͷ SolrCloud ڥͰɺSQL Request Handler ͷ aggregation ϞʔυΛ facet ͱͨ͠߹ (aggregationMode=facet)ɺྫ֎͕ൃੜ͢Δόά͕͋ Δɻ ClassCastException occurs in /sql handler with GROUP BY aggregationMode=facet and single shard https://github.com/apache/lucene-solr ࠷৽ͷιʔεΛར༻͢Δɻ -
Apache Flume Some rights reserved by post406 Data Lake Data
Source User Interface Data Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream -
Apache Flume Flume Λ Data Source ͔Βσʔλநग़͠ɺData Lake ͷసૹΛ͢ΔͨΊʹར༻Λ ͢ΔɻετϦʔϛϯάॲཧʹΑΓɺ΄΅ϦΞϧλΠϜͰͷॲཧ͕Մೳɻ
ݱࡏϦϦʔε͞Ε͍ͯΔɺFlume 1.6.0 Ͱɺαϙʔτ͞Ε͍ͯΔ Solr ͷόʔδϣ ϯ 4.3 ͱݹ͍ɻ ελϯυΞϩʔϯϞʔυͷ Solr σʔλΛૹ৴Ͱ͖Δ͕ɺSolr 5.x ͔Βͷ APIɺ༷ͷมߋʹ͍ɺSolrCloud Ϟʔυͷ Solr ʹσʔλΛૹ৴Ͱ͖ͳ͍ɻ https://issues.apache.org/jira/browse/FLUME-2919 Solr 6.x ରԠͨ͠ https://github.com/mosuka/flume Λར༻͢Δ -
Apache Zeppelin Some rights reserved by probabilistic Data Lake Data
Source User Interface Data Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream -
Apache Zeppelin Zeppelin Λ User Interface ͱͯ͠ར༻͠ɺData Mart ͷσʔλΛඞཁʹԠͯ͡ɺΞυ ϗοΫͳΫΤϦʹΑΔɺΠϯλϥΫςΟϒͳσʔλ͍߹Θͤɾੳ͕ՄೳͱͳΔɻ
ݱࡏϦϦʔε͞Ε͍ͯΔɺZeppelin 0.5.6-incubating ͰɺJDBC Driver αϙʔτ͞ Ε͍ͯͳ͍ɻ JDBC ରԠ 0.6.0 Ͱ༧ఆɻ JDBC Driver ରԠͨ͠ https://github.com/apache/incubator-zeppelin Λར༻͢ Δɻ Solr ͷଓઃఆํ๏Լه URL Ͱհ͞Ε͍ͯΔɻ Solr JDBC - Apache Zeppelin (incubating) https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=63406991 -
Lucidworks Banana Some rights reserved by danbri Data Lake Data
Source User Interface Data Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream -
Lucidworks Banana https://github.com/lucidworks/banana Banana Λ User Interface ͱͯ͠ར༻͠ɺData Mart ͷσʔλΛϦΞϧλΠϜʹՄ
ࢹԽΛ͢Δ͜ͱ͕͕ՄೳͱͳΔɻ Banana Kibana 3.x Λ Solr ͚ʹҠ২ͨ͠ͷɻ ݱࡏϦϦʔε͞Ε͍ͯΔɺBanana 1.6.0 ͰɺϚϧνϊʔυߏͨ͠߹ɺ Dashboard ઃఆΛ Solr ʹอଘ͢ΔࡍɺϦϞʔτͷ Solr Λࢀর͢Δ͜ͱ͕Ͱ͖ͳ ͍όά͕͋Δɻ·ͨɺSolr ίϨΫγϣϯ໊ͷ༷มߋʹ͍ɺDashboard ઃఆอ ଘ༻ͷίϨΫγϣϯ໊Λมߋ͢Δඞཁ͕͋Δɻ Pull Request : https://github.com/lucidworks/banana/pull/270 ্هόάରԠͨ͠ https://github.com/mosuka/banana Λར༻͢Δɻ -
Lucidworks Silk Some rights reserved by karen2754 Data Lake Data
Source User Interface Data Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream -
Lucidworks Silk https://github.com/lucidworks/silk Silk Λ User Interface ͱͯ͠ར༻͠ɺData Mart ͷσʔλΛϦΞϧλΠϜʹՄ
ࢹԽΛ͢Δ͜ͱ͕͕ՄೳͱͳΔɻ Silk Kibana 4.x Λ Solr ͚ʹҠ২ͨ͠ͷɻ ݱࡏͷ dev ϒϥϯν Solr 6.x ͔Βͷ schema.xml ʹະରԠͷField type ͷه ड़෦͕͋Δɻ Pull Request : https://github.com/lucidworks/silk/pull/11 ରԠͨ͠ https://github.com/mosuka/silk Λར༻͢Δɻ -
SQuirreL SQL Some rights reserved by likeaduck Data Lake Data
Source User Interface Data Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream -
SQuirreL SQL http://squirrel-sql.sourceforge.net SQuirrel SQL άϥϑΟΧϧͳσεΫτοϓΞϓϦέʔγϣϯɻ Java Ͱ։ൃ͞Ε͓ͯΓɺJVM ͕Πϯετʔϧ͞Ε͍ͯΔϚγϯͰ͋Εɺ
OS ΛΘ࣮ͣߦՄೳɻ JDBC Driver Λఏڙ͢ΔɺϦϨʔγϣφϧσʔλϕʔεͰ͋ΕɺଓՄೳɻ Solr ͷଓઃఆํ๏Լه URL Ͱհ͞Ε͍ͯΔɻ Solr JDBC - SQuirreL SQL https://cwiki.apache.org/confluence/display/solr/Solr+JDBC+-+SQuirreL +SQL -
Demonstration Some rights reserved by Sebastian Sikora User Interface Data
Lake /Data Mart Data Source Data Lake Data Source User Interface Data Mart Data Service Log Storage Storage Search Engine KVS RDBMS Search Graph Chart Storage Data Stream Banana / Silk SQuirreL SQL -
Some rights reserved by Miss Dilettante Documents and Software Apache
Solr Reference Guide https://cwiki.apache.org/confluence/display/solr/Apache+Solr+Reference+Guide Flume 1.6.0 User Guide https://flume.apache.org/FlumeUserGuide.html Zeppelin 0.6.0-SNAPSHOT document https://zeppelin.incubator.apache.org/docs/0.6.0-SNAPSHOT/ lucidworks/banana: Banana for Solr - A Port of Kibana https://github.com/lucidworks/banana lucidworks/silk: Silk is a port of Kibana 4 project. https://github.com/lucidworks/silk Cloudera Morphlines Reference Guide http://www.slideshare.net/cloudera/using-morphlines-for-onthefly-etl OSSͷπʔϧʮSolrʯʮFlumeʯʮBananaʯͷΈ߹ΘͤʹΑΔ σʔλՄࢹԽϓϥοτϑΥʔϜߏங https://codezine.jp/article/detail/8707 ʦվగ৽൛ʧApache Solrೖ――ΦʔϓϯιʔεશจݕࡧΤϯδϯ http://gihyo.jp/book/2014/978-4-7741-6163-1 αʔόʗΠϯϑϥΤϯδχΞཆಡຊ ϩάऩूʙՄࢹԽฤ http://gihyo.jp/book/2014/978-4-7741-6983-5 -
Documents and Software Apache Solr https://cwiki.apache.org/confluence/display/solr/ Apache+Solr+Reference+Guide Apache Flume https://flume.apache.org/FlumeUserGuide.html
Logstash https://codezine.jp/article/detail/8707 logstash-output-solr http://gihyo.jp/book/2014/978-4-7741-6163-1 Fluentd http://gihyo.jp/book/2014/978-4-7741-6163-1 fluent-plugin-output-solr http://gihyo.jp/book/2014/978-4-7741-6983-5 Apache Zeppelin https://zeppelin.incubator.apache.org/docs/0.6.0- SNAPSHOT/ Lucidworks Banana https://github.com/lucidworks/banana Lucidworks Silk https://github.com/lucidworks/silk SQuirrel SQL http://www.slideshare.net/cloudera/using-morphlines- for-onthefly-etl