使用sqoop从MySQL导入数据到hdfs

使用sqoop从MySQL导入数据到hdfs

1、代码解析：

bin/sqoop import --connect jdbc:mysql://hadoop102:3306/ \【此处为换行符，必须是空格加\】
book【数据库名】 --username root --password 123

–table orders【表名】

–columns id,order_id,order_time \【所需的列名】
–where “order_id >= 1 and order_id<=20” \【所需要的行数】

上面三行可以合并：
–query "select order_id,order_id,order_time from orders where order_id >= 1 and order_id<=20 and \KaTeX parse error: Undefined control sequence: \ at position 13: CONDITIONS" \̲ ̲[CONDITIONS占位符，…前面的\是因为双引号引起的错误解析，单引号没有这样的问题

2、数据到哪里去
–target-dir /user
–delete-target-dir
[如果路径存在就删除，保证数据可同步执行]
–fields-terminated-by ‘\t’
[指定分隔符]
–num-mappers 2
[map的个数,默认4]
–split-by order_id
[切片的操作]
[最后一个操作不要换行符]
[在sqoop根目录执行]

最后

2、最终代码：

bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select order_id,order_time from orders
where order_id >= 1 and order_id<=201727433 and $CONDITIONS”
–target-dir /user/sql-hdfs/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 6
–split-by order_id

3、执行

创建文件：vim mysql_to_hdfs.sh，将自己的代码复制到这里面，形成执行脚本

授权文件：chmod -x mysql_to_hdfs.sh

执行文件：sh mysql_to_hdfs.sh

4、网页端查看

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1Ww5fAiY-1634129156690)(D:\Software\笔记\Typora\note\picture\image-20211013202852169.png)]

从MySQL上传数据到hdfs大功告成

5、我的项目代码（没有意义，感兴趣可以看）

– 从mysql上传到hdfs

– books表
bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select * from books
where $CONDITIONS”
–target-dir /user/sql-hdfs/books/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 1

– ip表
bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select * from ip
where $CONDITIONS”
–target-dir /user/sql-hdfs/ip/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 1

– logistics表
bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select * from logistics
where $CONDITIONS”
–target-dir /user/sql-hdfs/logistics/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 1

– mobile表
bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select * from mobile
where $CONDITIONS”
–target-dir /user/sql-hdfs/mobile/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 1

– orders表
bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select * from orders
where $CONDITIONS”
–target-dir /user/sql-hdfs/orders/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 1

– refunds表
bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select * from refunds
where $CONDITIONS”
–target-dir /user/sql-hdfs/refunds/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 1

– users表
bin/sqoop import
–connect jdbc:mysql://hadoop102:3306/book
–username root
–password 123
–query “select * from users
where $CONDITIONS”
–target-dir /user/sql-hdfs/users/
–delete-target-dir
–fields-terminated-by ‘\t’
–num-mappers 1

• from users
  where $CONDITIONS"
  –target-dir /user/sql-hdfs/users/
  –delete-target-dir
  –fields-terminated-by ‘\t’
  –num-mappers 1