sqoop使用

布满荆棘的人生 2023-09-26 23:37 115阅读 0赞

### sqoop使用 ###

*   *  1. 导入数据
     *  2. 从mysql向hive导入数据
     *   *  2.1 导入用户信息表
     *  2.导入订单表
     *   *  2.2 导入订单表
         *  2.3 导入商品信息表
         *  2.4 导入国家信息表
         *  2.5 导入省份信息表
         *  2.6 导入城市信息表
         *  2.7 创建hive表文件
     *  3. 导出数据
     *  4. 从hive向mysql导出数据
     *   *  4.1 导出城市销售总额表
         *  4.2 mysql修改字符集为UTF-8
         *   *  4.2.1 启动mysql服务
             *  4.2.2 登录mysql
             *  4.2.3 查询mysql字符集
             *  4.2.4 退出mysql并关闭mysql
             *  4.2.5 编辑my.cnf配置文件
             *  4.2.6 启动mysql并登录
             *  4.2.7 再次查询status;
     *  4.3 查看销售总额表结果

在使用sqoop之前，需要提前启动hadoop, yarn和对应的数据库mysql

## 1. 导入数据 ##

在sqoop中，导入的概念是从非大数据集群(关系型数据库向大数据集群(thdfs,hive\]中传输数据,使用import关键字

## 2. 从mysql向hive导入数据 ##

### 2.1 导入用户信息表 ###

sqoop import  \
    --connect jdbc:mysql://bigdata03:3306/mall \
    --username root  \
    --password 111111 \
    --table t_user_info \
    --num-mappers 1 \
    --hive-import  \
    --fields-terminated-by "," \
    --hive-overwrite \
    --hive-table mall_bigdata.ods_user_info

> 里面的 \\ 是代表换行符，这里指令可以写在一行，也可以使用换行符将参数部分分来来写，显得更加直观，num-mappers是指定mapper任务个数，这里表只有一个，数据量也少，任务可以设为1，当表多，数据量大时，可以适当增大num-mappers参数，fields-terminated-by是指定分隔符

注：bigdata03虚拟机会开两个tab窗口，一个用于输入相关shell命令，一个用于开启hive命令行界面进行相关数据查询等。

![在这里插入图片描述][204d4f98814f422cb1bdbc26768aaf0d.png]  
![在这里插入图片描述][4803744e418a49fba059885e151b7703.png]

![在这里插入图片描述][593456f3ea0e4db9a7abc9f22b9a6389.png]

hive 导入完成

![在这里插入图片描述][ee36089490d742e9afc9030a504b185c.png]

查看导入的用户信息表数据

> select \* from mall\_bigdata.ods\_user\_info;

![在这里插入图片描述][23020f0fed3941849e18fc87b1fd1a60.png]

## 2.导入订单表 ##

### 2.2 导入订单表 ###

sqoop import  \
    --connect jdbc:mysql://bigdata03:3306/mall \
    --username root  \
    --password 111111 \
    --table t_sale_order \
    --num-mappers 1 \
    --hive-import  \
    --fields-terminated-by "," \
    --hive-overwrite \
    --hive-table mall_bigdata.ods_sale_order

![在这里插入图片描述][f8c3bf84871645d1b8fd229efda12e77.png]

查看导入的订单表数据

> select \* from mall\_bigdata.ods\_sale\_info;

![在这里插入图片描述][f52444f85ec149828b8c6e0ce95063b8.png]

### 2.3 导入商品信息表 ###

sqoop import  \
    --connect jdbc:mysql://bigdata03:3306/mall \
    --username root  \
    --password 111111 \
    --table dim_goods_info \
    --num-mappers 1 \
    --hive-import  \
    --fields-terminated-by "," \
    --hive-overwrite \
    --hive-table mall_bigdata.dim_goods_info

![在这里插入图片描述][f285bdd270ec49eca7adca9cd9e52bce.png]

> select \* from mall\_bigdata.dim\_goods\_info;

![在这里插入图片描述][455af2605fbe443f978304c214a9313c.png]

### 2.4 导入国家信息表 ###

sqoop import  \
    --connect jdbc:mysql://bigdata03:3306/mall \
    --username root  \
    --password 111111 \
    --table dim_country_info \
    --num-mappers 1 \
    --hive-import  \
    --fields-terminated-by "," \
    --hive-overwrite \
    --hive-table mall_bigdata.dim_country_info

![在这里插入图片描述][34e76247f8a448c69d3f2ff8cd302955.png]

> select \* from mall\_bigdata.dim\_country\_info;

![在这里插入图片描述][a1e2de75c8e240feac96329714271521.png]

### 2.5 导入省份信息表 ###

sqoop import  \
    --connect jdbc:mysql://bigdata03:3306/mall \
    --username root  \
    --password 111111 \
    --table dim_province_info \
    --num-mappers 1 \
    --hive-import  \
    --fields-terminated-by "," \
    --hive-overwrite \
    --hive-table mall_bigdata.dim_province_info

![在这里插入图片描述][fda38c39f5e14b5a8b21645d0bb0405c.png]

> select \* from mall\_bigdata.dim\_province\_info;

![在这里插入图片描述][b2c9c8d14d3843898331b2f101e2aa05.png]

### 2.6 导入城市信息表 ###

sqoop import  \
    --connect jdbc:mysql://bigdata03:3306/mall \
    --username root  \
    --password 111111 \
    --table dim_city_info \
    --num-mappers 1 \
    --hive-import  \
    --fields-terminated-by "," \
    --hive-overwrite \
    --hive-table mall_bigdata.dim_city_info

![在这里插入图片描述][e1461d0924ba4421b5726e58c9c96862.png]

> select \* from mall\_bigdata.dim\_city\_info;

![在这里插入图片描述][f506304fcbc54554abd6d87a5e8800f4.png]  
创建tmp\_dwd\_user\_info.sql 并上传到 /opt/file

![在这里插入图片描述][b2162a721d3e4e9f9e1b5cd6f37f7821.png]

### 2.7 创建hive表文件 ###

创建hive临时表文件tmp\_dwd\_user\_info.txt

执行该hive文件

![在这里插入图片描述][22efb79a74d0468d999400f4bc608abe.png]

> select \* from tmp\_dwd\_user\_info;

![在这里插入图片描述][4e20184096de41718bc606c9e2d4e05c.png]

创建hive表文件dwd\_sale\_order\_detail.sql到 /opt/file/目录

-- 切换hive的数据库
    use mall_bigdata;
    
    -- 补全用户信息表中的国家名称,省份名称和城市名称
    create table if not exists mall_bigdata.tmp_dwd_user_info
    as
    select
    	 user_id
    	,user_name
    	,sex
    	,age
    	,country_name
    	,province_name
    	,city_name
    from
    (select
    	 user_id
    	,user_name
    	,sex
    	,age
    	,country_code
    	,province_code
    	,city_code
    from ods_user_info
    ) a
    left join
    (select
    	country_code
    	,country_name
    	from dim_country_info
    ) b
    on a.country_code=b.country_code
    left join
    (select
    	province_code
    	,province_name
    	,country_code
    	from dim_province_info
    ) c
    on a.province_code=c.province_code and a.country_code=c.country_code
    left join
    (select
    	city_code
    	,city_name
    	,province_code
    	from dim_city_info
    ) d
    on a.city_code=d.city_code and a.province_code=d.province_code;
    
    
    --补全订单表中的商品名称
    --过滤国家名称为中国的订单记录
    create table if not exists mall_bigdata.dwd_sale_order_detail
    as
    select
    	sale_id,
    	a.user_id,
    	user_name,
    	sex,
    	age,
    	country_name,
    	province_name,
    	city_name,
    	a.goods_id,
    	goods_name,
    	price,
    	sale_count,
    	total_price,
    	create_time
    from
    (select
    	sale_id
    	,user_id
    	,goods_id
    	,price
    	,sale_count
    	,total_price
    	,create_time
    from ods_sale_order) a
    left join
    (
    	select
    		goods_id
    		,goods_name
    	from dim_goods_info
    ) b
    on a.goods_id=b.goods_id
    left join
    (
    	select
    		user_id
    		,user_name
    		,sex
    		,age
    		,country_name
    		,province_name
    		,city_name
    	from tmp_dwd_user_info
    )c
    on a.user_id=c.user_id
    where country_name='中国';
    
    --删除临时表
    --drop table if exists mall_bigdata.tmp_dwd_user_info;

执行该sql文件

> hive -f dwd\_sale\_order\_detail.sql

![在这里插入图片描述][bb5fc2c629ad485eb60979c7779c376a.png]

> select \* from dwd\_sale\_order\_detail;

![在这里插入图片描述][90c3f4b5ebf14eaf9c8f1c1e5d0c6ba8.png]  
创建dws\_sale\_order\_city\_total.sql文件至 /opt/file/目录

-- 切换hive的数据库
    use mall_bigdata;
    
    --计算不同城市的销售总额
    create table if not exists mall_bigdata.dws_sale_order_city_total
    as
    	select
    		city_name,
    		sum(total_price) as total_price
    	from dwd_sale_order_detail
    	group by city_name;

文件执行hive命令

> hive -f dws\_sale\_order\_city\_total.sql

![在这里插入图片描述][9fe65d72ae7a45e6821d411986e2527e.png]

> select \* from dws\_sale\_order\_city\_total;

![在这里插入图片描述][c08f08ab0cfb49e98a70b3e1c9a6f49d.png]

## 3. 导出数据 ##

在sqoop中，导出的概念是从大数据集群(hdfs,hive)向非大数据集群(关系型数据库)中传输数据;使用export关键字

## 4. 从hive向mysql导出数据 ##

### 4.1 导出城市销售总额表 ###

sqoop export \
    --connect jdbc:mysql://bigdata03:3306/result \
    --username root \
    --password 111111 \
    --table t_city_sale_total \
    --num-mappers 1 \
    --export-dir /user/hive/warehouse/mall_bigdata.db/dws_sale_order_city_total \
    --input-fields-terminated-by "\001"

export-dir 对应的目录位置可以通过show create table 表名 查看

> show create table dws\_sale\_order\_city\_total;

![在这里插入图片描述][be71182562094eabb64c46b8d4a2946e.png]  
当不指定分隔符时，hive默认分隔符为 “\\001”

![在这里插入图片描述][4cc4682d6ea24e48bb4b58f3e401a68e.png]

进入result数据库查看结果

![在这里插入图片描述][69186d1e4fcd40fba01136e831d8f67c.png]

这里的？？？是由于mysql编码不一致导致的，更改编码为UTF-8即可。

### 4.2 mysql修改字符集为UTF-8 ###

## mysql修改字符集为UTF-8

#### 4.2.1 启动mysql服务 ####

systemctl start mysqld

#### 4.2.2 登录mysql ####

mysql -uroot -p
    
    ##然后输入root密码进行登录

#### 4.2.3 查询mysql字符集 ####

##在mysql命令行下查询mysql状态
    mysql>status;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Ph9VI3M-1682961556155)(../../photo/image-20221108152312770.png)\]][img-0Ph9VI3M-1682961556155_.._.._photo_image-20221108152312770.png]

#### 4.2.4 退出mysql并关闭mysql ####

## 退出mysql
    mysql>exit;
    
    ## 关闭mysql
    systemctl stop mysqld

#### 4.2.5 编辑my.cnf配置文件 ####

vim /etc/my.cnf
    
    ##添加如下内容
    
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    [client]
    default-character-set=utf8

![在这里插入图片描述][d11c899254ab44eaabfbdacb887dc146.png]

#### 4.2.6 启动mysql并登录 ####

##启动mysql
    systemctl start mysqld
    
    ##登录mysql
    mysql -uroot -p

#### 4.2.7 再次查询status; ####

![在这里插入图片描述][f7bf60885775433186c8aeb2ed64f8dd.png]

## 4.3 查看销售总额表结果 ##

进入result数据库，刷新查看销售总额表结果

![在这里插入图片描述][00f210a2458d48d0b69e65c6f81b0cb2.png]

[204d4f98814f422cb1bdbc26768aaf0d.png]: https://img-blog.csdnimg.cn/204d4f98814f422cb1bdbc26768aaf0d.png
[4803744e418a49fba059885e151b7703.png]: https://img-blog.csdnimg.cn/4803744e418a49fba059885e151b7703.png
[593456f3ea0e4db9a7abc9f22b9a6389.png]: https://img-blog.csdnimg.cn/593456f3ea0e4db9a7abc9f22b9a6389.png
[ee36089490d742e9afc9030a504b185c.png]: https://img-blog.csdnimg.cn/ee36089490d742e9afc9030a504b185c.png
[23020f0fed3941849e18fc87b1fd1a60.png]: https://img-blog.csdnimg.cn/23020f0fed3941849e18fc87b1fd1a60.png
[f8c3bf84871645d1b8fd229efda12e77.png]: https://img-blog.csdnimg.cn/f8c3bf84871645d1b8fd229efda12e77.png
[f52444f85ec149828b8c6e0ce95063b8.png]: https://img-blog.csdnimg.cn/f52444f85ec149828b8c6e0ce95063b8.png
[f285bdd270ec49eca7adca9cd9e52bce.png]: https://img-blog.csdnimg.cn/f285bdd270ec49eca7adca9cd9e52bce.png
[455af2605fbe443f978304c214a9313c.png]: https://img-blog.csdnimg.cn/455af2605fbe443f978304c214a9313c.png
[34e76247f8a448c69d3f2ff8cd302955.png]: https://img-blog.csdnimg.cn/34e76247f8a448c69d3f2ff8cd302955.png
[a1e2de75c8e240feac96329714271521.png]: https://img-blog.csdnimg.cn/a1e2de75c8e240feac96329714271521.png
[fda38c39f5e14b5a8b21645d0bb0405c.png]: https://img-blog.csdnimg.cn/fda38c39f5e14b5a8b21645d0bb0405c.png
[b2c9c8d14d3843898331b2f101e2aa05.png]: https://img-blog.csdnimg.cn/b2c9c8d14d3843898331b2f101e2aa05.png
[e1461d0924ba4421b5726e58c9c96862.png]: https://img-blog.csdnimg.cn/e1461d0924ba4421b5726e58c9c96862.png
[f506304fcbc54554abd6d87a5e8800f4.png]: https://img-blog.csdnimg.cn/f506304fcbc54554abd6d87a5e8800f4.png
[b2162a721d3e4e9f9e1b5cd6f37f7821.png]: https://img-blog.csdnimg.cn/b2162a721d3e4e9f9e1b5cd6f37f7821.png
[22efb79a74d0468d999400f4bc608abe.png]: https://img-blog.csdnimg.cn/22efb79a74d0468d999400f4bc608abe.png
[4e20184096de41718bc606c9e2d4e05c.png]: https://img-blog.csdnimg.cn/4e20184096de41718bc606c9e2d4e05c.png
[bb5fc2c629ad485eb60979c7779c376a.png]: https://img-blog.csdnimg.cn/bb5fc2c629ad485eb60979c7779c376a.png
[90c3f4b5ebf14eaf9c8f1c1e5d0c6ba8.png]: https://img-blog.csdnimg.cn/90c3f4b5ebf14eaf9c8f1c1e5d0c6ba8.png
[9fe65d72ae7a45e6821d411986e2527e.png]: https://img-blog.csdnimg.cn/9fe65d72ae7a45e6821d411986e2527e.png
[c08f08ab0cfb49e98a70b3e1c9a6f49d.png]: https://img-blog.csdnimg.cn/c08f08ab0cfb49e98a70b3e1c9a6f49d.png
[be71182562094eabb64c46b8d4a2946e.png]: https://img-blog.csdnimg.cn/be71182562094eabb64c46b8d4a2946e.png
[4cc4682d6ea24e48bb4b58f3e401a68e.png]: https://img-blog.csdnimg.cn/4cc4682d6ea24e48bb4b58f3e401a68e.png
[69186d1e4fcd40fba01136e831d8f67c.png]: https://img-blog.csdnimg.cn/69186d1e4fcd40fba01136e831d8f67c.png
[img-0Ph9VI3M-1682961556155_.._.._photo_image-20221108152312770.png]: https://img-blog.csdnimg.cn/e48b992a053149238896b4812b0c010d.png
[d11c899254ab44eaabfbdacb887dc146.png]: https://img-blog.csdnimg.cn/d11c899254ab44eaabfbdacb887dc146.png
[f7bf60885775433186c8aeb2ed64f8dd.png]: https://img-blog.csdnimg.cn/f7bf60885775433186c8aeb2ed64f8dd.png
[00f210a2458d48d0b69e65c6f81b0cb2.png]: https://img-blog.csdnimg.cn/00f210a2458d48d0b69e65c6f81b0cb2.png