Sqoop01：Sqoop部署安装

1.linux环境准备和搭建Hadoop全分布环境

Hadoop全分布模式的搭建过程请参看前面的文章：
linux环境和Hadoop环境搭建

2.下载安装包并解压

下载地址：
http://archive.apache.org/dist/sqoop/1.4.7/
]#wget http://archive.apache.org/dist/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
解压：
tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz

3.配置

3.1 环境变量：
vim /root/.bash_profile

SQOOP_HOME=/usr/local/src/sqoop-1.4.7.bin__hadoop-2.6.0
export SQOOP_HOME
PATH=$SQOOP_HOME/bin:$PATH
export PATH

source /root/.bash_profile
3.2 修改配置文件

cd $SQOOP_HOME/conf
mv sqoop-env-template.sh sqoop-env.sh

打开sqoop-env.sh并编辑下面几行：

export HADOOP_COMMON_HOME=/usr/local/src/hadoop-2.7.3
export HADOOP_MAPRED_HOME=/usr/local/src/hadoop-2.7.3
export HIVE_HOME=/usr/local/src/apache-hive-3.1.0-bin

3.3 加入mysql的jdbc驱动包:

]# cd /usr/local/src/sqoop-1.4.7.bin__hadoop-2.6.0
]# cp /usr/local/src/apache-hive-3.1.0-bin/lib/mysql-connector-java-5.1.46-bin.jar lib/

3.4 拷贝hive的home目录下中的hive-common和hive-exec的jar包：

]# cp hive-common-3.1.0.jar /usr/local/src/sqoop-1.4.7.bin__hadoop-2.6.0/lib/
]# cp hive-exec-3.1.0.jar /usr/local/src/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

3.5 将hive-site.xml拷贝到Sqoop下的conf目录下。

4.验证启动与连接

4.1 验证启动
]# sqoop-version
成功输出：

至此，Sqoop安装完成。
4.2 验证连接数据库
]# sqoop list-databases --connect jdbc:mysql://bigdata101:3306/ --username root --password 123456
成功输出数据库名称

5.测试导入数据

5.1 MySQL建库建表：

create database if not exists nshop;
use nshop;
CREATE TABLE `customer` (
  `customer_id` varchar(20) NOT NULL COMMENT '用户ID',
  `customer_login` varchar(20) NOT NULL COMMENT '用户登录名',
  `customer_nickname` varchar(10) NOT NULL COMMENT '用户名(昵称)',
  `customer_name` varchar(10) NOT NULL COMMENT '用户真实姓名',
  `customer_pass` varchar(8) NOT NULL COMMENT '用户密码',
  `customer_mobile` varchar(20) NOT NULL COMMENT '用户手机',
  `customer_idcard` varchar(20) NOT NULL COMMENT '身份证',
  `customer_gender` tinyint(4) NOT NULL COMMENT '性别：1男 0女',
  `customer_birthday` varchar(10) NOT NULL COMMENT '出生年月',
  `customer_age` tinyint(4) NOT NULL COMMENT '年龄',
  `customer_age_range` varchar(2) NOT NULL COMMENT '年龄段',
  `customer_email` varchar(50) DEFAULT NULL COMMENT '用户邮箱',
  `customer_natives` varchar(10) DEFAULT NULL COMMENT '所在地区',
  `customer_ctime` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `customer_utime` bigint(20) DEFAULT NULL COMMENT '修改时间',
  `customer_device_num` varchar(20) NOT NULL COMMENT '用户手机设备号',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.2 导入的部分数据如下：
在/data/project/下创建mysql_data.txt

20101000081999919   8ad36d3n    679388  冯环纨 e2kf8k  15014633858 620422197306088525  2   19730608    46  03  d4jk9e@qq.com   620422  1572608369000   1572608369000   732373
20101000162999838   gd49m895    465832  阳玲茗 n52ebn  13542449912 540522200010256229  2   20001025    19  02  66bb6c@qq.com   540522  1572608474000   1572608474000   696287
20101000243999757   hmgh2ee3    128842  宫舒妍 egbhk2  18667933180 370826196611225598  1   19661122    53  03  59gg17@qq.com   370826  1572608438000   1572608438000   888626
20101000324999676   625825842   929267  海宜伊 gie4ie  17745840940 652824198808234812  1   19880823    31  02  2gfgmh@qq.com   652824  1572608355000   1572608355000   224412
20101000405999595   89nn4a65    275972  乐蓉伊 jm697k  18300065808 652824197905318215  1   19790531    40  02  241eh5@qq.com   652824  1572608342000   1572608342000   228249
20101000486999514   mhii8n6d    859622  卫凡瑾 j5m27j  18106799491 513331197703284734  1   19770328    42  02  dcheci@qq.com   513331  1572608508000   1572608508000   257535

导入数据到MySQL

load data local infile '/data/project/mysql_data.txt' into table customer; 

5.3 hive建库建表

create database if not exists ods_nshop;
use ods_nshop;
CREATE TABLE
IF NOT EXISTS ods_nshop.ods_02_customer (
    customer_id string COMMENT '用户ID',
    customer_login string COMMENT '用户登录名',
    customer_nickname string COMMENT '用户名(昵称)',
    customer_name string COMMENT '用户真实姓名',
    customer_pass string COMMENT '用户密码',
    customer_mobile string COMMENT '用户手机',
    customer_idcard string COMMENT '身份证',
    customer_gender TINYINT COMMENT '性别：1男 0女',
    customer_birthday string COMMENT '出生年月',
    customer_email string COMMENT '用户邮箱',
    customer_natives string COMMENT '所在地区',
    customer_ctime BIGINT COMMENT '创建时间',
    customer_utime BIGINT COMMENT '修改时间'
) location '/data/nshop/ods/ods_02_customer/';

执行命令：

sqoop import \
--connect jdbc:mysql://master:3306/nshop \
--username root \
--password 111111 \
--table customer \
--hive-import \
--hive-overwrite \
--hive-table ods_nshop.ods_02_customer \
-m 1

访问/data/nshop/ods/ods_02_customer/目录：

查看hive表：

数据导入成功.

导入案例2

1）在 Mysql 中新建一张表并插入一些数据

$ mysql -uroot -p000000
mysql> create database company;
mysql> create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255));
mysql> insert into company.staff(name, sex) values('Thomas', 'Male'); 
mysql> insert into company.staff(name, sex) values('Catalina', 'FeMale');

2）导入数据

$ bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 000000 \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

3）检查结果

至此，Sqoop安装完毕。