基于SQL 模版的大數(shù)據(jù)批處理設(shè)計(jì)與實(shí)現(xiàn)

曾姣艷，高宋俤，曾美艷

（1.福州外語外貿(mào)學(xué)院 大數(shù)據(jù)學(xué)院，福建 福州 350003；2.福州悟理妙信息科技有限公司，

福建 福州 350003；3.郴州職業(yè)技術(shù)學(xué)院，湖南 郴州 423000）

本文選擇了使用ANTLR 解析的Hive、Spark SQL、Presto 來搭建基于SQL 模版的大數(shù)據(jù)批處理平臺(tái)，用SQL 語言作為調(diào)用接口。使用ANTLR 解析工具給重新定制SQL 語句以及對(duì)SQL 語句的二次解析帶來了極大的便利。上游使用SQL 實(shí)現(xiàn)ETL，下游使用SQL調(diào)用計(jì)算引擎來設(shè)計(jì)與實(shí)現(xiàn)大數(shù)據(jù)平臺(tái)?；赟QL 語句的規(guī)則，業(yè)務(wù)方通過編寫特定的注釋標(biāo)識(shí)符選擇底層計(jì)算引擎。大數(shù)據(jù)平臺(tái)負(fù)責(zé)解析SQL 模版以及注釋標(biāo)識(shí)符，從上游業(yè)務(wù)庫(kù)抽取業(yè)務(wù)數(shù)據(jù)（ETL），通過路由轉(zhuǎn)發(fā)到底層計(jì)算引擎，從而完成數(shù)據(jù)分析及數(shù)據(jù)質(zhì)量監(jiān)控等各項(xiàng)功能。

1 相關(guān)工作

Apache Hive?數(shù)據(jù)倉(cāng)庫(kù)軟件支持使用SQL 讀取、寫入和管理分布存儲(chǔ)中的大型數(shù)據(jù)集，并提供了一個(gè)命令行工具和JDBC 驅(qū)動(dòng)程序，方便用戶連接到Hive［1］，可以在Hadoop 的Mapreduce、Spark、Tez 計(jì)算引擎之上運(yùn)行。Hive 將SQL 語句轉(zhuǎn)化成底層計(jì)算引擎可識(shí)別的程序，極大地降低了開發(fā)難度，減少了代碼量。

Presto 是一個(gè)開源的分布式SQL 查詢引擎，適用于交互式分析查詢，數(shù)據(jù)量支持GB 到PB 字節(jié)。Presto 以分析師的需求作為目標(biāo)，期望響應(yīng)時(shí)間小于1 s 到幾分鐘。Presto 終結(jié)了數(shù)據(jù)分析的兩難選擇：要么使用速度快的昂貴的商業(yè)方案，要么使用消耗大量硬件的慢速的“免費(fèi)”方案［2］。Presto 和Hive 都是由Facebook 公司開發(fā)的，兩者之間有很多的共性。

Spark SQL 是Spark 中的一個(gè)模塊，允許用戶運(yùn)行SQL/HQL 查詢，可以使用Spark SQL 處理結(jié)構(gòu)化和半結(jié)構(gòu)化數(shù)據(jù)［3］。Spark SQL 與Hive 相似，支持SQL 讀取、寫入和管理分布存儲(chǔ)中的大型數(shù)據(jù)集，但Hive具備切換執(zhí)行引擎的能力。

ANTLR 是一個(gè)強(qiáng)大的解析器生成器，可以用來讀取、處理、執(zhí)行或翻譯結(jié)構(gòu)化文本或二進(jìn)制文件。在學(xué)術(shù)界和工業(yè)界，ANTLR 廣泛用于構(gòu)建各種語言、工具和框架。Hive 和Pig 的語言以及用于Hadoop 的數(shù)據(jù)倉(cāng)庫(kù)和分析系統(tǒng)都使用ANT‐LR。Lex Machina 使用ANTLR 從法律文本中提取信息。Oracle 在SQL Developer IDE 及其遷移工具中使用ANTLR。NetBeans IDE 使用ANTLR解析C++。Hibernate 對(duì)象-關(guān)系映射框架中的HQL 語言是用ANTLR 構(gòu)建的［4］。Presto 和Spark SQL同樣使用ANTLR作為SQL語句的解析工具。

在丁巖等人［5］提出的統(tǒng)一SQL 引擎研究與設(shè)計(jì)的方案中，給出了上層SQL 代理層、中層SQL 統(tǒng)一服務(wù)層以及下層計(jì)算引擎和存儲(chǔ)層的設(shè)計(jì)思路。核心的SQL 統(tǒng)一服務(wù)層完成了對(duì)各大組件SQL 語句差異的屏蔽，實(shí)現(xiàn)了業(yè)務(wù)方調(diào)用的SQL 的統(tǒng)一化。但因適配不同組件SQL 語句的差異性，使得實(shí)現(xiàn)成本較高、難度較大。本設(shè)計(jì)與文獻(xiàn)［5］的目標(biāo)相近，都是為了實(shí)現(xiàn)通過對(duì)外提供SQL 接口的大數(shù)據(jù)平臺(tái)，降低平臺(tái)的使用難度，但設(shè)計(jì)方法以及組件的選型是有差異的：采用SQL 模版和基于ANTLR 解析的SQL 計(jì)算引擎組件來搭建大數(shù)據(jù)平臺(tái)。SQL 語句直接使用底層自身引擎提供的組件（因?yàn)锳NTLR 解析的Hive、Spark SQL、Presto 差異極小）降低對(duì)SQL 語句的侵入性，利用ANTLR對(duì)3 個(gè)組件使用的SQL 進(jìn)行二次解析和定制化開發(fā)，幫助平臺(tái)解決業(yè)務(wù)層面上的數(shù)據(jù)權(quán)限及因SQL語句帶來的各種問題。

2 設(shè)計(jì)與實(shí)現(xiàn)

大數(shù)據(jù)業(yè)務(wù)的處理平臺(tái)通常有3類。第1類是實(shí)時(shí)數(shù)據(jù)處理：業(yè)務(wù)指標(biāo)需要在秒甚至是毫秒級(jí)別內(nèi)得出結(jié)果；第2 類是批量數(shù)據(jù)處理：此類數(shù)據(jù)比較大，早期的Hadoop 就是針對(duì)此類業(yè)務(wù)開發(fā)出來的，用于處理大批量的離線數(shù)據(jù)；第3 類是面向數(shù)據(jù)分析師的即席查詢業(yè)務(wù)：數(shù)據(jù)分析師可以在平臺(tái)上直接輸入SQL 語句，在秒級(jí)下得到分析結(jié)果，但因一次性處理數(shù)據(jù)量也較大，亦可歸納為大數(shù)據(jù)批處理一類。

調(diào)質(zhì)效果對(duì)于沉性水產(chǎn)飼料水中穩(wěn)定性的研究也表明了在生產(chǎn)膨化沉性料的過程中，調(diào)質(zhì)效果的關(guān)鍵作用。Eugenio Bortone等[8]對(duì)調(diào)質(zhì)時(shí)間和機(jī)械能輸入對(duì)膨化蝦料水中穩(wěn)定性帶來的影響，進(jìn)行了實(shí)驗(yàn)研究。分別對(duì)4組實(shí)驗(yàn)(PPC、PPO、LSME和MSME)進(jìn)行了對(duì)比研究，實(shí)驗(yàn)結(jié)果如圖4所示。

2.1 架構(gòu)設(shè)計(jì)

本平臺(tái)（如圖1）底層計(jì)算基于Hive、Spark‐SQL、Presto，上層使用SQL語言作為調(diào)用接口的大數(shù)據(jù)批處理平臺(tái)。雖然Hive 并不是真正意義上的計(jì)算引擎，其自身依賴底層（類似Hadoop MapRe‐duce 或Tez 計(jì)算引擎），但為了方便描述，將Hive、Spark SQL 和Presto當(dāng)作同一個(gè)層級(jí)的計(jì)算引擎看待。業(yè)務(wù)方根據(jù)自身計(jì)算任務(wù)的效率要求，通過SQL標(biāo)記的方式，自主選擇計(jì)算引擎進(jìn)行計(jì)算。大數(shù)據(jù)平臺(tái)語句解析層根據(jù)標(biāo)記不同，選擇計(jì)算引擎完成SQL語句的分發(fā)。

圖1 大數(shù)據(jù)平臺(tái)架構(gòu)

大數(shù)據(jù)平臺(tái)的數(shù)據(jù)一般來源于兩部分：一是業(yè)務(wù)數(shù)據(jù)，一是用戶的行為數(shù)據(jù)。用戶的行為數(shù)據(jù)通常會(huì)在生成數(shù)據(jù)時(shí)，使用Flume等工具直接寫入到大數(shù)據(jù)平臺(tái)數(shù)據(jù)倉(cāng)庫(kù)中，而業(yè)務(wù)數(shù)據(jù)需要從業(yè)務(wù)方的數(shù)據(jù)庫(kù)中同步抽取到數(shù)據(jù)倉(cāng)庫(kù)中［6］。大數(shù)據(jù)開源工具Sqoop［7］專門用于業(yè)務(wù)庫(kù)與數(shù)據(jù)倉(cāng)庫(kù)之間的數(shù)據(jù)同步遷移，然而重新使用一款新的組件會(huì)提高額外的學(xué)習(xí)成本，增加系統(tǒng)的復(fù)雜度，也不易于開發(fā)與維護(hù)。為了簡(jiǎn)化平臺(tái)的復(fù)雜度，平臺(tái)中引入了基于SQL 語句的ETL 方法，實(shí)現(xiàn)從不同的業(yè)務(wù)數(shù)據(jù)源（Mysql、MongoDB）拉取、轉(zhuǎn)換、加載到數(shù)據(jù)倉(cāng)庫(kù)的功能。

為保證業(yè)務(wù)的運(yùn)行結(jié)果符合預(yù)期，平臺(tái)還需要實(shí)現(xiàn)數(shù)據(jù)質(zhì)量監(jiān)控的功能。其實(shí)現(xiàn)方式同樣使用SQL 語句，由業(yè)務(wù)方自行編寫，平臺(tái)根據(jù)SQL 運(yùn)行的結(jié)果來判斷是否發(fā)送告警信息，各業(yè)務(wù)方需要提供回調(diào)接口來接收平臺(tái)的報(bào)警信息。

業(yè)務(wù)方各作業(yè)的工作方式一般是使用DAG圖來組織調(diào)用的，DAG 的每一個(gè)節(jié)點(diǎn)都代表不同的、獨(dú)立的SQL 語句作業(yè)。為了滿足業(yè)務(wù)方各作業(yè)有效運(yùn)作，平臺(tái)方引入了Airflow［8］作為作業(yè)調(diào)度器。選用Airflow 的目的是基于Airflow 靈活、可編程式的特點(diǎn)，方便平臺(tái)開發(fā)者修改DAG 圖的組織樣式、配置參數(shù)，實(shí)現(xiàn)未來可視化式的DAG 圖等功能。

2.2 SQL模版設(shè)計(jì)

在實(shí)際業(yè)務(wù)中，有大量的場(chǎng)景需要定時(shí)、定量完成計(jì)算。業(yè)務(wù)方需要在作業(yè)提交之前提供SQL語句模版，先行存放在平臺(tái)規(guī)約好的分布式存儲(chǔ)文件中。SQL語句模版中包含可替換的變量，變量由平臺(tái)解析層完成解析、具體賦值。賦值后的SQL語句交給平臺(tái)路由模塊，由路由模塊完成路由轉(zhuǎn)發(fā)到底層計(jì)算引擎的工作。

平臺(tái)中SQL 模版變量用占位符${變量名}來表示，其定義為兩種類型變量：業(yè)務(wù)方自定義的變量和時(shí)間變量。

自定義的變量主要是指業(yè)務(wù)方SQL語句中易變的信息值，比如服務(wù)器地址、數(shù)據(jù)庫(kù)名、數(shù)據(jù)版本號(hào)等。如SQL語句中：

CREATE OR REPLACE TEMPORARY VIEW video_view USING com.stratio.datasource.mongodb

OPTIONS（host '${content_center_url}'，data‐base'${content_center_db}'，collection'video'）;

語句的含義是業(yè)務(wù)方從MongoDB中將數(shù)據(jù)拉取到數(shù)據(jù)倉(cāng)庫(kù)。占位符${content_center_db}和${content_center_url}綁定了具體服務(wù)器的地址、數(shù)據(jù)庫(kù)名信息。這類變量要求業(yè)務(wù)方在提交Airflow JOB的時(shí)候一并上傳。

時(shí)間變量包含系統(tǒng)時(shí)間和時(shí)間周期數(shù)據(jù)。在業(yè)務(wù)方實(shí)施具體數(shù)據(jù)分析時(shí)，通常會(huì)根據(jù)當(dāng)下時(shí)間選取一定時(shí)間范圍內(nèi)的數(shù)據(jù)，比如昨日日活、7 日留存、30 日留存等這類的業(yè)務(wù)指標(biāo)，都有時(shí)間范圍和啟動(dòng)時(shí)間點(diǎn)的要求。系統(tǒng)時(shí)間通常無法由業(yè)務(wù)方在提交job 的時(shí)候定義，業(yè)務(wù)方只能定義時(shí)間周期數(shù)。時(shí)間變量使用占位符${time.yyyyMMddHHmm}來表示當(dāng)下系統(tǒng)時(shí)間，時(shí)間粒度由業(yè)務(wù)方自行決定，平臺(tái)方可支持的最小粒度為分鐘。同時(shí)，時(shí)間變量占位符還提供了基本加減運(yùn)算，增加時(shí)間定義的靈活性，方便業(yè)務(wù)使用。下面SQL 語句表示拉取最近半個(gè)月數(shù)據(jù)進(jìn)行計(jì)算：

其中占位符${time.yyyyMMdd-15d}表示當(dāng)下日期的前15 d 的時(shí)間，倘若要表示昨日的時(shí)間，可以用占位符${time.yyyyMMdd-1d}來表示。

2.3 支持業(yè)務(wù)方的數(shù)據(jù)拉取

大數(shù)據(jù)分析的業(yè)務(wù)數(shù)據(jù)通常存放在各自業(yè)務(wù)庫(kù)中，為了結(jié)合用戶的行為數(shù)據(jù)進(jìn)行分析，必須從業(yè)務(wù)庫(kù)中拉取數(shù)據(jù)進(jìn)入數(shù)據(jù)倉(cāng)庫(kù)中。平臺(tái)使用SQL 語句模版完成ETL 的任務(wù)，目前平臺(tái)的設(shè)計(jì)主要支持Mysql和MongoDB這兩種業(yè)務(wù)庫(kù)。

從業(yè)務(wù)庫(kù)拉取數(shù)據(jù)分成兩步，同一個(gè)作業(yè)中的語句中間用“；”隔開。

步驟1：定義視圖，如下：

步驟2：從視圖拉取數(shù)據(jù)，并寫入到數(shù)據(jù)倉(cāng)庫(kù)中。下列語句展示了從視圖tmp_dim_region 中拉取數(shù)據(jù)，并插入到數(shù)據(jù)倉(cāng)庫(kù)中的具體實(shí)現(xiàn)過程。

同理，還可以從MongoDB 中拉取數(shù)據(jù)寫入到數(shù)據(jù)倉(cāng)庫(kù)中，也是兩個(gè)步驟，語句寫法相似。

CREATE OR REPLACE TEMPORARY VIEW article_view USING com.stratio.datasource.mongodb

2.4 基于ANTLR解析與定制SQL語句

企業(yè)內(nèi)部各大業(yè)務(wù)的數(shù)據(jù)統(tǒng)一寫入到一個(gè)數(shù)據(jù)倉(cāng)庫(kù)中，平臺(tái)方并沒有要求業(yè)務(wù)方提交的SQL語句需要進(jìn)行數(shù)據(jù)訪問范圍的限制，如果語句被路由直接轉(zhuǎn)發(fā)到計(jì)算引擎上，各大業(yè)務(wù)就可以直接訪問整個(gè)數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)（整個(gè)公司的數(shù)據(jù)）。而在實(shí)際項(xiàng)目運(yùn)行過程中，業(yè)務(wù)方想跨業(yè)務(wù)訪問其他業(yè)務(wù)的數(shù)據(jù)，通常是不被允許的。業(yè)務(wù)間數(shù)據(jù)互通，需要由業(yè)務(wù)方授權(quán)或者上層決策者同意才能訪問，技術(shù)上需對(duì)數(shù)據(jù)權(quán)限進(jìn)行規(guī)約限定。而數(shù)據(jù)的訪問限制，不應(yīng)該在業(yè)務(wù)方的SQL 語句中體現(xiàn)，底層平臺(tái)數(shù)據(jù)對(duì)業(yè)務(wù)方是透明的。為了解決SQL 語句數(shù)據(jù)訪問權(quán)限的問題，在大數(shù)據(jù)平臺(tái)中增加SQL二次解析功能，對(duì)業(yè)務(wù)方提交的SQL 語句進(jìn)一步解析、定制組裝來限定數(shù)據(jù)范圍，再由路由模塊交給計(jì)算引擎去處理。

在數(shù)據(jù)倉(cāng)庫(kù)中，各大業(yè)務(wù)的行為數(shù)據(jù)通常會(huì)按照同一種schema 存入到數(shù)據(jù)倉(cāng)庫(kù)行為表（events）中，使用業(yè)務(wù)的appid 來區(qū)分不同業(yè)務(wù)記錄。業(yè)務(wù)方提供的語句通常形如SELECT* FROM `events`WHERE `date`='20200101'，如果平臺(tái)沒有加入權(quán)限限制，則會(huì)拉取出整個(gè)數(shù)據(jù)倉(cāng)庫(kù)中所有業(yè)務(wù)的'20200101'這一天的數(shù)據(jù)。這不但不是業(yè)務(wù)方想要的結(jié)果，反而會(huì)給大數(shù)據(jù)平臺(tái)帶來IO 上的壓力。大數(shù)據(jù)平臺(tái)需要通過對(duì)SQL 語句進(jìn)行二次解析、重新定制修改語句來解決數(shù)據(jù)權(quán)限方面的問題。但是，并不能簡(jiǎn)單地在末尾加上條件限制，如針對(duì)語句SELECT * FROM `events` WHERE `date`='20200101'LIMIT 10做限定，簡(jiǎn)單地在語句末尾加上WHERE`appid`='000001'，明顯不符合SQL語句規(guī)范。

所以，利用ANTLR 對(duì)原業(yè)務(wù)語句進(jìn)行語法樹的解析（如圖2），需要在querySpecification節(jié)點(diǎn)下，WHERE 關(guān)鍵字后面，添加合適的限制條件才能生成符合標(biāo)準(zhǔn)的SQL 語句。平臺(tái)通過ANTLR 提供的接口，完成數(shù)據(jù)權(quán)限的設(shè)置，圖3 給出了加上條件限定后的正確語法解析樹。