一、Sqoop 基本命令 1. 查看所有命令 2. 查看某條命令的具體使用方法 二、Sqoop 與 MySQL 1. 查詢MySQL所有資料庫 通常用於 Sqoop 與 MySQL 連通測試: 2. 查詢指定資料庫中所有數據表 三、Sqoop 與 HDFS 3.1 MySQL數據導入到HDFS 1. ...
一、Sqoop 基本命令
1. 查看所有命令
# sqoop help
2. 查看某條命令的具體使用方法
# sqoop help 命令名
二、Sqoop 與 MySQL
1. 查詢MySQL所有資料庫
通常用於 Sqoop 與 MySQL 連通測試:
sqoop list-databases \
--connect jdbc:mysql://hadoop001:3306/ \
--username root \
--password root
2. 查詢指定資料庫中所有數據表
sqoop list-tables \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root
三、Sqoop 與 HDFS
3.1 MySQL數據導入到HDFS
1. 導入命令
示例:導出 MySQL 資料庫中的 help_keyword
表到 HDFS 的 /sqoop
目錄下,如果導入目錄存在則先刪除再導入,使用 3 個 map tasks
並行導入。
註:help_keyword 是 MySQL 內置的一張字典表,之後的示例均使用這張表。
sqoop import \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--table help_keyword \ # 待導入的表
--delete-target-dir \ # 目標目錄存在則先刪除
--target-dir /sqoop \ # 導入的目標目錄
--fields-terminated-by '\t' \ # 指定導出數據的分隔符
-m 3 # 指定並行執行的 map tasks 數量
日誌輸出如下,可以看到輸入數據被平均 split
為三份,分別由三個 map task
進行處理。數據預設以表的主鍵列作為拆分依據,如果你的表沒有主鍵,有以下兩種方案:
- 添加
-- autoreset-to-one-mapper
參數,代表只啟動一個map task
,即不並行執行; - 若仍希望並行執行,則可以使用
--split-by <column-name>
指明拆分數據的參考列。
2. 導入驗證
# 查看導入後的目錄
hadoop fs -ls -R /sqoop
# 查看導入內容
hadoop fs -text /sqoop/part-m-00000
查看 HDFS 導入目錄,可以看到表中數據被分為 3 部分進行存儲,這是由指定的並行度決定的。
3.2 HDFS數據導出到MySQL
sqoop export \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--table help_keyword_from_hdfs \ # 導出數據存儲在 MySQL 的 help_keyword_from_hdf 的表中
--export-dir /sqoop \
--input-fields-terminated-by '\t'\
--m 3
表必須預先創建,建表語句如下:
CREATE TABLE help_keyword_from_hdfs LIKE help_keyword ;
四、Sqoop 與 Hive
4.1 MySQL數據導入到Hive
Sqoop 導入數據到 Hive 是通過先將數據導入到 HDFS 上的臨時目錄,然後再將數據從 HDFS 上 Load
到 Hive 中,最後將臨時目錄刪除。可以使用 target-dir
來指定臨時目錄。
1. 導入命令
sqoop import \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--table help_keyword \ # 待導入的表
--delete-target-dir \ # 如果臨時目錄存在刪除
--target-dir /sqoop_hive \ # 臨時目錄位置
--hive-database sqoop_test \ # 導入到 Hive 的 sqoop_test 資料庫,資料庫需要預先創建。不指定則預設為 default 庫
--hive-import \ # 導入到 Hive
--hive-overwrite \ # 如果 Hive 表中有數據則覆蓋,這會清除表中原有的數據,然後再寫入
-m 3 # 並行度
導入到 Hive 中的 sqoop_test
資料庫需要預先創建,不指定則預設使用 Hive 中的 default
庫。
# 查看 hive 中的所有資料庫
hive> SHOW DATABASES;
# 創建 sqoop_test 資料庫
hive> CREATE DATABASE sqoop_test;
2. 導入驗證
# 查看 sqoop_test 資料庫的所有表
hive> SHOW TABLES IN sqoop_test;
# 查看表中數據
hive> SELECT * FROM sqoop_test.help_keyword;
3. 可能出現的問題
如果執行報錯 java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf
,則需將 Hive 安裝目錄下 lib
下的 hive-exec-**.jar
放到 sqoop 的 lib
。
[root@hadoop001 lib]# ll hive-exec-*
-rw-r--r--. 1 1106 4001 19632031 11 月 13 21:45 hive-exec-1.1.0-cdh5.15.2.jar
[root@hadoop001 lib]# cp hive-exec-1.1.0-cdh5.15.2.jar ${SQOOP_HOME}/lib
4.2 Hive 導出數據到MySQL
由於 Hive 的數據是存儲在 HDFS 上的,所以 Hive 導入數據到 MySQL,實際上就是 HDFS 導入數據到 MySQL。
1. 查看Hive表在HDFS的存儲位置
# 進入對應的資料庫
hive> use sqoop_test;
# 查看表信息
hive> desc formatted help_keyword;
Location
屬性為其存儲位置:
這裡可以查看一下這個目錄,文件結構如下:
3.2 執行導出命令
sqoop export \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--table help_keyword_from_hive \
--export-dir /user/hive/warehouse/sqoop_test.db/help_keyword \
-input-fields-terminated-by '\001' \ # 需要註意的是 hive 中預設的分隔符為 \001
--m 3
MySQL 中的表需要預先創建:
CREATE TABLE help_keyword_from_hive LIKE help_keyword ;
五、Sqoop 與 HBase
本小節只講解從 RDBMS 導入數據到 HBase,因為暫時沒有命令能夠從 HBase 直接導出數據到 RDBMS。
5.1 MySQL導入數據到HBase
1. 導入數據
將 help_keyword
表中數據導入到 HBase 上的 help_keyword_hbase
表中,使用原表的主鍵 help_keyword_id
作為 RowKey
,原表的所有列都會在 keywordInfo
列族下,目前只支持全部導入到一個列族下,不支持分別指定列族。
sqoop import \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--table help_keyword \ # 待導入的表
--hbase-table help_keyword_hbase \ # hbase 表名稱,表需要預先創建
--column-family keywordInfo \ # 所有列導入到 keywordInfo 列族下
--hbase-row-key help_keyword_id # 使用原表的 help_keyword_id 作為 RowKey
導入的 HBase 表需要預先創建:
# 查看所有表
hbase> list
# 創建表
hbase> create 'help_keyword_hbase', 'keywordInfo'
# 查看表信息
hbase> desc 'help_keyword_hbase'
2. 導入驗證
使用 scan
查看表數據:
六、全庫導出
Sqoop 支持通過 import-all-tables
命令進行全庫導出到 HDFS/Hive,但需要註意有以下兩個限制:
- 所有表必須有主鍵;或者使用
--autoreset-to-one-mapper
,代表只啟動一個map task
; - 你不能使用非預設的分割列,也不能通過 WHERE 子句添加任何限制。
第二點解釋得比較拗口,這裡列出官方原本的說明:
- You must not intend to use non-default splitting column, nor impose any conditions via a
WHERE
clause.
全庫導出到 HDFS:
sqoop import-all-tables \
--connect jdbc:mysql://hadoop001:3306/資料庫名 \
--username root \
--password root \
--warehouse-dir /sqoop_all \ # 每個表會單獨導出到一個目錄,需要用此參數指明所有目錄的父目錄
--fields-terminated-by '\t' \
-m 3
全庫導出到 Hive:
sqoop import-all-tables -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://hadoop001:3306/資料庫名 \
--username root \
--password root \
--hive-database sqoop_test \ # 導出到 Hive 對應的庫
--hive-import \
--hive-overwrite \
-m 3
七、Sqoop 數據過濾
7.1 query參數
Sqoop 支持使用 query
參數定義查詢 SQL,從而可以導出任何想要的結果集。使用示例如下:
sqoop import \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--query 'select * from help_keyword where $CONDITIONS and help_keyword_id < 50' \
--delete-target-dir \
--target-dir /sqoop_hive \
--hive-database sqoop_test \ # 指定導入目標資料庫 不指定則預設使用 Hive 中的 default 庫
--hive-table filter_help_keyword \ # 指定導入目標表
--split-by help_keyword_id \ # 指定用於 split 的列
--hive-import \ # 導入到 Hive
--hive-overwrite \ 、
-m 3
在使用 query
進行數據過濾時,需要註意以下三點:
- 必須用
--hive-table
指明目標表; - 如果並行度
-m
不為 1 或者沒有指定--autoreset-to-one-mapper
,則需要用--split-by
指明參考列; - SQL 的
where
字句必須包含$CONDITIONS
,這是固定寫法,作用是動態替換。
7.2 增量導入
sqoop import \
--connect jdbc:mysql://hadoop001:3306/mysql \
--username root \
--password root \
--table help_keyword \
--target-dir /sqoop_hive \
--hive-database sqoop_test \
--incremental append \ # 指明模式
--check-column help_keyword_id \ # 指明用於增量導入的參考列
--last-value 300 \ # 指定參考列上次導入的最大值
--hive-import \
-m 3
incremental
參數有以下兩個可選的選項:
- append:要求參考列的值必須是遞增的,所有大於
last-value
的值都會被導入; - lastmodified:要求參考列的值必須是
timestamp
類型,且插入數據時候要在參考列插入當前時間戳,更新數據時也要更新參考列的時間戳,所有時間晚於last-value
的數據都會被導入。
通過上面的解釋我們可以看出來,其實 Sqoop 的增量導入並沒有太多神器的地方,就是依靠維護的參考列來判斷哪些是增量數據。當然我們也可以使用上面介紹的 query
參數來進行手動的增量導出,這樣反而更加靈活。
八、類型支持
Sqoop 預設支持資料庫的大多數欄位類型,但是某些特殊類型是不支持的。遇到不支持的類型,程式會拋出異常 Hive does not support the SQL type for column xxx
異常,此時可以通過下麵兩個參數進行強制類型轉換:
- --map-column-java<mapping> :重寫 SQL 到 Java 類型的映射;
- --map-column-hive <mapping> : 重寫 Hive 到 Java 類型的映射。
示例如下,將原先 id
欄位強制轉為 String 類型,value
欄位強制轉為 Integer 類型:
$ sqoop import ... --map-column-java id=String,value=Integer
參考資料
更多大數據系列文章可以參見 GitHub 開源項目: 大數據入門指南