hive学习笔记之八：Sqoop

ゝ一世哀愁。 2022-11-21 14:56 158阅读 0赞

### 欢迎访问我的GitHub ###

> 这里分类和汇总了欣宸的全部原创(含配套源码)：[https://github.com/zq2599/blog\_demos][https_github.com_zq2599_blog_demos]

### 《hive学习笔记》系列导航 ###

1.  [基本数据类型][Link 1]
2.  [复杂数据类型][Link 2]
3.  [内部表和外部表][Link 3]
4.  [分区表][Link 4]
5.  [分桶][Link 5]
6.  [HiveQL基础][HiveQL]
7.  [内置函数][Link 6]
8.  [Sqoop][]
9.  [基础UDF][UDF]
10. [用户自定义聚合函数(UDAF)][UDAF]
11. [UDTF][]

### 关于Sqoop ###

Sqoop是Apache开源项目，用于在Hadoop和关系型数据库之间高效传输大量数据，本文将与您一起实践以下内容：

1.  部署Sqoop
2.  用Sqoop将hive表数据导出至MySQL
3.  用Sqoop将MySQL数据导入到hive表

### 部署 ###

1.  在hadoop账号的家目录下载Sqoop的1.4.7版本：

wget https://mirror.bit.edu.cn/apache/sqoop/1.4.7/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz

1.  解压：

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

1.  解压后得到文件夹sqoop-1.4.7.bin\_\_hadoop-2.6.0，将mysql-connector-java-5.1.47.jar复制到sqoop-1.4.7.bin\_\_hadoop-2.6.0/lib目录下
2.  进入目录sqoop-1.4.7.bin\_\_hadoop-2.6.0/conf，将sqoop-env-template.sh改名为sqoop-env.sh：

mv sqoop-env-template.sh sqoop-env.sh

1.  用编辑器打开sqoop-env.sh，增加下面三个配置，HADOOP\_COMMON\_HOME和HADOOP\_MAPRED\_HOME是完整的hadoop路径，HIVE\_HOME是完整的hive路径：

export HADOOP_COMMON_HOME=/home/hadoop/hadoop-2.7.7
    export HADOOP_MAPRED_HOME=/home/hadoop/hadoop-2.7.7
    export HIVE_HOME=/home/hadoop/apache-hive-1.2.2-bin

1.  安装和配置完成了，进入sqoop-1.4.7.bin\_\_hadoop-2.6.0/bin，执行./sqoop version查看sqoop版本，如下所示，可见是1.4.7版本（有些环境变量没配置会输出告警，在此先忽略）：

[hadoop@node0 bin]$ ./sqoop version
    Warning: /home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hbase does not exist! HBase imports will fail.
    Please set $HBASE_HOME to the root of your HBase installation.
    Warning: /home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../hcatalog does not exist! HCatalog jobs will fail.
    Please set $HCAT_HOME to the root of your HCatalog installation.
    Warning: /home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../accumulo does not exist! Accumulo imports will fail.
    Please set $ACCUMULO_HOME to the root of your Accumulo installation.
    Warning: /home/hadoop/sqoop-1.4.7.bin__hadoop-2.6.0/bin/../../zookeeper does not exist! Accumulo imports will fail.
    Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
    20/11/02 12:02:58 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
    Sqoop 1.4.7
    git commit id 2328971411f57f0cb683dfb79d19d4d19d185dd8
    Compiled by maugli on Thu Dec 21 15:59:58 STD 2017

*  sqoop装好之后，接下来体验其功能

### MySQL准备 ###

为了接下来的实战，需要把MySQL准备好，这里给出的MySQL的配置供您参考：

1.  MySQL版本：5.7.29
2.  MySQL服务器IP：192.168.50.43
3.  MySQL服务端口：3306
4.  账号：root
5.  密码：123456
6.  数据库名：sqoop

关于MySQL部署，我这为了省事儿，是用docker部署的，参考[《群晖DS218+部署mysql》][DS218_mysql]

### 从hive导入MySQL（export） ###

*  执行以下命令，将hive的数据导入到MySQL：

./sqoop export \
    --connect jdbc:mysql://192.168.50.43:3306/sqoop \
    --table address \
    --username root \
    --password 123456 \
    --export-dir '/user/hive/warehouse/address' \
    --fields-terminated-by ','

*  查看address表，数据已经导入：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_left]

### 从MySQL导入hive（import） ###

1.  在hive的命令行模式执行以下语句，新建名为address2的表结构和address一模一样：

create table address2 (addressid int, province string, city string) 
    row format delimited 
    fields terminated by ',';

1.  执行以下命令，将MySQL的address表的数据导入到hive的address2表，\-m 2表示启动2个map任务：

./sqoop import \
    --connect jdbc:mysql://192.168.50.43:3306/sqoop \
    --table address \
    --username root \
    --password 123456 \
    --target-dir '/user/hive/warehouse/address2' \
    -m 2

1.  执行完毕后，控制台输入类似以下内容：

Virtual memory (bytes) snapshot=4169867264
    		Total committed heap usage (bytes)=121765888
    	File Input Format Counters 
    		Bytes Read=0
    	File Output Format Counters 
    		Bytes Written=94
    20/11/02 16:09:22 INFO mapreduce.ImportJobBase: Transferred 94 bytes in 16.8683 seconds (5.5726 bytes/sec)
    20/11/02 16:09:22 INFO mapreduce.ImportJobBase: Retrieved 5 records.

1.  去查看hive的address2表，可见数据已经成功导入：

hive> select * from address2;
    OK
    1	guangdong	guangzhou
    2	guangdong	shenzhen
    3	shanxi	xian
    4	shanxi	hanzhong
    6	jiangshu	nanjing
    Time taken: 0.049 seconds, Fetched: 5 row(s)

*  至此，Sqoop工具的部署和基本操作已经体验完成，希望您在执行数据导入导出操作时，此文能给您一些参考；

### 欢迎关注公众号：程序员欣宸 ###

> 微信搜索「程序员欣宸」，我是欣宸，期待与您一同畅游Java世界…

[https_github.com_zq2599_blog_demos]: https://github.com/zq2599/blog_demos
[Link 1]: https://xinchen.blog.csdn.net/article/details/109304044
[Link 2]: https://xinchen.blog.csdn.net/article/details/109344642
[Link 3]: https://xinchen.blog.csdn.net/article/details/109393908
[Link 4]: https://xinchen.blog.csdn.net/article/details/109404278
[Link 5]: https://xinchen.blog.csdn.net/article/details/109412454
[HiveQL]: https://xinchen.blog.csdn.net/article/details/109432395
[Link 6]: https://xinchen.blog.csdn.net/article/details/109440325
[Sqoop]: https://xinchen.blog.csdn.net/article/details/109445825
[UDF]: https://xinchen.blog.csdn.net/article/details/109457019
[UDAF]: https://xinchen.blog.csdn.net/article/details/109499956
[UDTF]: https://xinchen.blog.csdn.net/article/details/109530630
[DS218_mysql]: https://blog.csdn.net/boling_cavalry/article/details/105460567
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_left]: /images/20221120/d857518204bf4842a17882f3289c4599.png