Spark SQL

因为整合了hive，所以每次启动hive都需要先启动hive的元数据服务 : root用户操作
nohup hive --service metastore >> metastore.log 2>&1 &

1、spark sql 练习总结 :

(1)使用Spark sql ， 需要导入隐式转换
     // spark sql 中必须要导入隐式转换， 才可以使用 $方法 获取列对象
     import spark.implicits._
         
     //导入 DSL 所有的函数
     import org.apache.spark.sql.functions._
 
 
 (2) 凡是涉及到 “累计”   -->  开窗函数 "sum() over"
     
     1、统计 每个公司每年 按月 累计 收入 
        sum(收入) over(partition by 每个公司,每年 order by 月份)
        
        
       1) sum() over 只分区     --  全局求和 （整列结果都一样，是最终结果）
              sum(pic) over(partition by burk,year)
              
       2) sum() over 分区加排序  --  累计求和 （第一行加第二行结果等于第三行结果）
              sum(pic) over(partition by burk,year order by month)
 
 
      2、统计 每个公司 当月比上年同期增长率 
         lag("字段", 1, 默认值)  :  获取上一个字段的值，未获取到取默认值  , 只能用于连续的数据
      
      
      
  (3) 一定要先将DF注册成一张临时的视图 (视图 -- 表的概念，不过视图没有数据)     -- 没有表就没办法写sql
  
       
  (4) explode :  一行转行成多行   ，参数: 数组、集合
  
      // 1、 参数 : Array    一行变一列
         spark.sql(
           """
             |select explode(Array(1,2,3,4,5,6,7))
           """.stripMargin
         ).show()

    //  2、 参数 : map    k,v     一行变两列
         spark.sql(
           """
             |select explode(map('001', 1, '002', 2, '003' , 3))
           """.stripMargin
         ).show()
         
  
  (5) sql行转列 :   lateral view + UDF函数
  
      spark.sql(
       """
         |select *
         |from(
         |   select burk, year, month, pic
         |   from burks
         |   lateral view explode(map(1, tsl01, 2, tsl02, 3, tsl03, 4, tsl04, 5, tsl05, 6, tsl06, 7, tsl07, 8, tsl08, 9, tsl09, 10, tsl10, 11, tsl11, 12, tsl12)) T as month,pic
         | )
       """.stripMargin
     ).show()

     // 直接将explode放在select后面，结果和lateral view效果一样
     spark.sql(
       """
         |select burk, year, explode(map(1, tsl01, 2, tsl02, 3, tsl03, 4, tsl04, 5, tsl05, 6, tsl06, 7, tsl07, 8, tsl08, 9, tsl09, 10, tsl10, 11, tsl11, 12, tsl12)) as (month, pic)
         |from burks
       """.stripMargin
     ).show()
     
     

2、 spark sql 写代码方式 :

 (1) idea里面将代码编写好打包上传到集群中运行，spark-submit提交         -- 上线使用
     
       1、项目打包 :    Maven -> spark -> Lifecycle -> package
                        项目在 target下
       2、上传到服务器任意位置（虚拟机master）
       3、在jars包位置 : spark-submit --master yarn-client --class com.shujia.spark.sql.day05.Demo07Submit spark-1.0.jar
       4、运行之后， 可以查看内容 :  hdfs dfs -cat /data/gender_num/*
     
 
 (2) spark shell  ( 又称 : repl模式    l:循环、r:读、e:执行、p:打印)    -- 测试使用
       
       Linux进入shell命令行:  1、spark-shell （默认local模式）
                              2、spark-shell --master yarn-client 
                                 不能使用yarn-cluster模式，因为Driver必须在本地启动    
                                     
       特点 : 可以边写代码，边出结果，然后可以再继续写代码
              可以将之前的结果存起来, 即代码不需要重复运行 ; 不好的是必须一行写完。
              
       退出 :  Ctrl + Z
               kill -9 (SparkSubmit)进程
       
 
 (3) spark-sql
   
     Linux进入sql命令行:  spark-sql
     
     特点 :  和hive命令行一样，可以写sql ; 不好的是会打印很多日志。
             默认元数据是放在本地文件中的，若切换一个目录再进入spark-sql，就找不到元数据，之前的表就丢了。
             
             Hive最终是把元数据放在mysql里面的; 
             而spark是和hive整合后，将元数据放到mysql里面的。
     
     退出 :  exit;
     
     

3、 Spark整合Hive :

 开启hive元数据服务的目的 :  让spark sql可以获取到hive的元数据
 
 步骤 :
     (1) 在hive的hive-site.xml修改一行配置
         
          cd /usr/local/soft/hive-3.1.2/conf/
          
          vim hive-site.xml
          
          增加 :
             <property>
             <name>hive.metastore.uris</name>
             <value>thrift://master:9083</value>
             </property>
        
        
      **** 至此hive就不能用了。  每次必须先开启元数据服务，才能进入hive  *****
          
      (2) 启动hive元数据服务 :
           nohup  hive --service metastore >> metastore.log 2>&1 &
         
         
          会在根目录下创建metastore.log目录  -- hive的元数据服务日志
          jps后会出现RunJar进程
 

    (3) 将 hive-site.xml  复制到 spark conf 目录下
          cd /usr/local/soft/hive-3.1.2/conf/
     
          cp hive-site.xml /usr/local/soft/spark-2.4.5/conf/

      (4) 将mysql 驱动包复制到spark jars目录下
          cd /usr/local/soft/hive-3.1.2/lib
         
          cp mysql-connector-java-5.1.37.jar /usr/local/soft/spark-2.4.5/jars/

4、整合好之后在 spark-sql 里面就可以使用hive的表了 :

 Linux进入sql命令行: 1、 spark-sql（默认local模式）
                     2、 spark-sql --master yarn-client
                         不能使用cluster模式
                         
 特点 :
     此时， Hive和Spark-sql用的是同一个元数据。
     在Hive里创建一张表， spark-sql里面可以查看到；
     同理，在spark-sql里创建一张表， Hive里面也可以查看到；
     
     
 设置分区数 :
     1、shell命令行:  spark-sql --master yarn-client  --conf  spark.sql.shuffle.partitions=2
     2、在spark-sql中设置:  set spark.sql.shuffle.partitions=2;
     3、在代码中设置 :  .config("spark.sql.shuffle.partitions", 2)
     
     
 spark 和 hive 区别 :
     相同的sql，在spark-sql里面的运行时间是hive的1/10
         因为spark是粗粒度资源调用，hive是细粒度资源调用
         spark可以将数据缓存在内存，而hive只能将数据存在HDFS（磁盘）
     
     
 spark sql 完全兼容hivesql ，hive sql 不完全兼容sparksql
         
 
 (1) spark 独有的 :

     1、缓存表   -- 缓存在内存中
        cache table student;

     2、删除缓存
        uncache table student;
        
 
      3、 广播小表  -- 实现mapjoin      

        在map端实现表关联，将小表加载到内存，小表的大小不能超过一个Executor的内存的0.6
         
         /*+broadcast(a)  */  --> hint语法: sql里的注解的语法

        
         将小表a广播出去 :
             select /*+broadcast(a) */ * from 
             student as a
             join 
             score as b
             on a.id=b.student_id
         
         

5、 hive 写代码不同的方式 :

 (1) hive -e "show tables"    :  执行完sql语句后自动退出hive
 
 (2) 先创建一个文件a.sql, 再在a.sql里面写sql语句 :  select * from students;
     hive -f a.sql  :  执行a.sql里的sql语句
     

6、 spark-sql 也可以 :
(1) spark-sql -e

 (2)  spark-sql -f