BI 系統(tǒng)的常見結構是:前端是 BI 應用,負責多維分析的用戶操作和結果呈現(xiàn);后臺是數(shù)據(jù)庫 / 數(shù)據(jù)倉庫,負責數(shù)據(jù)計算和存儲。前端和后臺之間用 SQL 作為接口。
成都網站建設哪家好,找創(chuàng)新互聯(lián)建站!專注于網頁設計、成都網站建設、微信開發(fā)、微信小程序、集團成都企業(yè)網站建設等服務項目。核心團隊均擁有互聯(lián)網行業(yè)多年經驗,服務眾多知名企業(yè)客戶;涵蓋的客戶類型包括:高空作業(yè)車租賃等眾多領域,積累了大量豐富的經驗,同時也獲得了客戶的一致稱贊!
實際應用中,常常出現(xiàn)后臺數(shù)據(jù)倉庫壓力過重的問題。問題表現(xiàn)為前端響應時間過長,數(shù)據(jù)倉庫反應速度變慢。
常見的解決方案是在數(shù)據(jù)倉庫和應用之間再增加一個前置數(shù)據(jù)庫。但是前置數(shù)據(jù)庫和后臺數(shù)據(jù)倉庫之間很難實現(xiàn)數(shù)據(jù)的路由和混合計算,例如:訪問頻次很高的熱點數(shù)據(jù)放在前置數(shù)據(jù)庫,大量冷數(shù)據(jù)放在數(shù)據(jù)倉庫中,查詢時按照一定規(guī)則來決定訪問前置數(shù)據(jù)庫還是后臺數(shù)據(jù)倉庫。而如果前置數(shù)據(jù)庫和后臺數(shù)據(jù)倉庫是不同的產品,還要考慮 SQL 的翻譯問題。
作為數(shù)據(jù)計算中間件(DCM),構建獨立的數(shù)據(jù)前置層是集算器的重要應用模式。數(shù)據(jù)前置層將 BI 系統(tǒng)重構為三層結構:數(shù)據(jù)存儲及批量數(shù)據(jù)計算層由數(shù)據(jù)庫承擔;數(shù)據(jù)前置及緩存層由集算器承擔;數(shù)據(jù)分析展現(xiàn)層由多維分析工具或者報表工具承擔。
集算器可以脫離數(shù)據(jù)庫進行數(shù)據(jù)緩存和獨立的復雜計算,同時具備可編程網關機制,可以在緩存計算和 SQL 透傳之間自由切換。利用集算器完成前置層數(shù)據(jù)計算,可以與數(shù)據(jù)庫承擔的批量數(shù)據(jù)計算任務分離,并且不必再建設另外一個數(shù)據(jù)庫。
集算器可以將熱點數(shù)據(jù)、近期數(shù)據(jù)放在數(shù)據(jù)前置層,從而起到數(shù)據(jù)緩存的作用,可以有效提高數(shù)據(jù)計算的速度,減少用戶等待時間。
系統(tǒng)架構圖如下:
前臺 BI 系統(tǒng),要針對訂單數(shù)據(jù)做自助查詢。查詢的必選條件是訂購日期。為了簡化起見,前臺 BI 系統(tǒng)用 tomcat 服務器中的 jdbc.jsp 來模擬。
集算器 JDBC 和智能網關集成在應用系統(tǒng)中。jdbc.jsp 模仿 BI 應用系統(tǒng),產生符合集算器簡單查詢規(guī)范的 SQL,通過集算器 JDBC 提交給集算器智能網關處理。
數(shù)據(jù)來自于 ORACLE 數(shù)據(jù)庫 demo 中的 ORDERS 表。ORDERS 訂單表是全量數(shù)據(jù),集算器只存儲最近三年的數(shù)據(jù),比如:2015 年 -2018 年。日期以訂購日期為準。
用下面的 orders.sql 文件在 ORACLE 數(shù)據(jù)庫中完成 ORDERS 表的建表和數(shù)據(jù)初始化。
點擊下載 orders.sql
在集算器中,新建一個數(shù)據(jù)源 orcl,連接 ORACLE 數(shù)據(jù)庫。用 SPL 語言腳本 etl1.dfx 將最近三年的數(shù)據(jù)預先讀取到集算器集文件 orders.btx 中。SPL 腳本如下:
A | B | |
---|---|---|
1 | =year(now())-3 | |
2 | =connect(“orcl”) | =A2.cursor@d(“select * from orders where to_char(orderdate,‘yyyy’)>=?”,A1) |
3 | =file(“C:/tomcat6/webapps/gateway/WEB-INF/data/orders.btx”) | |
4 | =A3.export@z(B2) | >A2.close() |
從 SPL 腳本可以看出,只要在 A4 單元格中用一句 export 就可以將數(shù)據(jù)庫中的數(shù)據(jù)導出到文件中。集文件是集算器內置的二進制文件格式,采用了簡單壓縮機制,相同數(shù)據(jù)量比數(shù)據(jù)庫的占用空間會更小。@z 選項表示寫出可以分段的文件,很適合常常需要并行的多維分析類運算。
B2 單元格中數(shù)據(jù)庫游標的 @d 選項,表示從 ORACLE 數(shù)據(jù)庫中取數(shù)的時候將 numeric 型數(shù)據(jù)轉換成 double 型,精度對于金額這樣的常見數(shù)值完全足夠了。如果沒有這個選項就會默認轉換成 big decimal 型數(shù)據(jù),計算性能會受到較大影響。
腳本可以用 windows 或者 linux 命令行的方式執(zhí)行,結合定時任務,可以定時執(zhí)行批量任務。windows 命令行的調用方式是:
C:\Program Files\raqsoft\esProc\bin>esprocx.exe C: \etl1.dfx
linux 命令是:
/raqsoft/esProc/bin/esprocx.sh /gateway/etl1.dfx
集算器 JDBC 智能網關接收到 SQL 后,轉給 gateway1.dfx 程序處理。gateway1.dfx 判斷是否三年內的查詢,如果是,就把表名換成文件名,查本地文件 orders.btx 返回結果。如果不是,把 SQL 轉換成 ORACLE 格式,提交數(shù)據(jù)庫處理。
1、下面的 gateway 目錄復制到 tomcat 的應用目錄。
點擊下載 gateway.zip
目錄結構如下圖:
注意:配置文件在 classes 中,在官網上獲取的授權文件也要放在 classes 目錄中。集算器的 Jar 包要放在 lib 目錄中(需要哪些 jar 請參照集算器教程)。另外,還需要檢查和修改 raqsoftConfig.xml 中的如下配置:
<mainPath>C:\\\tomcat6\\\webapps\\\gateway\\\WEB-INF\\\dfx\\</mainPath> <JDBC> <load>Runtime,Server\\</load> <gateway>gateway1.dfx\\</gateway> </JDBC> <mainPath>C:\\tomcat6\\webapps\\gateway\\WEB-INF\\dfx\</mainPath> <JDBC> <load>Runtime,Server\</load> <gateway>gateway1.dfx\</gateway> </JDBC><mainPath>C:\\\tomcat6\\\webapps\\\gateway\\\WEB-INF\\\dfx\\</mainPath><JDBC><load>Runtime,Server\\</load><gateway>gateway1.dfx\\</gateway> </JDBC> <mainPath>C:\\tomcat6\\webapps\\gateway\\WEB-INF\\dfx\</mainPath><JDBC><load>Runtime,Server\</load><gateway>gateway1.dfx\</gateway></JDBC>
這里標簽的內容就是網關 dfx 文件。在 BI 系統(tǒng)中調用集算器 JDBC 時,所執(zhí)行的 SQL 都將交由網關文件處理。如果不配置這個標簽,JDBC 提交的語句都被集算器當作腳本直接解析運算,而無法實現(xiàn)希望的路由規(guī)則。
2、編輯 gateway 目錄中的 jdbc.jsp,模擬前臺界面提交 sql 展現(xiàn)結果。
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ page import="java.sql.*" %> <body> <% String driver = "com.esproc.jdbc.InternalDriver"; String url = "jdbc:esproc:local\\://"; try { Class.forName(driver); Connection conn = DriverManager.getConnection(url); Statement statement = conn.createStatement(); String sql ="select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date('2015-07-18') and AMOUNT>100 "; out.println("Data gateway test page v1 <br><br><br><pre>"); out.println("訂單ID"+"\\\t"+"客戶ID"+"\\\t"+"雇員ID"+"\\\t"+"訂購日期"+"\\\t"+"訂單金額"+"<br>"); ResultSet rs = statement.executeQuery(sql); int f1,f6; String f2,f3,f4; float f5; while (rs.next()) { f1 = rs.getInt("ORDERID"); f2 = rs.getString("CUSTOMERID"); f3 = rs.getString("EMPLOYEEID"); f4 = rs.getString("ORDERDATE"); f5 = rs.getFloat("AMOUNT"); out.println(f1+"\\\t"+f2+"\\\t"+f3+"\\\t"+f4+"\\\t"+f5+"\\\t"+"<br>"); } out.println("</pre>"); rs.close(); conn.close(); } catch (ClassNotFoundException e) { System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } %> </body> <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ page import="java.sql.*" %> <body> <% String driver = "com.esproc.jdbc.InternalDriver"; String url = "jdbc:esproc:local\://"; try { Class.forName(driver); Connection conn = DriverManager.getConnection(url); Statement statement = conn.createStatement(); String sql ="select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date('2015-07-18') and AMOUNT>100 "; out.println("Data gateway test page v1 <br><br><br><pre>"); out.println("訂單ID"+"\\t"+"客戶ID"+"\\t"+"雇員ID"+"\\t"+"訂購日期"+"\\t"+"訂單金額"+"<br>"); ResultSet rs = statement.executeQuery(sql); int f1,f6; String f2,f3,f4; float f5; while (rs.next()) { f1 = rs.getInt("ORDERID"); f2 = rs.getString("CUSTOMERID"); f3 = rs.getString("EMPLOYEEID"); f4 = rs.getString("ORDERDATE"); f5 = rs.getFloat("AMOUNT"); out.println(f1+"\\t"+f2+"\\t"+f3+"\\t"+f4+"\\t"+f5+"\\t"+"<br>"); } out.println("</pre>"); rs.close(); conn.close(); } catch (ClassNotFoundException e) { System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } %> </body><%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ page import="java.sql.*" %> <body> <% String driver = "com.esproc.jdbc.InternalDriver"; String url = "jdbc:esproc:local\\://";try { Class.forName(driver); Connection conn = DriverManager.getConnection(url); Statement statement = conn.createStatement(); String sql ="select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date('2015-07-18') and AMOUNT>100 ";out.print ln("Data gateway test page v1 <br><br><br><pre>");out.println("訂單ID"+"\\\t"+"客戶ID"+"\\\t"+"雇員ID"+"\\\t"+"訂購日期"+"\\\t"+"訂單金額"+"< br>"); ResultSet rs = statement.executeQuery(sql);int f1,f6; String f2,f3,f4;float f5;while (rs.next()) { f1 = rs.getInt("ORDERID"); f2 = rs.getString("CUSTOMERID"); f3 = rs.getString("EMPLOYEEID"); f4 = rs.getString("ORDERDATE"); f5 = rs.getFloat("AMOUNT");out.println(f1+"\\\t"+f2+"\\\t"+f3+"\\\t"+f4+"\\\t"+f5+"\\\t"+"<br>"); }out.println("</pre>"); rs.close(); conn.close(); } catch (ClassNotFoundException e) { System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } %> </body> <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ page import="java.sql.*" %> <body> <% String driver = "com.esproc.jdbc.InternalDriver"; String url = "jdbc:esproc:local\://";try { Class.forName(driver); Connection conn = DriverManager.getConnection(url); Statement statement = conn.createStatement(); String sql ="select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date('2015-07-18') and AMOUNT>100" ;out.print ln("Data gateway test page v1 <br><br><br><pre>");out.println("訂單ID"+"\\t"+"客戶ID"+"\\t"+"雇員ID"+"\\t"+"訂購日期"+"\\t"+"訂單金額"+"<br>"); ResultSet rs = statement.executeQuery(sql);int f1,f6; String f2,f3,f4;float f5;while (rs.next()) { f1 = rs.getInt("ORDERID"); f2 = rs.getString("CUSTOMERID"); f3 = rs.getString("EMPLOYEEID"); f4 = rs.getString("ORDERDATE"); f5 = rs.getFloat("AMOUNT");out.println(f1+"\\t"+f2+"\\t"+f3+"\\t"+f4+"\\t"+f5+"\\t"+"<br>"); }out.println("</pre>"); rs.close(); conn.close(); } catch (ClassNotFoundException e) { System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } %> </body>
可以看到,jsp 中先連接集算器的 JDBC,然后提交執(zhí)行 SQL。步驟和一般的數(shù)據(jù)庫完全一樣,具有很高的兼容性和通用性。對于 BI 工具來說,雖然是界面操作來連接 JDBC 和提交 SQL,但是基本原理和 jsp 完全一樣。
3、打開 dfx 目錄中的 gateway1.dfx,觀察理解 SPL 代碼。
首先,可以看到 gateway1.dfx 傳入?yún)?shù)是 sql 和 args,例如傳入 SQL:
select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date(‘2015-07-18’) and AMOUNT>100。
接下來,可以看到 SPL 腳本如下:
A | B | C | |
---|---|---|---|
1 | =filename=“C:/tomcat6/webapps/gateway/WEB-INF/data/orders.btx” | ||
2 | =sql.sqlparse@w().split(" ") | =A2.select@1(like(~,“ORDERDATE=date(‘????-??-??’)”)) | |
3 | =mid(right(B2,14),3,10) | =year(now())-year(date(A3)) | |
4 | if B3<=3 | =connect() | =sql=replace(sql,“from ORDERS”,"from "+filename) |
5 | =B4.cursor@x(sql) | return B5 | |
6 | else | =connect(“orcl”) | =sql=sql.sqltranslate(“ORACLE”) |
7 | =B6.cursor@x(sql) | return B7 |
說明:
A1:定義集算器集文件的絕對路徑。
A2:解析 SQL,獲取 where 子句,并用空格來拆分成序列。
B2、A3:在 A2 序列找到必選條件訂購日期,獲取日期值。
B3:計算訂購日期的年份和當前日期年份相差幾年。
A4:判斷相差的年份是否超過 3 年。
B4-C5:如果不超過 3 年,就連接文件系統(tǒng)。將 SQL 中的 from 訂單,替換成 from 文件名。執(zhí)行 SQL 得到游標并返回。
B6-C7:如果超過 3 年,就連接數(shù)據(jù)庫。將 SQL 翻譯成符合 ORACLE 數(shù)據(jù)庫規(guī)范的 SQL, 執(zhí)行 SQL 得到游標并返回。
4、啟動 tomcat,在瀏覽器中訪問 http://localhost:8080/gateway/jdbc.jsp,查看結果。
還可以繼續(xù)測試如下情況:
(1) 超出三年的查詢
sql =“select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date(‘2014-07-18’) and AMOUNT>100”;
由于日期 2014 年已經超出三年的限制,所以在 C6 中 SQL 會被翻譯成 ORACLE 規(guī)范如下:
SELECT * FROM (select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=TO_DATE(‘2014-07-18’,‘YYYY-MM-DD’) and AMOUNT>100)t WHERE ROWNUM<=10
(2) 分組匯總
sql =“select CUSTOMERID,EMPLOYEEID,sum(AMOUNT) 訂單總額,count(1) 訂單數(shù)量 from ORDERS where ORDERDATE=date(‘2015-07-18’) group by CUSTOMERID,EMPLOYEEID”;
(3) 并行查詢
sql="select /*+ parallel (4) */
top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date(‘2015-07-18’) and AMOUNT>100"
和 ORACLE 類似,集算器簡單 SQL 也支持 /*+ parallel (4) */ 這樣的并行查詢。
第一種解決辦法是利用應用服務器的資源。在并發(fā)量很大,或者數(shù)據(jù)量很大的情況下,應用服務器會出現(xiàn)較大壓力。這種情況下,推薦用獨立的節(jié)點服務器進行數(shù)據(jù)計算。節(jié)點服務器可以進行橫向擴展,應對大并發(fā)或大數(shù)據(jù)量計算的壓力。
集算器 JDBC 智能網關接受到 SQL 后,轉給 gateway2.dfx 程序處理。gateway2.dfx 調用節(jié)點服務器上的 gatewayServer2.dfx 進行計算。gatewayServer2.dfx 判斷是否三年內的查詢,如果是,就把表名換成文件名,查本地文件 orders.btx 返回結果。如果不是三年內的查詢,把 sql 轉換成 ORACLE 格式,提交數(shù)據(jù)庫處理。
1、下面的 gatewayServer 目錄復制到需要的目錄。集算器的節(jié)點服務器具備跨平臺的特性,可以運行在任何支持 Java 的操作系統(tǒng)上,部署方法參見集算器教程。這里假設放到 windows 操作系統(tǒng)的 C 盤根目錄。
點擊下載 gatewayServer.zip
2、修改前面的 dfx,將 A3 改為 =file(“C:/gatewayServer/data/orders.btx”),另存為 etl2.dfx。修改好的 etl2.dfx 在 c:\gatewayServer 目錄。
3、打開應用服務器中的 C:\tomcat6\webapps\gateway\WEB-INF\dfx\gateway2.dfx,觀察理解 SPL 代碼。參數(shù)不變,還是傳入的 sql 和 args。
A | B | |
---|---|---|
1 | =callx(“gatewayServer2.dfx”,[sql];[“127.0.0.1:8281”]) | |
2 | return A1.ifn() |
A1:調用節(jié)點機上的 gatewayServer2.dfx。參數(shù)是 [sql],中括號表示序列,此時是只有一個成員的序列。[“127.0.0.1:8281”] 是節(jié)點機的序列,采用 IP: 端口號的方式。節(jié)點機是集群的時候,可以有多個 IP 地址,例如:["IP1:PORT1″,"IP2:PORT2″,“IP3:PORT3”]。
A2:返回 A1 調用的結果。因為調用結果可以是序列,所以要用 ifn 函數(shù)找到序列中第一個不為空的成員,就是 SQL 對應的返回結果。
修改 C:\tomcat6\webapps\gateway\WEB-INF\classes\raqsoftConfig.xml 中的如下配置 gateway1.dfx 改為 gateway2.dfx。
<JDBC> <load>Runtime,Server\\</load> <gateway>gateway2.dfx\\</gateway> </JDBC> <JDBC> <load>Runtime,Server\</load> <gateway>gateway2.dfx\</gateway> </JDBC><JDBC><load>Runtime,Server\\</load><gateway>gateway2.dfx\\</gateway></JDBC> <JDBC><load>Runtime,Server\</load><gateway>gateway2.dfx\</gate way></JDBC>
4、啟動節(jié)點服務器。
運行 esprocs.exe, 如下圖:
點擊配置按鈕,配置相關參數(shù):
點擊確定后,返回主界面,點擊啟動按鈕。
5、打開 C:\gatewayServer\dfx\gatewayServer2.dfx,觀察理解 SPL 代碼。
A | B | C | |
---|---|---|---|
1 | =filename=“C:/gatewayServer/data/orders.btx” | ||
2 | =sql.sqlparse@w().split(" ") | =A2.select@1(like(~,“ORDERDATE=date(‘????-??-??’)”)) | |
3 | =mid(right(B2,14),3,10) | =year(now())-year(date(A3)) | |
4 | if B3<=3 | =connect() | =sql=replace(sql,“from ORDERS”,"from "+filename) |
5 | =B4.cursor@x(sql) | return B5 | |
6 | else | =connect(“orcl”) | =sql=sql.sqltranslate(“ORACLE”) |
7 | =B6.cursor@x(sql) | return B7 |
代碼基本和前面的 gateway1.dfx 一致。區(qū)別是這個 dfx 是在節(jié)點服務器 unitServer 上執(zhí)行的,數(shù)據(jù)是存在節(jié)點服務器上。
5、重啟 tomcat,在瀏覽器中訪問 http://localhost:8080/gateway/jdbc.jsp,查看結果。
當數(shù)據(jù)量很大同時又需要秒級的查詢速度時,我們建議采用集算器組表來存儲數(shù)據(jù)。組表適用的場合包括:數(shù)據(jù)表字段有幾十個甚至更多;數(shù)據(jù)量幾千萬行,存成集文件在 1G 以上;查詢要求秒級響應。
對于簡單 SQL 來說,組表文件的用法和集文件沒有什么不同, 只是文件名不一樣。gatewayServer2.dfx 中只需要把 A1 改為 =filename=“C:/gatewayServer/data/orders.ctx”,另存為 gatewayServer3.dfx。相應的 gateway2.dfx 中的 A1 改為 =callx(“gatewayServer3.dfx”,[sql];[“127.0.0.1:8281”]),另存為 gateway3.dfx。
修改 C:\tomcat6\webapps\gateway\WEB-INF\classes\raqsoftConfig.xml 中的如下配置 gateway2.dfx 改為 gateway3.dfx。
<JDBC> <load>Runtime,Server\\</load> <gateway>gateway3.dfx\\</gateway> </JDBC> <JDBC> <load>Runtime,Server\</load> <gateway>gateway3.dfx\</gateway> </JDBC><JDBC><load>Runtime,Server\\</load><gateway>gateway3.dfx\\</gateway></JDBC> <JDBC><load>Runtime,Server\</load><gateway>gateway3.dfx\</gate way></JDBC>
我們重點理解如何改寫 etl 過程,修改前面的 etl2.dfx,另存為 etl3.dfx。
A | |
---|---|
1 | =year(now())-3 |
2 | =connect(“orcl”) |
3 | =A2.cursor@d(“select CUSTOMERID,EMPLOYEEID,ORDERDATE,ORDERID,AMOUNT from ORDERS where to_char(ORDERDATE,‘yyyy’)>=? order by CUSTOMERID,EMPLOYEEID,ORDERDATE,ORDERID",A1) |
4 | =file(“C:/gatewayServer/data/orders.ctx”) |
5 | =A4.create(#CUSTOMERID,#EMPLOYEEID,#ORDERDATE,#ORDERID,AMOUNT) |
6 | =A5.append(A3) |
7 | >A2.close() |
組表與集文件不同,默認是采用列式存儲的,支持任意分段的并行計算,可以有效提升查詢速度。同時,生成組表的時候,要注意數(shù)據(jù)預先排序和合理定義維字段。本例中,按照經常過濾、分組的字段,將維字段確定為:CUSTOMERID,EMPLOYEEID,ORDERDATE,ORDERID。
A3 取得數(shù)據(jù)的時候,要按照維字段排序。因為 CUSTOMERID,EMPLOYEEID,ORDERDATE 對應的重復數(shù)據(jù)多,所以放在前面排序;ORDERID 對應的重復數(shù)據(jù)少,所以放在后面排序。
A4 中定義組表的時候用 #來表示維字段。
需要說明的是,組表也支持并行查詢 /*+ parallel (n) */。
BI 應用的特點是:
1、響應時間要求高,一般不超過 5-10 秒。
2、查詢對應數(shù)據(jù)量在幾百兆到幾 G 范圍,字段有幾十個甚至上百個。
3、并發(fā)量較大,幾十到幾百個并發(fā)。
性能優(yōu)化的方法是:
1、采用組表,提高單任務查詢的響應速度。
◇ 根據(jù)需求,合理定義維字段。
組表定義的時候,要按照業(yè)務的需要確定維字段。要選擇經常作為過濾條件或者用來分組的字段作為維字段,維字段前用 #標識。
◇ 按照維字段,預先排序。
要按照維字段做好數(shù)據(jù)的排序,重復記錄數(shù)多的字段在前面,例如:按照 order by 省,市,縣的字段順序來排序,而不是反過來。
◇ 根據(jù)并發(fā)量,選擇是否用并行查詢。
并發(fā)量比較大的時候,單個 SQL 查詢就不建議用并行查詢了 /*+ parallel (n) */。并行查詢會消耗更多的線程數(shù),反而會影響大的并發(fā)性能。
2、合理配置節(jié)點服務器的參數(shù),發(fā)揮每個節(jié)點的性能。
每臺服務器(實體機或者虛擬機)要啟動一個節(jié)點服務器,每個節(jié)點服務器啟動分機的配置界面如下:
◇ 根據(jù)硬件資源,配置進程數(shù)
進程列表中的進程數(shù)(也就是適合作業(yè)數(shù))建議是不要超過 CPU 總核數(shù) *2/3。例如:服務器有 8 個 CPU 每個兩核,總核數(shù)是 8*2=16,那么進程數(shù)量就不要超過 16*2/3=10 個。最大作業(yè)數(shù)推薦是適合作業(yè)數(shù) *2,也就是 10*2=20 個。
◇ 盡量多分配內存,但要避免超量
節(jié)點服務器每個進程的最大內存要盡量多分配,但是總數(shù)加起來要比實際的物理內存小,避免操作系統(tǒng)用硬盤來補充內存的不足。例如,總內存是 32G,進程數(shù)量是 8 個,那么每個進程的最大內存就不要大于 4G。配置進程的最大最小內存是在 C:\Program Files\raqsoft\esProc\bin\config.txt 中,例如:
jvm_args=-Xms128m -Xmx4845m 最小內存是 128M,最大是 4G。
3、橫向擴展節(jié)點服務器,多機應對大并發(fā)訪問。
◇ 橫向擴展,應對大并發(fā)。
隨著并發(fā)量的增大,當性能不能滿足要求的時候,要增加節(jié)點服務器的數(shù)量,通過橫向擴展來滿足需求。
◇ 增加服務器列表配置項。
這時候要修改 gateway3.dfx 中的 callx 函數(shù)的服務器序列參數(shù)??梢詫⒎掌餍蛄袇?shù)寫到配置文件中,這樣就可以不必每次都修改 dfx 文件了。
4、使用本機硬盤數(shù)據(jù)進行計算,避免跨網絡訪問。
硬盤的 IO 速度是比較有保證的。
節(jié)點服務器通過網絡去取其他服務器上的數(shù)據(jù),或者通過訪問共享存儲上的數(shù)據(jù),經常會出現(xiàn)網絡阻塞的情況,降低查詢響應速度。因此,盡可能每臺節(jié)點服務器僅僅執(zhí)行本機上的數(shù)據(jù),不要跨網絡訪問。
可編程數(shù)據(jù)路由是數(shù)據(jù)計算中間件(DCM)的重要應用場景。
在前述的例子中,數(shù)據(jù)路由的策略是:最近三年的數(shù)據(jù)作為熱數(shù)據(jù)放路由到集算器中計算,其他數(shù)據(jù)作為冷數(shù)據(jù),路由到數(shù)據(jù)庫中計算。
類似的路由規(guī)則還有:最近三天和最近十二個月的最后一天的數(shù)據(jù)作為熱數(shù)據(jù),路由到集算器中計算,其他數(shù)據(jù)路由到數(shù)據(jù)庫匯總計算。
對于冷熱數(shù)據(jù)計算路由規(guī)則,本篇只介紹了一次查詢只涉及冷或熱數(shù)據(jù)的情況,如果在一次查詢中可能同時涉及冷熱兩種數(shù)據(jù),我們將在后續(xù)文章中進行介紹。
實際應用中,數(shù)據(jù)路由的規(guī)則可能會很復雜和多變,通過配置來實現(xiàn)會非常困難,用編程的方式實現(xiàn)是最佳方案。采用集算器的編程語言 SPL 來實現(xiàn)復雜的數(shù)據(jù)路由規(guī)則是最簡單和最高效的。集算器支持多樣性異構數(shù)據(jù)源的混合計算,可以編程實現(xiàn)涉及到各種異構數(shù)據(jù)源的復雜數(shù)據(jù)路由規(guī)則。
用作多維分析后臺時,數(shù)據(jù)計算中間件(DCM)要提供必要的 SQL 解析與翻譯功能。
數(shù)據(jù)路由的實現(xiàn)離不開集算器對 SQL 語句的解析和翻譯。首先要用集算器的 SQL 解析能力,找到 where 條件中的日期字段,然后根據(jù)規(guī)則來決定路由到文件還是數(shù)據(jù)庫。如果是路由到數(shù)據(jù)庫,那么要把集算器的標準 SQL 翻譯成數(shù)據(jù)庫的 SQL,就要用到集算器的 SQL 翻譯能力。
集算器的 SQL 解析用 sqlparse()函數(shù)實現(xiàn),SQL 翻譯用 sqltranslate() 函數(shù)實現(xiàn)。
SQL 性能優(yōu)化也是數(shù)據(jù)計算中間件(DCM)必不可少的能力。
BI 應用允許用戶拖拽生成 SQL,就會出現(xiàn)很多性能不高的 SQL。比如直接在明細查詢的 SQL 外面加上一層 count 來統(tǒng)計結果總條數(shù):select count(1) from (select f1,f2,f3,f4…f30 from table1 where f1=1 and 1=1)。此時子查詢中的 f1 到 f30 如果全部取出,就會降低查詢的性能。1=1 這樣的過濾條件也會造成沒有意義的時間消耗。
集算器簡單 SQL 引擎,可以完成自動查詢優(yōu)化。去掉 1=1 這樣不必要的條件,也不會取出所有字段來完成 count。從而實現(xiàn) SQL 解析和優(yōu)化,有效的提高查詢性能。
類似的,還有 select top 10 f1,f2 from table1 order by f1。集算器會采用小結果集比較的方式實現(xiàn)??梢宰龅綗o須大排序,只遍歷一邊數(shù)據(jù)即可得到需要的結果,有效提升查詢速度。
先進的數(shù)據(jù)存儲方式,是數(shù)據(jù)計算中間件(DCM)成功實施的重要保障。
集算器組表采用列存方式存儲數(shù)據(jù),對于字段特別多的寬表查詢,性能提升特別明顯。組表采用的列存機制和常規(guī)列存是不同的。常規(guī)列存(比如 parquet 格式),只能分塊之后,再在塊內列存,在做并行計算的時候是受限的。組表的可并行壓縮列存機制,采用倍增分段技術,允許任意分段的并行計算,可以利用多 CPU 核的計算能力把硬盤的 IO 發(fā)揮到極致。
組表生成的時候,要指定維字段,數(shù)據(jù)本身是按照維字段有序存放的,常用的條件過濾計算不依賴索引也能保證高性能。文件采用壓縮存儲,減小在硬盤上占用的空間,讀取更快。由于采用了合適的壓縮比,解壓縮占用的 CPU 時間可以忽略不計。
組表也可以采取行存和全內存存儲數(shù)據(jù),支持內存數(shù)據(jù)庫方式運行。
敏捷的集群能力可以保證數(shù)據(jù)計算中間件(DCM)的高性能和高可用性。
集算器節(jié)點服務器是獨立進程,可以接受集算器網關程序的計算請求并返回結果。對于并發(fā)訪問的情況,可以發(fā)給多個服務器同時計算,提高并發(fā)容量。對于單個大計算任務的情況,可以分成多個小任務,發(fā)給多個服務器同時計算,起到大數(shù)據(jù)并行計算的作用。
集算器集群計算方案,具備敏捷的橫向擴展能力,并發(fā)量或者數(shù)據(jù)量大時可以通過快速增加節(jié)點來解決。集算器集群也具備容錯能力,即有個別節(jié)點失效時還能確保整個集群能工作,計算任務能繼續(xù)執(zhí)行完畢,起到多機熱備和保證高可用性的作用。
作為數(shù)據(jù)計算中間件(DCM),集算器實現(xiàn)的數(shù)據(jù)計算網關和路由,可以解決數(shù)據(jù)倉庫無法滿足性能要求,冷熱數(shù)據(jù)分開又要混合計算的場景,不僅僅限于前端是 BI 的情況。例如:大屏展示、管理駕駛艙、實時報表、大數(shù)據(jù)量清單報表、報表批量訂閱等等。
本文名稱:用計算路由的方法優(yōu)化BI后臺性能
網站網址:http://muchs.cn/article44/iehhee.html
成都網站建設公司_創(chuàng)新互聯(lián),為您提供網站策劃、網站制作、品牌網站建設、手機網站建設、網頁設計公司、網站維護
聲明:本網站發(fā)布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯(lián)