Spark DataFrame、DataSet、SparkToHive、SparkToMysql

目录

DataFrameDemo

  DataSetDemo

 SparkToHive

 SparkToMysql

---

DataFrameDemo

package cn.kgc.ds


import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, Row, SparkSession}
import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}

object DataFrameDemo {
  def main(args: Array[String]): Unit = {


    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("dsdemo1").getOrCreate()
    val sc: SparkContext = spark.sparkContext

    import spark.implicits._
    val people: RDD[String] = sc.textFile("in/people.txt")
    people.foreach(println)

    //DataFrame ==> rdd[ROW]  schema =>> [StructType(Array(StructField)]


//     //数组map 非 RDD map

    val schemaString="id name age"
    val fields: Array[StructField] = schemaString.split(" ").map(x => StructField(x, StringType, true))
    val schema: StructType = StructType(fields)

    val peopleRddRow: RDD[Row] = people.map(x => {
      val strings: Array[String] = x.split(" ")
      Row(strings(0), strings(1), strings(2))
    })


    val df1: DataFrame = spark.createDataFrame(peopleRddRow,schema)
    df1.printSchema()
    df1.show()


    //DataFrame ==> rdd[ROW]  schema =>> [StructType(Array(StructField)]

//    val fields = Array(
//      StructField("id", IntegerType, true),
//      StructField("name", StringType, true),
//      StructField("age", IntegerType, true)
//    )
//
//    val schema: StructType = StructType(fields)
//
//        val peopleRddRow: RDD[Row] = people.map(x => {
//          val strings: Array[String] = x.split(" ")
//          Row(strings(0).toInt, strings(1), strings(2).toInt)
//        })
//
//    val frame: DataFrame = spark.createDataFrame(peopleRddRow,schema)
//    frame.printSchema()
//    frame.show()










  }

}

  DataSetDemo

package cn.kgc.ds

import java.util.Locale.Category

import cn.kgc.ds
import org.apache.spark
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.types.{DoubleType, LongType}
import org.apache.spark.sql.{DataFrame, Dataset, SparkSession}
import org.apache.spark.{SparkConf, SparkContext}


//样例类
case class Point(label:String,x:Double,y:Double)
case class Category(id:Long,name:String)


object DataSetDemo {
  def main(args: Array[String]): Unit = {


//    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName("dsdemo")
//    val sc: SparkContext = SparkContext.getOrCreate(conf)


    val spark: SparkSession = SparkSession.builder().master("local[*]").appName("ds1demo").getOrCreate()
    val sc: SparkContext = spark.sparkContext

    import spark.implicits._

//    val rdd1: RDD[Int] = sc.parallelize(1 to 6)

//    val ds1: Dataset[Int] = spark.createDataset(1 to 6)  //ds => rdd schema
//    ds1.printSchema()
//    ds1.show()
//
//    val ds2: Dataset[(String, Int)] = spark.createDataset(List(("a",1),("b",2)))  //ds2.DataSet => rdd schema
//    ds2.printSchema()
//    ds2.show()
//
//    val df = ds2.withColumnRenamed("_1","name").withColumnRenamed("_2","id")
//    df.printSchema()
//    df.show()
//
//    val df2: DataFrame = df.withColumn("id",$"id".cast(LongType))
//    df2.printSchema()
//
//    val ds3: Dataset[(String, Int, Int)] = spark.createDataset(sc.parallelize(List(("gree",38,60),("ant",9,25))))
//    ds3.printSchema()
//    ds3.show()
//
//
//    val df3 = ds3.withColumnRenamed("_1","name").withColumnRenamed("_2","age").withColumnRenamed("_3","weight")
//    df3.printSchema()
//    df3.show()
//
//    val df4 = df3.withColumn("weight",$"weight".cast(DoubleType))
//    df4.printSchema()


//    val points: Seq[Point] = Seq(Point("jsnj",32.12,43.12),Point("scdt",65.23,54.12))
//    val pointDS: Dataset[Point] = points.toDS()
//
//    pointDS.printSchema()
//    pointDS.show()
//
//    val categories = Seq(Category(1,"jsnj"),Category(2,"sxdt"))
//    val categoriesDS: Dataset[Category] = categories.toDS()
//    categoriesDS.printSchema()
//    categoriesDS.show()
//
//    val df2: DataFrame = pointDS.join(categoriesDS,pointDS("label")===categoriesDS("name"))
//    df2.printSchema()
//    df2.show()


    val pointRDD: RDD[(String, Double, Double)] = sc.parallelize(List(("jsnj",32.12,43.12),("sxdt",65.23,54.12)))
    val categoriesRDD: RDD[(Long,String)] = sc.parallelize(List((1,"jsnj"),(2,"sxdt")))


    val pointDS: Dataset[Point] = pointRDD.map(x=>Point(x._1,x._2,x._3)).toDS()
    pointDS.printSchema()
    pointDS.show()

    val categoriesDS: Dataset[Category] = categoriesRDD.map(x=>ds.Category(x._1,x._2)).toDS()
    categoriesDS.printSchema()
    categoriesDS.show()








  }

}

 SparkToHive

package cn.kgc.ds

import org.apache.spark
import org.apache.spark.sql.{DataFrame, SparkSession}

object SparkToHive {
  def main(args: Array[String]): Unit = {

    val spark = SparkSession.builder().appName("sparktohive")
      .master("local[*]")
      .config("hive.metastore.uris", "thrift://192.168.111.131:9083")
      .enableHiveSupport()
      .getOrCreate()



    val torontoDF: DataFrame =spark.sql ("select * from spark.toronto")
    torontoDF.printSchema()
    torontoDF.show()


//    val orderDF: DataFrame =spark.sql ("select orderid,count(1) cishu from lalian.orders group by orderid ")
//    orderDF.printSchema()
//    orderDF.show()

//    orderDF.write.saveAsTable("lalian.orderscount")


  }

}

 SparkToMysql

package cn.kgc.ds

import java.util.Properties

import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}

object SparkToMysql {
  def main(args: Array[String]): Unit = {


    val spark = SparkSession.builder().appName("sparktosql" )
      .master("local[*]")
//      .config("hive.metastore.uris", "thrift://192.168.111.131:9083")
//      .enableHiveSupport()
      .getOrCreate()


    val url="jdbc:mysql://192.168.111.131:3306/mybatisdb"
    val driver="com.mysql.jdbc.Driver"
    val user="root"
    val pwd="root"
    val properties = new Properties()
    properties.setProperty("user",user)
    properties.setProperty("password",pwd)
    properties.setProperty("driver",driver)



    val tblsDF: DataFrame = spark.read.jdbc(url,"student",properties)
    tblsDF.printSchema()
    tblsDF.show()



    import org.apache.spark.sql.functions._
    val frame: DataFrame = tblsDF.agg(
      max("age").as("maxage"),
      min("age").as("minage"),
      avg("age").as("avgage")

    )
    frame.printSchema()
    frame.show()

    //覆盖
    frame.write.mode(SaveMode.Overwrite).jdbc(url,"maxage",properties)




  }

}