sqoop安装 & 使用

妖狐艹你老母 2024-03-26 19:48 68阅读 0赞

**目录**

拷贝安装包

解压

改名

拷贝jar包

修改配置文件

配置环境变量

查看安装情况

查看指定mysql服务器中数据库

查看指定mysql服务器数据库中的表

在hive中创建一个teacher表跟mysql的students中的teacher结构相同

将mysql中students库中的sc数据导出到hdfs指定的文件目录中

将mysql中test1库中的a数据导出到hdfs指定的文件目录中

带条件过滤

带条件过滤，指定查询列

query使用

将students中sc表导入到hive default库中

将students中sc表导入到hive test库中

重写，原数据会被覆盖

增量导入

--------------------

前提：这里需要两个压缩包

![e24a72f5c40444a79d67f126c67178e2.png][]

### 拷贝安装包 ###

使用xftp拉到虚拟机opt/install里面

![f12e885ad67a419b960eb294ec7dbb56.png][]

### 解压 ###

> tar -zxf sqoop-1.4.7.tar.gz -C ../soft

### 改名 ###

> cd ../soft/
> 
> mv sqoop-1.4.7/ sqoop147

### 拷贝jar包 ###

> cp /opt/soft/hive312/lib/hive-common-3.1.2.jar ./opt/soft/sqoop147/lib
> 
> cp /opt/soft/hive312/lib/mysql-connector-java-8.0.25.jar ./opt/soft/sqoop147/lib
> 
> 把sqoop-1.4.7.bin\_\_hadoop-2.6.0.tar.gz里面的lib下面的的avro-1.8.1.jar拷贝到/opt/soft/sqoop147/lib 里面
> 
> 把sqoop-1.4.7.bin\_\_hadoop-2.6.0.tar.gz里面的sqoop-test-1.4.7.jar拷贝到/opt/soft/sqoop147里面

### 修改配置文件 ###

> 转到sqoop/conf目录下面
> 
> cp sqoop-env-template.sh sqoop-env.sh
> 
> vim sqoop-env.sh
> 
> 里面修改五行，没有的添加
> 
>     #Set path to where bin/hadoop is available
>     export HADOOP_COMMON_HOME=/opt/soft/hadoop313
>     
>     #Set path to where hadoop-*-core.jar is available
>     export HADOOP_MAPRED_HOME=/opt/soft/hadoop313
>     
>     #Set the path to where bin/hive is available
>     export HIVE_HOME=/opt/soft/hive312
>     export HIVE_CONF_DIR=/opt/soft/hive312/conf
>     
>     #Set the path for where zookeper config dir is
>     export ZOOCFGDIR=/opt/soft/zk345/conf

### 配置环境变量 ###

> vim /etc/profile
> 
>     #SQOOP_HOME
>     export SQOOP_HOME=/opt/soft/sqoop147
>     export PATH=$PATH:$SQOOP_HOME/bin
> 
> 刷新环境变量
> 
> source /etc/profile

### 查看安装情况 ###

> sqoop version

![1fcca7fd34414fabaf23cf28407308fc.png][]

### 查看指定mysql服务器中数据库 ###

> \[root@hadoop3 ~\]\# sqoop list-databases --connect jdbc:mysql://192.168.152.184:3306 --username root --password 12345
> 
> ![1ee7536a65c7456ab7812a080b6ca93f.png][]

### 查看指定mysql服务器数据库中的表 ###

> \[root@hadoop3 ~\]\# sqoop list-tables --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345
> 
> ![3704977b39c74d2ead02bf7658c5e3dd.png][]

### 在hive中创建一个teacher表跟mysql的students中的teacher结构相同 ###

> \[root@hadoop3 ~\]\# sqoop create-hive-table --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table teacher --hive-table teachers
> 
> ![df6fb670ec914701bbc87f838390d887.png][]

### 将mysql中students库中的sc数据导出到hdfs指定的文件目录中 ###

> \[root@hadoop3 ~\]\# \[root@hadoop3 conf\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table sc --target-dir /tmp/student/sc1 --fields-terminated-by '\\t' -m 1
> 
> ![ac62c2c1eb1c422d9f2e46779191136b.png][]

### 将mysql中test1库中的a数据导出到hdfs指定的文件目录中 ###

> \[root@hadoop3 conf\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/test1 --username root --password 12345 --table a --target-dir /tmp/test1/a --fields-terminated-by '\\t' -m 2
> 
> ![31f65523fa7847ddbcd4c2d7ca7566f0.png][]

### 带条件过滤 ###

> sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table sc --where "id='1'" --target-dir /tmp/students/id1 -m 1
> 
> ![2be57fe8f8384089bdec63de47f22b7f.png][]

### 带条件过滤，指定查询列 ###

> \[root@hadoop3 ~\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table sc --columns "CID,score" --where "SID='01'" --target-dir /tmp/students/sid01columns -m 1  
> ![25b74242ba434d05b80b695fbac4c833.png][]

### query使用 ###

> \[root@hadoop3 ~\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --target-dir /tmp/students/scquerys --query 'select \* from sc where $CONDITIONS and CID="02" and score>80 ' --fields-terminated-by '\\t' -m 1  
> ![e6915bec3ffb4529bfde70a32033e08f.png][]

### 将students中sc表导入到hive default库中 ###

> \[root@hadoop3 ~\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table sc --hive-import test -m 1  
>   
> ![bd65c32c2ab448d9821612992e72bf67.png][]

### 将students中sc表导入到hive test库中 ###

> \[root@hadoop3 ~\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table sc --hive-import --hive-database test -m 1  
> ![50ea4e783e464e21a09ae201b5c5fc29.png][]

### 重写，原数据会被覆盖 ###

> \[root@hadoop3 ~\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table sc --hive-import --hive-overwrite --hive-database test -m 1

### 增量导入 ###

> \[root@hadoop3 ~\]\# sqoop import --connect jdbc:mysql://192.168.152.184:3306/students --username root --password 12345 --table sc --hive-import --incremental append --hive-database test1 -m 1

[e24a72f5c40444a79d67f126c67178e2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/89d92ced2dec48c996fdb46ef9a209f8.png
[f12e885ad67a419b960eb294ec7dbb56.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/aeadd871b51b4fb09fb63df2203700ab.png
[1fcca7fd34414fabaf23cf28407308fc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/0d33a86fe4a94d2995cdbe91f74d5205.png
[1ee7536a65c7456ab7812a080b6ca93f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/5038d1cb5861410087d83b068a891043.png
[3704977b39c74d2ead02bf7658c5e3dd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/2ae1df416b714cda8c3f65e76f495bf2.png
[df6fb670ec914701bbc87f838390d887.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8c323ba775c148a18c4592e6bf623db0.png
[ac62c2c1eb1c422d9f2e46779191136b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/266b49d43d8f4336a4b886f5a77a10c7.png
[31f65523fa7847ddbcd4c2d7ca7566f0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9d9b6080736548799cc732f31f5b70dd.png
[2be57fe8f8384089bdec63de47f22b7f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1114ec61fb28412085a563dd7eb7d713.png
[25b74242ba434d05b80b695fbac4c833.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/05f33f4cdbf74a9faa439021f0d881fe.png
[e6915bec3ffb4529bfde70a32033e08f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/96e3dc89d25d4a8e908b308b40406c53.png
[bd65c32c2ab448d9821612992e72bf67.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/bd1856b12469441ea68e704c2177d325.png
[50ea4e783e464e21a09ae201b5c5fc29.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/62ce1e0f2be24fc9b4f54eb6adb1b9a8.png