SparkSQL之操作Hive

r囧r小猫 2023-07-19 04:53 7阅读 0赞

首先启动`spark-shell`:  
命令：`spark-shell --master local[2] --jars ~/software/mysql-connector-java-5.1.35.jar --driver-class-path /home/iie4bu/software/mysql-connector-java-5.1.35.jar`  
查看当前有哪些表：  
`spark.sql("show tables").show`，结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70]  
查询sal表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 1]  
按照`customerid`进行统计，然后获取出不是空的customerid：  
`scala> spark.sql("select customerid,count(1) from sal group by customerid").filter("customerid is not null").show`

# 将查询结果再保存到Hive中 #

命令：`scala> spark.sql("select customerid,count(1) from sal group by customerid").filter("customerid is not null").write.saveAsTable("hive_table_1")`，报错信息：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 2]  
需要修改成：`scala> spark.sql("select customerid,count(1) as mount from sal group by customerid").filter("customerid is not null").write.saveAsTable("hive_table_1")`  
这样查询表就可以看到刚创建的表了：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 4]

# 浏览器中查看 #

我们在浏览器中查看刚才的作业：`ip:4040`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][20200329175101473.png]  
200表示配置分区的数量。  
这个值可以手动进行配置：  
`sparksession.sqlContext.setConf("spark.sql.shuffle.partitions", "10")`  
![在这里插入图片描述][20200329175502812.png]  
然后执行上面的保存成表的操作：  
`scala> spark.sql("select customerid,count(1) as mount from sal group by customerid").filter("customerid is not null").write.saveAsTable("hive_table_2")`  
这时再查看看浏览器，发现分区是10了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 6]  
因此在生产环境中，注意设置这个值，默认是200

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70]: /images/20230528/c754ef42b1b048839eefad1ca217e572.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 1]: /images/20230528/db72bcc414ab47dca6501d4248450f7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 2]: /images/20230528/6b98b6e6aa2746b1a6b5bc138d2fdc6c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 3]: /images/20230528/142a0615d9c54c44a7bfe6e499a77407.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 4]: /images/20230528/e3d2ec341619475db868deca41513bfc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 5]: /images/20230528/92bb7bb6ee0a4a308a82c5bf0a05ad4b.png
[20200329175101473.png]: /images/20230528/553fe245c261455e9ca08cc9242edc38.png
[20200329175502812.png]: /images/20230528/33d07899ca3c4b24b66152e83f46de0f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbmNlbnRfZHVhbg_size_16_color_FFFFFF_t_70 6]: /images/20230528/56ef23886eb848a28aa46b7a7d60dd17.png