sqoop面试理论和应用

理论

sqoop是什么

sqoop是apache旗下一款“Hadoop和关系数据库服务器之间传送数据”的工具。

将大数据平台数据导出到mysql可以用什么工具？

sqoop。

Sqoop底层运行的任务是什么？

只有Map阶段，没有Reduce阶段的任务。

qoop数据导出的时候一次执行多长时间？

Sqoop任务5分钟-2个小时的都有。取决于数据量。

数据量和集群的规模 数据量多 集群机器多也快

Sqoop导入使用什么语法？

sqoop import

Sqoop导出使用什么语法？

sqoop export

大数据平台和业务平台数据来回倒
准确点就是Hadoop里的和mysql里的

sqoop数据导入

以下例子用的mysql里自带的mysql库、user表

“导入工具”导入单个表从RDBMS到HDFS。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）

命令行查看帮助

bin/sqoop list-databases --help

列出win7主机所有的数据库

bin/sqoop list-databases --connect jdbc:mysql://192.168.72.130:3306?serverTimezone=GMT%2B8 --username root --password My12345!

查看某一个数据库下面的所有数据表(hive库)

bin/sqoop list-tables --connect jdbc:mysql://192.168.72.130:3306/hive?serverTimezone=GMT%2B8 --username root --password My12345!

数据库数据导入到hdfs

下面的命令用于从MySQL数据库服务器中的user表导入HDFS。

从mysql里的mysql库里 把user表导入到hdfs里

默认目录：/user/root/user

bin/sqoop import --connect jdbc:mysql://192.168.72.130:3306/mysql  --username root --password My12345! --table user --m 1

为了验证在HDFS导入的数据，请使用以下命令查看导入的数据

hdfs  dfs  -ls  /user/root/user

hdfs dfs -text /user/root/user/part-m-00000

导入到HDFS指定目录

在导入表数据到HDFS使用Sqoop导入工具，我们可以指定目标目录。

使用参数 --target-dir来指定导出目的地，

使用参数 —delete-target-dir来判断导出目录是否存在，如果存在就删掉

bin/sqoop import  --connect jdbc:mysql://192.168.72.130:3306/mysql?serverTimezone=GMT%2B8 --username root --password My12345! --delete-target-dir --table user  --target-dir /sqoop/user --m 1

查看导出的数据

hdfs dfs -text /sqoop/user/part-m-00000

它会用逗号（，）分隔表的数据和字段。

导入到hdfs指定目录并指定字段之间的分隔符

bin/sqoop import  --connect jdbc:mysql://192.168.72.130:3306/mysql?serverTimezone=GMT%2B8 --username root --password My12345! --delete-target-dir --table user  --target-dir /sqoop/user2 --m 1 --fields-terminated-by '\t'

查看文件内容

hdfs dfs -text /sqoop/user2/part-m-00000

导入关系表到HIVE

第一步：拷贝jar包

将我们mysql表当中的数据直接导入到hive表中的话，我们需要将hive的一个叫做hive-exec-1.1.0-cdh5.14.0.jar的jar包拷贝到sqoop的lib目录下

cp /export/servers/hive-1.1.0-cdh5.14.0/lib/hive-exec-1.1.0-cdh5.14.0.jar /export/servers/sqoop-1.4.6-cdh5.14.0/lib/

第二步：准备hive数据库与表

将我们mysql当中的数据导入到hive表当中来

hive 创建表

create database sqooptohive;
use sqooptohive;
create external table emp_hive(id int,name string,deg string,salary int ,dept string) row format delimited fields terminated by '\001';

第三步：开始导入

bin/sqoop import --connect jdbc:mysql://192.168.72.130:3306/userdb?serverTimezone=GMT%2B8 --username root --password My12345! --table emp --fields-terminated-by '\001' --hive-import --hive-table sqooptohive.emp_hive --hive-overwrite --delete-target-dir --m 1

第四步：hive表数据查看

select * from emp_hive;

导入关系表到hive并自动创建hive表

我们也可以通过命令来将我们的mysql的表直接导入到hive表当中去

bin/sqoop import --connect jdbc:mysql://192.168.72.1:3306/userdb?serverTimezone=GMT%2B8 --username root --password My12345! --table emp --hive-import -m 1 --hive-database sqooptohive;

通过这个命令，我们可以直接将我们mysql表当中的数据以及表结构一起倒入到hive当中去

导入表数据子集

我们可以导入表的使用Sqoop导入工具，"where"子句的一个子集。它执行在各自的数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。
where子句的语法如下。

–where

按照条件进行查找，通过—where参数来查找表emp_add当中city字段的值为sec-bad的所有数据导入到hdfs上面去

bin/sqoop import \
--connect jdbc:mysql://192.168.72.1:3306/userdb?serverTimezone=GMT%2B8 \
--username root --password My12345! --table emp \
--target-dir /sqoop/emp_add -m 1  --delete-target-dir \
--where "id=1"

sql语句查找导入hdfs

我们还可以通过 –query参数来指定我们的sql语句，通过sql语句来过滤我们的数据进行导入

bin/sqoop import \
--connect jdbc:mysql://192.168.72.130:3306/userdb?serverTimezone=GMT%2B8 --username root --password My12345! \
--delete-target-dir -m 1 \
--query 'select name from emp where 1=1 and  $CONDITIONS' \
--target-dir /sqoop/emp_conn

查看hdfs数据内容

hdfs dfs -text /sqoop/emp_conn/part*

增量导入 (全量导入 增量导入)

Sqoop的数据导出

1、将数据从HDFS把文件导出到RDBMS数据库
导出前，目标表必须存在于目标数据库中。

• 默认操作是从将文件中的数据使用INSERT语句插入到表中
• 更新模式下，是生成UPDATE语句更新表数据

hdfs导出到mysql

数据是在HDFS当中的如下目录/sqoop/emp，数据内容如下

1201,gopal,manager,50000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1202,manisha,Proof reader,50000,TP,2018-06-15 18:54:32.0,2018-06-17 20:26:08.0,1
1203,khalil,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1
1204,prasanth,php dev,30000,AC,2018-06-17 18:54:32.0,2018-06-17 21:05:52.0,0
1205,kranthi,admin,20000,TP,2018-06-17 18:54:32.0,2018-06-17 18:54:32.0,1

第一步：创建mysql表

CREATE TABLE `emp_out` (
  `id` INT(11) DEFAULT NULL,
  `name` VARCHAR(100) DEFAULT NULL,
  `deg` VARCHAR(100) DEFAULT NULL,
  `salary` INT(11) DEFAULT NULL,
  `dept` VARCHAR(10) DEFAULT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_delete` BIGINT(20) DEFAULT '1'
) ENGINE=INNODB DEFAULT CHARSET=utf8;

第二步：执行导出命令

通过export来实现数据的导出，将hdfs的数据导出到mysql当中去

bin/sqoop export \
--connect jdbc:mysql://192.168.72.1:3306/test?serverTimezone=GMT%2B8userdb \
--username root --password admin \
--table emp_out \
--export-dir /sqoop/emp \
--input-fields-terminated-by ","

第三步：验证mysql表数据

select * from emp_out