Sqoop连接mysql的参数_Sqoop使用shell命令的各种参数的配置及使用方法

1、Sqoop简介

Sqoop将用户编写的sqoop命令翻译为MR程序&＃xff0c;MR程序读取关系型数据库中的数据&＃xff0c;写入到HDFS或读取HDFS上的数据&＃xff0c;写入到关系型数据库

在MR程序中如果要读取关系型数据中的数据&＃xff0c;必须制定输入格式为DBInputformat

在MR程序中如果要向关系型数据吸入数据&＃xff0c;必须制定输出格式为DBOutputformat

Sqoop命令运行的MR程序&＃xff0c;只有Map阶段&＃xff0c;没有reduce阶段。只需要数据传输&＃xff0c;不需要对数据进行合并和排序

2、Sqoop导入数据

2.1 到入到HDFS

2.1.1 全表导入

bin/sqoop import \

##连接的关系型数据库的url,用户名&＃xff0c;密码--connect jdbc:mysql://hadoop102:3306/test \

--username root \--password 123\

##连接的表--table t_emp \

##导出数据在hdfs上存放路径--target-dir /sqoopTest \

##如果路径已存在则先删除--delete-target-dir \

##导入到Hdfs上后&＃xff0c;每个字段使用什么参数进行分割--fields-terminated-by "\t"\

##要启动几个MapTask&＃xff0c;默认4个--num-mappers 2\

##数据集根据哪个字段进行切分&＃xff0c;切分后每个MapTask负责一部分--split-by id \

##要实现部分导入&＃xff0c;加入下面的参数&＃xff0c;表示导入哪些列

##columns中如果涉及到多列&＃xff0c;用逗号分隔&＃xff0c;分隔时不要添加空格--columns id,name,age

2.1.2 使用sqoop关键字筛选查询导入数据

bin/sqoop import \--connect jdbc:mysql://hadoop102:3306/test \

--username root \--password 123\--table t_emp \

##指定过滤的where语句,where语句最好使用引号包裹--where &＃39;id>6&＃39;\--target-dir /sqoopTest \--delete-target-dir \--fields-terminated-by "\t"\--num-mappers 1\--split-by id

2.1.3 使用查询语句导入

bin/sqoop import \--connect jdbc:mysql://hadoop102:3306/test \

--username root \--password 123\

##查询语句最好使用单引号

##如果query后使用的是双引号&＃xff0c;则$CONDITIONS前必须加转移符&＃xff0c;防止shell识别为自己的变量--query &＃39;select * from t_emp where id>3 and $CONDITIONS&＃39;\--target-dir /sqoopTest \--delete-target-dir \--fields-terminated-by "\t"\--num-mappers 1\--split-by id

注意&＃xff1a;

如果使用了--query&＃xff0c;就不能指定--table&＃xff0c;和--columns和--where

--query 和 --table一定不能同时存在&＃xff01;

--where和--query同时存在时&＃xff0c;--where失效

--columns和--query同时存在时&＃xff0c;--where无效&＃xff01;

--query 必须跟--target-dir

2.2 导入到hive

bin/sqoop import \--connect jdbc:mysql://hadoop102:3306/test \

--username root \--password 123\--query &＃39;select * from t_emp where id>3 and $CONDITIONS&＃39;\--target-dir /sqoopTest \

##如果不限定分隔符&＃xff0c;那么hive存储的数据将不带分隔符&＃xff0c;之后再想操作很麻烦&＃xff0c;所以建议加上--fields-terminated-by "\t"\--delete-target-dir \

##导入到hive--hive-import \

##是否覆盖写&＃xff0c;不加这个参数就是追加写--hive-overwrite \

##指定要导入的hive的表名--hive-table t_emp \--num-mappers 1\--split-by id

原理还是分俩步&＃xff1a;先把数据从关系数据库里导到hdfs中&＃xff0c;然后再从hdfs中导到hive中&＃xff0c;此时hdfs中的文件会被删除

注意&＃xff1a;如果hive中没表会自动创建表&＃xff0c;但是类型是自动生成的&＃xff0c;所以还是建议手动创建

也可以分俩步走&＃xff1a;

(1)先导入hdfs

#!/bin/bash

import_data(){

$sqoop import \--connect jdbc:mysql://hadoop102:3306/gmall \

--username root \--password 123\--target-dir /origin_data/gmall/db/$1/$do_date \--delete-target-dir \--query "$2 and \$CONDITIONS"\--num-mappers 1\--fields-terminated-by &＃39;\t&＃39;\

# 使用压缩&＃xff0c;和指定压缩格式为lzop--compress \--compression-codec lzop \

#将String类型和非String类型的空值替换为\N,方便Hive读取--null-string &＃39;\\N&＃39;\--null-non-string &＃39;\\N&＃39;}

(2)利用  load data  命令导入hive

注意&＃xff1a;

Hive中的Null在底层是以“\N”来存储&＃xff0c;而MySQL中的Null在底层就是Null&＃xff0c;为了保证数据两端的一致性。

在导出数据时采用--input-null-string和--input-null-non-string两个参数。

导入数据时采用--null-string和--null-non-string。

2.3 导入到HBase

bin/sqoop import \--connect jdbc:mysql://hadoop102:3306/test \

--username root \--password 123\--query &＃39;select * from t_emp where id>3 and $CONDITIONS&＃39;\--target-dir /sqoopTest \--delete-target-dir \

##表不存在是否创建--hbase-create-table \

##hbase中的表名--hbase-table "t_emp"\

##将导入数据的哪一列作为rowkey--hbase-row-key "id"\

##导入的列族--column-family "info"\--num-mappers 2\--split-by id

当选用自动创建表时&＃xff0c;如果版本不兼容会报错&＃xff1a;

20/03/24 13:51:24 INFO mapreduce.HBaseImportJob: Creating missing HBase table t_emp

Exception in thread "main" java.lang.NoSuchMethodError: org.apache.hadoop.hbase.HTableDescriptor.addFamily(Lorg/apache/hadoop/hbase/HColumnDescriptor;)V

此时只能自己手动创建或者可以重新编译sqoop源码

如果要多列族导入&＃xff0c;只能多次运行命令&＃xff0c;一次导入一个列族

3、导出

3.1 表为空表时

bin/sqoop export \--connect &＃39;jdbc:mysql://hadoop102:3306/test?useUnicode&＃61;true&characterEncoding&＃61;utf-8&＃39;\--username root \--password 123\

##导出的表名&＃xff0c;需要自己提前创建好--table t_emp2 \--num-mappers 1\

##hdfs上导出的数据的路径--export-dir /user/hive/warehouse/t_emp \

##hdfs上数据的分隔符--input-fields-terminated-by "\t"

3.2 表不为空时

如果插入的数据的主键和表中已有数据的主键冲突&＃xff0c;那么会报错Duplicate entry &＃39;5&＃39; for key &＃39;PRIMARY&＃39;

3.2.1 在mysql中

INSERT INTO t_emp2 VALUE(5,&＃39;jack&＃39;,30,3,1111)

ON DUPLICATE KEY UPDATE NAME&＃61;VALUES(NAME),deptid&＃61;VALUES(deptid),

empno&＃61;VALUES(empno);

指定当插入时&＃xff0c;主键重复时时&＃xff0c;对于重复的记录&＃xff0c;只做更新&＃xff0c;不做插入&＃xff01;

而用sqoop时&＃xff0c;则可以启用以下俩种模式

3.2.2 updateonly模式

bin/sqoop export \--connect &＃39;jdbc:mysql://hadoop103:3306/mydb?useUnicode&＃61;true&characterEncoding&＃61;utf-8&＃39;\--username root \--password 123456\--table t_emp2 \--num-mappers 1\--export-dir /hive/t_emp \--input-fields-terminated-by "\t"\--update-key id

利用 --update-key 字段  &＃xff0c;表示主键重复时会进行更新&＃xff0c;但是主键不重复的时候&＃xff0c;数据不会插入进来

3.2.3 allowinsert模式

bin/sqoop export \--connect &＃39;jdbc:mysql://hadoop103:3306/mydb?useUnicode&＃61;true&characterEncoding&＃61;utf-8&＃39;\--username root \--password 123456\--table t_emp2 \--num-mappers 1\--export-dir /hive/t_emp \--input-fields-terminated-by "\t"\--update-key id \--update-mode allowinsert

表示主键重复时会进行更新&＃xff0c;主键不重复的时候&＃xff0c;数据也会插入进来

3.3 查看导出命令的具体实现

3.3.1配置/etc/my.cnf

[mysqld]

#开启binlog日志功能

log-bin&＃61;mysql-bin

3.3.2重启mysql服务

3.3.3进入/var/lib/mysql&＃xff0c;调用方法

sudo mysqlbinlog mysql-bin.000001