sqoop

sqoop
使用 sqoop help 来查看，sqoop 支持哪些命令
然后得到这些支持了的命令之后，如果不知道使用方式，可以使用 sqoop command 的方式 来查看某条具体命令的使用方式，比如:sqoop help import
列出MySQL数据有哪些数据库:
[hadoop@hadoop3 ~]$ sqoop list-databases \

–connect jdbc:mysql://hadoop1:3306/
–username root
–password root

–null-string ‘\N’ --null-non-string ‘\N’ \ 防止mysql中的null值到hive中变null字符串
列出MySQL中的某个数据库有哪些数据表：
[hadoop@hadoop3 ~]$ sqoop list-tables \

–connect jdbc:mysql://hadoop1:3306/mysql
–username root
–password root

创建一张跟mysql中的help_keyword表一样的hive表hk：
sqoop create-hive-table
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–hive-table keepa.hkkj
注意 hive-table 后面只有表名，表建默认库default，mysql.hk才是指定库

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

常用参数
–connect jdbc 连接地址
–connection-manager 连接管理者
–driver 驱动类
–hadoop-mapred-home

$HADOOP_MAPRED_HOME
–help help 信息
-P 从命令行输入密码
–password 密码
–username 账号
–verbose 打印流程信息
–connection-param-file 可选参数

普通导入：导入mysql库中的help_keyword的数据到HDFS上

导入的默认路径：/user/root/saihe_category

hdfs dfs -cat /user/root/saihe_category/part-m-00000查看hdfs上的数据
hadoop fs -cat /user/root/saihe_category/part-m-00000查看hdfs上的数据

hdfs dfs -ls /user/root/saihe_category/ 查看目录下文件
hadoop fs -ls /user/root/saihe_category/ 查看目录下文件

Found 2 items
-rw-r–r-- 2 root hdfs 0 2021-08-30 17:40 /user/root/saihe_category/_SUCCESS
-rw-r–r-- 2 root hdfs 11817389 2021-08-30 17:40 /user/root/saihe_category/part-m-00000
hdfs dfs -rm /user/root/saihe_category/part-m-00000 删除hdfs文件
hdfs dfs -rm -r /user/root/saihe_category/ 删除hdfs目录

sqoop import -D mapred.job.queue.name=usershell \ 指定队列导出文件
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
-m 1

导入： 指定分隔符和导入路径

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–target-dir /apps/hive/warehouse/hot_words_view.db/saihe_category
–fields-terminated-by ‘\t’
-m 2 开了两个map，加快传输速度，不进行合并，会占用内存
一个块128m，占用两个块，只使用了11m多
使用hdfs dfs -cat /apps/hive/warehouse/hot_words_view.db/saihe_category/part-m* | hdfs dfs -copyFromLocal - /apps/hive/warehouse/hot_words_view.db/saihe_category/part-m-00002 合并小文件，然后删除小文件，释放空间

删除文件夹
hadoop fs -rm /apps/hive/warehouse/hot_words_view.db/saihe_category

导入数据：带where条件
sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–where “id IN (109486)”
–table saihe_category
–target-dir /apps/hive/warehouse/hot_words_view.db/saihe_category
-m 1

导入：指定自定义查询SQL
sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–target-dir /apps/hive/warehouse/hot_words_view.db/saihe_category
–query ‘select BrowseNodeNameCn,id,TrueBrowseNodeName from develop.saihe_category where $CONDITIONS and TrueBrowseNodeName = “Categories”’
–split-by id
–fields-terminated-by ‘\t’
-m 4
关于sqoop的导入时候,默认导入后的文件的分割符为’ , ’
如果想要指定分割符需要使用–fields-terminated-by “\t”,代表以"\t"作为导入后文件的分隔符

在以上需要按照自定义SQL语句导出数据到HDFS的情况下：
1、引号问题，要么外层使用单引号，内层使用双引号，$CONDITIONS的$符号不用转义， 要么外层使用双引号，那么内层使用单引号，然后$CONDITIONS的$符号需要转义
2、自定义的SQL语句中必须带有WHERE $CONDITIONS

m和–split-by参数一般是放在一起使用，-m:表明需要使用几个map任务并发执行，–split-by :拆分数据的字段. -m设置为4,数据有100条,sqoop首先会获取拆分字段的最大值,最小值,步长为100/4=25;
当m不等于1时，split-by拆分字段默认为主键，拆分字段的数据类型最好为int,如果不是则将-m设置为1,split-by不设置，拆分字段的值最好分布均匀,否则会造成数据倾斜的问题。
split-by不设置，即只有一个map运行，缺点是不能并行map录入数据。（注意，当-m 设置的值大于1时，split-by必须设置字段）

假设有一张表test，sqoop命令中–split-by ‘id’，-m 10，会发生怎样奇特的事情。首先呢，sqoop会去查表的元数据等等，重点说一下sqoop是如何根据–split-by进行分区的。首先sqoop会向关系型数据库比如mysql发送一个命令:select max(id),min(id) from test。然后会把max、min之间的区间平均分为10分，最后10个并行的map去找数据库，注意点：–split-by对非数字类型的字段支持不好。一般用于主键及数字类型的字段。
hdfs dfs -cat /apps/hive/warehouse/hot_words_view.db/saihe_category/part-m* 查看数据

2、把MySQL数据库中的表数据导入到Hive中
Sqoop 导入关系型数据到 hive 的过程是先导入到 hdfs，然后再 load 进入 hive
普通导入：数据存储在默认的default hive库中，表名就是对应的mysql的表名：

第一步：导入develop.saihe_category的数据到hdfs的路径/apps/hive/warehouse/keepa.db
sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–target-dir /apps/hive/warehouse/keepa.db/saihe_category
–fields-terminated-by ‘\t’
-m 1
第二步：自动仿造develop.saihe_category去创建一张hive表, 创建在keepa库中
*sqoop create-hive-table
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–hive-table keepa.hkkj *
查看数据
hadoop fs -cat /apps/hive/warehouse/keepa.db/saihe_category/part-m-00000
由于仿造建表没有指定分隔符，导入会失败
手动建表
CREATE TABLE keepa.hkkj(
id int,
browsenodeid string,
browsenodenamecn string,
truebrowsenodename string,
browsenodename string,
browsepathbyname string,
level int,
parent_id string,
country_code string,
browsepathbyid string,
is_last int)
row format delimited fields terminated by ‘\t’; 指定分隔符\t
第三步：把临时目录中的数据导入到hive表中
hive中执行set tez.queue.name=usershell; 指定队列
load data inpath ‘/apps/hive/warehouse/keepa.db/saihe_category/part-m-00000’ OVERWRITE into table keepa.hkkj 将hdfs数据插入表中。
执行sql查看数据select * from keepa.hkkj limit 10;
linux中执行hdfs dfs -rm -r /apps/hive/warehouse/keepa.db/hkkj/part-m-00000;后表keepa.hkkj无数据。

指定行分隔符和列分隔符，指定hive-import，指定覆盖导入，指定自动创建hive表，指定表名，指定删除中间结果数据目录
sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–fields-terminated-by “\t”
–lines-terminated-by “\n”
–hive-import
–hive-overwrite
–create-hive-table
–delete-target-dir
–hive-database keepa
–hive-table hhhk

hdfs dfs -cat /apps/hive/warehouse/keepa.db/hhhk/part-m* | hdfs dfs -copyFromLocal - /apps/hive/warehouse/keepa.db/hhhk/part-m-00004 文件合并；删除小文件
hdfs dfs -rm -r /apps/hive/warehouse/keepa.db/hhhk/part-m-00000、1、2、3
select * from keepa.hhhk limit 10;查看数据

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://192.168.4.249:3306/develop
–username root
–password root1234
–table saihe_category
–fields-terminated-by “\t”
–lines-terminated-by “\n”
–hive-import
–hive-overwrite
–create-hive-table
–delete-target-dir
–hive-database keepa
–hive-table hhhk
–m 1
设置m为1 ，只生成一个文件块

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–fields-terminated-by “\t”
–lines-terminated-by “\n”
–hive-import
–hive-overwrite
–create-hive-table
–delete-target-dir
–hive-table keepa.hhhk
可以将数据库和表放一起，效果一样

hdfs dfs -rm -r /apps/hive/warehouse/keepa.db/hhhk删除hive数据，select表无数据

增量导入
执行增量导入之前，先清空hive数据库中的keepa.hhhk表中的数据
truncate table keepa.hhhk;

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–target-dir /apps/hive/warehouse/keepa.db/myimport_add
–incremental append
–check-column id
–last-value 109484
-m 1

load data inpath ‘/apps/hive/warehouse/keepa.db/myimport_add/part-m-00000’ OVERWRITE into table keepa.hhhk导入数据到表

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–target-dir /apps/hive/warehouse/keepa.db/hhhk/myimport_add
–incremental append
–check-column id
–last-value 109484
–fields-terminated-by “\t”
-m 1

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://hadoop1:3306/develop
–username root
–password root1234
–table saihe_category
–target-dir /apps/hive/warehouse/keepa.db/hhhk
–incremental append
–check-column id
–last-value 109484
–fields-terminated-by “\t”
–null-string ‘\N’ --null-non-string ‘\N’ \ 防止mysql中的null值到hive中变null字符串
-m 1

结果如下


指定分隔符，不容易出错，负责读取数据会出现空

全量 --delete-target-dir 会删除全部数据

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://192.168.0.54:3306/finance
–username root --password password
–target-dir /apps/hive/warehouse/report_ods.db/amazon_settlement_report -m 4 --delete-target-dir
–query ‘select * from finance.amazon_settlement_report where 1=1 and $CONDITIONS’
–split-by id --fields-terminated-by ‘,’
–null-string ‘\N’ --null-non-string ‘\N’ 防止mysql中的null值到hive中变null字符串

增量 --incremental append 没有delete-target-dir

sqoop import -D mapred.job.queue.name=qxupdate
–connect jdbc:mysql://192.168.0.54:3306/finance
–username root --password password
–target-dir /apps/hive/warehouse/report_ods.db/amazon_settlement_report -m 4
–query ‘select * from finance.amazon_settlement_report where store_id=2256 and $CONDITIONS’
–split-by id --fields-terminated-by ‘\t’
–incremental append
–check-column posted_date_time
–null-string ‘\N’ --null-non-string ‘\N’ \ 防止mysql中的null值到hive中变null字符串
–last-value ‘2021-04-15 00:00:00’

The file that you are trying to load does not match the file format of the destination table.
Hive 3.x系列不支持load data 命令向内部表导入数据
解决办法
1.创建外部表
2.创建临时表再用 select 方式导入