Sqoop下载安装及使用教程

老马聊技术

已于 2024-05-18 20:33:37 修改

阅读量989

点赞数 19

分类专栏：大数据文章标签： sqoop 大数据

于 2024-03-14 19:04:13 首次发布

本文链接：https://blog.csdn.net/majingbobo/article/details/136577707

版权

大数据专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1. Sqoop 简介

Sqoop(发音：skup)是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql...)间进行数据的传递，可以将一个关系型数据库（例如： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。
Sqoop项目开始于2009年，最早是作为Hadoop的一个第三方模块存在，后来为了让使用者能够快速部署，也为了让开发人员能够更快速的迭代开发，Sqoop独立成为一个Apache项目。

2. 官网学习

2.1 sqoop 官网：Sqoop -

2.2 Sqoop 两个大的版本

Sqoop1 和 Sqoop2

通过官网的解读：目前一直在使用sqoop1，最新的1.4.7 版本，而Sqoop2 的版本则是 1.99.7，此

版本与 1.4.7 版本是不兼容的，而且 sqoop2 只是测试使用，不计划使用在生产环境下，具体官方的解释如下图所示：

2.3 原理

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

2.4 与Flume的区别

2.4.1 flume 是进行文件采集，可以将文件采集到hdfs中

2.4.2 sqoop 是对关系型数据库进行操作，可以实现关系型数据库与hdfs的数据交互

2.5 Sqoop 架构图

3. Sqoop 下载与安装

前提：必须安装Hadoop

3.1 进入官网，官网中有两个位置可以进入sqoop下载的页面，如下图所示

进入到 apache sqoop 的官方页面，点击downloads后面的下载链接

选择所下载的版本，例如：当前下载1.4.7 linux 版本

下载之后的文件，如下：

3.2 安装与配置

3.2.1 将sqoop 安装文件上传到 /usr/local/software 目录下

3.2.2 解压此文件

tar -zxvf sqoop-1.4.7.bin_hadoop-2.6.0.tar.gz

3.2.3 将解压之后的文件重命名

mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop

3.2.4 配置环境变量

export SQOOP_HOME=/usr/local/software/sqoop

export PATH=$SQOOP_HOME/bin:$PATH

3.2.5 使配置文件生效

source /etc/profile.d/my-env.sh

测试： sqoop version

注意：sqoop不需要启动服务，使用的时候在启动即可。

3.3 Sqoop 常用命令解释

3.3.1 执行 sqoop help 查看常用命令

3.3.2 命令解释

4. Sqoop链接MySQL数据库配置

4.1 需要将 mysql依赖包与commons-lang包放到sqoop/lib目录下

存放的依赖如下：

注意：1. mysql的依赖包必须与所连接的数据库版本相同

2. commons-lang 不能使用 commons-lang3，建议使用commons-lang-2.6.jar

3. 依赖包可以从这里下载：https://mvnrepository.com/

4.2 查看MySQL中所有的数据库

格式：sqoop list-databases -username 用户名 -password '密码' --connect jdbc:mysql://IP地址:端口号?characterEncoding=UTF-8

例如：

sqoop list-databases -username root -password '123456' --connect jdbc:mysql://192.168.170.100:3306?characterEncoding=UTF-8

4.3 查看MySQL数据库中指定数据库中所有的表

格式：sqoop list-tables -username 用户名 -password '密码' --connect jdbc:mysql://IP地址:端口号/数据库名?characterEncoding=UTF-8

例如：

sqoop list-tables -username root -password '123456' --connect jdbc:mysql://192.168.170.100:3306/shoppingdb?characterEncoding=UTF-8

4.4 eval 命令测试

4.4.1 创建sqoop_db 数据库，在数据库中创建表t_product，将数据保存到t_product表中

create database sqoop_db default charset utf8 collate utf8_general_ci;

将 product.sql 文件导入到 sqoop_db数据库中

mysql -uroot -p sqoop_db < /usr/local/data/product.sql

备注：product.sql 内容，请查看 “资源绑定”文件

4.4.2 eval 操作

sqoop eval \

--connect jdbc:mysql://192.168.170.100:3306/sqoop_db \

--username root \

--password 123456 \

--query 'select * from t_product limit 10'

5. 数据导入之 (从 MySQL 到 HDFS)

5.1 使用到HDFS 需要在 Sqoop的配置文件中配置hadoop的相关内容：

进入到 sqoop/conf目录下，复制sqoop-env-template.sh 为：sqoop-env.sh

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

主要修改：HADOOP_COMMON_HOME= 与 HADOOP_MAPRED_HOME= 修改为hadoop的安装目录，如下：

5.2 数据导入：mysql=》hdfs，使用关键字 import

5.2.1 语法

sqoop import \
--connect 数据库链接 \
--username 数据库用户名 \
--password 数据库密码 \
--table 表名 \
--target-dir HDFS位置 \
--delete-target-dir \
--fields-terminated-by "\t" \
--num-mappers 3 \
--split-by 切分数据依据 \
--query 'SQL语句'

5.2.2 将指定表(t_product)的全部数据导入到hdfs中

查看 hdfs中的内容：

5.2.3 将指定表(t_product)的数据按条件查询的结果导入到hdfs中

查看hdfs中的内容

注意：1. 如果查询中有where条件，则条件后必须加上 $CONDITIONS 关键字

2. 当sqoop使用--query+sql执行多个maptask并行运行导入数据时，每个maptask将执行一部分数据的导入，原始数据需要使用“-split-by 某个字段来切分数据，不同的数据交给不同的maptask去处理。maptask执行sql副本时，需要在where条件中添加$CONDITIONS条件，这个是linux系统的变量，可以根据sqoop对边界条件的判断来替换成不同的值，这就是说若split-byid，则sqoop会判断id的最小值和最大值判断id的整体区间，然后根据maptask的个数来进行区间拆分，每个maptask执行一定id区间范围的数值导入任务。

6. 数据导入 Hive 之（从 MySQL 到 Hive）

准备：hadoop与hive需要启动

启动hadoop：start-dfs.sh start-yarn.sh

启动hive：可以先执行schematool -initSchema -dbType mysql（是否需要执行，看看对应的mysql中是否有hive数据库），在执行：hive --service metastore & hive --service hiveserver2 &

6.1 分两种类型：全量导入与增量导入

全量导入：将mysql表里的数据全部导入到hive表里

增量导入：每次将mysql表里的一部分数据导入到hive表里

6.2 使用到Hive 需要在 Sqoop的配置文件中配置Hive的相关内容：

进入到 sqoop/conf目录下，复制sqoop-env-template.sh 为：sqoop-env.sh

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

主要修改：HIVE_HOME= 修改为hive的安装目录，如下

6.2.1 需要将hive下的 hive-common-3.1.3.jar 上传到 sqoop的lib目录下（待测：有可能还需要上传hive-exec-*.jar，有可能还需要将HBase的环境变量配置去掉）

将hadoop下的hadoop-common-xx.jar 上传道sqoop的lib目录下

6.3 数据导入：mysql=》hive，使用关键字 import

6.3.1 全量：案例：将MySQL中Sqoop_db数据库中的t_product表中的数据导入到hive中

注意：需要先再hive中创建出laoma 此数据库

sqoop import \

--connect jdbc:mysql://192.168.170.100:3306/sqoop_db \

--username root \

--password 123456 \

--table t_product \

--num-mappers 1 \

--delete-target-dir \

--hive-import \ 这表示全量导入

--fields-terminated-by "\001" \

--hive-overwrite \

--hive-table laoma.t_product

上面过程分为两部分：

1) 第一步将数据导入到HDFS,默认的临时目录是 /user/当前操作用户/mysql表名

2）第二步将导入到HDFS的数据迁移到Hive表，如果hive表不存在，sqoop会自动创建内部表

6.3.2 查看上传的内容

6.3.2.1 先查看hdfs中的内容

6.3.2.2 查看hive中的内容

6.4 增量数据导入

7. 数据导入 HBase 之（从 MySQL 到 HBase）

前提：安装hadoop 与 hbase

start-hdfs.sh start-yarn.sh start-hbase.sh

注入：如果hbase使用到了zookeeper的集群，就需要开启zookeeper服务(或者将hbase配置文件中配置是否使用外部zookeeper进行修改)

zkServer.sh start

7.1 在hbase中先创建一个对用的命名空间：

例如：create_namespace 'laoma'

[root@hadoop101 /]# sqoop import \
> --connect jdbc:mysql://192.168.170.100:3306/sqoop_db \
> --username root \
> --password 123456 \
> --table t_product \
> --hbase-create-table \ 如果hbase中没有对应的表，则创建
> --hbase-table laoma:t_product \
> --column-family cf \ 指定列簇，表中所有的字段除id之外都放到此列簇下
> --hbase-row-key id 要求mysql表必须有主键，将主键作为rowkey 标识一行

8. 数据导出之（从HDFS 到 MySQL）

8.1 说明

将数据导出到MySQL，目前支持两种形式从HDFS导出到MySQL中，再有就是从Hive导出到 MySQL中。HBase是不支持直接往MySQL中导入数据的，如果想要把HBase的数据导出到MySQL中，那就必须先将HBase的数据导出到Hive中，然后再从Hive导出到MySQL中很麻烦。

8.2 准备

8.2.1 在HDFS中准备数据，在MySQL中创建出对应的表

启动 Hadoop 服务器

8.2.2 准备data.txt 数据，将文件上传到HDFS 的 /data 目录下

10001,刘备,53

10002,关羽,42

10003,张飞,35

10004,赵云,33

10005,马超,38

10006,黄忠,70

数据库创建表结构：create table t_user(id int,name varchar(10),age int)

8.3 数据导出

sqoop export \
> --connect jdbc:mysql://192.168.170.100:3306/sqoop_db \
> --username root \
> --password 123456 \
> --export-dir /data/data.txt \ 指定hdfs中数据的位置
> --table t_user \ 指定mysql中对应的表名
> --num-mappers 1 \
> --input-fields-terminated-by ',' hdfs中文件数据的分隔符

8.3.1 查看数据库表里面是否有数据

8.4 数据格式 json格式数据的导出

8.4.1 数据内容：

{"rank":1,"title":"《逃出大英博物馆》第二集","dzl":"77.8","bfl":"523.9","zfl":"39000","type":"影视","time":"3-5"}
{"rank":2,"title":"兄弟们这一期真的要看到最后！","dzl":"89.6","bfl":"636.1","zfl":"4246","type":"搞笑","time":"3-5"}
{"rank":3,"title":"全世界学生都痛恨的一种教育方式","dzl":"27.3","bfl":"313.6","zfl":"13000","type":"搞笑","time":"5-8"}
{"rank":4,"title":"这是我玩过最抽象的宝可梦了3","dzl":"29.7","bfl":"193.6","zfl":"4631","type":"搞笑","time":"10+"}
{"rank":5,"title":"我们又又又改造公司了","dzl":"25.0","bfl":"269.7","zfl":"3084","type":"搞笑","time":"10+"}
{"rank":6,"title":"我在尝试当一种很新的美食博主","dzl":"48.0","bfl":"258.7","zfl":"6596","type":"美食","time":"0-3"}
{"rank":100,"title":"很听劝我和男装的自己搞在一起","dzl":"18.7","bfl":"87.8","zfl":"4077","type":"搞笑","time":"0-3"}
{"rank":99,"title":"探秘中国最贵的面！70只大闸蟹做一碗面！到底什么味道？","dzl":"8.8","bfl":"88.2","zfl":"4527","type":"美食","time":"5-8"}
{"rank":7,"title":"土布","dzl":"26.4","bfl":"224.8","zfl":"3291","type":"生活","time":"5-8"}
{"rank":34,"title":"我的工作周报vs我实际的工作","dzl":"18.2","bfl":"162.6","zfl":"21000","type":"生活","time":"3-5"}
{"rank":8,"title":"麒麟9000S性能分析：华为Mate60 Pro能效如何？","dzl":"18.7","bfl":"151.7","zfl":"34000","type":"知识","time":"10+"}
{"rank":98,"title":"Time Vent！十秒带你穿越2023-2009！感受令和与新平成假面骑士的力量叭！","dzl":"14.4","bfl":"124.3","zfl":"6918","type":"动画","time":"0-3"}
{"rank":35,"title":"魔术师的三个阶段","dzl":"12.2","bfl":"180.0","zfl":"3841","type":"影视","time":"3-5"}
{"rank":9,"title":"高斯一败涂地","dzl":"21.3","bfl":"181.3","zfl":"3424","type":"搞笑","time":"10+"}
{"rank":97,"title":"千匹之战！中国车VS特斯拉！","dzl":"7.2","bfl":"117.4","zfl":"15000","type":"知识","time":"10+"}
{"rank":96,"title":"真实事件改编没想到一个综艺节目这么敢拍孩子需要被改造吗？","dzl":"11.2","bfl":"441.4","zfl":"1640","type":"影视","time":"5-8"}
{"rank":36,"title":"哈哈哈哈哈谁是卧底音乐版","dzl":"14.2","bfl":"232.9","zfl":"16000","type":"生活","time":"0-3"}
{"rank":10,"title":"偷子","dzl":"58.1","bfl":"699.6","zfl":"20000","type":"搞笑","time":"10+"}
{"rank":37,"title":"感谢还有读书这条路能让我摆脱这样的原生家庭","dzl":"11.2","bfl":"162.4","zfl":"13000","type":"生活","time":"10+"}
{"rank":11,"title":"“当 代 热 门 游 戏 现 状”","dzl":"26.3","bfl":"205.0","zfl":"2511","type":"游戏","time":"0-3"}
{"rank":95,"title":"欧洲资本积累到底有多肮脏？揭秘罪恶的黑奴贸易历史书为何只字不提？","dzl":"8.1","bfl":"77.8","zfl":"3752","type":"生活","time":"10+"}
{"rank":38,"title":"永不停息的制裁华为能否王者归来？","dzl":"16.0","bfl":"128.1","zfl":"3785","type":"知识","time":"8-10"}
{"rank":12,"title":"章鱼哥疯了","dzl":"10.2","bfl":"425.1","zfl":"1550","type":"游戏","time":"10+"}
{"rank":13,"title":"当你以游戏的方式打开军训","dzl":"18.6","bfl":"146.8","zfl":"11000","type":"生活","time":"8-10"}
{"rank":14,"title":"这下终于听懂秀才的小曲了","dzl":"17.7","bfl":"233.5","zfl":"9093","type":"搞笑","time":"0-3"}
{"rank":15,"title":"【毕导】这个视频里说的都是真的但你却永远无法证明","dzl":"32.3","bfl":"436.4","zfl":"35000","type":"知识","time":"10+"}
{"rank":16,"title":"【冰冰vlog.011】总要在烟雨青天去趟江南吧","dzl":"15.7","bfl":"150.3","zfl":"15000","type":"生活","time":"8-10"}
{"rank":17,"title":"【深度科普】长期摆烂如何恢复体能？","dzl":"9.9","bfl":"135.9","zfl":"12000","type":"运动","time":"5-8"}
{"rank":18,"title":"恋与提瓦特空桃篇：谁让你是我命定的另一半","dzl":"16.3","bfl":"122.1","zfl":"9901","type":"动画","time":"3-5"}
{"rank":19,"title":"帮唐山一位身残志坚的大姐卖冰激凌","dzl":"19.6","bfl":"134.2","zfl":"2845","type":"生活","time":"3-5"}
{"rank":20,"title":"350元一桶巨型泡面！新晋“天价网红泡面”值不值？？？","dzl":"13.5","bfl":"270.4","zfl":"1517","type":"美食","time":"10+"}
{"rank":21,"title":"深夜抢救我爸被喷一脸血怕得病猛搓脸找药吃","dzl":"21.1","bfl":"239.2","zfl":"1103","type":"生活","time":"5-8"}
{"rank":22,"title":"新铁锅怎么开锅更好用酒店大厨教你诀窍保证不锈不粘锅","dzl":"22.2","bfl":"425.3","zfl":"7099","type":"美食","time":"0-3"}
{"rank":94,"title":"九小时畅享听完一整本书不是一剪梅！书荒丨小说","dzl":"2.0","bfl":"119.8","zfl":"88","type":"动画","time":"10+"}
{"rank":23,"title":"潮汕大妈在北美说唱圈里的生意经","dzl":"21.9","bfl":"417.8","zfl":"1106","type":"生活","time":"3-5"}
{"rank":93,"title":"再次出发","dzl":"13.7","bfl":"104.6","zfl":"3630","type":"知识","time":"0-3"}
{"rank":24,"title":"万人雪书的长视频终于来啦！架不住你们天天催啊花了好多时间呜呜呜～","dzl":"25.6","bfl":"376.5","zfl":"4181","type":"生活","time":"8-10"}
{"rank":92,"title":"2023年8月热歌榜TOP50今夕是何年？难以置信这是2023年？","dzl":"6.7","bfl":"92.1","zfl":"3226","type":"音乐","time":"10+"}
{"rank":25,"title":"《明日方舟》SideStory「不义之财」活动宣传PV","dzl":"14.1","bfl":"172.7","zfl":"36000","type":"游戏","time":"5-8"}
{"rank":26,"title":"当中二青年来买单！(猜不到结局版）","dzl":"22.3","bfl":"235.5","zfl":"18000","type":"影视","time":"3-5"}
{"rank":91,"title":"日本排海为何中国网友互喷？日本政府是如何正当化排海的？","dzl":"6.4","bfl":"70.7","zfl":"7570","type":"知识","time":"10+"}
{"rank":90,"title":"她似了又活了。她没似他似了所以她也似了。她又活了可他们似了所以她又似了。","dzl":"8.0","bfl":"46.5","zfl":"7960","type":"搞笑","time":"3-5"}
{"rank":28,"title":"宿舍规则怪谈","dzl":"27.1","bfl":"205.1","zfl":"5178","type":"搞笑","time":"10+"}
{"rank":89,"title":"光头强家里捡的","dzl":"0.4","bfl":"320.9","zfl":"14","type":"动画","time":"8-10"}
{"rank":88,"title":"全网首拆！麒麟 5G 确认回归 | 华为 Mate 60 Pro","dzl":"56.2","bfl":"703.5","zfl":"90000","type":"知识","time":"10+"}
{"rank":29,"title":"穷小子强行与富少爷交换人生本想荣华富贵哪知少爷家里更是离谱！","dzl":"17.7","bfl":"288.5","zfl":"2355","type":"动画","time":"3-5"}
{"rank":87,"title":"我精神状态完全没有出问题的啦！！！","dzl":"16.4","bfl":"127.9","zfl":"4622","type":"动画","time":"0-3"}
{"rank":30,"title":"一个疯子却获得了诺贝尔奖真实故事改编高分电影《美丽心灵》","dzl":"12.5","bfl":"329.1","zfl":"3109","type":"影视","time":"10+"}
{"rank":86,"title":"最诡异的一局","dzl":"11","bfl":"101.4","zfl":"1702","type":"游戏","time":"3-5"}
{"rank":85,"title":"拥有几十台能上路的经典老车是什么样的体验？","dzl":"9.8","bfl":"163.4","zfl":"1669","type":"知识","time":"3-5"}
{"rank":31,"title":"这款游戏出现在21世纪还是太迷幻了","dzl":"12.5","bfl":"114.6","zfl":"1791","type":"游戏","time":"10+"}
{"rank":84,"title":"史上最好的原神模组！！（boss篇）","dzl":"8.7","bfl":"104.8","zfl":"8998","type":"游戏","time":"5-8"}
{"rank":32,"title":"关于下半身的生理知识没人告诉你这些！｜拉撒保姆级教程","dzl":"10.9","bfl":"100.1","zfl":"7921","type":"知识","time":"10+"}
{"rank":83,"title":"努力有错吗？学习有罪吗？高考没有一个韩国人敢退出的战争","dzl":"8.2","bfl":"168.7","zfl":"9989","type":"知识","time":"10+"}
{"rank":33,"title":"宝可梦日语OP大全【挪威小哥Pellek｜中字】","dzl":"11.7","bfl":"77.4","zfl":"3124","type":"音乐","time":"8-10"}
{"rank":82,"title":"不同年代的上班族白领都是怎么办公的？最后真的别演我工作状态哈哈哈哈！","dzl":"15.6","bfl":"147.5","zfl":"1688","type":"生活","time":"0-3"}
{"rank":81,"title":"我们分手了","dzl":"8.1","bfl":"241.4","zfl":"7008","type":"运动","time":"5-8"}
{"rank":39,"title":"老鼠们每天用捕鼠夹健身只为发动鼠界第三次世界大战","dzl":"19.2","bfl":"627.6","zfl":"10000","type":"影视","time":"10+"}
{"rank":40,"title":"大意了！这些不是全国统一的","dzl":"17.0","bfl":"219.7","zfl":"1325","type":"生活","time":"5-8"}
{"rank":41,"title":"青春没有售价米哈游贴脸开大","dzl":"28.6","bfl":"147.1","zfl":"29000","type":"游戏","time":"0-3"}
{"rank":42,"title":"早自习补作业事件","dzl":"29.0","bfl":"331.6","zfl":"5866","type":"影视","time":"0-3"}
{"rank":43,"title":"凌晨12.00教100个仙人掌唱歌没想到邻居找上门来了","dzl":"10.7","bfl":"330.0","zfl":"11000","type":"生活","time":"3-5"}
{"rank":44,"title":"这一次我们重新定义砖块","dzl":"24.7","bfl":"309.7","zfl":"5792","type":"生活","time":"0-3"}
{"rank":45,"title":"抗塔两分钟还是满血我没开玩笑！","dzl":"12.9","bfl":"279.4","zfl":"2197","type":"游戏","time":"5-8"}
{"rank":67,"title":"世界纪录保持者的拍摄间该怎么提升？","dzl":"9.3","bfl":"117.2","zfl":"2266","type":"知识","time":"10+"}
{"rank":68,"title":"一口气看完海贼王真人版第1季！1-8集！真人版符合你的预期吗？","dzl":"4.0","bfl":"238.6","zfl":"11000","type":"影视","time":"10+"}
{"rank":69,"title":"叶问之摆烂宗师","dzl":"9.2","bfl":"139.3","zfl":"10000","type":"搞笑","time":"5-8"}
{"rank":70,"title":"甲方花钱助我出道","dzl":"10.5","bfl":"78.4","zfl":"4665","type":"生活","time":"3-5"}
{"rank":71,"title":"秀 才 小 曲 最 骚 版 本","dzl":"12.9","bfl":"144.9","zfl":"3663","type":"搞笑","time":"0-3"}
{"rank":72,"title":"还原一下著名的《潘博文事件》","dzl":"21.8","bfl":"499.1","zfl":"5908","type":"生活","time":"3-5"}
{"rank":73,"title":"手持烟火以谋生 心怀诗意以谋爱","dzl":"15.3","bfl":"120.8","zfl":"5448","type":"动画","time":"0-3"}
{"rank":74,"title":"大家散伙！唐僧闹分手悟空为何大开杀戒？","dzl":"6.8","bfl":"101.4","zfl":"2224","type":"影视","time":"10+"}
{"rank":75,"title":"数学揭示万物密码 当然这还不够我们要去探访一下永恒。","dzl":"8.4","bfl":"132.2","zfl":"1487","type":"知识","time":"5-8"}
{"rank":76,"title":"如果2077的CV来配音《赛博朋克：边缘行者》（第八话）","dzl":"7.5","bfl":"57.5","zfl":"4243","type":"动画","time":"10+"}
{"rank":77,"title":"自己做的游戏终于发布了！赚了多少钱！？","dzl":"17.6","bfl":"155.7","zfl":"3834","type":"游戏","time":"0-3"}
{"rank":78,"title":"《要有自己的颜色》","dzl":"21.2","bfl":"199.9","zfl":"1782","type":"生活","time":"0-3"}
{"rank":79,"title":"买一块几十斤巨大安格斯上脑又被坑惨了涮麻辣火锅却爽翻了","dzl":"11.2","bfl":"136.6","zfl":"803","type":"美食","time":"8-10"}
{"rank":80,"title":"鹦鹉螺：我不是活化石","dzl":"14.3","bfl":"199.4","zfl":"1950","type":"知识","time":"10+"}
{"rank":27,"title":"【洛天依游学记原创曲】歌行四方 | AI歌手X非遗音乐","dzl":"10.4","bfl":"200.7","zfl":"5512","type":"音乐","time":"3-5"}
{"rank":46,"title":"我居然穿越回了10年前的B站！！！","dzl":"7.7","bfl":"116.6","zfl":"3811","type":"生活","time":"8-10"}
{"rank":47,"title":"陈Sir的工作日","dzl":"13.5","bfl":"110.8","zfl":"26000","type":"生活","time":"0-3"}
{"rank":48,"title":"干嘛啊","dzl":"24.0","bfl":"266.1","zfl":"7128","type":"生活","time":"0-3"}
{"rank":49,"title":"你看你又不敢对峙命运了吧！！！","dzl":"14.0","bfl":"97.8","zfl":"696","type":"游戏","time":"3-5"}
{"rank":50,"title":"我花1万块重庆打车到上海却被全国网友说成老赖","dzl":"15.3","bfl":"140.3","zfl":"10000","type":"生活","time":"10+"}
{"rank":51,"title":"摸 气 挑 战 （2）","dzl":"26.3","bfl":"247.7","zfl":"9562","type":"生活","time":"0-3"}
{"rank":52,"title":"仙人揉腹操九式详解版！做完大拉特拉","dzl":"7.9","bfl":"69.7","zfl":"14000","type":"运动","time":"0-3"}
{"rank":53,"title":"看着徒弟一点点长大逝去才发现长生是苦【我和徒弟03】","dzl":"5.6","bfl":"150.4","zfl":"618","type":"动画","time":"10+"}
{"rank":54,"title":"祝妹妹一路顺风","dzl":"25.4","bfl":"170.8","zfl":"18000","type":"生活","time":"0-3"}
{"rank":55,"title":"300w粉丝特别节目！拍了一些大家想看的但是也太怪了？","dzl":"12.3","bfl":"66.5","zfl":"710","type":"知识","time":"5-8"}
{"rank":56,"title":"爆肝几个星期我建出了最细节的海岛小镇！！！","dzl":"24.3","bfl":"214.8","zfl":"5545","type":"游戏","time":"5-8"}
{"rank":57,"title":"让巴黎看到国人的美","dzl":"24.4","bfl":"186.1","zfl":"784","type":"生活","time":"0-3"}
{"rank":58,"title":"村里来了新成员漠叔好心劝上学做社会有用的人","dzl":"11.9","bfl":"119.5","zfl":"1510","type":"美食","time":"10+"}
{"rank":59,"title":"《原神》角色演示-「菲米尼：海露幽响」","dzl":"11.7","bfl":"110.7","zfl":"7387","type":"游戏","time":"5-8"}
{"rank":61,"title":"当你碰到经验就会「瞬间暴毙」!!？","dzl":"10.0","bfl":"105.6","zfl":"554","type":"游戏","time":"10+"}
{"rank":62,"title":"大学开学时的各种人|大学学习","dzl":"9.0","bfl":"294.9","zfl":"224","type":"搞笑","time":"3-5"}
{"rank":63,"title":"都什么年代谁还用传统方式结义？！！","dzl":"9.0","bfl":"60.6","zfl":"3278","type":"搞笑","time":"10+"}
{"rank":64,"title":"【闽南婚宴】中式流水席天花板吃过一辈子忘不掉。","dzl":"39.1","bfl":"393.2","zfl":"6.5","type":"美食","time":"10+"}
{"rank":65,"title":"口腔溃疡为什么是白色的？","dzl":"18.1","bfl":"318.9","zfl":"3562","type":"知识","time":"3-5"}
{"rank":66,"title":"我今年拼过最牛的积木！（上）","dzl":"7.8","bfl":"172.8","zfl":"8298","type":"动画","time":"10+"}
{"rank":60,"title":"【太君の噩梦】打服日本一战成名比你想象的更夸张！苏联军神朱可夫","dzl":"10.7","bfl":"130.1","zfl":"1218","type":"知识","time":"10+"}

8.4.2 mysql数据库中创建对应的表

create table t_movie(id int,title varchar(200),dzl decimal,bfl decimal,zfl decimal,type varchar(20),time varchar(10));

9. 数据导出之（从Hive 到 MySQL）

9.1 准备

9.1.1 在hive中创建student表，插入三条数据

create table student(id int,name string,age int);

insert into student values(1,'Tom',20);

insert into student values(2,'Jack',22);

insert into student values(3,'Petter',23)

查看 student表在hdfs中的存储位置

9.1.2 在mysql中同样创建student表

9.2 Sqoop的export命令支持 insert、update 到关系型数据库，但是不支持merge

sqoop export \
> --connect jdbc:mysql://192.168.170.100:3306/sqoop_db \
> --username root \
> --password 123456 \
> --export-dir /user/hive/warehouse/laoma.db/student \
> --table t_student \
> --num-mappers 1 \
> --input-fields-terminated-by '\001' 注意：hadoop默认的分隔符是 001

查看MySQL数据库中是否有数据

9.3 表导出到MySQL的时候，还可以执行update的模式

因为hive中不支持update，因此可以将hive中的student表删除掉，重新创建一个并插入数据，插入的数据和之前一样，但是需要将某个年龄进行修改，这样在导入到mysql中，进行测试。

9.4 根据id进行更新

sqoop export \
> --connect jdbc:mysql://192.168.170.100:3306/sqoop_db \
> --username root \
> --password 123456 \
> --export-dir /user/hive/warehouse/laoma.db/student \
> --table t_student \

> --update-key id \
> --num-mappers 1 \
> --input-fields-terminated-by '\001' 注意：hadoop默认的分隔符是 001

10. 应用案例脚本编写

10.1 需求：编写一个脚本

将sqoop_db中的t_goods表每天抽取所有数据并导入到hdfs://user/laoma/goods目录下，要按照每天的日期生成对应的目录保存表数据。以shell脚本的方式运行每天定时运行。

vim goods_op.sh

batch_date=$1

sqoop import \

> --connect jdbc:mysql://192.168.170.100:3306/sqoop_db \
> --username root \
> --password 123456 \
> --target-dir /user/laoma/goods/${batch_date}/ \

> --delete-target-dir \
> --fields-terminated-by '\t' \

> --split-by id \

> --query 'select * from t_goods where $CONDITIONS'

result=$?

if($result!=0);then

echo "执行失败" 'date' >>/home/hadoop/laoma/goods.log

exit 1

else

echo "执行成功" 'date' >>/home/hadoop/laoma/goods.log

10.2 给此文件添加执行权限

chmod x vim goods_op.sh

10.3 执行此文件，并传入日期

sh -x goods_op.sh 20240314

老马聊技术

关注

19
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
Sqoop下载安装及使用教程

Sqoop(发音：skup)是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql...)间进行数据的传递，可以将一个关系型数据库（例如： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。Sqoop项目开始于2009年，最早是作为Hadoop的一个第三方模块存在，后来为了让使用者能够快速部署，也为了让开发人员能够更快速的迭代开发，Sqoop独立成为一个Apache项目。
复制链接

扫一扫