Sqoop筆記整理

[toc]

Sqoop筆記整理

概述

SQOOP   ---數據搬用工
    可以將外部數據遷移到hdfs目錄或者hive表或者hbase表

import原理

從傳統數據庫獲取元數據信息(schema、table、field、field type)，把導入功能轉換為只有Map的Mapreduce作業，
在mapreduce中有很多map，每個map讀一片數據，進而并行的完成數據的拷貝。

export原理

獲取導出表的schema、meta信息，和Hadoop中的字段match；多個map only作業同時運行，完成hdfs中數據導出到關系型數據庫中。

Sqoop安裝

下載地址：https://mirrors.tuna.tsinghua.edu.cn/apache/sqoop/1.4.7/
下載之后進行解壓：
   tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /home/uplooking/app
重命名：
   [uplooking@uplooking01 ~]$ mv app/sqoop-1.4.7.bin__hadoop-2.6.0/ app/sqoop
配置SQOOP_HOME到環境變量中
  export SQOOP_HOME=/home/uplooking/app/sqoop
  export PATH=$PATH:$SQOOP_HOME
配置$SQOOP_HOME/conf/sqoop-env.sh
   export HADOOP_COMMON_HOME=/home/uplooking/app/hadoop
   export HADOOP_MAPRED_HOME=/home/uplooking/app/hadoop
   export HBASE_HOME=/home/uplooking/app/hbase
   export HIVE_HOME=/home/uplooking/app/hive
   export ZOOCFGDIR=/home/uplooking/app/zookeeper/conf

   下面這個在1.4.7中需要配置，否則在執行數據導入到hive時會報錯
   export HIVE_CONF_DIR=/home/uplooking/app/hive/conf
注意：
1、數據庫驅動：
  在執行sqoop命里的受需要拷貝相關數據庫驅動jar包到$SQOOP_HOME/lib目錄下，例如mysql需要mysql-connector-java-5.1.32-bin.jar以上版本支持。
2、JDK版本
  JDK版本最好1.7以上。
3、hive的核心包拷貝（這個在1.4.7中需要配置，否則在執行數據導入到hive時會報錯）
    將$HIVE_HOME/lib/hive-exec.jar拷貝到$SQOOP_HOME/lib目錄下，不然會報
    18/03/15 15:50:54 ERROR hive.HiveConfig: Could not load org.apache.hadoop.hive.conf.HiveConf. Make sure HIVE_CONF_DIR is set correctly.
    18/03/15 15:50:54 ERROR tool.ImportTool: Import failed: java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf

數據導入import

由mysql導入數據到HDFS

people表中的數據：

+----+-----------+------+--------+
| id | name      | age  | height |
+----+-----------+------+--------+
|  1 | 小甜甜    |   18 |    168 |
|  2 | 小丹丹    |   19 |    167 |
|  3 | 大神      |   25 |    181 |
|  4 | 團長      |   38 |    158 |
|  5 | 記者      |   22 |    169 |
+----+-----------+------+--------+

數據導入:

sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username root --password root --table people
將msyql數據test中的表people，導入到hdfs目錄，該目錄在/user/用戶/people下面，其中people為導入的表名，

這是sqoop導入到的默認目錄，如果要想導入到指定的目錄，添加一個選項--target-dir：

sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username root --password root --table people --target-dir /output/sqoop/people

因為默認執行sqoop會有4個maptasks任務，為了滿足業務的需要，可以進行修改，只需要在命令后面加一個選項-m：

sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username root --password root --table people --target-dir /output/sqoop/people -m 2

執行的過程中，如果輸出目錄已經存在，報錯，要想輸出到該目錄 使用選項--delete-target-dir：

sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username root --password root --table people --target-dir /output/sqoop/people -m 2 --delete-target-dir

如果想在原來的基礎之上追加新的數據，只需要添加一個選項--append,但是注意，--append和--delete-target-dir不能同時存在：

sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username root --password root --table people --target-dir /output/sqoop/people -m 2 --append

條件導入：

sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username root --password root --table people --target-dir hdfs://ns1/input/sqoop/people --append -m 1 --where "age < 20 and height > 167"

通過sql導入：

#!/bin/bash

SQOOP_HOME=/home/uplooking/app/sqoop

sqoop import \
--connect jdbc:mysql://192.168.43.116:3306/test \
--username root \
--password root \
--target-dir hdfs://ns1/input/sqoop/people \
--query "select id, name, age, height from people where age < 30 and height > 168 and \$CONDITIONS" \
--append -m 1 \

由mysql導入到hive

sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username 'root' --password 'root' --table people --hive-import -m 1

覆蓋數據（只覆蓋數據，不覆蓋表結構）
sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username 'root' --password 'root' --table people --hive-import -m 1 --hive-overwrite

創建表名
sqoop import --connect jdbc:mysql://192.168.43.116:3306/test --username 'root' --password 'root' --table people --hive-import -m 1 --hive-table "hpeople" --hive-overwrite

導出所有的表到hive中
sqoop import-all-tables --connect jdbc:mysql://192.168.43.116:3306/test --username root --password root --hive-import --fields-terminated-by "\001" --lines-terminated-by "\n"
# 這里指定了行和列的分隔符，因為默認情況下，MySQL導入到hive的數據是以逗號作為分隔符的，
# 數據從hive（準確說應該是HDFS）導出到mysql時默認也是以逗號作為列分隔符的，這點尤其需要注意

由mysql導入到HBase

sqoop import \
--connect jdbc:mysql://192.168.43.116:3306/test \
--username 'root' \
--password 'root' \
--table people \
--hbase-create-table \
--hbase-row-key id \
--hbase-table hpeople \
--column-family cf

導入Hbase的時候，默認使用主鍵做key，沒有主鍵使用--split-by，暫時處理不了聯合主鍵，最好現在hbase中建立相關的表結構

查看HBase中表的數據：

hbase(main):002:0> scan 'hpeople'
ROW                           COLUMN+CELL                                                                         
 1                            column=cf:age, timestamp=1521846328316, value=18                                    
 1                            column=cf:height, timestamp=1521846328316, value=168.0                              
 1                            column=cf:name, timestamp=1521846328316, value=\xE5\xB0\x8F\xE7\x94\x9C\xE7\x94\x9C 
 2                            column=cf:age, timestamp=1521846328096, value=19                                    
 2                            column=cf:height, timestamp=1521846328096, value=167.0                              
 2                            column=cf:name, timestamp=1521846328096, value=\xE5\xB0\x8F\xE4\xB8\xB9\xE4\xB8\xB9 
 3                            column=cf:age, timestamp=1521846329182, value=25                                    
 3                            column=cf:height, timestamp=1521846329182, value=181.0                              
 3                            column=cf:name, timestamp=1521846329182, value=\xE5\xA4\xA7\xE7\xA5\x9E             
 4                            column=cf:age, timestamp=1521846328454, value=38                                    
 4                            column=cf:height, timestamp=1521846328454, value=158.0                              
 4                            column=cf:name, timestamp=1521846328454, value=\xE5\x9B\xA2\xE9\x95\xBF             
 5                            column=cf:age, timestamp=1521846330135, value=22                                    
 5                            column=cf:height, timestamp=1521846330135, value=169.0                              
 5                            column=cf:name, timestamp=1521846330135, value=\xE8\xAE\xB0\xE8\x80\x85             
5 row(s) in 0.1620 seconds

數據導出export

數據導出到mysql，默認以逗號作為分隔符

從HDFS導出到mysql

導出的時候字段需要一一對應
sqoop export \
--connect jdbc:mysql://192.168.43.116:3306/test \
--username root --password root \
--table people \
--export-dir hdfs://ns1/input/sqoop/people

  中文亂碼：
sqoop export \
--connect "jdbc:mysql://192.168.43.116:3306/test?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password root \
--table people \
--export-dir hdfs://ns1/input/sqoop/people

插入或更新
如果存在就更新，不存在就插入（指的是某一條記錄，而不是表本身）
sqoop export 
--connect "jdbc:mysql://192.168.43.116:3306/test?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password root \
--table people
--export-dir /export -m 1 \
--update-key id \
--update-mode allowinsert

從Hive導出到mysql

和導入類似 -input-fields-terminated-by解析HDFS上面的數據到數據庫時使用參數

sqoop export \
--connect jdbc:mysql://192.168.43.116:3306/test \
--username root \
--password root \
--table people \
--export-dir /user/hive/warehouse/hpeople
--input-fields-terminated-by '\001'

從HBase導出到mysql

目前沒有直接從HBase導出的方法，但是可以先將數據導出到HDFS中（通過Hive和HBase的整合），然后再從HDFS導出到mysql中。