Sqoop基础

一、Sqoop介绍

场景：数据在RDBMS中，我们如何使用Hive或者Hadoop来进行数据分析

1）RDBMS ==> Hadoop

2）Hadoop ==> RDBMS 

传统解决办法：

MapReduce   InputFormat    OutputFormat

现在：

Sqoop（Sql To Hadoop）：RDBMS和Hadoop之间的一个桥梁（实际底层还是MapReduce实现）

1、官网

sqoop1.x：   1.4.7（重点了解）

底层是通过MapReduce来实现的，而且只有Map没有Reduce

sqoop2.x：   1.99.7（不是很方便）

两者不兼容，不是一样东西

导入：RDBMS==>Hadoop

导出：Hadoop==>RDBMS

二、搭建Sqoop

1、查看Hadoop环境

echo $HADOOP_HOME

2、sqoop-1.4.6-cdh5.7.0.tar.gz

3、解压到app

tar -zxvf sqoop-1.4.6-cdh5.7.0.tar.gz

chown -R HBinz:HBinz sqoop-1.4.6-cdh5.7.0.tar.gz

mv sqoop-1.4.6-cdh5.7.0.tar.gz /opt/app

4、Sqoop文件夹

5、配置系统环境变量

export SQOOP_HOME=/home/hadoop/app/sqoop-1.4.6-cdh5.7.0

export PATH=$SQOOP_HOME/bin:$PATH

6、配置Sqoop的环境

cp sqoop-env-template.sh sqoop-env.sh

7、调整sqoop-env.sh

vi sqoop-env.sh

补充以上内容，让Sqoop可以根据配置导出导入Hadoop文件

8、拷贝mysql驱动mysql-connector-java-5.1.27.jar到$SQOOP_HOME/lib下

前提：我进入了hive的lib里面找到驱动文件

cp mysql-connector-java-5.1.27.jar /opt/app/sqoop-1.4.6-cdh5.7.0/lib/

三、sqoop help

1、进入bin，sqoop查看有没错误

2、sqoop help

sqoop + commands

3、如何连接数据库

sqoop help list-databases

sqoop list-databases +common

4、尝试连接数据库

5、sqoop展示现有mysql数据库中的库

sqoop list-databases --connect jdbc:mysql://localhost:3306 \

--username root --password 123456

mysql:

两者一致，说明已连接。

6、展示hbinz001中的表

sqoop list-tables --connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456

mysql上hbinz001的表:

两者一致，说明已连接。

四、RDBMS==>Hadoop

1、查看jmport的命令帮助

sqoop help import 

2、数据库相关的

3、输入相关的

4、导入表中某几列

5、导入sql语句的结果

6、选择task的数量来进行处理

五、Sqoop操作

1、在hbinz001建emp表

---

create table emp (

EMPNO int primary key,

ENAME varchar(20),

JOB varchar(20),

MGR int,

HIREDATE datetime,

SAL int,

COMM int,

DEPTNO int

);

---

2、插入数据、提交

---

insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);

insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);

insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);

insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

---

3、把emp导入HDFS

sqoop import --connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp

8088控制台：

当前日志：

4、分析当前日志

（1）首先测试表是否存在

select t.* from emp as t limit 1;

（2）inputformat的子类

（3）jobsubmitter:number of splits:4

说明默认有4个map task。

（4）文件导入到HDFS的位置

5）查看emp文件

hdfs dfs -ls emp

hdfs dfs -text emp/part*

（6）默认的参数

-m :4

emp默认存放在hdfs dfs -ls 或者 hdfs dfs -ls /user/HBinz/下

名字：emp.jar 

（7）根据默认的参数修改自定义的参数

sqoop import --connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMysqlToHDFS \

--delete-target-dir 

m2修改成功：

hdfsjobname修改成功：

（8）只取需要的列

sqoop import --connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMysqlToHDFS \

--delete-target-dir \

--columns "EMPNO,ENAME,JOB,SAL,COMM" \

--target-dir EMP_COLUMNS

只取了"EMPNO,ENAME,JOB,SAL,COMM"几个字段：

（9）用\t分隔，然后null改为0

sqoop import --connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMysqlToHDFS \

--delete-target-dir \

--columns "EMPNO,ENAME,JOB,SAL,COMM" \

--target-dir EMP_COLUMNS_SPLIT \

--fields-terminated-by '\t' \

--null-string ' ' --null-non-string '0'

字段已用\t（tab键）隔开，Null已转换成0

（10）只取SAL>2000的数据

sqoop import --connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMysqlToHDFS \

--delete-target-dir \

--columns "EMPNO,ENAME,JOB,SAL,COMM" \

--target-dir EMP_COLUMNS_WHERE \

--fields-terminated-by '\t' \

--null-string ' ' --null-non-string '0' \

--where 'SAL>2000'

5、导入一个SQL语句

sqoop import \

sqoop import 

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMySQLToHDFS \

--delete-target-dir \

--columns "EMPNO,ENAME,JOB,SAL,COMM" \

--target-dir EMP_COLUMN_QUERY \

--fields-terminated-by '\t' \

--null-string '' --null-non-string '0' \

--query 'select * from emp where EMPNO >= 7566'

报错：

修改：select 语句不能跟--table 同时出现

sqoop import \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password root \

-m 2 \

--mapreduce-job-name FromMySQLToHDFS \

--delete-target-dir \

--columns "EMPNO,ENAME,JOB,SAL,COMM" \

--target-dir EMP_COLUMN_QUERY \

--fields-terminated-by '\t' \

--null-string '' --null-non-string '0' \

--query 'select * from emp where EMPNO >= 7566'

报错：

修改：最后加上--split-by 'EMPNO'

sqoop import \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password root \

-m 2 \

--mapreduce-job-name FromMySQLToHDFS \

--delete-target-dir \

--columns "EMPNO,ENAME,JOB,SAL,COMM" \

--target-dir EMP_COLUMN_QUERY \

--fields-terminated-by '\t' \

--null-string '' --null-non-string '0' \

--query 'select * from emp where EMPNO >= 7566'  \

--split-by 'EMPNO'

还报错：

继续修改：where后面加上'$CONDITIONS'

sqoop import \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

-m 2 \

--mapreduce-job-name FromMySQLToHDFS \

--delete-target-dir \

--columns "EMPNO,ENAME,JOB,SAL,COMM" \

--target-dir EMP_COLUMN_QUERY \

--fields-terminated-by '\t' \

--null-string '' --null-non-string '0' \

--query 'select * from emp where EMPNO >= 7566 and $CONDITIONS'  \

--split-by 'EMPNO'

6、sqoop eval

: Evaluate a SQL statement and display the results

sqoop eval \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--query 'select * from emp'

可以直接在控制台把sql语句结果打印出来

7、通过执行.opt文件执行脚本

emp.opt【内容严格遵守规则，命令分行，末尾去掉空格】

import

--connect

jdbc:mysql://localhost:3306/hbinz001

--username

root

--password

123456

--table

emp

--delete-target-dir

sqoop --options-file emp.opt

8、导出文件到RDBMS

跟import很像，但有个前提是导出的表一定要存在，否则会失败。

第一次尝试：

sqoop export \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

-m 2 \

--mapreduce-job-name FromHDFSToMySQL \

--table emp_demo \

--export-dir /user/HBinz/emp 

报错：

修改：拷贝emp的表结构给emp_demo

进入MySql:

create table emp_demo as select * from emp where 1=2;

成功：

9、导入到Hive

前面语法跟import到sql的差不多，后面加上

sqoop import \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMySQLToHive \

--delete-target-dir \

--hive-database hive2 \

--hive-table emp_sqoopdemo \

--hive-import 

报错：

修改（测试只有这个办法可行）：

在HBinz用户下添加环境变量HADOOP_CLASSPATH 
# vi ~/.bash_profile 
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/opt/cloudera/parcels/CDH/lib/hive/lib/* 

# source ~/.bash_profile

如果报错：Database does not exist: hive2

将hive的hive-site.xml复制一份到sqoop-conf下即可

成功：

注意：

但是这个表的结构是不完整的，由于是自动新建表，因此是sqoop根据数据的特征来建表，这里的数据类型是不准确的。--create-hive-table不建议使用。

因此生产要谨慎使用，尽量hive建表后再导入数据。

10、mysql导入hive分区表

（1）hive创建分区表

create table ruozedata_emp_partition 

(empno int, ename string, job string, mgr int, hiredate string, salary double, comm double, deptno int)

partitioned by (pt string)

ROW FORMAT DELIMITED 

FIELDS TERMINATED BY '\t'

（2）导入数据

sqoop import \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMySQLToHive \

--delete-target-dir \

--hive-database hive2_ruozedata \

--hive-table ruozedata_emp_partition \

--hive-import \

--hive-partition-key 'pt' \

--hive-partition-value '2018-9-10'

（3）虽成功但有问题

（4）加上分隔符并且覆盖前面错误的数据

sqoop import \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMySQLToHive \

--delete-target-dir \

--hive-database hive2_ruozedata \

--hive-table ruozedata_emp_partition \

--hive-import \

--hive-partition-key 'pt' \

--hive-partition-value '2018-9-10' \

--fields-terminated-by '\t' --hive-overwrite 

11、Hive To MySQL

（1）使用场景

大数据统计完的结果，需要写入MySQL里面

（2）MySQL里面创建表

create table emp_demo as select * from emp where 1=2;

（3）Hive相当于HDFS，跟HDFS导出到MySQL是一样的，后面自己做一下。

四、job

1、sqoop help job

2、创建job

sqoop job --create ruozejob -- \

import \

--connect jdbc:mysql://localhost:3306/hbinz001 \

--username root --password 123456 \

--table emp -m 2 \

--mapreduce-job-name FromMySQLToHDFS \

--delete-target-dir

创建成功，不执行。

（2）执行

sqoop job --exec ruozejob（要输入密码！！！）

（3）其他执行方式

crontab

        job 

sqoop --options-file 

五、综合案例

需求：统计各个区域下最热门的TOP3的商品 

1) MySQL: city_info 静态

2) MySQL: product_info 静态

 DROP TABLE product_info;

 CREATE TABLE `product_info` (

   `product_id` int(11) DEFAULT NULL,

   `product_name` varchar(255) DEFAULT NULL,

   `extend_info` varchar(255) DEFAULT NULL

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

3) Hive: user_click 用户行为日志 date分区 

user_id int

session_id string

action_time string

city_id int

product_id int

 

 

实现需求：

1) city_info ===> hive

2) product_info ===> hive

3) 三表的join 取 TOP3(按区域进行分组) 按天分区表（overwhrite覆盖今天的数据）

 

最终的统计结果字段如下：

product_id 商品ID

product_name 商品名称

area 区域

click_count 点击数/访问量

rank 排名

day 时间

====> MySQL 

day : 20180808

hive 2 mysql: delete xxx from where day='20180808'

 

id=10

id=11 

作业：

1) 导出EMP_COLUMN_SPLIT到RDBMS

2) Hive2MySQL测试