[TOC]

最近在研究數(shù)據(jù)采集相關(guān)的知識(shí)，需要用到Sqoop把關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)導(dǎo)入到Hive里，這里記錄下自己的使用心得，更多的是需要注意的地方。

環(huán)境準(zhǔn)備

OS: MacOS
Hadoop: 2.7.2
Hive: 1.0.0
Sqoop: 1.4.6

根據(jù)Sqoop官網(wǎng)說法，Sqoop2 目前還未開發(fā)完，不建議在生產(chǎn)環(huán)境使用，所以這里選的是Sqoop的穩(wěn)定版 1.4.6
Hadoop和Hive的安裝與配置可以參考網(wǎng)上的資料，Sqoop環(huán)境配置注意修改 ${SQOOP_HOME}/conf/sqoop_env.sh ，如果使用MySQL，還要將MySQL的驅(qū)動(dòng)jar包拷貝到 ${SQOOP_HOME}/lib 目錄下

Sqoop簡(jiǎn)介

Sqoop是一個(gè)用于Hadoop和關(guān)系型數(shù)據(jù)庫或主機(jī)之間的數(shù)據(jù)傳輸工具。它可以將數(shù)據(jù)從關(guān)系型數(shù)據(jù)庫import到HDFS，也可以從HDFS export到關(guān)系型數(shù)據(jù)庫，通過Hadoop的MapReduce實(shí)現(xiàn)。

Sqoop命令

執(zhí)行 $sqoop help, 可以看到Sqoop支持的命令：

Sqoop命令

具體使用方法參見官方文檔, 這里我們主要介紹下 import 命令的注意點(diǎn)

sqoop-import 注意點(diǎn)

import 可能會(huì)用到的參數(shù)：

1. 如果是分布式環(huán)境，--connect參數(shù)不要寫成localhost，應(yīng)該寫TaskTracker節(jié)點(diǎn)的地址

2. 數(shù)據(jù)庫登錄密碼可以指定文件 --password-file ${user.home}/.password這個(gè)文件可以放到local或者h(yuǎn)dfs上，注意：此文件不能有空格，echo -n "secret" > password.file

3. 盡量別用 --password，不安全（ps命令可以獲取到此參數(shù)）， 可以用-P輸入console方式， 注意：密碼可以設(shè)置別名：--password-alias

4. -m <n> 并發(fā)數(shù)默認(rèn)是4 task, 并發(fā)數(shù)n不要超過可用的MapReduce集群數(shù)，也不要超過數(shù)據(jù)庫支持限度。并發(fā)原理：假定split-column = id，數(shù)據(jù)庫中 id = 0-1000，sqoop默認(rèn)4個(gè)task，切成0-250，251-500 ... 不支持多列 split. (可以看到hdfs里有按并發(fā)數(shù)切了4個(gè)切片)

5. 當(dāng)使用Oozie啟動(dòng)Sqoop job時(shí)，添加 --skip-dist-cache 參數(shù)，Oozie會(huì)cache job需要的lib包

6. --warehouse-dir 可以指定父級(jí)目錄，與 --target-dir 不共存

7. --map-column-java, --map-column-hive 指定某幾列映射成Java或Hive的列屬性

8. 支持增量 --incremental <mode> mode指定為append或lastmodified，還要指定列增量的值，感覺不是很好用，萬一數(shù)據(jù)是更新怎么辦？

9. 導(dǎo)出數(shù)據(jù)文件分兩種 delimited 和 序列化文件，還可以-z壓縮

10. 如果列含有BLOB或CLOB等大型數(shù)據(jù)列，--inline-lob-limit 可以限制下，暫時(shí)先不研究

11. 其他涉及到import export特殊字符轉(zhuǎn)義的先不考慮，如果需要可以參見 Table6、7 Format

12. 數(shù)據(jù)導(dǎo)入Hive

    
    

• --hive-overwrite: 全表overwrite（需配合--hive-import使用，如果Hive里沒有表會(huì)先創(chuàng)建之）

• --create-hive-table: 自動(dòng)推斷表字段類型直接建表,overwrite功能可以完全替代掉了（但hive里此表不能exist，所以在import前要先drop下）

• --hive-import - -hive-overwrite 這兩個(gè)參數(shù)組合很不錯(cuò) (直接overwrite也可以，但是考慮到如果表結(jié)構(gòu)有變化，hive里是不會(huì)感知到的，所以還是先drop吧)

• 如果含有特殊字符，需要轉(zhuǎn)義可以用 -detlims 參數(shù)，這個(gè)參數(shù)只能用于hive默認(rèn)分隔符

• 默認(rèn)用"null" 代替 NULL，而Hive \N 標(biāo)識(shí) NULL值，可以使用--null-string '\\N' --null-non-string '\\N' 解決這個(gè)問題， 沒什么特殊要求就加上吧

• Hive也可以使用 --compress 壓縮，但是缺點(diǎn)是很多解碼器不支持并行任務(wù)的split，貌似lzop解壓可以，但是要求頗多，不建議使用

• Hive里不支持 SQL 類型：bit(2)，這樣是不妥的，應(yīng)該改成 bit(1)

14. Sqoop支持import到 HBase，Accumulo 時(shí)間關(guān)系，暫不研究
15. 支持額外的參數(shù) conf/sqoop-site.xml 自己配置去（-…-）

sqoop-import-all-tables 注意點(diǎn)

1. import-all-tables 可以整庫的import，但有以為下限制條件：

• 每個(gè)表都必須有一個(gè)單列主鍵，或者指定--autoreset-to-one-mapper參數(shù)
• 每個(gè)表只能import全部列，即不可指定列import
• 不能使用非默認(rèn)的分隔符，不能指定 where 從句

2. --exclude-tables <tables>此參數(shù)可以 exlude掉不需要import的表(多個(gè)表逗號(hào)分隔)
3. 不可以使用 --table, --split-by, --columns, --where，--delete-target-dir 等等參數(shù)
4. 不支持 --class-name，可以用 --package-name 指定package
5. 一旦執(zhí)行過程中有異常拋出，會(huì)立即停止

sqoop-import-mainframe

直接import主機(jī)！這個(gè)命令過于暴力，和import-all-tables差別不大，等有需要在研究研究吧。

sqoop-job 注意點(diǎn)

Sqoop可以將import任務(wù)保存為job，可以理解為起了個(gè)別名，這樣方便的Sqoop任務(wù)的管理。

參數(shù)列表：

job存儲(chǔ)方案

job的是有兩種存儲(chǔ)方案的，通過配置--meta-connect或者在conf/sqoop-site.xml 里配置 sqoop.metastore.client.autoconnect.url 參數(shù)來指定是否使用metastore-client

方案A(推薦)：不使用metastore-client

如果job信息放到 ${HOME}/.sqoop 目錄下，此目錄下有兩個(gè)文件：
metastore.db.properties：metastore的配置信息
metastore.db.script：job的詳細(xì)信息，通過sql語句存儲(chǔ)

方案B： 不使用metastore-client

此時(shí)，job的信息會(huì)存儲(chǔ)到配置的 autoconnect.url 的 SQOOP_SESSION 表里，但是此方案會(huì)有個(gè)bug，我本地在執(zhí)行創(chuàng)建job的時(shí)候報(bào)錯(cuò)：

ERROR tool.JobTool: I/O error performing job operation: java.io.IOException: Invalid metadata version

上網(wǎng)查了下解決方案，發(fā)現(xiàn) SQOOP_ROOT 表需要插入條數(shù)據(jù):

INSERT INTO SQOOP_ROOT VALUES(NULL,'sqoop.hsqldb.job.storage.version','0');

所以，還是使用方案A吧，這樣也不會(huì)對(duì)數(shù)據(jù)庫造成入侵

注意： 如果使用Oozie執(zhí)行sqoop-job的話，務(wù)必將sqoop-site.xml 中的 sqoop.metastore.client.record.password參數(shù)設(shè)置為true

其他命令

1. sqoop-export：HDFS export 到關(guān)系型數(shù)據(jù)庫，目標(biāo)table必須存在，可分為insert和update模式
2. sqoop-metastore：指定metadata倉庫，可在配置文件中設(shè)置
3. sqoop-merge：合并兩個(gè)數(shù)據(jù)集，一個(gè)數(shù)據(jù)集的記錄應(yīng)該重新舊數(shù)據(jù)集的條目
4. sqoop-codegen：將數(shù)據(jù)集封裝成Java類
5. sqoop-create-hive-table：Sqoop單獨(dú)提供了針對(duì)Hive的命令，可以代替 sqoop import --hive-import, 其他參數(shù)一樣
6. sqoop-eval：執(zhí)行sql語句，結(jié)果會(huì)打印在控制臺(tái)，可以用來校驗(yàn)下import的查詢條件是否正確
7. sqoop-list-databases，sqoop-list-tables：列出數(shù)據(jù)庫，列出表

以上命令暫時(shí)沒用到或很簡(jiǎn)單，等以后需要的時(shí)候再去研究吧

HCatalog

HCatalog提供表和存儲(chǔ)管理服務(wù)，使不同的Hadoop數(shù)據(jù)處理工具如：Pig，MapReduce，Hive更容易讀取和寫入數(shù)據(jù)網(wǎng)格

看文檔比較復(fù)雜，暫時(shí)用不到，等以后需要的時(shí)候再去研究吧o(╯□╰)o

需要注意的地方

1. MySQL

• MySQL允許Date類型字段出現(xiàn)'0000-00-00'，Sqoop有三種方式處理這種情況：1. 轉(zhuǎn)成NULL(默認(rèn))，2. 拋出異常 3. 置成'0001-01-01', 配置zeroDateTimeBehavior參數(shù)設(shè)置，跟在connect string后面
• UNSIGNED 列, MySQL 范圍：0 ~ 2^32 ，Sqoop范圍：-2^31~ +2^31-1, 注意不要越界

2. Hive

• DATE, TIME, TIMESTAMP 類型轉(zhuǎn)換到Hive里會(huì)被處理為 String
• NUMERIC, DECIMAL 類型轉(zhuǎn)換到Hive里會(huì)被處理為 DOUBLE
• Sqoop會(huì)打印warn日志警告可能會(huì)丟失精度

3. --direct 參數(shù)目前只有MySQL和PostgreSQL(import) 支持，不支持BLOB, CLOB, LONGVARBINARY 列
4. --direct不支持視圖

最佳實(shí)踐

根據(jù)我的個(gè)人需求，配置了適合我的最佳Sqoop參數(shù)(∩_∩)

單表import到Hive：

sqoop import  \
  --connect jdbc:mysql://127.0.0.1:3306/database_name \
  --username root \
  --password 123456 \
  --table table_name \
  --outdir \${HOME}/.sqoop/java  \
  --delete-target-dir \
  --hive-import \
  --hive-overwrite \
  --null-string '\\N' \ 
  --null-non-string '\\N' \
  -m 8 \
  --direct

整庫import到Hive：

sqoop import-all-tables \
  --connect jdbc:mysql://127.0.0.1:3306/database_name \
  --username root \
  --password 123456 \
  --outdir \${HOME}/.sqoop/java  \
  --hive-import \
  --hive-overwrite \
  --null-string '\\N' \ 
  --null-non-string '\\N'  \
  -m 8 \
  --direct

說明：
--out-dir：指定Java文件的路徑
--delete-target-dir：刪除HDFS目錄
--hive-import：直接導(dǎo)入的Hive
--hive-overwrite：全表覆蓋（如果表不存在會(huì)直接創(chuàng)建表）
--null-string：指定String列如果為NULL轉(zhuǎn)換到Hive里也為NULL
--null-non-string：指定String列如果為NULL轉(zhuǎn)換到Hive里也為NULL
-m 8：8個(gè)并發(fā)
--direct：直連模式，使用mysqldump加快速度(本地測(cè)試13W條數(shù)據(jù)，74M, 快了10%左右)

最后

通過幾天對(duì)Sqoop的研究，掌握了Sqoop import的基本用法，更高級(jí)的用法還有待探索，最近在寫個(gè)Sqoop的Java工具類，希望能通過API的形式執(zhí)行Sqoop命令，以后也會(huì)加入Oozie對(duì)Sqoop job的管理。

PS：Sqoop2 有很多新特性， 支持命令行、Web UI、REST API，支持server模式同時(shí)也更安全易用，所以很期待Sqoop的 2.0 版！