讓Birt報表腳本數據源變得既簡單又強大

發布時間：2020-06-04 01:05:12 來源：網絡閱讀：312 作者：raqsoft 欄目：大數據

概述：運行在 JVM 上的 SQL 函數和存儲過程

總所周知，有些數據庫沒有強大的分析函數（eg. Mysql）, 有些數據庫沒有存儲過程（eg. Vertica），當遇到復雜的數據計算，往往只能通過 Python,R 等外部腳本來實現，但這些腳本語言和主流工程語言（Java）集成性不好，如果直接用工程語言實現類似 SQL 函數和存儲過程的功能，經常只是針對某個計算需求編寫冗長的代碼，代碼幾乎不可復用。

另外，即便擁有強大的分析函數，實現稍復雜的邏輯其實也不算容易，比如下面這種常見的業務計算，找出“銷售額占到一半的前 n 個客戶，并按銷售額從大到小排序”，在 Oracle 中 SQL 實現如下：

with A as
(selectCUSTOM,SALESAMOUNT,row_number() over (order by SALESAMOUNT) RANKING
from SALES)
select CUSTOM,SALESAMOUNT
from (select CUSTOM,SALESAMOUNT,sum(SALESAMOUNT) over (order by RANKING) AccumulativeAmount
from A)
where AccumulativeAmount>(select sum(SALESAMOUNT)/2 from SALES)
order by SALESAMOUNT descwith A as
(selectCUSTOM,SALESAMOUNT,row_number() over (order by SALESAMOUNT) RANKING
from SALES)select CUSTOM,SALESAMOUNTfrom (select CUSTOM,SALESAMOUNT,sum(SALESAMOUNT) over (order by RANKING) AccumulativeAmountfrom A)where AccumulativeAmount>(select sum(SALESAMOUNT)/2 from SALES)order by SALESAMOUNT desc

說明：按照銷售額累計值從小到大排序，通過累計值大于“一半銷售額”的條件，逆向找出占到銷售額一半的客戶。為了避免窗口函數在計算累計值時對銷售額相同的值處理出現錯誤，用子查詢先計算了排名。

下面是用集算器實現相同邏輯的代碼：
讓Birt報表腳本數據源變得既簡單又強大
從上述代碼我們可以看到，集算器利用一套簡潔的語法取代了需嵌套 SQL+ 窗口函數才能實現的邏輯，并且具有通用一致性（任何數據源代碼一致）。

集算器是一套運行在 JVM 上專門處理結構化數據的腳本語言，類似用 SQL 函數和存儲過程，與 Java 集成可以創建可移植、功能強大和與數據庫無關的計算邏輯，運行于中間層的計算邏輯和運行于數據庫層的數據邏輯之間的分離，提高了應用程序的可擴展性、靈活性和可維護性。

應用場景：報表數據準備

應用結構

讓Birt報表腳本數據源變得既簡單又強大

集成后，集算器嵌入報表應用層，相當于本地的邏輯數據庫（不需要單獨服務器部署），在報表與數據源間作為報表數據準備層，完成各種復雜的計算任務。

如何集成

下面以 Vertica 為數據源，Birt 為報表工具，描述怎樣集成 esProc 作為數據準備層。

（一） Birt 開發環境

1、基礎 jar 集成

集算器 JDBC 需要三個基礎 jar 包，都可以在 [esProc 安裝目錄]\esProc\lib 目錄下找到，分別為：

dm.jar 集算器計算引擎及JDBC驅動包
jdom.jar 解析配置文件
icu4j\_3\_4_5.jar 處理國際化dm.jar 集算器計算引擎及JDBC驅動包jdom.jar 解析配置文件icu4j\_3\_4_5.jar 處理國際化

除了基礎包外，還有一些為完成特定功能的 jar 包。如，要在集算器 JDBC 用其它數據庫作為集算器的數據源，那么還需要相應數據庫的驅動 jar 包，本文涉及到 vertica，所以要同時增加其 JDBC 驅動包（以 vertica9.1。0 為例）

vertica-jdbc-9.1.0-0.jar vertica 官網即可下載

獲取到以上 Jar 后，將其拷貝至 Birt 開發環境 [安裝目錄]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212 下。

注意：標紅部分，不同 birt 版本略有不同

2、配置文件集成

raqsoftConfig.xml，主要包含集算器授權、腳本文件路徑、其他作為集算器數據源的連接配置等。

可在 [esProc 安裝目錄]\esProc\config 下找到，需復制后放置在類路徑下，同樣將其拷貝至 Birt 開發環境 [安裝目錄]\plugins\org.eclipse.birt.report.data.oda.jdbc_4.6.0.v20160607212 下。

注意：配置文件名不能改變

（二） Birt 應用環境

1、將（一）中的所有 jar 拷貝至應用的 WEB-INF\lib 下

2、將 raqsoftConfig.xml 拷貝至應用的 WEB-INF\classes 下

例一：常規調用

1、 Vertica 內 Sales 表字段及數據說明（通過 vsql 查詢，本測試庫有 2013/14/15 年數據）
讓Birt報表腳本數據源變得既簡單又強大
2、編寫并部署 esProc 腳本

（1） esProc 設計器添加 vertica JDBC 驅動包

vertica 官網下載 jdbc 驅動包（如，vertica-jdbc-9.1.0-0.jar），放至【esProc 安裝目錄】\common\jdbc 下

（2）新增 vertica 數據源

打開設計器，Tool—Datasource connection 新增 JDBC 方式連接

讓Birt報表腳本數據源變得既簡單又強大

點 ok 保存，再點 connect 連接

讓Birt報表腳本數據源變得既簡單又強大

數據源名稱變為粉色即表示連接成功。

（3）編寫算法腳本 (文件：VerticaExternalProcedures.dfx)

File – New

讓Birt報表腳本數據源變得既簡單又強大

（4）部署腳本

將腳本文件部署到 raqsoftConfig.xml 配置的腳本文件主目錄下。

讓Birt報表腳本數據源變得既簡單又強大

3、 esProc 配置文件 raqsoftConfig.xml 內增加 verticaLink 數據源連接配置

<DB name="verticaLink">
<property name="url" value="jdbc:vertica://192.168.10.10:5433/ForEsprocTestDB"></property>
<property name="driver" value="com.vertica.jdbc.Driver"></property>
<property name="type" value="0"></property>
<property name="user" value="dbadmin"></property>
<property name="password" value="runqian"></property>
<property name="batchSize" value="0"></property>
<property name="autoConnect" value="false"></property>
<property name="useSchema" value="false"></property>
<property name="addTilde" value="false"></property>
<property name="needTransContent" value="false"></property>
<property name="needTransSentence" value="false"></property>
<property name="caseSentence" value="false"></property>
</DB><DB name="verticaLink"><property name="url" value="jdbc:vertica://192.168.10.10:5433/ForEsprocTestDB"/><property name="driver" value="com.vertica.jdbc.Driver"/><property name="type" value="0"/><property name="user" value="dbadmin"/><property name="password" value="runqian"/><property name="batchSize" value="0"/><property name="autoConnect" value="false"/><property name="useSchema" value="false"/><property name="addTilde" value="false"/><property name="needTransContent" value="false"/><property name="needTransSentence" value="false"/><property name="caseSentence" value="false"/></DB>

4、 Birt 開發工具內新建報表，并增加 esProc 數據源“esProcConnection”

讓Birt報表腳本數據源變得既簡單又強大

Driver class 為”com.esproc.jdbc.InternalDriver(v1.0)”，會用到 dm.jar 等
Database URL 為”jdbc:esproc:local://”

5、 Birt 調用 Vertica 外部存儲過程（esProc 數據集）

新建“Data Sets”，選擇配置的集算器數據源（esProcConnection），數據集類型選擇存儲過程（SQL Stored Procedure Query）
讓Birt報表腳本數據源變得既簡單又強大
Next，查詢腳本（Query-Query Text）輸入：{call VerticaExternalProcedures()}