sqoop的简单使用

1. sqoop简介

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

• 工作机制

将导入或导出命令翻译成mapreduce程序来实现。在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。

• 安装

下载=================》解压========================》修改配置文件

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

export HADOOP_COMMON_HOME=/export/servers/hadoop-2.6.0-cdh5.14.0
export HADOOP_MAPRED_HOME=/export/servers/hadoop-2.6.0-cdh5.14.0
export HIVE_HOME=/export/servers/hive-1.1.0-cdh5.14.0
--还有对应的hbase

引入依赖包

sqoop的使用需要添加两个额外的依赖包，一个是mysql的驱动包，一个是java-json的的依赖包，不然就会报错
mysql-connector-java-5.1.40.jar
java-json.jar

启动验证

bin/sqoop-version

2. 数据导入

• 查看命令

  • 查看帮助

    bin/sqoop list-databases --help
    

查看所有数据库

bin/sqoop list-databa： --connect jdbc:mysql://188.131.192.45:3306/ --username root --password 111111

查看一个库中的所有表

bin/sqoop list-databa： --connect jdbc:mysql://188.131.192.45:3306/userdb --username root --password 111111

2.1 导入到HDFS

• 直接导入到HDFS

  bin/sqoop import --connect jdbc:mysql://188.131.192.45:3306/userdb --password 111111 --username root --table emp --m 1
  

• 导入时指定路径

  bin/sqoop import  
  --connect jdbc:mysql://188.131.192.45:3306/userdb 
  --username root 
  --password 111111 
  --table emp 
  --delete-target-dir 
  --target-dir /sqoop/emp --m 1
  

• 导入时指定分隔符（默认","）

  bin/sqoop import  
  --connect jdbc:mysql://188.131.192.45:3306/userdb 
  --username root 
  --password 111111 
  --table emp 
  --delete-target-dir 
  --target-dir /sqoop/emp2 --m 1 
  --fields-terminated-by '\t';
  

2.2 导入到hive

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

• 先创建表，再导入数据

  hive (default)> create database sqooptohive;
  hive (default)> use sqooptohive;
  hive (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://188.131.192.45:3306/userdb 
  --username root 
  --password 111111 
  --table emp 
  --fields-terminated-by '\001' 
  --hive-import 
  --hive-table sqooptohive.emp_hive 
  --hive-overwrite --delete-target-dir --m 1
  

• 直接导入到hive，自动创建表

  bin/sqoop import 
  --connect jdbc:mysql://188.131.192.45:3306/userdb 
  --username root 
  --password 111111 
  --table emp_conn 
  --hive-import 
  --hive-database sqooptohive -m 1;
  

2.3 导入表数据子集

bin/sqoop import \
--connect jdbc:mysql://188.131.192.45:3306/userdb \
--username root --password 111111 --table emp_add \
--delete-target-dir --target-dir /sqoop/emp_add -m 1\
--where "city = 'sec-bad'"

2.4 查询结果导入hdfs

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

bin/sqoop import \
--connect jdbc:mysql://188.131.192.45:3306/userdb --username root --password 111111 \
--query 'select phno from emp_conn where 1=1 and  $CONDITIONS'  \
--target-dir /sqoop/emp_conn \
--delete-target-dir -m 1

2.5 数据增量导入

注意：增量导入的时候，一定不能加参数–delete-target-dir否则会报错

（1）方式一（使用较少，灵活性太低）

bin/sqoop import \
--connect jdbc:mysql://188.131.192.45:3306/userdb \
--username root \
--password 111111 \
--table emp \
--incremental append \
--check-column id \
--last-value 1202  \
-m 1 \
--target-dir /sqoop/increment

（2）方式二

bin/sqoop import \
--connect jdbc:mysql://188.131.192.45:3306/userdb \
--username root \
--password 111111  \
--table emp \
--incremental append  \
--check-column id  \
--where "create_time > '2018-06-17 00:00:00' and is_delete='1' and create_time < '2018-06-17 23:59:59'" \
--target-dir /sqoop/incement2 \
--m 1

2.6 数据减量

首先，保证MySQL中的数据库删除是逻辑删除，从而将删除转化为修改，在根据更新时间，将修改后的新数据再次导入到数据仓里。（在数仓里没有删除和修改操作，因为删除和修改本身也是有原因的，这个事情也要记录）

3. 数据导出

• 导出前，目标表必须存在于目标数据库中。
• 默认操作是从将文件中的数据使用INSERT语句插入到表中
• 更新模式下，是生成UPDATE语句更新表数据

（1）创建表

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;

（2）执行导出命令

bin/sqoop export \
--connect jdbc:mysql://188.131.192.45:3306/userdb \
--username root --password 111111 \
--table emp_out \
--export-dir /sqoop/emp \
--input-fields-terminated-by ","

4. java 远程执行shell命令

为了解决上述问题，google公司给提出了对应的解决方案，开源出来了一个jar包叫做sshxcute，通过这个jar包我们可以通过java代码，非常便捷的操作我们的linux服务器了

项目地址如下：项目地址

使用说明：使用说明

（1）导入sshxcute.jar

由于这个jar包没有maven坐标，所以需要我们手动导入并添加到工程的build path当中去

（2）开发案例

	public static void main(String[] args) throws TaskExecFailException {
		ConnBean connBean = new ConnBean("192.168.75.103", "root", "102079");
		SSHExec instance = SSHExec.getInstance(connBean);
		instance.connect();
		ExecCommand execCommand = new ExecCommand("echo 'hello world'");
		instance.exec(execCommand);
		instance.disconnect();
	}