Cloudera公司已经推出了基于Hadoop平台的查询统计分析工具Impala,只要熟悉SQL,就可以熟练地使用Impala来执行查询与分析的功能。不过Impala的SQL和关系数据库的SQL还是有一点微妙地不同的。
下面,我们设计一个表,通过该表中的数据,来将SQL查询与统计的语句,使用Solr查询的方式来与SQL查询对应。这个翻译的过程,是非常有趣的,你可以看到Solr一些很不错的功能。
用来示例的表结构设计,如图所示:
<ignore_js_op>
下面,我们通过给出一些SQL查询统计语句,然后对应翻译成Solr查询语句,然后对比结果
查询对比
条件组合查询
SQL查询语句:
- SELECT log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type
- FROM v_i_event
- WHERE prov_id = 1 AND net_type = 1 AND area_id = 10304 AND time_type = 1 AND time_id >= 20130801 AND time_id <= 20130815
- ORDER BY log_id LIMIT 10;
查询结果,如图所示:
<ignore_js_op>
Solr查询URL:
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=prov_id:1 AND net_type:1 AND area_id:10304 AND time_type:1 AND time_id:[20130801 TO 20130815]&sort=log_id asc&start=0&rows=10
查询结果,如下所示:
- <response>
- <lst name="responseHeader">
- <int name="status">0</int>
- <int name="QTime">4</int>
- </lst>
- <result name="response" numFound="77" start="0">
- <doc>
- <int name="log_id">6827</int>
- <long name="start_time">1375072117</long>
- <long name="end_time">1375081683</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10304</int>
- <int name="idt_id">11002</int>
- <int name="cnt">0</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6827</int>
- <long name="start_time">1375072117</long>
- <long name="end_time">1375081683</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10304</int>
- <int name="idt_id">11000</int>
- <int name="cnt">0</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6851</int>
- <long name="start_time">1375142158</long>
- <long name="end_time">1375146391</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10304</int>
- <int name="idt_id">14001</int>
- <int name="cnt">5</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6851</int>
- <long name="start_time">1375142158</long>
- <long name="end_time">1375146391</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10304</int>
- <int name="idt_id">11002</int>
- <int name="cnt">23</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6851</int>
- <long name="start_time">1375142158</long>
- <long name="end_time">1375146391</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10304</int>
- <int name="idt_id">10200</int>
- <int name="cnt">55</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6851</int>
- <long name="start_time">1375142158</long>
- <long name="end_time">1375146391</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10304</int>
- <int name="idt_id">14000</int>
- <int name="cnt">4</int>
对比上面结果,除了根据idt_id排序方式不同以外(Impala是升序,Solr是降序),其他是相同的。
单个字段分组统计
SQL查询语句:
- SELECT prov_id, SUM(cnt) AS sum_cnt, AVG(cnt) AS avg_cnt, MAX(cnt) AS max_cnt, MIN(cnt) AS min_cnt, COUNT(cnt) AS count_cnt
- FROM v_i_event
- GROUP BY prov_id;
查询结果,如图所示:
<ignore_js_op>
Solr查询URL:
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&stats=true&stats.field=cnt&rows=0&indent=true
查询结果,如下所示:
- <response>
- <lst name="responseHeader">
- <int name="status">0</int>
- <int name="QTime">2</int>
- </lst>
- <result name="response" numFound="4088" start="0"></result>
- <lst name="stats">
- <lst name="stats_fields">
- <lst name="cnt">
- <double name="min">0.0</double>
- <double name="max">1258.0</double>
- <long name="count">4088</long>
- <long name="missing">0</long>
- <double name="sum">32587.0</double>
- <double name="sumOfSquares">9170559.0</double>
- <double name="mean">7.971379647749511</double>
- <double name="stddev">46.69344567709268</double>
- <lst name="facets" />
- </lst>
- </lst>
- </lst>
- </response>
对比查询结果,Solr提供了更多的统计项,如标准差(stddev)等,与SQL查询结果是一致的。
IN条件查询
SQL查询语句:
[cde lang="sql"]
SELECT log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_typ
FROM v_i_event
WHERE prov_id = 1 AND net_type = 1 ANDcity_id IN(106,103) AND idt_id IN(12011,5004,6051,6056,8002) AND time_type = 1AND time_id >= 20130801 AND time_id <= 20130815
ORDER BY log_id, start_time DESC LIMIT 10;
[/code]
查询结果,如图所示:
<ignore_js_op>
Solr查询URL:
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id, cnt,net_type&fq=prov_id:1 AND net_type:1 AND (city_id:106 OR city_id:103) AND (idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002) AND time_type:1 AND time_id:[20130801 TO 20130815]&sort=log_id asc ,start_time desc&start=0&rows=10
或
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id, cnt ,net_type&fq=prov_id:1&fq=net_type:1&fq=(city_id:106 OR city_id:103)&fq=(idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002)&fq=time_type:1&fq=time_id:[20130801 TO 20130815]&sort=log_id asc,start_time desc&start=0&rows=10
查询结果,如下所示:
- <response>
- <lst name="responseHeader">
- <int name="status">0</int>
- <int name="QTime">6</int>
- </lst>
- <result name="response" numFound="63" start="0">
- <doc>
- <int name="log_id">6553</int>
- <long name="start_time">1374054184</long>
- <long name="end_time">1374054254</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10307</int>
- <int name="idt_id">12011</int>
- <int name="cnt">0</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6553</int>
- <long name="start_time">1374054184</long>
- <long name="end_time">1374054254</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10307</int>
- <int name="idt_id">5004</int>
- <int name="cnt">2</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6555</int>
- <long name="start_time">1374055060</long>
- <long name="end_time">1374055158</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">70104</int>
- <int name="idt_id">5004</int>
- <int name="cnt">3</int>
- <int name="net_type">1</int>
对比查询结果,是一致的。
开区间范围条件查询
SQL查询语句:
SELECTlog_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type
FROM v_i_event
WHERE net_type = 1 AND idt_idIN(12011,5004,6051,6056,8002) AND time_type = 1 AND start_time >= 1373598465AND end_time < 1374055254
ORDER BY log_id, start_time, idt_id DESCLIMIT 30;
查询结果,如图所示:
<ignore_js_op>
Solr查询URL:
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=net_type:1 AND (idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002) AND time_type:1 AND start_time:[1373598465 TO 1374055254]&fq =-start_time:1374055254&sort=log_id asc,start_time asc,idt_id desc&start=0&rows=30
或
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=net_type:1 AND (idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002) AND time_type:1 AND start_time:[1373598465 TO 1374055254] AND -start_time:1374055254&sort=log_id asc,start_time asc,idt_id desc&start=0&rows=30
或
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=net_type:1&fq=idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002&fq =time_type:1&fq=start_time:[1373598465 TO 1374055254]&fq =-start_time:1374055254&sort=log_id asc,start_time asc,idt_id desc&start=0&rows=30
查询结果,如下所示:
- <response>
- <lst name="responseHeader">
- <int name="status">0</int>
- <int name="QTime">5</int>
- </lst>
- <result name="response" numFound="4" start="0">
- <doc>
- <int name="log_id">6553</int>
- <long name="start_time">1374054184</long>
- <long name="end_time">1374054254</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10307</int>
- <int name="idt_id">12011</int>
- <int name="cnt">0</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6553</int>
- <long name="start_time">1374054184</long>
- <long name="end_time">1374054254</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">10307</int>
- <int name="cnt">2</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6555</int>
- <long name="start_time">1374055060</long>
- <long name="end_time">1374055158</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">70104</int>
- <int name="idt_id">12011</int>
- <int name="cnt">0</int>
- <int name="net_type">1</int>
- </doc>
- <doc>
- <int name="log_id">6555</int>
- <long name="start_time">1374055060</long>
- <long name="end_time">1374055158</long>
- <int name="prov_id">1</int>
- <int name="city_id">103</int>
- <int name="area_id">70104</int>
- <int name="idt_id">5004</int>
- <int name="cnt">3</int>
- <int name="net_type">1</int>
- </doc>
- </result>
- </response>
多个字段分组统计(只支持count函数)
SQL查询语句:
SELECT city_id, area_id, COUNT(cnt) AScount_cnt
FROM v_i_event
WHERE prov_id = 1 AND net_type = 1
GROUP BY city_id, area_id;
查询结果,如图所示:
<ignore_js_op>
Solr查询URL:
- http://slave1:8888/solr-cloud/i_event/select?q=*:*&facet=true&facet.pivot=city_id,area_id&fq=prov_id:1 AND net_type:1&rows=0&indent=true
对比上面结果,Solr查询结果,需要从上面的各组中进行合并,得到最终的统计结果,结果和SQL结果是一致的。
多个字段分组统计(支持count、sum、max、min等函数)
一次对多个字段进行独立分组统计,Solr可以很好的支持。这相当于执行两个带有GROUP BY子句的SQL,这两个GROUP BY分别只对一个字段进行汇总统计。
SQL查询语句:
- SELECT city_id, area_id, COUNT(cnt) AS count_cnt
- FROM v_i_event
- WHERE prov_id = 1 AND net_type = 1
- GROUP BY city_id;
- SELECT city_id, area_id, COUNT(cnt) AS count_cnt
- FROM v_i_event
- WHERE prov_id = 1 AND net_type = 1
- GROUP BY area_id;
查询结果,不再显示。
Solr查询URL:
- >http://slave1:8888/solr-cloud/i_event/select?q=*:*&stats=true&stats.field=cnt&f.cnt.stats.facet=city_id&&f.cnt.stats.facet=area_id&fq=prov_id:1 AND net_type:1&rows=0&indent=true
查询结果,如下所示:
- <response>
- <lst name="responseHeader">
- <int name="status">0</int>
- <int name="QTime">72</int>
- </lst>
- <result name="response" numFound="1171" start="0"></result>
- <lst name="facet_counts">
- <lst name="facet_queries" />
- <lst name="facet_fields" />
- <lst name="facet_dates" />
- <lst name="facet_ranges" />
- <lst name="facet_pivot">
- <arr name="city_id,area_id">
- <lst>
- <str name="field">city_id</str>
- <int name="value">103</int>
- <int name="count">678</int>
- <arr name="pivot">
- <lst>
- <str name="field">area_id</str>
- <int name="value">10307</int>
- <int name="count">298</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">10315</int>
- <int name="count">120</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">10317</int>
- <int name="count">86</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">10304</int>
- <int name="count">67</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">10310</int>
- <int name="count">49</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">70104</int>
- <int name="count">48</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">10308</int>
- <int name="count">6</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">0</int>
- <int name="count">2</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">10311</int>
- <int name="count">2</int>
- </lst>
- </arr>
- </lst>
- <lst>
- <str name="field">city_id</str>
- <int name="value">0</int>
- <int name="count">463</int>
- <arr name="pivot">
- <lst>
- <str name="field">area_id</str>
- <int name="value">0</int>
- <int name="count">395</int>
- </lst>
- <lst>
- <str name="field">area_id</str>
- <int name="value">10307</int>
- <int name="count">68</int>
对比上面结果,Solr查询结果,需要从上面的各组中进行合并,得到最终的统计结果,结果和SQL结果是一致的。
多个字段联合分组统计(支持count、sum、max、min等函数)
SQL查询语句:
SELECT city_id, area_id, SUM(cnt) ASsum_cnt, AVG(cnt) AS avg_cnt, MAX(cnt) AS max_cnt, MIN(cnt) AS min_cnt,COUNT(cnt) AS count_cnt
FROM v_i_event
WHERE prov_id = 1 AND net_type = 1
GROUP BY city_id, area_id;
查询结果,如图所示:
<ignore_js_op>
Solr目前不能简单的支持这种查询,如果想要满足这种查询统计,需要在schema的设计上,将一个字段设置为多 值,然后通过多个值进行分组统计。如果应用中查询统计分析的模式比较固定,预先知道哪些字段会用于联合分组统计,完全可以在设计的时候,考虑设置多值字段 来满足这种需求。
感兴趣的读者,还可以看看这里:基于Solr DIH实现MySQL表数据全量索引和增量索引
相关推荐
使用java实现solr-7.1.0的api和solr最新支持的sql查询.使用java实现solr-7.1.0的api和solr最新支持的sql查询.
Solr-SQL为Solr Cloud提供了SQL接口,开发人员可以通过JDBC协议在Solr Cloud上运行。同时,solr-sql是用于solr的Apache Calcite(见 http://calcite.apache.org)适配器。solr-sql 是用 Scala 编写的,它可以生成像 ...
solr和sql映射工具
Solr数据库连接[SQL,Oracle]SQL Service及Oracle连接Jar报
sparksql模型solr-poc POC在Solr中存储机器学习模型在hadoop生态系统的大多数技术部分(例如hive,spark等)中,推荐的存储机器学习模型的格式是木地板格式(由ASF开发)。 此POC试图在Solr中读取,解析并存储实木...
拓展知识中首先讲解了Solr的一些比较生僻的知识点,如伪域、多语种索引支持、安全认证,以及Solr 6.x中的SQL接口和Streaming表达式等;然后讲解了Solr与MapReduce、HDFS、Hbase、Kafka、Flume、Storm、Spark等...
solr-sql是针对solrcloud封装的sql编程接口,主要支持SELECT...FROM...WHERE查询语句。 SolrCloud是基于ZooKeeper和Solr的分布式解决方案,为Solr添加分布式功能,用于建立高可用,高伸缩,自动容错,分布式...
拓展知识中首先讲解了Solr的一些比较生僻的知识点,如伪域、多语种索引支持、安全认证,以及Solr 6.x中的SQL接口和Streaming表达式等;然后讲解了Solr与MapReduce、HDFS、Hbase、Kafka、Flume、Storm、Spark等...
Cloudera Search 使用的...Cloudera Search提供近实时(Near-Real-Time)数据访问服务,允许非技术人员通过简单的全文搜索接口,实现快速查询、浏览存储在Hadoop和Hase上的数据,而不需要掌握SQL、编程技能就可以使用。
JAVA语言,实现SSM+SQL Server 数据库整合,通过spring-data-solr框架实现与solr平台的互通,实现全文搜索功能,亲测,完全了可以用
SQL语句实现按关健字模糊查询,并按匹配度排序
solr_client_sql_api solr sql客户端封装 jdbc + sql 转换
solr4.4版本,解压后可以放于tomcat下运行,可以配置数据库连接及SQL语句,将查询结果放在solr中缓存,项目直接操作solr,可以配置定时任务(PS:定时任务只支持到4.4版本,以后版本目前没有)solr作为数据库和项目...
Hive作为Hadoop生态系统里面离线的数据仓库,可以非常方便的使用SQL的方式来离线分析海量的历史数据,并根据分析的结果,来干一些其他的事情,如报表统计查询等。 Solr作为高性能的搜索服务器,能够提供快速,强大的...
Tutorialspoint Selenium SQLServer Swift Solr SVN SQLite Socket Smarty Shell Sed 教程
Phoniex 可以让开发者在HBase数据集上使用SQL查询。Phoenix查询引擎会将SQL查询转换为一个或多个HBase scan,并编排执行以生成标准的JDBC结果集,对于简单查询来说,性能甚至胜过Hive。 标签:Phoniex
springjdbc-typed-queries.zip,使用sql查询获取文件,并生成java方法以使用jdbctemplate执行每个查询。
索引是设计表的一部分,创建的索引对sql的语句木有任何影响,对sql语句的执行效率有影响
SQLToNoSQLImporter是类似于Solr的数据导入处理程序,用于将Sql(MySQL,Oracle,PostgreSQL)数据导入NoSQL系统(Mongodb,CouchDB,Elastic Search)。 迁移现在完全由配置驱动。 希望用户编写配置,此工具会将...
solr-loader3 Clojure 工具,用于从数据库中的关系数据创建 solr 索引。 此工具可用作 solr 发行版中提供的数据导入处理程序 (DIH) 的替代方案。 使用此工具代替 DIH 的主要优点是性能和简单的配置。 用法 从命令行...