使用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