【摘要】
用實(shí)例、分步驟，詳細(xì)講解多維分析（OLAP）基礎(chǔ)寬表的實(shí)現(xiàn)。
實(shí)踐目標(biāo)
本期目標(biāo)是練習(xí)如何搭建多維分析后臺(tái)的基礎(chǔ)寬表，并通過 SPL 和 SQL 訪問基礎(chǔ)寬表。
實(shí)踐的步驟：
1、 準(zhǔn)備基礎(chǔ)寬表：將基礎(chǔ)寬表數(shù)據(jù)從數(shù)據(jù)庫(kù)中取出，存成組表文件。
2、 訪問基礎(chǔ)寬表：用 SPL 或者 SQL 語(yǔ)句訪問。
本期樣例寬表為 customer 表。從 Oracle 數(shù)據(jù)庫(kù)中取出寬表數(shù)據(jù)的 SQL 語(yǔ)句是 select * from customer。執(zhí)行結(jié)果如下圖：
其中字段包括：
CUSTOMER_ID NUMBER(10,0), 客戶編號(hào)
FIRST_NAME VARCHAR2(20), 名
LAST_NAME VARCHAR2(25), 姓
PHONE_NUMBER VARCHAR2(20), 電話號(hào)碼
BEGIN_DATE DATE, 開戶日期
JOB_ID VARCHAR2(10), 職業(yè)編號(hào)
JOB_TITLE VARCHAR2(32), 職業(yè)名稱
BALANCE NUMBER(8,2), 余額
EMPLOYEE_ID NUMBER(4,0), 開戶雇員編號(hào)
DEPARTMENT_ID NUMBER(4,0), 分支機(jī)構(gòu)編號(hào)
DEPARTMENT_NAME VARCHAR2(32), 分支結(jié)構(gòu)名稱
FLAG1 CHAR(1), 標(biāo)記 1
FLAG2 CHAR(1), 標(biāo)記 2
FLAG3 CHAR(1), 標(biāo)記 3
FLAG4 CHAR(1), 標(biāo)記 4
FLAG5 CHAR(1), 標(biāo)記 5
FLAG6 CHAR(1), 標(biāo)記 6
FLAG7 CHAR(1), 標(biāo)記 7
FLAG8 CHAR(1), 標(biāo)記 8
多維分析計(jì)算的目標(biāo)可以用下面的 SQL 語(yǔ)句表示：
select department_id,job_id,begin_date,sum(balance) sum,count(customer_id) count
from customer
where department_id in (10,20,50,60,70,80) and flag1='1' and flag8='1'
group by department_id,job_id,begin_date
準(zhǔn)備寬表
編寫 etl.dfx，從數(shù)據(jù)庫(kù)中取出數(shù)據(jù)生成組表文件 customer.ctx，即存儲(chǔ)為寬表。代碼示例如下：
A1：連接預(yù)先配置好的數(shù)據(jù)庫(kù) oracle。@l 選項(xiàng)是將字段名處理成小寫，l 是字母 L 的小寫，不是數(shù)字 1。
B1：建立數(shù)據(jù)庫(kù)游標(biāo)，準(zhǔn)備取出 customer 表的數(shù)據(jù)。customer 是事實(shí)表，實(shí)際應(yīng)用中一般都比較大，所以用游標(biāo)的方式，避免內(nèi)存溢出。
A2：定義列存組表文件。字段名和 B1 完全一致。
A3：邊取出游標(biāo) B1，邊輸出到組表文件中。
B3：關(guān)閉組表文件和數(shù)據(jù)庫(kù)連接。
當(dāng)寬表數(shù)據(jù)量有一千萬行時(shí)，導(dǎo)出組表文件約 393MB。
部署集算服務(wù)器
按照教程部署好集算器節(jié)點(diǎn)機(jī)。將 meta.txt 放入主目錄中，文件內(nèi)容是表名和文件名的對(duì)應(yīng)關(guān)系，如下：
Table File Column Type
customer data/customer.ctx
文件名是相對(duì)于主目錄的，假如節(jié)點(diǎn)機(jī)主目錄是 d:/esproc/，那么完整的文件名就是 d:/esproc/data/customer.ctx。
訪問寬表
多維分析后臺(tái)需要被通用的前端所調(diào)用。調(diào)用的方式有兩種，第一種是使用 SQL；第二種是執(zhí)行腳本，提交過濾條件、分組字段等參數(shù)給后臺(tái)執(zhí)行。
一、SQL
我們先來看第一種，以 Java 調(diào)用集算器 JDBC 為例來說明。Java 示例代碼如下：
public void testOlapServer(){
Connection con = null;
java.sql.Statement st;
try{
// 建立連接
Class.forName("com.esproc.jdbc.InternalDriver");
// 根據(jù) url 獲取連接
con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true&sqlfirst=plus");
st = con.createStatement();
// 直接執(zhí)行 SQL+ 語(yǔ)句，獲取結(jié)果集
ResultSet rs = st.executeQuery("select department_id,job_num,begin_date,sum(balance) sum,count(customer_id) count from customer where department_id in (10,20,50,60,70,80) and flag1='1'and flag8='1'group by department_id,job_num,begin_date");
// 繼續(xù)處理結(jié)果集，將結(jié)果集展現(xiàn)出來
}
catch(Exception e){
out.println(e);
}
finally{
// 關(guān)閉連接
if (con!=null) {
try {con.close();}
catch(Exception e) {out.println(e); }
}
}
}
這里的 customer.ctx 會(huì)被完全讀入內(nèi)存，不適合大數(shù)據(jù)量的情況。對(duì)于大數(shù)據(jù)量，可以在表名前面加 /*+ external*/，組表會(huì)被處理成游標(biāo)?；蛘咭部梢圆捎孟旅鎴?zhí)行腳本的方法。
加上 /*+ external*/ 之后的 SQL 如下：
select department_id,job_num,begin_date,sum(balance) sum,count(customer_id) count
from /*+ external*/ customer
where department_id in (10,20,50,60,70,80) and flag1='1' and flag8='1'
group by department_id,job_num,begin_date
用游標(biāo)時(shí)，Java 程序執(zhí)行的總時(shí)間是 8 秒。
需要說明的是，這里的執(zhí)行時(shí)間絕對(duì)數(shù)值并不重要（它和硬件環(huán)境相關(guān)）。記錄執(zhí)行時(shí)間是為了后面的實(shí)踐中采用多種優(yōu)化方法時(shí)，看看可以將時(shí)間縮短多少。
我們可以用多線程并行的方式執(zhí)行 SQL，方法是給表名加上/*+parallel (n) */。2 線程并行完整的 SQL 如下：
select department_id,job_num,begin_date,sum(balance) sum,count(customer_id) count
from /*+ external*/ /*+parallel (2) */ customer
where department_id in (10,20,50,60,70,80) and flag1='1' and flag8='1'
group by department_id,job_num,begin_date
2 線程并行時(shí)，Java 程序執(zhí)行的總時(shí)間是 4 秒。
二、執(zhí)行腳本
編寫 olap-spl.dfx，用 SPL 代碼訪問寬表并進(jìn)行過濾和分組匯總計(jì)算。
先定義網(wǎng)格參數(shù)，將文件名、過濾條件、分組字段、聚合表達(dá)式分別傳入。
參數(shù)設(shè)置窗口如下：
參數(shù)值設(shè)置如下：
filename="data/customer.ctx"
where="[10,20,50,60,70,80].contain(department_id) && flag1==\"1\"&& flag8==\"1\"
group="department_id,job_id,begin_date"
aggregate="sum(balance):sum,count(customer_id):count"
SPL 代碼示例如下：
A1：打開組表對(duì)象。
B1：建立游標(biāo)，定義游標(biāo)前過濾，條件是 where 變量的值。
A2：對(duì)游標(biāo)做分組匯總計(jì)算，分組字段是 group 變量的值。匯總計(jì)算是 aggregate 變量的值。因?yàn)榉纸M之后數(shù)據(jù)量較小，所以用 groups，結(jié)果集直接放在內(nèi)存中。
執(zhí)行結(jié)果如下圖：
A3：將結(jié)果集返回給調(diào)用者。
olap-spl.dfx 編寫好之后，可以在多維分析中作為存儲(chǔ)過程調(diào)用，Java 代碼如下：
public void testOlapServer(){
Connection con = null;
java.sql.PreparedStatement st;
try{
// 建立連接
Class.forName("com.esproc.jdbc.InternalDriver");
// 根據(jù) url 獲取連接
con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true&sqlfirst=plus");
// 調(diào)用存儲(chǔ)過程，其中 olap-spl 是 dfx 的文件名
st =con.prepareCall("call olap-spl(?,?,?,?)");
st.setObject(1, "data/customer.ctx");
st.setObject(2, "[10,20,50,60,70,80].contain(department_id) && flag1==\"1\"&& flag8==\"1\" ");
st.setObject(3, "department_id,job_id,begin_date");
st.setObject(4, "sum(balance):sum,count(customer_id):count");
// 執(zhí)行存儲(chǔ)過程
st.execute();
// 獲取結(jié)果集
ResultSet rs = st.getResultSet();
// 繼續(xù)處理結(jié)果集，將結(jié)果集展現(xiàn)出來
}
catch(Exception e){
out.println(e);
}
finally{
// 關(guān)閉連接
if (con!=null) {
try {con.close();}
catch(Exception e) {out.println(e); }
}
}
}
此時(shí)，我們是采用單線程進(jìn)行的計(jì)算。
單線程時(shí)，Java 程序執(zhí)行的總時(shí)間是 12 秒。
我們也可以用多線程并行的方式執(zhí)行腳本。2 線程并行的 olap-spl.dfx 腳本如下：
B1：cursor 函數(shù)的 @m 是指多路游標(biāo)，最后一個(gè)參數(shù) 2 表示 2 路，也就是 2 線程并行。
2 線程時(shí)，Java 程序執(zhí)行的總時(shí)間是 6 秒。
