sparksql整合hive

RDD、dataframe和dataset的区别：

RDD：弹性分布式数据集：对spark中的数据一种抽象（一种类：封装了各种对象和方法），也是一种数据结构

特点：

• 1.弹性（数据可以在内存也可以在磁盘、容错性：一个task失败了会自动重试不要手动操作，重试默认是从血缘关系链的头开始、手动持久化了之后从持久化的地方开始）、
• 2.分布式（一个RDD数据散落在不同节点）
• 3.数据集：从hdfs加载到内存，持久化到磁盘

dataframe：起源于python的pandas，等于RDD（数据）+schema（元数据信息）

dataframe=dataset[Row]

dataset: RDD+schema

dataframe和dataset最大的不同：

dataframe数据结构只能写sql方法（select,groupby），不能用高阶算子；dataset除了能写sql之外还能够转换成RDD进行高阶算子的使用

sparksql整合hive

sparksql可以使用hive的表，如果想操作hive需要很多依赖（配置文件、jar包）

jar包：一系列的程序，提供了各种功能

spark默认没有这些依赖，需要配置。hive jar在classpath下spark会自动加载这些jar[/etc/profile]

spark所有的节点都需要进行相关的配置，spark最终处理数据的结果，需要经过序列化和反序列化才能写hive里

注意：分区散落在不同的节点，数据落盘的时候每个节点都需要经过序列化反序列化的机制数据落盘

mybatis：数据能够写入到mysql，并且能够被mysql进行解析。

源：数据传输经过网络（物理层）【要进行序列化，不序列化不能传输】，

目标：解析【反序列化的过程】

程序如果想运行的话需要三个配置文件：

hive-site.xml、core-site.xml、hdfs-site.xml

过程：

1.需要在节点进行配置（master、worker每个节点中都要配置、hive（hive-site.xml））

放到spark安装目录conf，所有节点都要进行配置

2.worker节点jars目录需要添加jar包（mysql-connector-java-5.1.40-bin.jar）

3.把三个文件放入resources目录下

4.maven打包成jar

5.把jar放到集群跑，编写运行spark启动脚本运行jar就整合完毕了。

测试：

1.vim sparksql.sql

create database test_456;
use test_456;
create table `test_456`.`teacher_basic`(
name string,
age int,
married boolean,
children int
) row format delimited
fields terminated by ',';
create table `test_456`.`teacher_info`(
name string,
height double
) row format delimited
fields terminated by ',';
load data local inpath 'file:///home/hadoop/teacher_basic.txt' into table `test_456`.`teacher_basic`;
load data local inpath 'file:///home/hadoop/teacher_info.txt' into table `test_456`.`teacher_info`;
create table teacher as
select b.name,b.age,b.married,b.children,i.height
from `test_456`.`teacher_basic` b left join `test_456`.`teacher_info` i
on b.name = i.name;

2.进入hive

开启本地模式：set hive.exec.mode.local.auto = true ;

source /home/hadoop/sparksql.sql

3.打包：

打源包：jar比较轻量，本地pom文件依赖的jar在集群没有，说明类找不到

解决：打一个依赖的jar；在集群上添加缺少的jar

注意：本地的pom文件最好和集群的配置一样

打带有依赖的包

maven-clean-package

4.启动脚本：

vim sshive.sh

SAPRK_HOME=/home/hadoop/apps/spark-2.2.0-bin-hadoop2.7
export HADOOP_CONF_DIR=/home/hadoop/apps/hadoop-2.7.4/etc/
hadoop
{SAPRK_HOME}/bin/spark-submit\
  --class com.hdfs.T1 \            
  --master local[*] \
  --executor-memory 1G \
  --total-executor-cores 2 \
  /home/hadoop/testspark/ss.jar \

给脚本设置可执行权限

chmod u+v sshive.sh

启动脚本

./sshive.sh

spark资源调优-----适合所有的spark程序

资源调优就是围绕这个启动脚本怎么写：（内存调优围绕内存和cpu核）

./bin/spark-submit \
需要执行的主类是哪一个
  --class org.apache.spark.examples.SparkPi \
现在就用local【启动脚本在哪个节点执行，哪个节点就是driver】
  --master spark://207.184.161.138:7077 \
  --executor-memory 20G \
  --total-executor-cores 100 \
jar包在哪里  
  /path/to/examples.jar \
  1000
  def main(args:Array[String]) 在给args传递参数

怎么调优？

程序执行的时候需要一定量数据规模的数据，数据量很大内存就要调大一点。还要看产品的需求，大概在多长时间跑完。

一个节点可以启动多个excutor。driver要提交程序需要申请资源（分配资源有两种方式：yarn【依赖hadoop】和standalone【不依赖hadoop】），看你的启动脚本的master配的是什么。

excutor持有一定的内存和cpu核。

它的内存分为持久化内存（占60%）、shuffler读（20%）、代码执行时所需的内存（20%）

例如在堆或者栈创建对象会产生一定内存，textfile().map()第二次会比第一次快，因为会有缓存，缓存在代码执行时所需的内存，如果使用了持久化级别，会把内存转移到持久化内存。

excutor中的task默认是由block块决定的（仅限于RDD），在sparksql不适用（默认分区200，但是可能有的分区不会有数据）

可以调什么？

num-executors:说明需要多少个executor

executor-memory：配置executor的内存（根据数据量和时长决定）

executor-memory * num-executors < 1/5 (例：集群内存100G，程序内存上限20G)

executor-cores：配置executor线程数,提高程序执行效率，提高程序并行度。 3-5个核

executor-cores * num-executors 不要超过整个集群的 1/4

driver-memory：1G

collect():把每个worker节点上的数据都拉取到driver，因为拉取到driver是存放在内存里的所以肯出现OOM，所以有collect要配置的稍微大一点。

spark.default.parallelism–>程序在执行的时候修改并行度，决定程序里产生多少个task

比如aa.txt 有5个block块，如果设置为50，则会产生50个task

默认RDD:一个block块就是一个分区：128M

比如一个task处理128M，扩大2倍一个task处理64M。扩大128倍，一个task处理1M会造成资源浪费。因为资源在进行执行的时候cpu核在操作系统层要进行线程切换，如果一个task只处理1M数据，那么切换的成本远远大于处理数据的成本。

num-executors * executor-cores * 2~4 [3]

怎么选出最优值：

编写一个程序，控制单一变量，测试乘1-10之后进行可视化，会出现类似正态分布的图，选出峰值，左右再各取1

sparksql和hive函数一样

• 1.关系运算：

等值函数 =

不等值函数 <> 和 !=

小于比较 <

空值判断 IS NULL

非空判断 IS NOT NULL

Like比较 模糊查询

• 2.数学运算

加、减、乘、除、取余（%）、位于运算（&）、位或运算（|）、异或运算（^）、位取反操作（~）

• 3.逻辑运算

逻辑与 AND

逻辑或 OR

逻辑非 NOT

• 4.数值运算

取整函数：round

向下取整函数：floor

向上取整函数：ceil

随机数：rand（随机种子）

自然指数函数：exp

以什么为底（对数函数）：log2、log10

幂运算pow

开方函数：sqrt

• 5.日期函数（*）

日期转换成年、月、日：year 、month、day

时间戳转日期

日期转时间戳

• 6.条件函数（*）

case when

if

• 7，字符串函数

字符串长度：length

字符串反转：reverse

字符串连接：concat concat_ws(可以指定连接符)

字符串截取：substring、substr

字符串大小写转换：upper 、lower

字符串去空格：trim

URL解析：parse_url

JSON解析：get_json_object

字符串切割：spilt

• 8.统计函数

个数统计：count

求和：sum

均值：avg

最大最小：max、min

• 9.三个高级的数据结构

（学会如何创建和访问元素）

array、map、struct

• 10.特殊函数

udaf（炸裂函数、分组TopN）

函数分为几大类型？

UDF:一个输入一个输出（substring、year）

UDAF：多个输入一个输出（count、sum、avg）

UDTF:一个输入多个输出（explode炸裂函数）

代码实现：sql写wordcount

object T2 {
  def main(args: Array[String]): Unit = {
    Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
    Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
    Logger.getLogger("org.spark-project").setLevel(Level.WARN)

    val spark: SparkSession= SparkSession.builder()
      .appName("T2").master("local[1]").getOrCreate()

    val df: DataFrame = spark.read.text("C:\\Users\\Administrator\\Desktop\\word.txt")
        .toDF("line")//起别名
    df.createOrReplaceTempView("test")//注册临时表
    //对每一行切割
    var sql1 =
    s"""
       |select split(line,',') from test
       |""".stripMargin
    //对切割的结果进行explode操作
    sql2=
      s"""
         |select explode(split(line,',')) as word
         | from test
         |""".stripMargin
    //对结果进行全局聚合
    sql3=
      s"""
         |select
         |tmp.word,count(tmp.word) as num
         |from(
         |select explode(split(line,',')) as word
         | from test
         | )tmp
         | group by tmp.word
         | order by num
         |""".stripMargin
    sql4=
      s"""
         |select
         |tmp.word,count(tmp.word) as num
         |from(
         |select explode(split(line,',')) as word
         | from test
         | )tmp
         | group by tmp.word
         | distribute by tmp.word sort by num desc
         |""".stripMargin
    //order by全局排序 distribute+bysort by局部排序
    //初始化数据的时候dataframe只有一个分区，dataframe如果经过了转换，生成新的dataframe默认的分区变为200
    println(spark.sql(sql).rdd.getNumPartitions)
    spark.sql(sql4).show()
    spark.stop()
  }

}

开窗函数 row_number()

def f2: Unit ={
    Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
    Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
    Logger.getLogger("org.spark-project").setLevel(Level.WARN)

    val spark: SparkSession= SparkSession.builder()
      .appName("T2").master("local[1]").getOrCreate()

    val df: DataFrame = spark.read.text("C:\\Users\\Administrator\\Desktop\\topn.txt")
      .toDF("line")//起别名

    import spark.implicits._

    val ds: Dataset[MyRow] = df.map(row => {
      val line: String = row.getAs[String]("line")
      val clazz: String = line.split("\\s+")(0)
      val score: Int = line.split("\\s+")(1).toInt
      MyRow(clazz, score)
    })
    ds.createOrReplaceTempView("class_score")
    //分组topn的统计+排序
    val sql =
      s"""
         |select
         | clazz,
         | score,
         | row_number() over(partition by clazz order by score desc) as rank
         |from class_score
         |""".stripMargin
    //取top3 两种方式
    val sql1 =
      s"""
         |select
         |tmp.clazz,tmp.score,tmp.rank
         |from(
         |select
         | clazz,
         | score,
         | row_number() over(partition by clazz order by score desc) as rank
         |from class_score) tmp
         |where tmp.rank<4
         |""".stripMargin
    val sql2 =
      s"""
         |select
         | clazz,
         | score,
         | row_number() over(partition by clazz order by score desc) as rank
         |from class_score having rank<4
         |""".stripMargin
    spark.sql(sql2).show()
    spark.stop()
  }
case class MyRow(clazz :String,score:Int)

spark自定义函数

自定义udf

def f3: Unit ={

    Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
    Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
    Logger.getLogger("org.spark-project").setLevel(Level.WARN)

    val spark: SparkSession= SparkSession.builder()
      .appName("T2").master("local[1]").getOrCreate()

    val df: DataFrame = spark.read.text("C:\\Users\\Administrator\\Desktop\\word.txt")
      .toDF("line")//起别名

    df.createOrReplaceTempView("temp")
    //自己构建udf函数
    spark.udf.register[Int,String]("myStr",str=>str.length)

    val sql =
      s"""
         |select
         | line,
         | length(line) as len,
         | myStr(line) as mylength
         |from temp
         |""".stripMargin

    spark.sql(sql).show()
    spark.stop()

  }

自定义udaf

  def f6: Unit = {
    Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
    Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
    Logger.getLogger("org.spark-project").setLevel(Level.WARN)
    val spark: SparkSession= SparkSession.builder()
      .appName("T2").master("local[1]").getOrCreate()
    //注册udaf
    spark.udf.register("myAvg",new MyAvgUDAF)
    val df: DataFrame = spark.read.text("C:\\Users\\Administrator\\Desktop\\topn.txt")
      .toDF("line")//起别名
    import spark.implicits._

    //创建dataset【泛型】，因为有了泛型所以可以直接注册临时表进行数据查询
    val ds: Dataset[MyRow] = df.map(row => {
      val line: String = row.getAs[String]("line")
      val clazz: String = line.split("\\s+")(0)
      val score: Int = line.split("\\s+")(1).toInt
      MyRow(clazz, score)
    })
    ds.createOrReplaceTempView("tmp")
    //分组topn的统计+排序
    val sql =
      s"""
         |select
         | clazz,
         | round(avg(score),2) as avg_score,
         | round(myAvg(score),2) as avg_score2
         |from tmp
         |group by clazz
         |""".stripMargin

    spark.sql(sql).show()
    spark.stop()
  }
case class MyRow(clazz :String,score:Int)

class MyAvgUDAF extends UserDefinedAggregateFunction{
  //udaf函数参数的类型   myAvg(score)
  override def inputSchema: StructType = {
    new StructType().add(StructField("score",DataTypes.IntegerType,false))
  }
  //udaf在计算中临时数据的类型     sum/count
  override def bufferSchema: StructType = {
    new StructType().add("sum",DataTypes.DoubleType,false)
      .add("count",DataTypes.IntegerType,false)
  }
//对udaf返回的数据类型
  override def dataType: DataType = DataTypes.DoubleType
//函数功能及参数是否都是确定的
  override def deterministic: Boolean = true
//初始化、初始化这些临时变量
  override def initialize(buffer: MutableAggregationBuffer): Unit = {
    buffer.update(0,0.0)//初始化 sum
    buffer.update(1,0)//初始化 count
  }
//分区内的聚合   buffer:每个分区内的做缓存的变量，默认的值就是初始化的值  input的值就是输入的数据
  //input为什么要给Row，输入数据可能不止一个参数
  override def update(buffer: MutableAggregationBuffer, input: Row): Unit = {
    val inputScore: Int = input.getInt(0)
    buffer.update(0,buffer.getDouble(0)+inputScore)//分区内更新sum   sum=sum+inputscore
    buffer.update(1,buffer.getInt(1)+1)//count = count+1
  }
//分区间的聚合
  override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = {
    buffer1.update(0,buffer1.getDouble(0)+buffer2.getDouble(0))
    buffer1.update(1,buffer1.getInt(1)+buffer2.getInt(1))
  }
//计算结果并返回  avg=sum/count
  override def evaluate(buffer: Row): Any = {
    buffer.getDouble(0)/buffer.getInt(1)
  }
}

group by两阶段聚合

sparksql使用groupby没有局部聚合

groupby产生数据倾斜：采用添加随机前缀两阶段聚合解决

一个key只能被一个task处理，但是一个task可以处理多种key

比如有三种key，要指定并行度 ，不然可能还是分到一个task

def f7: Unit ={
    Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
    Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
    Logger.getLogger("org.spark-project").setLevel(Level.WARN)

    val spark: SparkSession= SparkSession.builder()
      .appName("T2").master("local[1]").getOrCreate()

    val df: DataFrame = spark.read.text("C:\\Users\\Administrator\\Desktop\\word.txt")
      .toDF("line")

    df.createOrReplaceTempView("test")//注册临时表
  //对每列数据进行切割
  var sql=
  s"""
     |select split(line,',') as word
     | from test
     |""".stripMargin
    //对切割的结果进行explode操作
    var sql1=
      s"""
         |select explode(split(line,',')) as word
         | from test
         |""".stripMargin
  //两阶段聚合第一步：打随机前缀
  var sql2=
    s"""
       |select
       |concat_ws('_',cast(floor(rand(10)*2) as string),t1.word) as prefix_word
       |from(
       |select explode(split(line,',')) as word
       | from test)t1
       |""".stripMargin
  var sql3=
    s"""
       |select
       |t2.prefix_word,count(prefix_word) part_count
       |from(
       |select
       |concat_ws('_',cast(floor(rand(10)*2) as string),t1.word) as prefix_word
       |from(
       |select explode(split(line,',')) as word
       | from test)t1)t2
       | group by t2.prefix_word
       |""".stripMargin
  //去掉随机前缀
  var sql4=
    s"""
       |select
       |t2.prefix_word,
       |substr(t2.prefix_word,instr(t2.prefix_word,'_')+1) as unprefix_word,
       |count(t2.prefix_word) part_count
       |from(
       |select
       |concat_ws('_',cast(floor(rand(10)*2) as string),t1.word) as prefix_word
       |from(
       |select explode(split(line,',')) as word
       | from test)t1)t2
       | group by t2.prefix_word
       |""".stripMargin
  //全局聚合
  var sql5=
    s"""
       |select
       |t3.unprefix_word,sum(part_count) as total
       |from(
       |select
       |t2.prefix_word,
       |substr(t2.prefix_word,instr(t2.prefix_word,'_')+1) as unprefix_word,
       |count(t2.prefix_word) part_count
       |from(
       |select
       |concat_ws('_',cast(floor(rand(10)*2) as string),t1.word) as prefix_word
       |from(
       |select explode(split(line,',')) as word
       from test)t1)t2
 	   |group by t2.prefix_word)t3
 	   |group by t3.unprefix_word
 	   |order by total desc,t3.unprefix_word asc
       |""".stripMargin
           
  spark.sql(sql5).show()
    spark.stop()
}

SparkSql整合RDD

  /**
    *  需求：每天检索词的前TopN
    * 统计每天的搜索词的前Top3  |  前提 ： 同样的人在相同一天搜索相同的次算一次
    * dailykey.txt :
  * 日期      姓名  搜索词  城市  终端
    * 2016-11-13	tom	china	beijing	pc	web
    * 2016-11-13	tom	news	tianjing	pc	web
    * 2016-11-13	john	china	beijing	pc	web
    * 2016-11-13	john	china	beijing	pc	web
    * 2016-11-13	john	china	beijing	pc	web
    * 2016-11-13	lucy	news	beijing	pc	web
        *
    * 2016-11-13	china 2
    * 2016-11-13	news 2
        *
        *
    * 思路：
    * （时间+人名+关键词）去重 = topn
        *
    * 时间 + 关键词 ： 人名的集合当成value进行去重 | Set[天生去重功能] .size()
        */

object Scala_List {  
    
 def main(args: Array[String]): Unit = {
  Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
  Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
  Logger.getLogger("org.spark-project").setLevel(Level.WARN)
  val spark = SparkSession.builder()
    .appName(s"${Scala_List.getClass.getSimpleName}")
    .master("local")
    .getOrCreate()
/**
  * 1.读取数据
  */
val lines:RDD[String] = spark.sparkContext.textFile("C:\\Users\\LSG\\Desktop\\data\\dailykey.txt")

/**
  * 2.  key [时间_搜索关键词]   ， value : user集合
  */
val dk2Users:RDD[(String, Iterable[String])] = lines.map(line => {
    val fields = line.split("\\s+")
    val date = fields(0) //日期
    val user = fields(1) // 名字
    val keyword = fields(2) // 搜索词
    (s"${date}_${keyword}", user)
  }).groupByKey()

/**
  * users ：Iterable[String] (china ,china ,america )
  */
  val rowRDD:RDD[DateKeyCount] = dk2Users.map{case (dateKey, users) => {
    val set = new util.HashSet[String]() //构建hashset
    set.addAll(JavaConversions.asJavaCollection(users)) //Iterable[String]这个数据整体放到集合里
    DateKeyCount(dateKey.split("_")(0), dateKey.split("_")(1), set.size())//2
  }}
  import spark.implicits._
/**
  * 如果一个RDD存在泛型的话，那么可以直接转换为DataSet
  */
  val ds = spark.createDataset[DateKeyCount](rowRDD)

  ds.createOrReplaceTempView("temp")

  val sql =
    """
      |select
      | tmp.*
      |from (
      |select
      |   `date`,
      |   keyword,
      |   `count`,
      |   row_number() over(partition by `date` order by `count` desc) rank
      |from temp
      |) tmp
      |where tmp.rank < 4
    """.stripMargin
  spark.sql(sql).show
  spark.stop()
}
}
// 日期 、 搜索词 、搜索次数
case class DateKeyCount(date:String, keyword:String, count:Int)