olap分析平台的设计与实现(二)_数据库读写

wh_xia_jun

已于 2023-04-19 19:59:26 修改

阅读量1k

点赞数

分类专栏： olap 文章标签： mondrian olap 非税 olap4j 财政

于 2019-05-04 21:26:23 首次发布

本文链接：https://blog.csdn.net/wh_xia_jun/article/details/89737696

版权

olap 专栏收录该内容

25 篇文章 15 订阅

订阅专栏

这一部分：开发配置及olap4j

maven 下引入mondrian相关的包：这里我引入的是mondrain3.14的版本，后来老夫升级到mondrian9的版本。

               <dependency>
                   <groupId>net.sf.json-lib</groupId>
                   <artifactId>json-lib</artifactId>
                   <version>2.4</version>
                   <classifier>jdk15</classifier>
               </dependency>

               <dependency>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-starter-data-jpa</artifactId>
               </dependency>
               <dependency>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-starter-web</artifactId>
               </dependency>
               <dependency>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-starter-jdbc</artifactId>
               </dependency>
               <dependency>
                   <groupId>org.projectlombok</groupId>
                   <artifactId>lombok</artifactId>
               </dependency>
               <dependency>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-starter-test</artifactId>
                   <scope>test</scope>
               </dependency>
               <dependency>
                   <groupId>org.apache.commons</groupId>
                   <artifactId>commons-lang3</artifactId>
               </dependency>
               <dependency>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-starter-aop</artifactId>
               </dependency>
               <dependency>
                   <groupId>commons-configuration</groupId>
                   <artifactId>commons-configuration</artifactId>
                   <version>1.10</version>
               </dependency>
               <dependency>
                   <groupId>commons-io</groupId>
                   <artifactId>commons-io</artifactId>
                   <version>2.5</version>
               </dependency>
               <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--JSON-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.31</version>
        </dependency>

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4.0</version>
        </dependency>

        <dependency>
            <groupId>mondrian</groupId>
            <artifactId>mondrian</artifactId>
            <version>3.14.0.0-12</version>
        </dependency>

<!--               <dependency>
                   <groupId>org.olap4j</groupId>
                   <artifactId>olap4j-xmlaserver</artifactId>
                   <version>1.2.0</version>
               </dependency>-->

         <dependency>
                    <groupId>org.olap4j</groupId>
                    <artifactId>olap4j</artifactId>
                    <version>1.2.0</version>
         </dependency>

              <dependency>
                   <groupId>eigenbase</groupId>
                   <artifactId>eigenbase-xom</artifactId>
                   <version>1.3.4</version>
               </dependency>

               <dependency>
                   <groupId>org.apache.commons</groupId>
                   <artifactId>commons-vfs2</artifactId>
                   <version>2.1</version>
               </dependency>


               <dependency>
                   <groupId>net.hydromatic</groupId>
                   <artifactId>eigenbase-properties</artifactId>
                   <version>1.1.5</version>
               </dependency>


               <dependency>
                   <groupId>eigenbase</groupId>
                   <artifactId>eigenbase-resgen</artifactId>
                   <version>1.3.1</version>
               </dependency>


               <dependency>
                   <groupId>commons-pool</groupId>
                   <artifactId>commons-pool</artifactId>
                   <version>1.6</version>
               </dependency>

               <dependency>
                   <groupId>apache-log4j</groupId>
                   <artifactId>log4j</artifactId>
                   <version>1.2.14</version>
               </dependency>

               <dependency>
                   <groupId>commons-dbcp</groupId>
                   <artifactId>commons-dbcp</artifactId>
                   <version>1.2.1</version>
               </dependency>

               <dependency>
                   <groupId>commons-math</groupId>
                   <artifactId>commons-math</artifactId>
                   <version>1.2</version>
               </dependency>

mondrian3.14版本我是从网上下的jar包，用如下maven命令安装：

mvn install:install-file -Dfile=.\mondrian.jar -DgroupId=mondrian -DartifactId=mondrian -Dversion=3.14.0.0-12 -Dpackaging=jar

先测试一下mondrian和数据库连接:

package com.muge.as.dao;
import mondrian.olap.Axis;
import mondrian.olap.Position;
import mondrian.olap.Util;
import mondrian.olap.Util.PropertyList;

import java.io.InputStream;
import java.io.PrintWriter;
import java.util.List;


/**
 * @author xj
 *
 */
public class TestMondrian {

    // 数据库连接信息，这里用的derby
    //private static String dbName = "sample";
    //private static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    private static String driver = "oracle.jdbc.driver.OracleDriver";
    //private static String url = "jdbc:derby:" + dbName;
    private static String url="jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
    //private static String urlCreate = url + ";create=true";
/*    private static String userName = "sa";
    private static String password = "sa";*/
    //private static String userName = "superVision";
    private static String userName = "fin_report";

    private static String password = "1";

    //
    //private static String sqlFile = "init.sql";

    // 立方体定义文件
    //private static String xmlFile = "sample.xml";
    private static String xmlFile = "olapSchema.xml";

    public static void main(String[] args) throws Exception {

        Class.forName(driver);

        String mdxStr = "select {[年].Members} on columns,{[组织].Members} on rows from 模型一";

        // mondrian提供的mdx查询接口
        System.out.println("====================mondrian接口=================");

        // 建立连接
        PropertyList connectInfo = new PropertyList();
        connectInfo.put("Provider", "mondrian");
        // connectInfo.put("JdbcDrivers", driver);
        connectInfo.put("Jdbc", url);
        connectInfo.put("JdbcUser", userName);
        connectInfo.put("JdbcPassword", password);
        // mondrian默认解析xml的方法不带字符集，中文会有乱码，故自行取得CatalogContent
        // connectInfo.put("Catalog", xmlFile);
        connectInfo.put("CatalogContent", getCatalogContent());
        // 执行查询
        mondrian.olap.Connection conn = mondrian.olap.DriverManager
                .getConnection(connectInfo, null);
        mondrian.olap.Query query = conn.parseQuery(mdxStr);
        mondrian.olap.Result result = conn.execute(query);

        // 输出结果
        PrintWriter pw = new PrintWriter(System.out);
        result.print(pw);
        System.out.println("----------------------------------------------------");
        Axis rowAxis = result.getAxes()[0];
        int rowNum = rowAxis.getPositions().size();
        List<Position> ps=rowAxis.getPositions();

        //int colNum = (List<Position>(rowAxis.getPositions())).get(0).size();
        System.out.println("colAxis.getPositions().size() rowNum:"+ps.size() );
        System.out.println("rowAxis.getPositions().get(0)).size() colNum:"+ps.get(0).size() );
        System.out.println("----------------result.getAxes().length:"+result.getAxes().length );
        pw.flush();
    }

    /**
     * 初始化数据库，建表
     *
     * @throws Exception
     */
    private static void initDB() throws Exception {/*

        if (!new File(dbName).exists()) {
            // derby数据库不存在，开始初始化
            System.out.println("初始化中...");

            Class.forName(driver);
            Connection conn = DriverManager.getConnection(urlCreate, userName, password);

            ScriptRunner runner = new ScriptRunner(conn);
            runner.setSendFullScript(false);
            runner.setStopOnError(true);

            runner.runScript(new FileReader(sqlFile));
            runner.closeConnection();

            System.out.println("初始化完毕");

        }

    */}

    private static String getCatalogContent() throws Exception {


        //InputStream inputStream = mondrian.olap.Util.readVirtualFile(xmlFile);

        InputStream inputStream =null;


        try {

            inputStream=mondrian.olap.Util.readVirtualFile(xmlFile);
            final byte[] bytes = Util.readFully(inputStream, 1024);

            // 下面是mondrian原来的实现，由于byte被强制转化为char，汉字全为乱码，故将这段处理处理掉。
            // final char[] chars = new char[bytes.length];
            // for (int i = 0; i < chars.length; i++) {
            // chars[i] = (char) bytes[i];
            // }
            String s=new String(bytes, "UTF-8");
            return s;
        }catch(Exception e){
            e.printStackTrace();
            return "";
        }

        finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }
}

 代码中conn.execute(query); 提示是过时方法。官方推荐利用olap4j的实现。olap4j是一个OLAP的API，现在已成了标准，下面就是olap4j的实现方式

        // olap4j的mdx接口，是一个jdbc实现，推荐
        System.out.println("\r\n===================olap4j接口==================");
        
        // 建立连接
        String strUrl = "jdbc:mondrian:";
        strUrl += "Jdbc=" + url;
        strUrl += ";JdbcUser=" + userName;
        strUrl += ";JdbcPassword=" + password;
        // strUrl += ";Catalog=" + xmlFile;
        strUrl += ";CatalogContent=" + getCatalogContent();
        Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
        Connection olap4jConn = DriverManager.getConnection(strUrl);
        OlapConnection olapConn = (OlapConnection) olap4jConn.unwrap(OlapConnection.class);
        
        // 执行查询
        OlapStatement statement = olapConn.createStatement();
        CellSet cellSet = statement.executeOlapQuery(mdxStr);
        // 输出结果
        CellSetFormatter formatter = new RectangularCellSetFormatter(false);
        formatter.format(cellSet, new PrintWriter(System.out, true));

运行结果：

|                                      | 年                                                                                                         |
|                                      |             | 2010年 | 2011年 | 2012年 | 2013年 | 2014年 | 2015年 | 2016年 | 2017年       | 2018年 | 2019年 | 2020年 |
+----+--------+-----------+------------+-------------+-------+-------+-------+-------+-------+-------+-------+-------------+-------+-------+-------+
| 组织 |        |           |            | 79440368.57 |       |       |       |       |       |       |       | 79440368.57 |       |       |       |
|    | 湖北省财政厅 |           |            | 79440368.57 |       |       |       |       |       |       |       | 79440368.57 |       |       |       |
|    |        | 十堰市       |            |    22248.69 |       |       |       |       |       |       |       |    22248.69 |       |       |       |
|    |        |           | 丹江口市       |     1298.05 |       |       |       |       |       |       |       |     1298.05 |       |       |       |
|    |        |           | 十堰市本级      |    12898.78 |       |       |       |       |       |       |       |    12898.78 |       |       |       |
|    |        |           | 张湾区        |          0. |       |       |       |       |       |       |       |          0. |       |       |       |
|    |        |           | 房县         |       737.3 |       |       |       |       |       |       |       |       737.3 |       |       |       |
|    |        |           | 武当山特区      |     1009.18 |       |       |       |       |       |       |       |     1009.18 |       |       |       |
|    |        |           | 白浪区        |             |       |       |       |       |       |       |       |             |       |       |       |
|    |        |           | 竹山县        |        116. |       |       |       |       |       |       |       |        116. |       |       |       |

运行过程中，会报如下错误：

09:28:16.943 [main] DEBUG mondrian.spi.impl.JdbcDialectImpl - NOT Using IMPALA dialect.
java.sql.SQLSyntaxErrorException: ORA-00923: 未找到要求的 FROM 关键字

这个错误好像不用管它，是mondrian的bug?,反正老夫没管这个问题。

这2天，升级了mondrian到9，从GitHub上下的mondrian9,编译的时候，要选择合适的java版本，高了低了貌似都有问题，最后测试java jdk-9.0.4可行。

尝试安装demo,好像和docker有关的一个插件报错，算了，不试了。

升级后，发现原来链接报错！

反复查询测试后，如下代码可行：

        public static OlapConnection getConnection(String url) throws ClassNotFoundException, SQLException{
            OlapConnection olapConnection = null;

            try {
                Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            java.sql.Connection connection = null;
            try {
                connection = java.sql.DriverManager.getConnection(url);
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                olapConnection = connection.unwrap(OlapConnection.class);
                return olapConnection;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;

        }


    @Test
    public  void  test003() throws Exception {
        String mdxStr = "SELECT {[Time].[1997].[Q1]} on columns," +" {[Product].children} on rows "
                +"FROM[Sales] ";
        //" jdbc:mondrian://localhost:3306/foodmart";
        String url=  "jdbc:mondrian:"
                +"Jdbc=jdbc:mysql://127.0.0.1:3306/foodmart?user=root&password=1;"
                +"Catalog=FoodMart.xml";

        OlapConnection olapConnection=getConnection(url);

        OlapStatement statement = olapConnection.createStatement();
        CellSet cellSet = statement.executeOlapQuery(mdxStr);
        System.out.println(cellSet);
    }

还问了万能的chatgpt,给我的答案如下：链接还是有问题，发现mondrian.olap.connection 没有unwrap方法了，why?

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import mondrian.olap.ConnectionInfo;
import mondrian.olap.DriverManager;
import mondrian.olap.MondrianDef;
import mondrian.olap.Result;
import mondrian.olap.Util;
import mondrian.olap4j.MondrianOlap4jConnection;
import mondrian.olap4j.MondrianOlap4jDriver;

public class WriteDataWithMondrian {
    public static void main(String[] args) {
        // Connection parameters for Oracle database
        String jdbcUrl = "jdbc:oracle:thin:@//localhost:1521/orcl";
        String username = "username";
        String password = "password";

        // Mondrian schema definition
        MondrianDef.Schema schema = MondrianDef.Schema.load(
            WriteDataWithMondrian.class.getResourceAsStream("mySchema.xml"));

        // Set up Mondrian connection
        Properties props = new Properties();
        props.setProperty("JdbcUser", username);
        props.setProperty("JdbcPassword", password);
        ConnectionInfo connectionInfo = new ConnectionInfo(jdbcUrl, props);
        DriverManager.registerDriver(new mondrian.olap4j.MondrianOlap4jDriver());
        mondrian.olap.Connection mondrianConnection = DriverManager.getConnection(connectionInfo, null);
        MondrianOlap4jConnection olap4jConnection = mondrianConnection.unwrap(MondrianOlap4jConnection.class);
        olap4jConnection.setCatalog(schema);

        // Write data to cube using MDX query
        String mdxQuery = "INSERT INTO [MyCube] ([Measures].[Sales], [Time].[2019].[Q1], [Product].[All Products], [Region].[All Regions]) VALUES (1000, null, null, null)";
        Result result = olap4jConnection.executeOlapQuery(mdxQuery);
        Util.discardResult(result);
        
        // Clean up resources
        try {
            mondrianConnection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}


This code assumes that you have a Mondrian schema definition file named mySchema.xml in the same package as the WriteDataWithMondrian class. The schema definition file should include a definition for a cube named MyCube with appropriate dimensions and measures.

Note that this code uses the OLAP4J API to execute the MDX query and write data to the cube. The OLAP4J API is a Java-based API that provides a standard way to interact with OLAP servers, including the Mondrian engine.


java -cp D:\FoodMart\mondrian.jar;D:\FoodMart\log4j-1.2.8.jar;D:\FoodMart\commons-logging-1.0.4.jar;D:\FoodMart\eigenbase-xom.jar;D:\FoodMart\eigenbase-resgen.jar;D:\FoodMart\eigenbase-properties.jar;D:\FoodMart\mysql-connector-java-5.1.20-bin.jar;D:\FoodMart\olap4j.jar;D:\FoodMart\mysql-connector-java-5.1.19.jar mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes -jdbcDrivers="com.mysql.jdbc.Driver" -inputFile=D:\FoodMart\FoodMartCreateData.sql -outputJdbcURL="jdbc:mysql://localhost:3309/foodmart?user=root&password=root1234

问了chatgt,olap数据库写入方法如下：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import mondrian.olap.ConnectionInfo;
import mondrian.olap.DriverManager;
import mondrian.olap.MondrianDef;
import mondrian.olap.Result;
import mondrian.olap.Util;
import mondrian.olap4j.MondrianOlap4jConnection;
import mondrian.olap4j.MondrianOlap4jDriver;

public class WriteDataWithMondrian {
    public static void main(String[] args) {
        // Connection parameters for Oracle database
        String jdbcUrl = "jdbc:oracle:thin:@//localhost:1521/orcl";
        String username = "username";
        String password = "password";

        // Mondrian schema definition
        MondrianDef.Schema schema = MondrianDef.Schema.load(
            WriteDataWithMondrian.class.getResourceAsStream("mySchema.xml"));

        // Set up Mondrian connection
        Properties props = new Properties();
        props.setProperty("JdbcUser", username);
        props.setProperty("JdbcPassword", password);
        ConnectionInfo connectionInfo = new ConnectionInfo(jdbcUrl, props);
        DriverManager.registerDriver(new mondrian.olap4j.MondrianOlap4jDriver());
        mondrian.olap.Connection mondrianConnection = DriverManager.getConnection(connectionInfo, null);
        MondrianOlap4jConnection olap4jConnection = mondrianConnection.unwrap(MondrianOlap4jConnection.class);
        olap4jConnection.setCatalog(schema);

        // Write data to cube using MDX query
        String mdxQuery = "INSERT INTO [MyCube] ([Measures].[Sales], [Time].[2019].[Q1], [Product].[All Products], [Region].[All Regions]) VALUES (1000, null, null, null)";
        Result result = olap4jConnection.executeOlapQuery(mdxQuery);
        Util.discardResult(result);
        
        // Clean up resources
        try {
            mondrianConnection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}


This code assumes that you have a Mondrian schema definition file named mySchema.xml in the same package as the WriteDataWithMondrian class. The schema definition file should include a definition for a cube named MyCube with appropriate dimensions and measures.

Note that this code uses the OLAP4J API to execute the MDX query and write data to the cube. The OLAP4J API is a Java-based API that provides a standard way to interact with OLAP servers, including the Mondrian engine.


java -cp D:\FoodMart\mondrian.jar;D:\FoodMart\log4j-1.2.8.jar;D:\FoodMart\commons-logging-1.0.4.jar;D:\FoodMart\eigenbase-xom.jar;D:\FoodMart\eigenbase-resgen.jar;D:\FoodMart\eigenbase-properties.jar;D:\FoodMart\mysql-connector-java-5.1.20-bin.jar;D:\FoodMart\olap4j.jar;D:\FoodMart\mysql-connector-java-5.1.19.jar mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes -jdbcDrivers="com.mysql.jdbc.Driver" -inputFile=D:\FoodMart\FoodMartCreateData.sql -outputJdbcURL="jdbc:mysql://localhost:3309/foodmart?user=root&password=root1234

这里学习一下olap4j:

● JAVA API

● PROVIDES OLAP ANALISYS

● QUERY LANGUAGE

– MDX STRING

– MDX PARSE TREE

– METADA IS AT THE HEART OF Olap4j

– DIFFERENT CUBE SERVERS ARE SUPPORTED

支持不同的cube 服务

● MSAS via XML/A

● MONDRIAN

MAIN CLASSES/INTERFACES：

– OlapConnection

– OlapWrapper

– OlapStatement/ PreparedOlapStatement

– CellSet

– CellSetAxis

– Position

– Cell

cellSet

● Cellset is the set which contains the cell returned by the MDX query.

● Cellset is the set where results are founded.

– Important API methods: getCell, getFilterAxis, getMetadata..

Cell

● Cell is the structure which contains a part of the cellset. Every cell, contains a part of the result we are searching with a MDX query.

● It could be said that cells are as rows in RDBs or documents in a document-oriented DB. This is just a very simple approach, a really simple analogy.

– Important API methods: drillThrough, getValue, isEmpty, isNull,...

CellSetAxis:

CellSetAxis is the axis of a cellset (OK?!!)

● A cell set has the same number of axes as the MDX statement which was executed to produce it.

单元格集具有与生成它的MDX语句相同的轴数。

● Each axis is an ordered collection of members or tuples.

● Each member or tuple on an axis is called Position.

– Important API methods: getPositions, getAxisMetada...

ps:tuples:元组

Position:

●Position is one of the CellSetAxis objects in a CellSet.

● An axis has a particular dimensionality, that is, a set of one or more dimensions which will appear on than axis, and every position on that axis will have a member of each of those dimensions. (Extracted from official API)

– Important API methods: getMembers, getOrdinal...

以下部分是github上对类的说明：

核心部分：

org.olap4j.driver.xmla - Generic XML/A driver.
org.olap4j.mdx - Core objects of the MDX model.
org.olap4j.mdx.parser - Parser for the MDX query language.
org.olap4j.metadata - Discovery of an OLAP server's metadata.
org.olap4j.type - System for the core MDX object model and the metadata package.

扩展部分：

org.olap4j.query - Programmatic Query Model.
org.olap4j.transform - Core MDX object model transformation utilities.
org.olap4j.layout - Utility classes to display CellSets.
org.olap4j.CellSetListener and all associated classes - Event-based system for real time updates of CellSet objects.
org.olap4j.Scenario and all associated classes - Statistical simulations module.

ps:maven 配置https://www.jianshu.com/p/e7cedd9eaac7

上一节：引子

下一节：安装footmart例子