Mondrian对Hive的支持
一.测试Mondrian对Hive的支持
1.创建一个web项目,把olap4j.jar Mondrian.jar以及hive相关的jar包放进项目中
2. 准备四张表 Customer - 客户信息维表 Product - 产品维表 ProductType - 产品类表维表 Sale - 销售记录表:
在hive shell下执行下面命令:
create database mondrian;
use mondrian;
create table Sale (saleId INT, proId INT, cusId INT, unitPrice FLOAT, number INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
create table Product (proId INT, proTypeId INT, proName STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
create table ProductType (proTypeId INT, proTypeName STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
create table Customer (cusId INT, gender STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
3.在hive的存放目录下新建一个文件夹myTmp,放进四个数据文件:
# Customer文件
1,F
2,M
3,M
4,F
# ProductType文件
1,electrical
2,digital
3,furniture
# Product数据文件
1,1,washing machine
2,1,television
3,2,mp3
4,2,mp4
5,2,camera
6,3,chair
7,3,desk
# Sale数据文件
1,1,1,340.34,2
2,1,2,140.34,1
3,2,3,240.34,3
4,3,4,540.34,4
5,4,1,80.34,5
6,5,2,90.34,26
7,6,3,140.34,7
8,7,4,640.34,28
9,6,1,140.34,29
10,7,2,740.34,29
11,5,3,30.34,28
12,4,4,1240.34,72
13,3,1,314.34,27
14,3,2,45.34,27
再把文件数据加载到表里(在hive shell下执行如下命令:)
load data local inpath "/home/apache-hive-2.1.1-bin/myTmp/Customer" OVERWRITE into table Customer
load data local inpath "/home/apache-hive-2.1.1-bin/myTmp/ProductType" OVERWRITE into table ProductType
load data local inpath "/home/apache-hive-2.1.1-bin/myTmp/Product" OVERWRITE into table Product
load data local inpath "/home/apache-hive-2.1.1-bin/myTmp/Sale" OVERWRITE into table Sale
4.将xml文件放进web项目的src下,取名olapSchema.xml
<Schema name="hello">
<Cube name="Sales">
<!-- 事实表(fact table) -->
<Table name="Sale"/>
<!-- 客户维 -->
<Dimension name="cusGender" foreignKey="cusId">
<Hierarchy hasAll="true" allMemberName="allGender" primaryKey="cusId">
<Table name="Customer"/>
<Level name="gender" column="gender"/>
</Hierarchy>
</Dimension>
<!-- 产品类别维 -->
<Dimension name="proType" foreignKey="proId">
<Hierarchy hasAll="true" allMemberName="allPro" primaryKey="proId" primaryKeyTable="Product">
<join leftKey="proTypeId" rightKey="proTypeId">
<Table name="Product"/>
<Table name="ProductType"/>
</join>
<Level name="proTypeId" column="proTypeId" nameColumn="proTypeName" uniqueMembers="true" table="ProductType"/>
<Level name="proId" column="proId" nameColumn="proName" uniqueMembers="true" table="Product"/>
</Hierarchy>
</Dimension>
<Measure name="numb" column="number" aggregator="sum" datatype="Numeric"/>
<Measure name="totalSale" aggregator="sum" formatString="$ #,##0.00">
<!-- unitPrice*number所得值的列 -->
<MeasureExpression>
<SQL dialect="generic">unitPr