Elasticsearch实现多维数据分析（OLAP）
-
1、前言
在线分析系统（OLAP）将已有的数据通过运算公式和转换规则聚合出信息，因此OLAP引擎应该至少能够进行：
- 一个或多个维度对数据进行提取、聚合、合计和预计算；
- 一个或多个维度进行逻辑运算、公式等方式的处理；
- 灵活的浏览分析，如一维和多维旋转、交叉表分析、上下钻取等；
Elasticsearch（ES）的聚合功能提供了多级分组和统计的能力。聚合类似关系数据库中group by的的功能，在ES中，一次查询中可以使用多维度聚合，这是一个很有用的功能，可以实现如下图所示的功能：
（图一）
-
2、导入数据到ES
我们可以使用多种方式将数据导入到ES中，本文中使用的工具是R3，R3默认提供了一个从Excel或CSV导入数据到ES的工具。在读取Excel文件的时候，R3会同时根据SHEET结构自动创建映射到ES的Mapping，格式如下：
{
rivues: {
mappings: {
1njcql: {
properties: {
000fir: {
type: "string",
index: "not_analyzed",
store: true
},
01sgte: {
type: "string",
index: "not_analyzed",
store: true
},
080ccu: {
type: "string",
index: "not_analyzed",
store: true
},
08dma1: {
type: "float",
store: true
},
0adfww: {
type: "string",
index: "not_analyzed",
store: true
},
0chxqe: {
type: "string",
index: "not_analyzed",
store: true
},
0dxunn: {
type: "string",
index: "not_analyzed",
store: true
},
0emwon: {
type: "string",
index: "not_analyzed",
store: true
},
0jakw8: {
type: "string",
index: "not_analyzed",
store: true
},
0ketwm: {
type: "float",
store: true
},
0kqgph: {
type: "string",
index: "not_analyzed",
store: true
},
0lzrdr: {
type: "float",
store: true
},
0mtoal: {
type: "float",
store: true
},
0osbwe: {
type: "float",
store: true
},
0ra89w: {
type: "string",
index: "not_analyzed",
store: true
},
0vkcgw: {
type: "string",
index: "not_analyzed",
store: true
},
0vpqs9: {
type: "string",
index: "not_analyzed",
store: true
},
0yrtd9: {
type: "string",
index: "not_analyzed",
store: true
},
19dyyx: {
type: "string",
index: "not_analyzed",
store: true
},
1dui0w: {
type: "string",
index: "not_analyzed",
store: true
},
1ii1lk: {
type: "string",
index: "not_analyzed",
store: true
},
1pgvv0: {
type: "string",
index: "not_analyzed",
store: true
},
1r9exi: {
type: "string",
index: "not_analyzed",
store: true
},
createtime: {
type: "string"
},
id: {
type: "string",
index: "not_analyzed",
store: true
}
}
}
}
}
}
字段名称来自于Excel文件中的列标题，对列标题做了Base62编码，编码之后的对应关系部分中文字段内容如下：
（图二）
-
3、建立数据分析模型
OLAP分析通常包括以下两个方面的内容。
- 第一，提供一个交互的数据分析的模式。OLAP分析把数据分成维度（Dimension）和度量（Measure或称之为指标）。用户通过选择Dimension（维度）和Measure（度量或指标），可以及时地计算数据在不同Dimension（维度）的组合下的汇总。维度包含层级关系，例如，时间维度，包含年，月，日等。
- 第二，OLAP分析是一种对数据的处理方式。
我们可以通过下图所示的结构描述数据分析模型
（图三）
- 上图中，维度下包含维度成员，维度成员之间，有层级关系。
-
4、简单的聚合查询
为了展现图一所示的查询效果，我们需要在数据模型的基础之上，构建查询语句。
ES官方的查询语言是DSL，主要分为两类：
- Query，相当于SQL中的SELECT等；
- Aggregation，相当于SQL中的GROUP部分，也支持Aggs的内部Filter。
DSL可以嵌套，表达复杂的查询操作，本文中使用的查询是由R3提供的一个查询构造器，R3提供的查询构造器，使用Hibernate作为开发接口，R3解析Hibernate的DetachedCriteria，并最终将DetachedCriteria翻译成为ES的查询DSL，查询语法如下：
{
"from" : 0,
"size" : 0,
"query" : {
"bool" : { }
},
"aggregations" : {
"项目类型" : {
"terms" : {
"field" : "1ii1lk",
"size" : 50,
"order" : {
"_term" : "asc"
}
},
"aggregations" : {
"合同时间" : {
"terms" : {
"field" : "01sgte",
"order" : {
"_term" : "asc"
}
},
"aggregations" : {
"合同甲方" : {
"terms" : {
"field" : "0emwon",
"order" : {
"_term" : "asc"
}
},
"aggregations" : {
"计划收款金额" : {
"sum" : {
"field" : "0ketwm",
"format" : "0.##"
}
},
"实际收款金额" : {
"sum" : {
"field" : "0lzrdr",
"format" : "###"
}
},
"计划付款金额" : {
"sum" : {
"field" : "0osbwe",
"format" : "0.##"
}
},
"实际付款金额" : {
"sum" : {
"field" : "0mtoal",
"format" : "0.##"
}
}
}
}
}
}
}
}
}
}
ES根据查询DSL进行查询，返回如下结果：
{
"took" : 40,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"failed" : 0
},
"hits" : {
"total" : 181,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"项目类型" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "",
"doc_count" : 1,
"合同时间" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "",
"doc_count" : 1,
"合同甲方" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "",
"doc_count" : 1,
"计划收款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际付款金额" : {
"value" : -6228345.0,
"value_as_string" : "-6228345"
},
"计划付款金额" : {
"value" : -6363768.0,
"value_as_string" : "-6363768"
},
"实际收款金额" : {
"value" : 0.0,
"value_as_string" : "0"
}
} ]
}
} ]
}
}, {
"key" : "R3",
"doc_count" : 76,
"合同时间" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "2011",
"doc_count" : 3,
"合同甲方" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "XXXXXXXXXXXXXXXXXXX",
"doc_count" : 3,
"计划收款金额" : {
"value" : xxxxx.0,
"value_as_string" : "xxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 400000.0,
"value_as_string" : "xxxxx"
}
} ]
}
}, {
"key" : "2013",
"doc_count" : 13,
"合同甲方" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "xxxxx",
"doc_count" : 1,
"计划收款金额" : {
"value" : 325000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 162500.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 1,
"计划收款金额" : {
"value" : 210000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 210000.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 2,
"计划收款金额" : {
"value" : 440000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 440000.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 2,
"计划收款金额" : {
"value" : 240000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 72000.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 3,
"计划收款金额" : {
"value" : 230000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 184000.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 4,
"计划收款金额" : {
"value" : 580000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 580000.0,
"value_as_string" : "xxxxx"
}
} ]
}
}, {
"key" : "2014",
"doc_count" : 30,
"合同甲方" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "xxxxx",
"doc_count" : 2,
"计划收款金额" : {
"value" : 380000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 380000.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 7,
"计划收款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际付款金额" : {
"value" : -444905.15625,
"value_as_string" : "xxxxx.16"
},
"计划付款金额" : {
"value" : -444905.1484375,
"value_as_string" : "xxxxx.15"
},
"实际收款金额" : {
"value" : 0.0,
"value_as_string" : "0"
}
}, {
"key" : "xxxxx",
"doc_count" : 10,
"计划收款金额" : {
"value" : 1016720.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 849720.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 3,
"计划收款金额" : {
"value" : 500000.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 450000.0,
"value_as_string" : "xxxxx"
}
}, {
"key" : "xxxxx",
"doc_count" : 8,
"计划收款金额" : {
"value" : 686400.0,
"value_as_string" : "xxxxx"
},
"实际付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"计划付款金额" : {
"value" : 0.0,
"value_as_string" : "0"
},
"实际收款金额" : {
"value" : 686400.0,
"value_as_string" : "xxxxx"
}
} ]
}
}
}
}
}
对结果进行格式化，通过表格显示如下图：
（图四）
- 通过图表显示：
（图五）
（图六）
（图七）