mysql多维模型,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"

}

} ]

}

}

}

}

}

对结果进行格式化，通过表格显示如下图：

(图四)

通过图表显示：

(图五)

(图六)

(图七)