ES in BI

  1. 云栖社区>
  2. 博客>
  3. 正文

ES in BI

小金子 2016-10-10 14:51:19 浏览688
展开阅读全文

一直想找一个用于大数据平台实时OLAP(甚至是实时计算)的框架,之前调研的Druid(druid.io)太过复杂,整个Druid由5、6个服务组成,而且加载数据也不太方便,性能一般,亦或是我还不太会用它。后来发现使用ElasticSearch就可以满足海量数据实时OLAP的需求。
ElasticSearch相信大家都很熟悉了,它在搜索领域已经有了举足轻重的地位,而且也支持越来越多的聚合统计功能,还和YARN、Hadoop、Hive、Spark、Pig、Flume等大数据框架兼容的越来越好,比如:可以将ElasticSearch跑在YARN上,还可以在Hive中建立外部表映射到ElasticSearch的Index中,直接在Hive中执行INSERT语句,将数据加载进ElasticSearch。
所谓OLAP,其实就是从事实表中统计任意组合维度的指标,也就是过滤、分组、聚合,其中,聚合除了一般的SUM、COUNT、AVG、MAX、MIN等,还有一个重要的COUNT(DISTINCT),看上去这些操作在SQL中是非常简单的统计,但在海量数据、低延迟的要求下,并不是那么容易做的。
ElasticSearch本来就是做实时搜索的,过滤自然不是问题,现在也支持各种聚合以及Pipeline aggregations(相当于SQL子查询的功能),而且ElasticSearch的安装部署也非常简单,一个节点只有一个服务进程,关于安装配置可参考:http://lxw1234.com/archives/2015/12/582.htm
本文以两个业务场景的例子,看一下ElasticSearch是如何满足我们的需求的。

例子1:网站流量报告

在我们的报表平台有这样一张报表,用于查看每个网站每天的流量指标:

elasticsearch

其中,维度有:天、小时、网站,指标有:PV、UV、访问次数、跳出率、平均停留时间、回访率等。另外,还有一张报表是地域报告,维度多了省份和城市,指标一样。目前的做法是将可选的维度组合及对应的指标先在Hive中分析好,再将结果同步至MySQL,供报表展现。

真正意义上的OLAP做法,我是这样做的:在Hive分析好一张最细粒度为visit_id(session_id)的事实表,字段及数据如下:

elasticsearch

然后将这张事实表的数据加载到ElasticSearch中的logs2/sitelog1211中。查看数据:


  1. curl -XGET 'http://localhost:9200/logs2/sitelog1211/_search?pretty'
  2. {
  3. "took" : 1015,
  4. "timed_out" : false,
  5. "_shards" : {
  6. "total" : 10,
  7. "successful" : 10,
  8. "failed" : 0
  9. },
  10. "hits" : {
  11. "total" : 3356328,
  12. "max_score" : 1.0,
  13. "hits" : [ {
  14. "_index" : "logs2",
  15. "_type" : "sitelog1211",
  16. "_id" : "AVGkoWowd8ibEMoyOhve",
  17. "_score" : 1.0,
  18. "_source":{"cookieid" : "8F97E07300BC7655F6945A","siteid" : "633","visit_id" : "feaa25e6-3208-4801-b7ed-6fa45f11ff42","pv" : 2,"is_return_cookie" : 0,
  19. "is_bounce_visit" : 0,"visit_stay_times" : 34,"visit_view_page_cnt" : 2, "region" : "浙江","city" : "绍兴"}
  20. },
  21. ……

该天事实表中总记录数为3356328
接着使用下面的查询,完成了上图中网站ID为1127,日期为2015-12-11的流量报告:


  1. curl -XGET 'http://localhost:9200/logs2/sitelog1211/_search?search_type=count&q=siteid:1127&pretty' -d '
  2. {
  3. "size": 0,
  4. "aggs" : {
  5. "pv" : {"sum" : { "field" : "pv" } },
  6. "uv" : {"cardinality" : {"field" : "cookieid" ,"precision_threshold": 40000}},
  7. "return_uv" : {
  8. "filter" : {"term" : {"is_return_cookie" : 1}},
  9. "aggs" : {
  10. "total_return_uv" : {"cardinality" : {"field" : "cookieid" ,"precision_threshold": 40000}}
  11. }
  12. },
  13. "visits" : {"cardinality" : {"field" : "visit_id" ,"precision_threshold": 40000}},
  14. "total_stay_times" : {"sum" : { "field" : "visit_stay_times" }},
  15. "bounce_visits" : {
  16. "filter" : {"term" : {"is_bounce_visit" : 1}},
  17. "aggs" : {
  18. "total_bounce_visits" : {"cardinality" : {"field" : "visit_id" ,"precision_threshold": 40000}}
  19. }
  20. }
  21. }
  22. }'

基本上1~2秒就可以返回结果:


  1. {
  2. "took" : 1887,
  3. "timed_out" : false,
  4. "_shards" : {
  5. "total" : 10,
  6. "successful" : 10,
  7. "failed" : 0
  8. },
  9. "hits" : {
  10. "total" : 5888,
  11. "max_score" : 0.0,
  12. "hits" : [ ]
  13. },
  14. "aggregations" : {
  15. "uv" : {
  16. "value" : 5859
  17. },
  18. "visits" : {
  19. "value" : 5889
  20. },
  21. "return_uv" : {
  22. "doc_count" : 122,
  23. "total_return_uv" : {
  24. "value" : 119
  25. }
  26. },
  27. "bounce_visits" : {
  28. "doc_count" : 5177,
  29. "total_bounce_visits" : {
  30. "value" : 5177
  31. }
  32. },
  33. "pv" : {
  34. "value" : 10820.0
  35. },
  36. "total_stay_times" : {
  37. "value" : 262810.0
  38. }
  39. }
  40. }
  41.  

接着是地域报告中维度为省份的指标统计,查询语句为:


  1. curl -XGET 'http://localhost:9200/logs2/sitelog1211/_search?search_type=count&q=siteid:1127&pretty' -d '
  2. {
  3. "size": 0,
  4. "aggs" : {
  5. "area_count" : {
  6. "terms" : {"field" : "region","order" : { "pv" : "desc" }},
  7. "aggs" : {
  8. "pv" : {"sum" : { "field" : "pv" } },
  9. "uv" : {"cardinality" : {"field" : "cookieid" ,"precision_threshold": 40000}},
  10. "return_uv" : {
  11. "filter" : {"term" : {"is_return_cookie" : 1}},
  12. "aggs" : {
  13. "total_return_uv" : {"cardinality" : {"field" : "cookieid" ,"precision_threshold": 40000}}
  14. }
  15. },
  16. "visits" : {"cardinality" : {"field" : "visit_id" ,"precision_threshold": 40000}},
  17. "total_stay_times" : {"sum" : { "field" : "visit_stay_times" }},
  18. "bounce_visits" : {
  19. "filter" : {"term" : {"is_bounce_visit" : 1}},
  20. "aggs" : {
  21. "total_bounce_visits" : {"cardinality" : {"field" : "visit_id" ,"precision_threshold": 40000}}
  22. }
  23. }
  24. }
  25. }
  26. }
  27. }'

因为要根据省份分组,比之前的查询慢一点,但也是秒级返回:


  1. {
  2. "took" : 4349,
  3. "timed_out" : false,
  4. "_shards" : {
  5. "total" : 10,
  6. "successful" : 10,
  7. "failed" : 0
  8. },
  9. "hits" : {
  10. "total" : 5888,
  11. "max_score" : 0.0,
  12. "hits" : [ ]
  13. },
  14. "aggregations" : {
  15. "area_count" : {
  16. "doc_count_error_upper_bound" : 0,
  17. "sum_other_doc_count" : 2456,
  18. "buckets" : [ {
  19. "key" : "北京",
  20. "doc_count" : 573,
  21. "uv" : {
  22. "value" : 568
  23. },
  24. "visits" : {
  25. "value" : 573
  26. },
  27. "return_uv" : {
  28. "doc_count" : 9,
  29. "total_return_uv" : {
  30. "value" : 8
  31. }
  32. },
  33. "bounce_visits" : {
  34. "doc_count" : 499,
  35. "total_bounce_visits" : {
  36. "value" : 499
  37. }
  38. },
  39. "pv" : {
  40. "value" : 986.0
  41. },
  42. "total_stay_times" : {
  43. "value" : 24849.0
  44. }
  45. }, {
  46. "key" : "山东",
  47. "doc_count" : 368,
  48. "uv" : {
  49. "value" : 366
  50. },
  51. "visits" : {
  52. "value" : 368
  53. },
  54. "return_uv" : {
  55. "doc_count" : 9,
  56. "total_return_uv" : {
  57. "value" : 9
  58. }
  59. },
  60. "bounce_visits" : {
  61. "doc_count" : 288,
  62. "total_bounce_visits" : {
  63. "value" : 288
  64. }
  65. },
  66. "pv" : {
  67. "value" : 956.0
  68. },
  69. "total_stay_times" : {
  70. "value" : 30266.0
  71. }
  72. },
  73. ……
  74.  

这里需要说明一下,在ElasticSearch中,对于去重计数(COUNT DISTINCT)是基于计数估计(Cardinality),因此如果去重记录数比较大(超过40000),便可能会有误差,误差范围是0~2%。

例子2:用户标签的搜索统计

有一张数据表,存储了每个用户ID对应的标签,同样加载到ElasticSearch中,数据格式如下:


  1. curl -XGET 'http://localhost:9200/lxw1234/user_tags/_search?&pretty'
  2. {
  3. "took" : 220,
  4. "timed_out" : false,
  5. "_shards" : {
  6. "total" : 10,
  7. "successful" : 10,
  8. "failed" : 0
  9. },
  10. "hits" : {
  11. "total" : 820165,
  12. "max_score" : 1.0,
  13. "hits" : [ {
  14. "_index" : "lxw1234",
  15. "_type" : "user_tags",
  16. "_id" : "222222222222222",
  17. "_score" : 1.0,
  18. "_source":{"sex" : "女性","age" : "27到30岁","income" : "5000到10000","edu" : "本科",
  19. "appcategory" : "娱乐类|1.0","interest" : "","onlinetime" : "9:00~12:00|1.0","os" : "IOS|1.0",
  20. "hobby" : "游戏|28.57,房产|8.57,服饰鞋帽箱包|28.57,互联网/电子产品|5.71,家居|8.57,餐饮美食|5.71,体育运动|14.29","region" : "河南省"}
  21. }
  22. ......

每个用户都有性别、年龄、收入、教育程度、兴趣、地域等标签,其中使用_id来存储用户ID,也是主键。

查询1:SELECT count(1) FROM user_tags WHERE sex = ‘女性’ AND appcategory LIKE ‘%游戏类%';


  1. curl -XGET 'http://localhost:9200/lxw1234/user_tags/_count?pretty' -d '
  2. {
  3. "filter" : {
  4. "and" : [
  5. {"term" : {"sex" : "女性"}},
  6. {"match_phrase" : {"appcategory" : "游戏类"}}
  7. ]
  8. }
  9. }'

返回结果:


  1. {
  2. "count" : 106977,
  3. "_shards" : {
  4. "total" : 10,
  5. "successful" : 10,
  6. "failed" : 0
  7. }
  8. }

查询2:先筛选,再分组统计:


  1. SELECT edu,COUNT(1) AS cnt
  2. FROM user_tags
  3. WHERE sex = '女性'
  4. AND appcategory LIKE '%游戏类%'
  5. GROUP BY edu
  6. ORDER BY cnt DESC
  7. limit 10;

查询语句:


  1. curl -XGET 'http://localhost:9200/lxw1234/user_tags/_search?search_type=count&pretty' -d '
  2. {
  3. "filter" : {
  4. "and" : [
  5. {"term" : {"sex" : "女性"}},
  6. {"match_phrase" : {"appcategory" : "游戏类"}}
  7. ]
  8. },
  9. "aggs" : {
  10. "edu_count" : {
  11. "terms" : {
  12. "field" : "edu",
  13. "size" : 10
  14. }
  15. }
  16. }
  17. }'

返回结果:


  1. {
  2. "took" : 479,
  3. "timed_out" : false,
  4. "_shards" : {
  5. "total" : 10,
  6. "successful" : 10,
  7. "failed" : 0
  8. },
  9. "hits" : {
  10. "total" : 106977,
  11. "max_score" : 0.0,
  12. "hits" : [ ]
  13. },
  14. "aggregations" : {
  15. "edu_count" : {
  16. "doc_count_error_upper_bound" : 0,
  17. "sum_other_doc_count" : 0,
  18. "buckets" : [ {
  19. "key" : "本科",
  20. "doc_count" : 802670
  21. }, {
  22. "key" : "硕士研究生",
  23. "doc_count" : 16032
  24. }, {
  25. "key" : "专科",
  26. "doc_count" : 1433
  27. }, {
  28. "key" : "博士研究生",
  29. "doc_count" : 25
  30. }, {
  31. "key" : "初中及以下",
  32. "doc_count" : 4
  33. }, {
  34. "key" : "中专/高中",
  35. "doc_count" : 1
  36. } ]
  37. }
  38. }
  39. }
  40.  

从目前的调研结果来看,ElasticSearch没有让人失望,部署简单,数据加载方便,聚合功能完备,查询速度快,目前完全可以满足我们的实时搜索、统计和OLAP需求,甚至可以作为NOSQL来使用,接下来再做更深入的测试。
另外,还有一个开源的SQL for ElasticSearch的框架Crate(crate.io),是在ElasticSearch之上封装了SQL接口,使得查询统计更加方便,不过SQL支持的功能有限,使用的ElasticSearch版本较低,后面试用一下再看。

网友评论

登录后评论
0/500
评论
小金子
+ 关注