如何使用Big SQL訪問大資料使用詳解

在大資料技術推廣、使用過程中，一個很大的挑戰就是如何使用目前企業使用者廣泛使用的標準 SQL 來訪問基於 Hadoop 平臺的大資料，使用企業原有應用來訪問大資料。

現在，使用大資料技術，通常使用 Hive、Pig 及 JAVA 程式來訪問大資料，只能支援標準 SQL 的子集，需要使用者學習新的程式設計語言，改寫企業原有的應用，為了解決上述問題，IBM 推出了 Big SQL，它使用標準的 SQL 來訪問基於 H adoop 平臺的 InfoSphere BigInsights，並提供標準的 JDBC、ODBC 介面，可以使廣大熟悉 SQL 的使用者直接存取大資料，而且，從性能優化角度，Big SQL 提供本地查詢及 Map Reduce 兩種運行模式，對 于小資料集，採用本地查詢方式減少 Map Reduce 的運行開銷，提高執行效率，對大規模資料，自動採用 Map Reduce 方式高效運行。 本文具體介紹了 Big SQL 的體系結構，如何使用 Big SQL 創建、裝載及查詢 Hive、Hbase 表，以及查詢優化的各種方法，希望可以使讀者對如何使用 Big SQL 訪問大資料有一個比較全面的瞭解。

我們知道，隨著資料規模爆炸式的增長、資料種類不斷翻新以及資料處理速度不斷加快，大資料處理技術及平臺應運而生。 大資料處理平臺為我們更好地綜合利用結構化、半結構化、非結構化資料、流資料以及海量資料進行分析提供了堅實的基礎。 大資料處理平臺主要包括用於靜態大資料分析的 Hadoop 平臺以及用於流資料處理的流處理平臺。 Hadoop 大資料處理平臺以 Apache Hadoop 為基礎，並存在不同的發行版本本。 IBM InfoSphere BigInsights 是 IBM 的 Hadoop 大資料處理平臺，它以 Apache Hadoop 為基礎，並增加了大資料處理平臺的企業特性，包括集成的安裝、管理、開發工具，NameNode、Job Trac ker 等元件的高可用，安全性增強，性能的增強，Map/Reduce 處理框架的增強，使用標準的 SQL 訪問大資料以及與現有基礎設施和大資料流程計算技術無縫的集成等，另外，IBM InfoSphere BigInsights 還提供了文本分析、機器學習、資料採礦、視覺化分析等大資料分析能力。 目前，Hadoop 大資料處理技術在互聯網企業已經得到了廣泛的應用，銀行、電信及政府等行業使用者也開始逐漸採用大資料處理技術。 從大資料應用場景角度來看，資料倉儲增強是 Hadoop 大資料應用處理的一個非常重要的使用場景。 我們知道，資料倉儲系統通常會採用高端伺服器、高端存儲來提供海量資料存儲及高效資料處理能力，隨著大資料時代到來，資料規模呈爆炸式增長，資料倉儲存儲及處理成本也會極具增長，為了降低成本， 我們可以將資料倉儲中的海量歷史資料卸載到 Hadoop 平臺，利用 Hadoop 平臺低廉的成本及 Map/Reduce 並行處理能力提供歷史資料的查詢、分析能力，並利用傳統的資料倉儲系統高效處理線上資料。

在大資料技術推廣、使用過程中，一個很大的挑戰就是如何使用目前企業使用者廣泛使用的標準 SQL 來訪問基於 Hadoop 平臺的大資料，使用企業原有應用來訪問大資料，特別是資料倉儲增強使用場景，我們處理的資料還主要是海量的結構化資料 ，我們仍然需要使用標準的 SQL 並使用企業原有的程式來訪問大資料。 目前，使用大資料技術，通常使用 Hive、Pig 及 JAVA 程式來訪問 Hadoop 大資料，需要使用者學習新的程式設計語言，改寫企業原有的應用，儘管 Hive QL 提供了 SQL 介面，但它只支援標準 SQL 的子集，不能完全滿足應用的需求， 為了解決上述問題，IBM 推出了 Big SQL，它使用標準的 SQL 來訪問基於 Hadoop 平臺的 InfoSphere BigInsights，並提供標準的 JDBC、ODBC 介面，可以使廣大熟悉 SQL 的使用者直接存取大資料，而且， 從性能優化角度，Big SQL 提供本地查詢及 Map Reduce 兩種運行模式，對小規模資料，採用本地查詢方式減少 Map Reduce 的運行開銷，提高執行效率，對大規模資料，自動採用 Map Reduce 方式高效運行。

Big SQL 概述

Big SQL 是 IBM 基於 Hadoop 平臺 InfoSphere BigInsights 的 SQL 介面，它提供標準的 ANSI SQL 92 標準，允許 SQL 開發人員能夠輕鬆地掌握對 Hadoop 管理的資料的查詢。 它使資料管理員能夠為 Hive、HBase 或他們的 BigInsights 分散式檔案系統中存儲的資料創建新表。 此外，LOAD 命令使管理員能夠在 Big SQL 表中填入來自各種來源的資料。 此外，Big SQL 提供標準的 JDBC 和 ODBC 驅動程式，可以使許多現有的工具使用 Big SQL 查詢分散式資料。 這些都解決了 Hive 存在的一些局限，目前，Hive 僅支援 ANSI SQL 的子集，它不支援子查詢、視窗函數，僅支援 ANSI JOIN 語法，對資料類型的支援也有局限，目前不支援 Varchar、Decimal 資料類型，另外，它對標 准的 JDBC 及 ODBC 驅動程式的支援也有局限性。 通過使用 Big SQL，可以使廣大熟悉 SQL 的使用者直接存取大資料。

從性能優化角度，Big SQL 提供 Big SQL 伺服器本地查詢及 Map Reduce 兩種運行模式，對於小資料集或獲取與一個特定 HBase 行鍵關聯的資料的查詢，通常會在單個節點上循序執行，減少 Map Reduce 的運行開銷，提高 執行效率，對大規模資料，自動採用 Map Reduce 方式高效運行。

除此之外，Big SQL 還針對 HBase 處理提供了增強。 通過使用 Big SQL，使用者不需要使用像 Hive 那樣複雜的語句來創建 HBase 表，可以支援創建組合行健、組合欄位，可以為 HBase 表創建輔助索引，可以使用 LOAD、insert 語句為 HBase 表裝載資料，可以指定壓縮方法等 。

Big SQL 體系結構

如下圖所示，Big SQL 同 Hive 共用元資料定義資訊，它通過 Hcatalog 訪問 Hive metastore，在 InfoSphere BigInsights 中，Hive metastore 預設採用 Derby 資料庫。 因此，Big SQL 中的定義的表可以和 Hive 中定義的表互相訪問，預設情況下，Big SQL 中創建的表即使 Hive 表。

外部應用通過標準的 JDBC/ODBC 驅動程式訪問 Big SQL，Big SQL 的 SQL 查詢引擎負責對輸入的 SQL 語句進行編譯，生成執行計畫。 它可以通過改寫相關的 SQL 語句來提高查詢性能，如將子查詢改寫成表連接操作，並可以通過 SQL 優化提示及配置選項，改變數據訪問策略。 根據查詢的性質、資料量和其他因素，Big SQL 可以使用 Hadoop 的 MapReduce 框架並行處理各種查詢任務，或者在單個節點上的 Big SQL 伺服器上本地執行您的查詢， 也可以部分查詢工作在 Hadoop 的 MapReduc e 框架上完成，部分查詢工作在 Big SQL 伺服器上完成。

Big SQL 通過 Hive 儲存引擎來讀寫資料。 SQL 查詢引擎可以根據不同的資料類型，選擇不同的存儲管理程式裝載不同的資料處理類來讀取資料。 Big SQL 支援 Delimited files、Sequence files、RC files、Custom、Partitioned tables 等多種資料格式，同時支援 Hive SerDe 提供的各種編碼方式，包括 Text、Bin ary、Avro、Thrift、JSON、Custom。 Big SQL 還提供了自己專有的 HBase 存儲管理程式，針對 HBase 處理提供了很多增強功能，包括支援字元或二進位不同的編碼方式、支援組合行健及組合欄位、可以為 HBase 表創建輔助索引等。

圖 1. Big SQL 架構

使用 Big SQL

啟動 Big SQL 服務

我們需要啟動 Big SQL 服務來訪問 Big SQL。 如下所示，我們以管理員身份 (biadmin) 登錄系統，並使用如下命令啟動、停止及查詢 Big SQL 服務：

清單 1. 啟動 Big SQL 服務

biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGSQL_HOME/bin/bigsql stop BigSQL pid 2850313 stopped. biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGSQL_HOME/bin/bigsql start BigSQL running, pid 2893219. biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGSQL_HOME/bin/bigsql status BigSQL server is running (pid 2893219) biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGSQL_HOME/bin/bigsql level IBM BigInsights Big SQL Server Version number is "V2.1.0.1 and level identifier is "20130821".

我們也可以使用 BigInsights 本身的集成管理命令來啟動、停止及查詢 Big SQL 服務，如下所示：

清單 2. 使用集成管理命令啟動 Big SQL 服務

biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGINSIGHTS_HOME/bin/stop.sh bigsql biadmin@imtebi1:/opt/ibm/ biginsights/bin> $BIGINSIGHTS_HOME/bin/start.sh bigsql biadmin@imtebi1:/opt/ibm/biginsights/bin> $ BIGINSIGHTS_HOME/bin/status.sh bigsql

我們還可以使用 BigInsights 本身的集成管理命令來啟動 BigInsights 的所有服務，包括 Big SQL 服務，如下所示：

清單 3. 啟動 BigInsights 的所有服務

biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGINSIGHTS_HOME/bin/stop-all.sh biadmin@imtebi1:/opt/ibm/ biginsights/bin> $BIGINSIGHTS_HOME/bin/start-all.sh

另外，我們還可以通過 BigInsights 提供的 Web Console 管理工具來啟動、停止及查詢 Big SQL 服務，如下所示：我們通過執行 HTTP://172.16.42.202:8080/ 來啟動 BigInsights Con sole，並選擇 Cluster Status 功能表。

圖 2. 使用 Web Console 管理 Big SQL 服務

訪問 Big SQL

BigInsights 提供多種工具訪問 Big SQL，包括：

JSqsh 命令列方式 BigInsights console 管理工具 Big SQL Eclipse plugin 通用的 JDBC/ODBC 管理工具 JDBC/ODBC 應用程式

使用命令列訪問 Big SQL

BigInsights 提供一個開源 JDBC 命令列工具 JSqsh 來訪問 Big SQL，我們可以執行如下命令來啟動 jsqsh 並執行 SQL 查詢 ，如下所示：

清單 4. 啟動 jsqsh 並執行 SQL 查詢

biadmin@imtebi1:/opt/ibm/biginsights/bigsql>$BIGSQL_HOME/bin/jsqsh --user=biadmin --password=password --server localhost --port 7052 --driver=bigsql JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray Type \help for availab le help topics. Using JLine. [localhost] [biadmin] 1> select * from hbase_staff;

我們也可以使用如下命令來運行 Big SQL 腳本，如下所示：

清單 5. 運行 Big SQL 腳本

biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> $BIGSQL_HOME/bin/jsqsh --user=biadmin -- password=password --server localhost --port 7052 --driver=bigsql -i GOSALESDW_starSchemaJoin.sql

另外，我們還可以使用 jsqsh – setup 命令定義自己的連接環境，如下所示：

清單 6. 定義自己的連接環境

biadmin@imtebi1:/opt/ibm/biginsights/bigsql/bin> ./jsqsh --setup

圖 3. 使用 jsqsh – setup 命令定義連接環境

這樣，我們可以使用定義的連接串進入自己的連接環境，如下所示：

清單 7. 連接自己的環境

biadmin@imtebi1:/opt/ibm/biginsights/bigsql/bin> ./jsqsh mybigsql

使用 Web Console 訪問 Big SQL

我們可以通過 BigInsights 提供的 Web Console 管理工具來訪問 Big SQL，如下所示：我們通過執行 HTTP://172.16.42.202:8080/ 來啟動 BigInsights Console，並選擇 Quic k Links 中的 Run Big SQL Queries 功能表來運行 Big SQL 查詢語句。

圖 4. 使用 Web Console 運行 Big SQL 查詢

使用 Eclipse 工具訪問 Big SQL

首先，通過執行 HTTP://172.16.42.202:8080/ 來啟動 BigInsights Console，並選擇 Quick Links 中的 Download the Big SQL Client drivers 來下載 Big SQL client drivers，然後通過執行 /usr/local/eclipse/eclipse/eclipse 命令打開 BigInsights Eclipse 開發工具，選擇 Windows 功能表下的 Preferences 功能表 ，通過選擇 Data Management 選項下的 Connectivity->Driver Definitions 來設置 Big SQL Driver，如下所示：

圖 5. 為 Eclipse 工具設置 Big SQL Driver

之後，在 BigInsights Eclipse 開發工具中，打開 Database Development Perspective，在 Data Source Explorer 視圖中，選擇 Database Connections 選項，點 擊右鍵創建 Big SQL JDBC 資料庫連接概要，如下圖所示：

圖 6. 為 Eclipse 工具創建 Big SQL JDBC 資料庫連接概要

之後，我們可以通過創建 BigInsights Project 以及創建 SQL Script 來創建並運行 GOSALESDW_Counts.sql 語句，如下所示：

圖 7. 使用 Eclipse 工具運行 Big SQL 查詢

使用 Db Visualizer 等 JDBC/ODBC 工具訪問 Big SQL

Big SQL 提供標準的 JDBC/ODBC 驅動程式，允許所有支援標準 JDBC/ODBC 的工具訪問 BigInsights Hadoop 大資料，這也是 Big SQL 相比 Hive 等大資料查詢語言的優勢之一。 我們以常見的 Db Visualizer 工具為例，首先，我們通過選擇 Tools 功能表下的 Driver Manager 功能表來定義 Big SQL Driver，如下所示：

圖 8. 為 Db Visualizer 定義 Big SQL Driver

之後，在 Database 標籤下，選擇 Connections 選項，點擊右鍵創建 BigSQL 資料庫連接，如下所示，連接資料庫，並選擇 File 功能表下的 New SQL Commander 功能表創建並運行 Big SQL 查詢。

圖 9. 使用 Db Visualizer 運行 Big SQL 查詢