使用Sqoop從MySQL導入數據到Hive和HBase 及近期感悟

基礎環境

sqoop：sqoop-1.4.5+cdh5.3.6+78，hive：hive-0.13.1+cdh5.3.6+397，hbase：hbase-0.98.6+cdh5.3.6+115

Sqool和Hive、HBase簡介

Sqoop

Sqoop是一個用來將Hadoop和關系型數據庫中的數據相互轉移的開源工具，可以將一個關系型數據庫（例如 ： MySQL ,Oracle ,Postgres等）中的數據導進到Hadoop的HDFS中，也可以將HDFS的數據導進到關系型數據庫中。

Hive

Hive的內置數據類型可以分為兩大類：(1)、基礎數據類型；(2)、復雜數據類型。其中，基礎數據類型包括：TINYINT、 SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、STRING、BINARY、TIMESTAMP、 DECIMAL、CHAR、VARCHAR、DATE。下面的表格列出這些基礎類型所占的字節以及從什么版本開始支持這些類型。

復雜類型包括ARRAY、MAP、STRUCT、UNION，這些復雜類型是由基礎類型組成的。

HBase

HBase作為面向列的數據庫運行在HDFS之上，HDFS缺乏隨即讀寫操作，HBase正是為此而出現。HBase以Google BigTable為藍本，以鍵值對的形式存儲。項目的目標就是快速在主機內數十億行數據中定位所需的數據并訪問它。HBase是一個數據庫，一個 NoSql的數據庫，像其他數據庫一樣提供隨即讀寫功能，Hadoop不能滿足實時需要，HBase正可以滿足。如果你需要實時訪問一些數據，就把它存入 HBase。

你可以用Hive作為靜態數據倉庫，HBase作為數據存儲，放那些進行一些會改變的數據。在Hive中，普通表是存儲在HDFS中，而你可以通過創建EXTERNAL TABLE外表來指定數據存儲位置，可以是系統目錄，也可以是ElasticSearch，還可以是HBase。在使用Sqoop從Mysql導出數據入 Hadoop時，就需要考慮是直接入Hive（此時是普通表），還是導入數據到HBase，Sqoop同時支持導入這兩種導入。

測試Sqoop

1. #測試MySQL連接
2. [hdfs@node196 bin]$ sqoop list-databases --connect jdbc:mysql://192.168.180.11/angel --username anqi –P
3. 
   
4. #檢驗SQL語句
5. [hdfs@node196 bin]$ sqoop eval --connect jdbc:mysql://192.168.180.11/angel --username anqi --password anqi_mima \
6. --query "SELECT xi.*, jing.name,wang.latitude,wang.longitude \
7. FROM xi ,jing, wang \
8. WHERE xi.id=jing.foreignId AND wang.id=xi.id AND xi.date>='2015-09-01' AND xi.date<='2015-10-01'"

以上Sqoop語句執行過后，可以確認Sqoop運行正常，Sqoop連接MySQL正常。

使用Sqoop從MySQL導入數據到Hive

使用復雜SQL

1. #從MySQL導入數據到Hive
2. [hdfs@node196 bin]$ sqoop eval --connect jdbc:mysql://192.168.180.11/angel --username anqi --password anqi_mima \
3. --query "SELECT xi.*, jing.name,wang.latitude,wang.longitude \
4. FROM xi ,jing, wang \
5. WHERE xi.id=jing.foreignId AND wang.id=xi.id AND xi.date>='2015-09-01' AND xi.date<='2015-10-01' \
6. AND \$CONDITIONS" \
7. --split-by date --hive-import -m 5 \
8. --target-dir /user/hive/warehouse/anqi_wang \
9. --hive-table anqi_wang

以下簡要列舉了Sqoop的執行過程:

1. BoundingValsQuery: SELECT MIN(date), MAX(date) FROM (SELECT xi.*, jing.name,wang.latitude,wang.longitude FROM xi ,jing, wang WHERE xi.id=jing.foreignId AND wang.id=xi.id AND xi.date>='2015-09-01' AND xi.date<='2015-10-01' AND (1 = 1) ) AS t1
2. 
   
3. 15/10/13 13:11:47 INFO mapreduce.JobSubmitter: number of splits:5
4. 
   
5. 15/10/12 13:40:28 INFO mapreduce.Job: map 0% reduce 0%
6. 15/10/12 13:40:39 INFO mapreduce.Job: map 20% reduce 0%
7. 15/10/12 13:40:40 INFO mapreduce.Job: map 40% reduce 0%
8. 15/10/12 13:40:47 INFO mapreduce.Job: map 60% reduce 0%
9. 15/10/12 13:40:48 INFO mapreduce.Job: map 80% reduce 0%
10. 15/10/12 13:40:52 INFO mapreduce.Job: map 100% reduce 0%

可以看出，--split-by設置后，job按設置值切分，切分個數為-m設置值（-m 5 不設置的話默認job切分數是4）。經檢驗，此種較復雜的SQL語句，Sqoop支持得很好。

調整Hive數據類型

上面任務執行成功后，經過檢測，發現Hive表結構中的數據類型與MySQL對應列有如下關系：

1. MySQL(bigint) --> Hive(bigint)
2. MySQL(tinyint) --> Hive(tinyint)
3. MySQL(int) --> Hive(int)
4. MySQL(double) --> Hive(double)
5. MySQL(bit) --> Hive(boolean)
6. MySQL(varchar) --> Hive(string)
7. MySQL(decimal) --> Hive(double)
8. MySQL(date/timestamp) --> Hive(string)

可以看出MySQL的decimal類型變成了Hive中的double類型。此時需要在導入時通過--map-column-hive 作出映射關系指定，如下所示：

1. [hdfs@node196 bin]$ sqoop import \
2. --connect jdbc:mysql://192.168.184.12/angel --username anqi --password anqi_mima \
3. --query "SELECT * FROM xi WHERE date>='2015-09-16' AND date<='2015-10-01' \
4. AND \$CONDITIONS" \
5. --split-by date --hive-import -m 5 \
6. --map-column-hive cost="DECIMAL",date="DATE" \
7. --target-dir /user/hive/warehouse/xi \
8. --hive-table xi

以上命令可以執行成功，然而Hive列類型設置為DECIMAL時，從Mysql[decimal(12,2)]-->Hive[decimal]會導致導入后小數丟失。

不斷更新

將上面Sqoop語句執行兩次，在執行第二次時會出現錯誤：

1. ERROR tool.ImportTool: Encountered IOException running import job: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://node190:8020/user/hive/warehouse/anqi_wang already exists

這表示HDFS中已經存在相應存儲，此時需要執行Sqoop-Hive的增量導入語句。

使用Sqoop從MySQL導入數據到HBase

使用復雜SQL

1. #從MySQL導入數據到HBase
2. [hdfs@node196 bin]$ sqoop import \
3. --connect jdbc:mysql://192.168.184.12/angel --username anqi --password anqi_mima \
4. --query "SELECT * FROM xi WHERE 1=1 \
5. AND \$CONDITIONS" \
6. --hbase-table hxi --hbase-create-table \
7. --hbase-row-key id --split-by date -m 7 \
8. --column-family aitanjupt

上面SQL語句較簡單。經檢驗，更復雜的SQL語句，Sqoop支持得很好，導入正常。

不斷更新

以上指定了HBase的Rowkey后，再次執行從MySQL導入數據到HBase的Sqoop語句，基于相同的Rowkey值，HBase內相應的行會進行更新替換。

Hive使用HBase數據

1. CREATE EXTERNAL TABLE default.angel(
2. id BIGINT,
3. username STRING,
4. password STRING)
5. ROW FORMAT SERDE 'org.apache.hadoop.hive.hbase.HBaseSerDe'
6. STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
7. WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key, angel:username, angel:password")
8. TBLPROPERTIES("hbase.table.name" = "hxi");

關于Hive使用存儲在HBase中的數據，更多詳細信息可以查看《使用Hive或Impala執行SQL語句，對存儲在HBase中的數據操作》一文。

關于Sqoop2

架構上，Sqoop1使用MapOnly作業進行Hadoop（HDFS/HBase/Hive）同關系數據庫進行數據的導入導出，用戶使用命令行方式與之交互，數據傳輸和數據格式緊密耦合；易用性欠佳，Connector數據格式支持有限，安全性不好，對Connector的限制過死。 Sqoop2則建立了集中化的服務，負責管理完整的MapReduce作業，提供多種用戶交互方式（CLI/WebUI/RESTAPI），具有權限管理機制，具有規范化的Connector，使得它更加易用，更加安全，更加專注。

綜上所述

使用Sqoop從MySQL導入數據到HBase要比導入到Hive方便，使用Hive對HBase數據操作時，也無decimal精度相關BUG，并且可以很好的支持更新。因此建議使用Sqoop從MySQL導入數據到HBase，而非直接Hive。

經過測試，使用Sqoop從MySQL導入數據到HBase，100萬條需花費7~12分鐘。impala對于hbase的查詢效率也沒有對hdfs效率高。

本文會不定期更新，若有不對之處請前往原文出處： http://www.cnblogs.com/wgp13x/ 進行指正。

其他感受

在大數據領域浸染的多了，才知道當前社會上的公司對于大數據方向的技術都是什么樣的理解。有的公司數據量足夠大，但數據并無價值或者價值不夠大，我們稱之為LU（large but unavailable）類型公司；有的公司數據量不多，但都價值連城，我們稱之為SV（small but valuable）類型公司；有的公司數據量很大，數據也都很有價值，我們稱之為LV（large and valuable）類型公司。

LU公司市場上有很多，造成數據無價值的因素可能是有數據本身的原因，也有產品的原因，他們也經常想要在當前環境下試下大數據的水，這便要細心甄別。SV公司也有不少，對于他們而言，好的數據分析師比大數據平臺更重要，欲要試水大數據，還是要對數據量有清晰的預估。LV公司是最需要搭建大數據平臺的，但又往往他們沒有技術與欲望去做這件事，這也比較可恨。

簡而言之，想做的不一定需要，不做的不一定不需要。