Spark-Sql快速入门系列(5) | Hive数据库

素颜马尾好姑娘i 2022-12-02 00:46 359阅读 0赞

### 目录 ###

*  一.hive和spark sql的集成方式(面试可能会问到)
 *  二.spark\_shell和spark\_sql操作
 *   *  spark\_shell
     *  spark\_sql
     *  使用hiveserver2 + beeline
 *  三.脚本使用spark-sql
 *  四.idea中读写Hive数据
 *   *  1.从hive中读数据
     *  2.从hive中写数据
     *  使用hive的insert语句去写
     *  使用df.write.saveAsTable("表名")(常用)
     *  使用df.write.insertInto("表名")
     *  3.saveAsTable和insertInto的原理
 *  五.聚合后的分区数

# 一.hive和spark sql的集成方式(面试可能会问到) #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center]  
**`hive on spark(版本兼容)`**  
官网[https://cwiki.apache.org/confluence/display/Hive/Hive+on+Spark%3A+Getting+Started][https_cwiki.apache.org_confluence_display_Hive_Hive_on_Spark_3A_Getting_Started]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 1]  
**`spark on hive(版本兼容)`**  
官网  
[http://spark.apache.org/docs/2.1.1/sql-programming-guide.html\#hive-tables][http_spark.apache.org_docs_2.1.1_sql-programming-guide.html_hive-tables]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 2]

# 二.spark\_shell和spark\_sql操作 #

## spark\_shell ##

![在这里插入图片描述][20200828151547485.png_pic_center]  
**`如果你在集群上使用了tez，你需要在spark/conf下spark-defaults.conf添加lzo的路径`**

spark.jars=/export/servers/hadoop-2.7.7/share/hadoop/common/hadoop-lzo-0.4.20.jar

spark-yarn模式启动

bin/spark-shell --master yarn

## spark\_sql ##

完全跟sql一样  
![在这里插入图片描述][202008281524295.png_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 4]

## 使用hiveserver2 + beeline ##

spark-sql 得到的结果不够友好, 所以可以使用hiveserver2 + beeline  
1.启动thriftserver(后台)

sbin/start-thriftserver.sh

2.启动beeline

bin/beeline
    # 然后输入
    !connect jdbc:hive2://hadoop102:10000
    # 然后按照提示输入用户名和密码

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 5]

# 三.脚本使用spark-sql #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 6]

# 四.idea中读写Hive数据 #

## 1.从hive中读数据 ##

![在这里插入图片描述][20200828162649909.png_pic_center]  
添加依赖

<dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-hive_2.11</artifactId>
        <version>2.1.1</version>
    </dependency>

代码实现

import org.apache.spark.sql.SparkSession
    
    object HiveRead { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession
          .builder()
          .master("local[*]")
          .appName("HiveRead")
          //添加支持外置hive
          .enableHiveSupport()
          .getOrCreate()
    
        spark.sql("show databases")
        spark.sql("use guli")
        spark.sql("select count(*) from gulivideo_orc").show()
    
        spark.close()
      }
    
    }

结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 7]

## 2.从hive中写数据 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 8]

## 使用hive的insert语句去写 ##

import org.apache.spark.sql.SparkSession
    
    object HiveWrite { 
      def main(args: Array[String]): Unit = { 
        System.setProperty("HADOOP_USER_NAME", "root");
        val spark = SparkSession
          .builder()
          .master("local[*]")
          .appName("HiveRead")
          //添加支持外置hive
          .enableHiveSupport()
          .config("spark.sql.warehouse.dir","hdfs://hadoop102:9000/user/hive/warehouse")
          .getOrCreate()
    
        //先创建一个数据库
        spark.sql("create database spark1602")
        spark.sql("use spark1602")
        spark.sql("create table user1(id int,name string)")
        spark.sql("insert into user1 values(10,'lisi')").show()
    
        spark.close()
    
      }
    }

## 使用df.write.saveAsTable(“表名”)(常用) ##

import org.apache.spark.sql.SparkSession
    
    object HiveWrite { 
      def main(args: Array[String]): Unit = { 
        System.setProperty("HADOOP_USER_NAME", "root");
        val spark = SparkSession
          .builder()
          .master("local[*]")
          .appName("HiveRead")
          //添加支持外置hive
          .enableHiveSupport()
          .config("spark.sql.warehouse.dir","hdfs://hadoop102:9000/user/hive/warehouse")
          .getOrCreate()
    
    
        val df = spark.read.json("D:\\idea\\spark-sql\\input\\user.json")
        spark.sql("use spark1602")
        //直接把数据写入到hive中,表可以存在也可以不存在
        df.write.saveAsTable("user2")
        //也可以进行追加
       //df.write.mode("append").saveAsTable("user2")
        spark.close()
    
      }
    
    }

## 使用df.write.insertInto(“表名”) ##

import org.apache.spark.sql.SparkSession
    
    object HiveWrite { 
      def main(args: Array[String]): Unit = { 
        System.setProperty("HADOOP_USER_NAME", "root");
        val spark = SparkSession
          .builder()
          .master("local[*]")
          .appName("HiveRead")
          //添加支持外置hive
          .enableHiveSupport()
          .config("spark.sql.warehouse.dir","hdfs://hadoop102:9000/user/hive/warehouse")
          .getOrCreate()
    
    
        val df = spark.read.json("D:\\idea\\spark-sql\\input\\user.json")
        spark.sql("use spark1602")
        df.write.insertInto("user2")
    
        spark.close()
      }
    
    }

## 3.saveAsTable和insertInto的原理 ##

**`saveAsTable`**  
使用列名进行分配值  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 9]

**`insertInto`**  
按照位置进行1对1  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 10]

# 五.聚合后的分区数 #

import org.apache.spark.sql.SparkSession
    
    object HiveWrite { 
      def main(args: Array[String]): Unit = { 
        System.setProperty("HADOOP_USER_NAME", "root");
        val spark = SparkSession
          .builder()
          .master("local[*]")
          .appName("HiveRead")
          //添加支持外置hive
          .enableHiveSupport()
          .config("spark.sql.warehouse.dir","hdfs://hadoop102:9000/user/hive/warehouse")
          .getOrCreate()
    
        val df = spark.read.json("D:\\idea\\spark-sql\\input\\user.json")
        df.createOrReplaceTempView("a")
    
        spark.sql("use spark1602")
        val df1 = spark.sql("select * from a ")
        val df2 = spark.sql("select sum(age) sum_age from a group by name")
        println(df1.rdd.getNumPartitions)
        println(df2.rdd.getNumPartitions)
       df1.write.saveAsTable("a3")
       df2.write.saveAsTable("a4")
    
        spark.close()
      }
    
    }

结果：聚合函数分区数默认200个  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 11]  
如果数据量小，没必要200两个分区，简直浪费  
将

df2.write.saveAsTable("a4")

修改为

df2.coalesce(1).write.saveAsTable("a4")

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/7b04c07612e540d28199f698554564d9.png
[https_cwiki.apache.org_confluence_display_Hive_Hive_on_Spark_3A_Getting_Started]: https://cwiki.apache.org/confluence/display/Hive/Hive+on+Spark:+Getting+Started
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/2aff186c370a40279aa4ad66354a3f27.png
[http_spark.apache.org_docs_2.1.1_sql-programming-guide.html_hive-tables]: http://spark.apache.org/docs/2.1.1/sql-programming-guide.html#hive-tables
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/6eae64f134e3430f99fef3d5f0acdeef.png
[20200828151547485.png_pic_center]: /images/20221123/d3fb3a9a80c34a0797ddf435f7cb3a87.png
[202008281524295.png_pic_center]: /images/20221123/6e5a577dedbb4051a963a4a488d77286.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/3c677330c35045868db0e74410928f3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/c6fd3bc61eb2452ab74a77e8ff90e4d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/9cbcba78317e4d779accfed7c40a7c3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/d6f9b1292ef74fb8912ea1fbc86efc90.png
[20200828162649909.png_pic_center]: /images/20221123/b639986448b94b3094210303b3c3bc10.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/8a6b711027cc40108026fab0ab7a3613.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/076a92b956024533b32d276d81d42e98.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/8ea5326482ad47b4ac85e083c71c25f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20221123/4efa339b3d6d475f8127814a5312e89e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20221123/4461840419f847f4b7d21bfb4ad62b15.png