【摘要】用實例、分步驟，詳細講解多維分析（OLAP）的實現(xiàn)。
實踐目標
本期目標是練習將數(shù)據(jù)庫讀出的數(shù)據(jù)，盡可能轉(zhuǎn)換為有利于性能優(yōu)化的數(shù)據(jù)類型，例如：小整數(shù)和浮點數(shù)。
實踐的步驟：
1、 準備基礎寬表：修改上期的代碼，完成數(shù)據(jù)類型優(yōu)化存為組表文件。
2、 訪問基礎寬表：修改上期的代碼，在傳入?yún)?shù)保持不變的前提下，查詢數(shù)據(jù)轉(zhuǎn)換之后的組表文件，結(jié)果集也要返回原有的數(shù)據(jù)顯示值。對于這個要求，SQL 是無法實現(xiàn)傳入?yún)?shù)和結(jié)果集的轉(zhuǎn)換的，所以訪問寬表的代碼以 SPL 為例。
本期樣例寬表不變，依然為 customer 表。從 Oracle 數(shù)據(jù)庫中取出寬表數(shù)據(jù)的 SQL 語句是 select * from customer。執(zhí)行結(jié)果如下圖：
其中字段包括：
CUSTOMER_ID NUMBER(10,0), 客戶編號
FIRST_NAME VARCHAR2(20), 名
LAST_NAME VARCHAR2(25), 姓
PHONE_NUMBER VARCHAR2(20), 電話號碼
BEGIN_DATE DATE, 開戶日期
JOB_ID VARCHAR2(10), 職業(yè)編號
JOB_TITLE VARCHAR2(32), 職業(yè)名稱
BALANCE NUMBER(8,2), 余額
EMPLOYEE_ID NUMBER(4,0), 開戶雇員編號
DEPARTMENT_ID NUMBER(4,0), 分支機構(gòu)編號
DEPARTMENT_NAME VARCHAR2(32), 分支結(jié)構(gòu)名稱
FLAG1 CHAR(1), 標記 1
FLAG2 CHAR(1), 標記 2
FLAG3 CHAR(1), 標記 3
FLAG4 CHAR(1), 標記 4
FLAG5 CHAR(1), 標記 5
FLAG6 CHAR(1), 標記 6
FLAG7 CHAR(1), 標記 7
FLAG8 CHAR(1), 標記 8
多維分析計算的目標也不變，用下面 Oracle 的 SQL 語句表示：
select department_id,job_id,to_char(begin_date,'yyyymm') begin_month ,sum(balance) sum,count(customer_id) count
from customer
where department_id in (10,20,50,60,70,80)
and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP')
and begin_date>=to_date('2002-01-01','yyyy-mm-dd')
and begin_date<=to_date('2020-12-31','yyyy-mm-dd')
and flag1='1' and flag8='1'
group by department_id,job_id,to_char(begin_date,'yyyymm')
準備寬表
一、數(shù)值整數(shù)化
在 customer 表中有些字段本身就是整數(shù)，比如：CUSTOMER_ID、EMPLOYEE_ID、DEPARTMENT_ID。
處理方法：
l 如果從數(shù)據(jù)庫中導出的是整型，就可以直接存儲到組表中。
l 如果從數(shù)據(jù)庫中導出的不是整型，要用類型轉(zhuǎn)換函數(shù)強制轉(zhuǎn)換為整型。
l 要注意盡量讓整數(shù)值小于 65536，這樣性能最好。如果原字段值被人為的轉(zhuǎn)換成較大的整數(shù)，例如：所有的數(shù)值都加上了一個 100000，變成 100001、100002…，就要去掉前面的 1。
二、字符串整數(shù)化
FLAG1 到 FLAG8 是字符串，但是存儲的依然是整型數(shù)據(jù)，可以用類型轉(zhuǎn)換函數(shù)轉(zhuǎn)為整型。
JOB_ID 字段也是字符串，取值是 jobs 維表的主鍵，屬于枚舉類型。我們可以用 jobs 表中的序號代替 JOB_ID 字段，實現(xiàn)整數(shù)化。
jobs 表結(jié)構(gòu)和樣例數(shù)據(jù)如下：
處理方法：
l 取出 jobs 中的 JOB_ID，排好序后構(gòu)成一個序列 job。customer 寬表中增加 JOB_NUM 字段存儲 JOB_ID 在序列 job 中的序號。
三、日期整數(shù)化
大多數(shù)情況下，日期型數(shù)據(jù)只是用來比較，并不需要計算間隔，所以也可以用小整數(shù)來存儲。在多維分析計算中，按照年、月來計算的情況比較常見。小整數(shù)化之后的日期，要求能很方便的把年、月拆分出來。
處理方法：
l 我們可以計算出 BEGIN_DATE 字段值與一個日期起點的間隔月數(shù)，乘以 100 后加上 BEGIN_DATE 的日值，來代替日期型數(shù)據(jù)存入組表。起點日期根據(jù)日期數(shù)據(jù)的特征來確定，值越大越好。
例如：我們發(fā)現(xiàn)所有的 BEGIN_DATE 都在 2000 年之后，則可以確定日期起點為 2000-01-01。
確定日期起點后，就可以轉(zhuǎn)化 customer 寬表中的 BEGIN_DATE 字段值了。例如：BEGIN_DATE 為 2010-11-20，先計算出和 2000-01-01 相差的整月數(shù)是 130，乘以 100 后加上日值 20 即可得到小整數(shù) 13020。
以 2000-01-01 為日期起點，BEGIN_DATE 小于 2050 年時，整數(shù)化之后的值都小于 65536?？梢钥吹?，在業(yè)務數(shù)據(jù)允許的前提下，日期起點盡量晚，可以更大程度避免出現(xiàn)寬表中的日期超出小整數(shù)范圍的情況。
四、無法整數(shù)化的情況
必須用字符串表示的字段，如 FIRST_NAME、JOB_TITLE 等；
必須用浮點數(shù)表示的字段，如金額、折扣率等有小數(shù)部分的字段；
必須用字符串加整數(shù)一起表示的字段，如國際電話號碼等。
處理方法：
l 保持字段原值不動。
根據(jù)以上要求，改寫 etl.dfx，從數(shù)據(jù)庫中取出數(shù)據(jù)，類型轉(zhuǎn)化后，生成組表文件，存儲基礎寬表。代碼示例如下：
A1：連接預先配置好的數(shù)據(jù)庫 oracle，@l 是指取出字段名為小寫。注意這里是小寫字母L。
B1：建立數(shù)據(jù)庫游標，準備取出 customer 表的數(shù)據(jù)。customer 是事實表，實際應用中一般都比較大，所以用游標方式，避免內(nèi)存溢出。游標的 @d 選項是將 oracle 的 numeric 型數(shù)據(jù)轉(zhuǎn)換成 double 型數(shù)據(jù)，而非 decimal 型數(shù)據(jù)。decimal 型數(shù)據(jù)在 java 中的性能較差。
A2：從數(shù)據(jù)庫中讀 jobs 表，只讀取 JOB_ID 字段并排序。jobs 是維表，一般都比較小，所以直接讀入到內(nèi)存中。
B2：將 A2 的數(shù)據(jù)存儲成集文件，待后面使用。
A3：將 A2 轉(zhuǎn)化為序列。
B3：定義日期 2000-01-01。
A4：用 new 函數(shù)定義三種計算。
1、 CUSTOMER_ID 等確定是整數(shù)的數(shù)值，從 double 或者 string 轉(zhuǎn)換為 int。方法是直接用 int 函數(shù)做類型轉(zhuǎn)換。注意 int 不能大于 2147483647，對于數(shù)據(jù)量超過這個數(shù)值的事實表，序號主鍵要用 long 型。
2、 將 JOB_ID 從字符串轉(zhuǎn)化為整數(shù)，提高計算性能。方法是用 pos 函數(shù)找到 job_id 在 A3 中的序號，定義為 JOB_NUM 字段。
3、 用 interval 計算 begin_date 和 2000-01-01 之間相差的整月數(shù)，乘以 100 加上 begin_date 的日值，用 int 轉(zhuǎn)換為整數(shù)存儲為新的 begin_date。
A5：定義列存組表文件。字段名和 A4 完全一致。
A6：邊計算游標 A4，邊輸出到組表文件中。
B6：關(guān)閉組表文件和數(shù)據(jù)庫連接。
數(shù)據(jù)量為一千萬，導出組表文件約 344MB。和第一期未做數(shù)據(jù)類型優(yōu)化的文件比較如下：
從上表可以看出，完成數(shù)據(jù)類型優(yōu)化之后，文件大小減少了 12%（49M）。文件變小，能減少磁盤讀取數(shù)據(jù)量，有效提高性能。
訪問寬表
如上所述，后臺組表的很多字段已經(jīng)優(yōu)化轉(zhuǎn)換，沒有辦法用原來的 SQL 進行查詢了。我們采用執(zhí)行腳本的方式，提交過濾條件、分組字段等參數(shù)，后臺將參數(shù)值轉(zhuǎn)換成優(yōu)化后的數(shù)據(jù)類型，再對組表進行計算。這樣做，可以保證通用多維分析前端傳入的參數(shù)保持不變。最后，計算結(jié)果也需要轉(zhuǎn)換為對應的顯示值。
例如：傳入的參數(shù) flag1='1'，需要轉(zhuǎn)換為 flag1=1；計算結(jié)果中的 job_num 和 begin_date，還要從整數(shù)轉(zhuǎn)換為字符串 job_id 和日期。
為了實現(xiàn)這個計算，要先在節(jié)點服務器主目錄中編寫 init.dfx 文件，預先加載全局變量 job，用于后續(xù)的轉(zhuǎn)換計算。
init.dfx 代碼如下：
A1：取出集文件中的數(shù)據(jù)，@i 表示只有一列時讀成序列。
B1：存入全局變量 job。
寫好的 init.dfx 要放入節(jié)點機主目錄，啟動或重啟節(jié)點機時會被自動調(diào)用。
按照數(shù)據(jù)類型優(yōu)化要求改寫 olap-spl.dfx，用 SPL 代碼訪問寬表并進行過濾和分組匯總計算。
定義網(wǎng)格參數(shù)，將文件名、部門編號、工作編號、標志位、日期范圍、分組字段、聚合表達式分別傳入。
參數(shù)設置窗口如下，和第一期完全一致：
參數(shù)值樣例：
filename="data/customer.ctx"
arg_department_id ="10,20,50,60,70,80"
arg_job_id="AD_VP,FI_MGR,AC_MGR,SA_MAN,SA_REP"
arg_begin_date_min = "2002-01-01"
arg_begin_date_max ="2020-12-31"
arg_flag ="flag1==\"1\"&& flag8==\"1\" "
group="department_id,job_id,begin_yearmonth"
aggregate="sum(balance):sum,count(customer_id):count"
說明：group 中如果是 begin_date 則按照日期分組，如果是 begin_yearmonth 則按照年月分組。對于多維分析前端來說，可以認為有兩個字段。
SPL 代碼示例如下：
A1：打開組表對象。B1：定義起點日期 2000-01-01 用于參數(shù)和結(jié)果中的日期值轉(zhuǎn)換。
A2、B2：將傳入日期參數(shù)按照前面介紹的方法轉(zhuǎn)化為整數(shù)。
A3：將傳入的逗號分隔字符串 job_id 轉(zhuǎn)換為在全局變量 job 序列中的位置，也就是 job_num 整數(shù)序列。
B3：將傳入的逗號分隔字符串 department_id 用 int 函數(shù)轉(zhuǎn)換為整數(shù)序列。
A4：將傳入的 flag 條件中的雙引號去掉，變?yōu)檎麛?shù)條件。
B4：將傳入的分組字段中的 job_id 替換為 job_num。
A5：將傳入的分組字段中的 begin_yearmonth，替換為 begin_date\100。begin_date 的字段值除以 100 取整，就是實際日期和起點日期相差的月數(shù)。
A6：定義帶過濾條件的游標。
A7：對游標計算小結(jié)果集分組匯總。
A8：將 A7 結(jié)果的字段名形成序列。
B8： 字段名中如果有 job_num，就替換成轉(zhuǎn)換語句。語句的作用是：將分組結(jié)果中的 job_num 轉(zhuǎn)換為 job_id。
A9：字段名中如果有 begin_yearmonth，替換為轉(zhuǎn)換語句，作用是：將分組字段中的月差值 begin_yearmonth 從整數(shù)轉(zhuǎn)化為 yyyymm。
A10：字段名中如果有 begin_date，替換為轉(zhuǎn)換語句，作用是：將分組字段中的整數(shù)化日期值轉(zhuǎn)換為日期型。
A11：將替換之后的 A8 重新用逗號連接成字符串，并對 A7 循環(huán)計算，完成字段類型和顯示值的轉(zhuǎn)換。
A12：返回 A11 結(jié)果集。
執(zhí)行結(jié)果如下圖：
olap-spl.dfx 編寫好之后，可以在多維分析中作為存儲過程調(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)用存儲過程，其中 olap-spl 是 dfx 的文件名
st =con.prepareCall("call olap-spl(?,?,?,?,?,?,?,?)");
st.setObject(1, "data/customer.ctx");//arg_filename
st.setObject(2, "10,20,50,60,70,80");//arg_department_id
st.setObject(3, "AD_VP,FI_MGR,AC_MGR,SA_MAN,SA_REP");//arg_job_id
st.setObject(4, "2002-01-01");//arg_begin_date_min
st.setObject(5, "2020-12-31");//arg_begin_date_max
st.setObject(6, "flag1==\"1\"&& flag8==\"1\" ");//arg_flag
st.setObject(7, "department_id,job_id,begin_yearmonth");//arg_group
st.setObject(8, "sum(balance):sum,count(customer_id):count");//arg_aggregate
// 執(zhí)行存儲過程
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); }
}
}
}
Java 代碼加上后臺計算返回結(jié)果總的執(zhí)行時間，和第一期比較如下：
如上期所述，表中的執(zhí)行時間硬件配置相關(guān)，其絕對數(shù)值并不重要。重要的是，通過上表的對比可以看出，數(shù)據(jù)類型優(yōu)化有效提高了計算性能。
