NO IMAGE

Sqoop

1)Sqoop介紹

Sqoop即 SQL to Hadoop ,是一款方便的在傳統型資料庫與Hadoop之間進行資料遷移的工具,充分利用MapReduce並行特點以批處理的方式加快資料傳輸,發展至今主要演化了二大版本,Sqoop1和Sqoop2,我們以Sqoop1為案例進行講解,Sqoop2商用不太穩定。Sqoop工具是Hadoop下連線關係型資料庫和Hadoop的橋樑,支援關係型資料庫和hive、hdfs,hbase之間資料的相互匯入,可以使用全表匯入和增量匯入。

2)sqoop安裝—安裝很簡單–>主要是給個jdbc的jar包。然後將bin目錄下的hive_home和zookeeper_home註釋掉,當然也得配置環境變數

①解壓tar包

②配置環境變數

export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*:$HIVE_HOME/conf

export SQOOP_HOME=/opt/sqoop

③把MySQL的JDBC驅動包複製到Sqoop的lib目錄下

④給jdk環境下的/opt/jdk/jre/lib/security/java.policy 檔案中加入

permission javax.management.MBeanTrustPermission “register”;

⑤使用sqoop list-databases –connect jdbc:mysql://127.0.0.1:3306/ –username root –password root 測試是否安裝成功

3)sqoop匯入匯出(hdfs)

A、匯入

a、普通匯入

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --columns '列1, 列2...'

b、指定輸出路徑、指定資料分隔符

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --target-dir '目標路徑' --fields-terminated-by '\001'

c、指定Map數量 -m

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --target-dir '目標路徑' --fields-terminated-by '\t' -m 2

d、增加where條件

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --where '條件' --target-dir '目標路徑'

e、使用query語句

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --query 'SELECT語句' --split-by user.age --target-dir '目標路徑'

使用query語句必須指定split-by和–target-dir

f、使用 \將語句換行

sqoop import \
--connect jdbc:mysql://192.168.1.101:3306/庫名 \
--username 資料庫使用者名稱 \
--password 密碼 \
--query 'SELECT語句' \
--split-by user.age \
--target-dir '目標路徑'

g、增量匯入

◇根據id

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名  --target-dir '目標路徑'  --incremental append --check-column id --last-value 10 -m 1
--incremental append選項使用append模式,
--check-column id 使用id欄位來做增量
--last-value為10,從MySQL表中主鍵id>10開始同步。

◇根據時間戳

--incremental lastmodified --check-column last_update --last-value '2017-03-20 11:00:00'

B、匯出

先建好相對應的表,才能匯出

sqoop export --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --export-dir '要匯出的資料所在的路徑' --table 表名 -m 1 --fields-terminated-by '\t'

4)sqoop匯入匯出(hive)

A、匯入

a、普通匯入

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名  --hive-import

內部執行實際分三部,1.將資料匯入hdfs(可在hdfs上找到相應目錄),2.建立hive表名相同的表,3,將hdfs上資料匯入hive表中

b、建立hive表

sqoop create-hive-table  --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名  --hive-table hive表名  --fields-terminated-by "\t"

c、指定hive表名、指定資料分隔符

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名  --hive-import --hive-table hive表名 --fields-terminated-by '\t'

d、通過create-hive-table建立表

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名  --hive-import --create-hive-table --hive-table hive表名 --fields-terminated-by '\t'

可以通過 –create-hive-table 建立表,如果表已經存在則會執行失敗

如果提示目錄已存在,可以在匯入指令碼後面追加:–delete-target-dir

f、覆蓋hive表的內容

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名  --hive-import --hive-overwrite --hive-table hive表名 --fields-terminated-by '\t'

g、增加where條件

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --where '條件' --hive-import --hive-table hive表名 --fields-terminated-by '\t'

h、指定編碼格式

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --where '條件' --hive-import --hive-table hive表名 --fields-terminated-by '\t' --default-character-set=utf-8

i、使用query語句

sqoop import --connect jdbc:mysql://localhost:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --query 'select語句' --hive-import --hive-table hive表名 --fields-terminated-by '\t' --target-dir '目標路徑' --split-by user.age

使用query語句必須指定split-by和–target-dir

j、增量匯入

◇根據id

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --hive-import --hive-table hive表名 --incremental append --check-column id --last-value 11 --fields-terminated-by '\t'

◇根據時間戳

通過時間戳來增量匯入的指令碼:

--incremental lastmodified --check-column last_update --last-value '2017-03-20 11:00:00'

完整指令碼:

sqoop import --connect jdbc:mysql://192.168.1.101:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --table 表名 --hive-import --hive-table hive表名 --incremental lastmodified --check-column last_update --last-value '2017-03-20 11:00:00' --fields-terminated-by '\t'

B、匯出

sqoop export --connect jdbc:mysql://localhost:3306/庫名 --username 資料庫使用者名稱 --password 密碼 --export-dir '要匯出的資料的路徑' --table 表名 -m 1 --fields-terminated-by '\t'