建議你們看到文末，不會(huì)虧待你們

日常一提數(shù)據(jù)分析和可視化，就想到這個(gè)工具操作要多簡(jiǎn)單易用，圖表要多美多炫，然而總是忽略背后的數(shù)據(jù)支撐。

excel 幾十萬行數(shù)據(jù)就卡死崩，談何數(shù)據(jù)透視表、可視化？

近千萬行的數(shù)據(jù)，訂單提交數(shù)據(jù)庫(kù)，sql sever處理要5分多鐘，如果頻繁入庫(kù)/取數(shù)的話.....

要知道，為了支撐起業(yè)務(wù)人員的數(shù)據(jù)分析，以及日常不考慮計(jì)算邏輯和技術(shù)難度，IT人員也是要花費(fèi)很大的心血和精力啊（心疼運(yùn)維人員n秒）。

隨著公司業(yè)務(wù)的發(fā)展，數(shù)據(jù)量變大是必然的事實(shí)。那么，數(shù)據(jù)部門要做分析，業(yè)務(wù)部門要看報(bào)表，要跑數(shù)據(jù)，要用BI，大數(shù)據(jù)量（千萬級(jí)及以上）的分析，性能該如何優(yōu)化？

這里借某公司的真實(shí)案例，來闡述一下方案。

----------------------------------

作為公司的IT科技部門人員，經(jīng)常聽到業(yè)務(wù)部門對(duì)自己使用的數(shù)據(jù)庫(kù)各種吐槽：

竟然存放在mongoDB中啊，震驚(ΩДΩ)。

數(shù)據(jù)庫(kù)慢慢熟悉了還好啊，但是現(xiàn)在每天的數(shù)據(jù)量越來越大，而且還在增加啊，增加大家很開心，然而數(shù)據(jù)庫(kù)并不開心啊，簡(jiǎn)單的查詢統(tǒng)計(jì)10多分鐘還出不來結(jié)果，更不用說有稍微復(fù)雜點(diǎn)的統(tǒng)計(jì)分析了。

我天天找DBA優(yōu)化啊，然而并沒有什么水花。

數(shù)據(jù)量還在不斷增長(zhǎng)，到現(xiàn)在都上億啦，全量查詢統(tǒng)計(jì)根本出不來結(jié)果啊。

... ...

最終業(yè)務(wù)人員找到科技部門提需求要弄個(gè)BI系統(tǒng)給處理下。

對(duì)mongodb瞄了一大通，這就是個(gè)業(yè)務(wù)庫(kù)。那直接對(duì)接mongodb自然不行，速度慢不說，mongodb掛了，分析系統(tǒng)也癱了。自然就想到了使用中間庫(kù)，emm mysql oracle 倒是有，可以跑調(diào)度抽過來，但是速度依舊不快呢，還要花功夫優(yōu)化，性價(jià)比不高。

公司有自己的hadoop平臺(tái)，將數(shù)據(jù)抽過來再對(duì)接倒是可以，但是要花很大精力跑調(diào)度，而且這個(gè)數(shù)據(jù)庫(kù)不能隨意給這個(gè)業(yè)務(wù)部門提供，萬一玩掛了可就得不償失。假設(shè)有個(gè)具備離線數(shù)據(jù)存儲(chǔ)功能的BI工具，豈不美哉。

于是將市面上有離線數(shù)據(jù)存儲(chǔ)功能的BI工具翻了個(gè)遍。期望找到個(gè)性能好，可以支持大數(shù)據(jù)量數(shù)據(jù)分析的BI工具。

Tableau的hyper功能看起來OK，經(jīng)不起實(shí)際使用，數(shù)據(jù)量過了億，等了好久數(shù)據(jù)抽不好，pass；

其他某BI工具有mpp離線存儲(chǔ)，看起來很棒，還能橫向擴(kuò)展，不錯(cuò)。抱有最大期望的用，結(jié)果數(shù)據(jù)量一上億，直接崩了，崩了，pass；

另一個(gè)BI工具去看了看，咦，數(shù)據(jù)是放在vertica里面的......

后來，找到了FineBI的分布式計(jì)算引擎方案，拿的『定制的 Alluxio』作為分布式內(nèi)存存儲(chǔ)框架，內(nèi)存存儲(chǔ)有數(shù)據(jù)安全性的擔(dān)心，所以持久化層存儲(chǔ)用了HDFS。為了數(shù)據(jù)分析嘛，自然是列式存儲(chǔ)的。計(jì)算核心則以熟知的Spark，加上自研算法來處理的。使用熟知的zookeeper整合框架，并用于調(diào)度通信。

分布式嘛，橫向擴(kuò)展自然不在話下。而列式存儲(chǔ)、并行內(nèi)存計(jì)算、計(jì)算本地化加上高性能算法，在FineBI中數(shù)據(jù)展示速度超快。有意思的是其計(jì)算本地化的操作，能減少不必要的shuffle，節(jié)省數(shù)據(jù)傳輸?shù)南?#xff0c;提升數(shù)據(jù)計(jì)算速度。

?

以下記錄利用FineBI工具的系統(tǒng)建設(shè)過程。

一、需求分析

?

針對(duì)以上的需求，可以預(yù)估到，18年內(nèi)，常用分析預(yù)計(jì)最大數(shù)據(jù)量會(huì)達(dá)到4.7kw，不常用分析會(huì)達(dá)到3億到4億（包含淡季），數(shù)據(jù)總的體量最多會(huì)達(dá)到100G。后面的情況難以預(yù)估，就需要系統(tǒng)可橫向擴(kuò)展節(jié)點(diǎn)。

二、方案描述

1.系統(tǒng)架構(gòu)

根據(jù)官方推薦，將FineBI的web應(yīng)用端與數(shù)據(jù)存儲(chǔ)的分布式引擎放在一個(gè)機(jī)器上（處于安全考慮，也可以分開。這里不涉及太多部門使用，放一起即可），架構(gòu)如下所示。

?

?

架構(gòu)圖難以理解的話，可以看看靈魂畫手的杰作~

?

結(jié)合分布式引擎說明的技術(shù)原理，將各個(gè)機(jī)器再細(xì)分化各個(gè)組件的作用。

?

以上，將系統(tǒng)架構(gòu)規(guī)劃完成，即可具體完成系統(tǒng)。

2.完成從MongoDB取數(shù)

在使用BI工具對(duì)接MongoDB的時(shí)候，使用MongoDB的BI連接器。

感興趣可以看：MongoDB Download Center

方案原理：mongodb是非結(jié)構(gòu)的數(shù)據(jù)庫(kù)，而要想BI來連接，通過建模的方式取表，拆表，建模來分析。通過MONGODB CONNECTOR FOR BI連接器的方式，使用mysql的JDBC驅(qū)動(dòng)來獲取數(shù)據(jù)。

實(shí)現(xiàn)過程：

第一步：安裝MONGODB CONNECTOR FOR BI

從官網(wǎng)選擇版本：MongoDB Download Center

第二步：生成DRDL文件

mongodrdl是生成該文件的主命令。通過添加monogdb的相關(guān)參數(shù)來獲取其中的表生成drdl文件。從官方文檔上我們可以找到生成DRDL文件命令的方式：

1 mongodrdl --host myhost.example.net:27017 \ 2 --username dbUser \ 3 --password myPassword \ 4 --db reports \ 5 --authenticationDatabase admin \ 6 --out schema.drdl

范式說明：

• --host 是mongodb的ip+端口號(hào)，通常可為127.0.0.1:27017
• --username 是mongodb的用戶，需具備相關(guān)的數(shù)據(jù)權(quán)限
• --password 是username的密碼
• --db 是要生成DRDL的數(shù)據(jù)庫(kù)實(shí)例名
• --authenticationDatabase 是指定創(chuàng)建用戶的數(shù)據(jù)庫(kù)。即username創(chuàng)建時(shí)被指定到的數(shù)據(jù)庫(kù)名。
• --out 是DRDL輸出文件定義。值使用.drdl的文件即可。

第三步：啟動(dòng)連接器，連接上monogdb

啟動(dòng)連接器的主命令服務(wù)是mongosqld，由于mongodb開啟用戶認(rèn)證了（auth=true）。從官方文檔上可知，連接器的啟動(dòng)需要使用-auth參數(shù)，再使用auth參數(shù)的情況下，mysql驅(qū)動(dòng)來取數(shù)就需要SSLl加密認(rèn)證。

所以第一步需要配置mongosqld的SSL認(rèn)證；才能啟動(dòng)連接器來取數(shù)！！！！！！！！！！這一步神坑，也是踩了多少坑，才找到的解決辦法。

此處認(rèn)證關(guān)系是FineBi端的mysql連接與mongosqld的連接之間的SSL認(rèn)證。在認(rèn)證時(shí)，只需要配置單向SSL的認(rèn)證即可（mongosqld認(rèn)證FineBI的連接）。

（1）生成SSL認(rèn)證文件

SSL認(rèn)證文件通常采用openSSL來生成，先看看是否安裝了openSSL；執(zhí)行命令：

rpm -qa|grep -i openssl

如安裝了會(huì)返回信息，未安裝需要自行安裝OpenSSL。

采用以下命令來生成證書：（由于是測(cè)試，就直接自己生成證書，密鑰）

openssl req -newkey rsa:2048 -new -x509 -days 365 -nodes -out mongodb-cert.crt -keyout mongodb-cert.key

命令返回會(huì)讓填寫一些值，在后面填寫即可。

一般情況下，文件會(huì)生成到你所使用的linux用戶的要根目錄下：比如我用的root用戶，就到/root下面查找。

再使用以下命令，將key合到.pem的文件里。

cat mongodb-cert.key mongodb-cert.crt > mongodb.pem

將該文件移動(dòng)/etc/ssl下面用于驗(yàn)證使用：

mv mongodb.pem /etc/ssl

（2）啟動(dòng)連接器&SSL

再來看官方文檔，從所有的參數(shù)命令里面，找一找需要的參數(shù)；得出以下的范式，在bin目錄下啟動(dòng)即可。

mongosqld --auth--sslMode--sslPEMKeyFile --sslAllowInvalidCertificates--defaultAuthSource--mongo-uri--mongo-username--mongo-password --mongo-authenticationSource--schema

說明：

• --auth 是開啟用戶認(rèn)證的參數(shù)，默認(rèn)值是true
• --sslMode是開啟SSL的標(biāo)識(shí)，如開啟可以選擇值為“requireSSL”
• --sslPEMKeyFile是SSL認(rèn)證文件，一般為.pem結(jié)尾的文件；單向認(rèn)證的時(shí)候。
• --sslAllowInvalidCertificates
• --defaultAuthSource是mongosqld使用username指向mongodb的有權(quán)限的庫(kù)，默認(rèn)值是admin
• --mongo-uri是mongodb的host，一般為ip+端口號(hào)
• --mongo-usrname是drdl生成的用戶名
• --mongo-password是drdl生成的密碼
• --mongo-authenticationSource指定用戶的創(chuàng)建庫(kù)
• --schema是要連接的drdl文件。

注：一般還是要用nohup 的命令來生成，保證shell斷掉，連接器依然可用。

第四步：啟動(dòng)FineBI連接到連接器上

啟動(dòng)FineBI，打開FineBI>數(shù)據(jù)配置>數(shù)據(jù)連接：添加數(shù)據(jù)連接選擇mysql，配置如下：

連接名：mongodb

URL：jdbc：mysql：//127.0.0.1:3307/test?ssl-key=/etc/ssl/mongodb.pem

用戶名：

密碼：

注：URL：jdbc：mysql://ip+3307/dbname?ssl-key=xx.pem，后面ssl-key是ssl參數(shù)

點(diǎn)擊測(cè)試連接即可。

?

過程坑點(diǎn)：mongodb的BI連接器神坑，官網(wǎng)文檔不多，踩過了幾腳坑。

（1）mongosqld按ssl認(rèn)證開啟成功，打印也不錯(cuò)，但是BI連接的時(shí)候，還是拋錯(cuò)1043 SQLSTATE: 08S01 (ER_HANDSHAKE_ERROR):this server only allows SSL xxxxx該拋錯(cuò)是mysql拋出來的，握手不良的錯(cuò)誤。按道理SSL已經(jīng)開啟了，不應(yīng)該是這樣。后來查了mysql的文檔，mysql5.5以上的版本才支持SSL；更換新的driver驅(qū)動(dòng)，使用的是5.1.44完全沒問題的。

（2）BI連接的時(shí)候拋錯(cuò) handshake error: ERROR 1043 (08S01): error performing authentication: unable to authenticate conversation 0: unable to authenticate using mechanism "SCRAM-SHA-1": (AuthenticationFailed) Authentication failed.

該拋錯(cuò)的意思，使用“SCRAM-SHA-1”的認(rèn)證方式，沒有認(rèn)證成功。SCRAM-SHA-1是指用戶名密碼的方式，這里看是不是用戶名/密碼錯(cuò)誤，注意mongosqld啟動(dòng)時(shí)的使用的用戶名/密碼

（3）一定要開啟SSL認(rèn)證啊！！！

（4）期間有設(shè)計(jì)器無故死掉的情況，發(fā)現(xiàn)是由于內(nèi)存不足導(dǎo)致。記得空閑內(nèi)存要足夠！！

三.系統(tǒng)效果

1.數(shù)據(jù)更新

（1） 單個(gè)表先全量抽取，之后每天對(duì)單表依據(jù)時(shí)間戳，做增量增加。其中有錯(cuò)誤數(shù)據(jù)做增量刪除即可。

（2）有些內(nèi)部使用的實(shí)時(shí)性較高的表，設(shè)定每2小時(shí)更新一次，從上午9點(diǎn)到下午6點(diǎn)。直接從業(yè)務(wù)庫(kù)抽取其實(shí)是有風(fēng)險(xiǎn)的，當(dāng)時(shí)數(shù)據(jù)庫(kù)壓力大，抽取比較慢，因此這部分僅作為非重點(diǎn)用戶需求場(chǎng)景。

2.數(shù)據(jù)展示速度

做了一個(gè)簡(jiǎn)單的依據(jù)時(shí)間的group by，時(shí)間在1s之內(nèi)，翻頁(yè)速度也很快。

?

?

至此，對(duì)接mongodb完成，一個(gè)用戶可以隨便玩的系統(tǒng)就好了。即使偶爾mongodb發(fā)瘋修整，有離線數(shù)據(jù)在，也不擔(dān)心業(yè)務(wù)部門來嚷嚷了。而且速度超快，體驗(yàn)很棒~

最后

如果你也在尋求一個(gè)高性能展示分析、數(shù)據(jù)分析的BI工具的話，不妨嘗試下FineBI。

關(guān)注我，并轉(zhuǎn)發(fā)該文章，私信回復(fù)“BI”，即可獲得FineBI個(gè)人永久免費(fèi)版和demo體驗(yàn)地址~

歡迎關(guān)注我的公眾號(hào)“商業(yè)智能研究”，私信回復(fù)“資料包”，即可領(lǐng)取大數(shù)據(jù)、數(shù)據(jù)中臺(tái)、商業(yè)智能、數(shù)據(jù)倉(cāng)庫(kù)等6G精華資料！

總結(jié)

以上是生活随笔為你收集整理的数据库查询经常卡死？面对亿级大数据量，我是这么展示分析的的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。