Spark SQL操作不同数据源、输出操作

Spark踩坑记——数据库（Hbase+Mysql） - xlturing - 博客园

Spark SQL支持通过DataFrame接口操作的多种不同的数据源。DataFrame提供支持统一的接口加载和保存数据源中的数据，包括：结构化数据，Parquet文件，JSON文件，Hive表 ，以及通过JDBC连接外部数据源。

与Hive类似的，Spark SQL也可以创建临时表和持久表（即管理表），使用registerTempTable命令创建临时表，使用saveAsTable命令将数据保存到值就表，该命令将创建一个“管理表”，这也就意味着 数据的位置由Metastore控制，当表删除的时候，管理表将表数据自动删除。

也可以通过配置SaveMode指定如何处理现有数据，实现保存模式不使用任何锁定，而且不是原子操作，因此，在并发环境下操作不能保证数据的安全性，保存模式参数选项如下:

|Scala/Java |python|含义
|-
|SaveMode.ErrorIfExists(default)|“error”|如果保存数据已经存在，抛出异常
|SaveMode.Append|“append”|如果保存数据已经存在，追写DataFrame数据
|SaveMode.Overwrite|“overwrite”|如果保存数据已经存在，重写DataFrame数据
|SaveMode.Ignore|“ignore”|如果保存数据已经存在，忽略DataFrame数据

1、文本数据

Spark SQL可以加载普通的文本文件来创建DataFrame来进行操作。
以下面数据为例:

shinelon,19
mike,20
wangwu,25

操作代码如下： 

case class Person(name: String, age: Int)

val sqlContext=new sql.SQLContext(sc)

import sqlContext.implicits._              //隐式转换，将一个RDD转换为DataFrame

//使用前缀hdfs://来标识HDFS存储系统的文件
val people=sc.textFile("file:///F:/spark-2.0.0/SparkApp/src/cn/just/shinelon/txt/SparkSql02.txt")
            .map(_.split(","))
            .map(p=>Person(p(0),p(1).trim.toInt)).toDF()

//DataFrame注册临时表
people.registerTempTable("person")
//使用sql运行SQL表达式
val result=sqlContext.sql("SELECT name,age from person WHERE age>=19")

println(result.map(t=>"Name:"+t(0)).collect())

        其实在上面将RDD转换为DataFrame，有两种方法，通过反射推断RDD模式和以编程方式定义模式，上面使用了反射方式，另一种方式参考上一篇文章末尾Spark SQL与DataFrame详解以及使用。

2、Parquet格式文件

        使用parquet格式文件，高效，因为其列式存储避免读入不需要的数据，有极好的性能和GC。而且方便压缩和解压缩，有更好的缓存效果。

读取Parquet格式文件，不需要制定表结构，文件自带schame

操作代码如下：

    /**
    * 将普通文本文件转换为parquet数据源来创建临时表
    */
case class Person(name: String, age: Int)

  def parquetTable(sc:SparkContext): Unit ={
    val sqlContext=new SQLContext(sc)
    //隐式转换为一个DataFrame
    import sqlContext.implicits._

    val peopleDF=sc.textFile("file:///F:/spark-2.0.0/SparkApp/src/cn/just/shinelon/txt/SparkSql02.txt")
      .map(_.split(",")).map(p=>Person(p(0),p(1).trim.toInt)).toDF()

    peopleDF.saveAsParquetFile("hdfs://hadoop-senior.shinelon.com:8020/user/shinelon/SparkSql/people.parquet")
    //加载Parquet为DataFrame
    val parquetFile=sqlContext.parquetFile("hdfs://hadoop-senior.shinelon.com:8020/user/shinelon/SparkSql/people.parquet")
    //将DataFrame注册为临时表，提供SQL查询使用
    parquetFile.registerTempTable("parquetTable")

    val result=sqlContext.sql("select name from parquetTable")

    result.map(t=>"Name:"+t(0)).collect().foreach(println)

    /**
      * 运行结果如下：
      * Name:shinelon
        Name:mike
        Name:wangwu
      */
  }

上面程序需要注意的是，parquet文件不能在本地读写，需要在集群上操作，在windows本地操作有可能会报错。

3、Json文件

Spark SQL可以自动推断出一个JSON数据集的Schema并作为一个DataFrame加载。下面是一个简单的实例。

json数据如下：

{"name":"Mirckel"}
{"name":"Andy","age":30}
{"name":"Jsutin","age":13}

def test01(sc:SparkContext): Unit ={
    val sqlContext=new sql.SQLContext(sc)
    val df=sqlContext.jsonFile("file:///F:/spark-2.0.0/SparkApp/src/cn/just/shinelon/txt/SparkSql01.json")
    df.registerTempTable("people")

    println(df.show())      //打印表数据
    println(df.printSchema())  //以树的形式打印DataFrame的Schema
    println(df.select(df("name"),df("age")+1).show())

    val result=sqlContext.sql("select name from people")
    result.foreach(println)
  }

4、Hive表

Spark SQL也可以从Hive表中读写数据，通过创建HiveContext进行一系列的操作。操作Hive表就需要将HIve的相关依赖或者jar包导入项目中，如果创建的是maven工程或者scala工程还必须将hive-site.xml和core-site.xml以及hdfs-site.xml配置文件拷贝到资源文件目录下。

测试的相关数据集格式如下：

238  val_238
86   val_86
311  val_311
27   val_27
165  val_165
409  val_409
255  val_255
278  val_278
98   val_98
即Spark源码中examples\src\main\resources目录下的数据集kv1.txt。读者可以去github中的Spark源码中下载。

  object SparkHiveText {
  def main(args: Array[String]) {
    val conf=new SparkConf().setMaster("local").setAppName("SparkHiveText")
    val sc=new SparkContext(conf)
    val hc=new HiveContext(sc)
    hc.sql("select * from mt1").show()
    sc.stop()
  }
}

  /**
    * Spark SQL集成Hive表
    * 在Spark-shell中可以运行 
    */
  def testHive(sc:SparkContext): Unit ={
    val hiveContext=new HiveContext(sc)
    //创建表
    hiveContext.sql("create table if not exists src (key int,value string)")
    //加载数据
    hiveContext.sql("load data local inpath 'file:///opt/cdh-5.3.6/spark-1.3.0-bin-2.5.0-cdh5.3.6/data/kv1.txt' into table src")
    //查询
    hiveContext.sql("from src select key,value").collect().foreach(println)
     sc.stop()
  } 
   

.scala 

/**
  * 通过spark操作hive  把hive.site.xml放到resources中即可把元数据信息写入配置的mysql中
  */
object HiveSupport {
  def main(args: Array[String]): Unit = {
    //创建sparkSession
val sparkSession: SparkSession = SparkSession.builder()
				.appName("HiveSupport")
				.master("local[2]")
                .config("hadoop.home.dir", "/user/hive/warehouse")
				.enableHiveSupport()
				.getOrCreate()
    //获取sc
    val sc: SparkContext = sparkSession.sparkContext
    sc.setLogLevel("WARN")

    //操作hive
//    sparkSession.sql("create table if not exists person(id int,name string,age int) row format delimited fields terminated by ','")

//    sparkSession.sql("load data local inpath './data/person.txt' into table person")
    sparkSession.sql("select * from person").show()

    sparkSession.stop()

  }
}

.java ：

public class SparkSQLHiveJava {
    public static void main(String[] args) {
        SparkSession spark= SparkSession
                .builder()
                .appName("Java Spark Hive Example")
                .master("local[*]")
                .config("spark.sql.warehouse.dir","hdfs://mycluster/user/hive/warehouse")
                .enableHiveSupport()
                .getOrCreate();

        spark.sql("show databases").show();
        spark.sql("select count(*) from mobike.logs").show();
        spark.sql("select * from mobike.logs").show();
    }
} 

 5、JDBC操作数据库mysql

除了上面介绍的一系列数据源之外，Spark SQL还支持使用JDBC操作关系型数据库，从关系型数据库中读写数据。这里连接mysql数据库。表中的数据如下所示 

 def testJDBC(sc:SparkContext): Unit ={
    val sqlContext=new SQLContext(sc) 
    val jdbcDF=sqlContext.load("jdbc",Map(
      "url"->"jdbc:mysql://127.0.0.1:3306/library?user=root&password=123456",
      "dbtable"->"book",
      "driver"->"com.mysql.jdbc.Driver"
      "user"->"root",
      "password"->"123456"
    )) 
    jdbcDF.show() 
    //lambda表达式
    val r = jdbcDF.filter($"age" <= 13) 
    //val r = jdbcDF.where($"age" <= 13) 
    val reslut: DataFrame = r.select($"id", $"name", $"age" * 10 as "age") 
  }

      Properties props = new Properties();
       props .put( "user" , "root" );
       props .put( "password" , "Spark<3Java" );
       props .put( "useSSL" , "false" ); 
      Dataset<Row> df = spark .read().jdbc(
             "jdbc:mysql://localhost:3306/sakila?serverTimezone=EST" ,
             "actor" , props );
       df = df .orderBy(df.col( "last_name" )); 
       df .show(5);

代码运行结果如下：

Spark SQL操作多数据源_不清不慎的博客-优快云博客_sparksql 多数据源

Spark同步mysql数据到hive

// scala 版
val df = spark.read.format("jdbc").option(
    "url",
    "jdbc:mysql://rr-bp1d22ltxgwa09g44720.mysql.rds.aliyuncs.com/" +
    dbname +
    "?useUnicode=true&characterEncoding=UTF-8"
).option("driver",
    "com.mysql.jdbc.Driver").option(
    "fetchsize", 1000).option(
    "numPartitions", 2).option(
    "dbtable", "(select * from " +
    tablename + ") as t").option(
    "user", "用户名").option(
    "password", "密码").load()

df.write.mode("Overwrite").saveAsTable(
    "写入hive的表名")

/**
  * sparksql读取文本文件，写入数据到mysql中
  */
//todo:创建样例类Student
case class Student(id: Int, name: String, age: Int)

object SparkSqlToMysql {
  def main(args: Array[String]): Unit = {
    //todo:1、创建sparkSession对象
    val spark: SparkSession = SparkSession.builder().appName("SparkSqlToMysql").getOrCreate()
    //todo:2、读取数据
    val data: RDD[String] = spark.sparkContext.textFile(args(0))
    //todo:3、切分每一行,
    val arrRDD: RDD[Array[String]] = data.map(_.split(" "))
    //todo:4、RDD关联Student
    val studentRDD: RDD[Student] = arrRDD.map(x => Student(x(0).toInt, x(1), x(2).toInt))
    //todo:导入隐式转换
    import spark.implicits._
    //todo:5、将RDD转换成DataFrame
    val studentDF: DataFrame = studentRDD.toDF()
    //todo:6、将DataFrame注册成表
    studentDF.createOrReplaceTempView("student")
    //todo:7、操作student表 ,按照年龄进行降序排列
    val resultDF: DataFrame = spark.sql("select * from student order by age desc")

    //todo:8、把结果保存在mysql表中
    //todo:创建Properties对象，配置连接mysql的用户名和密码
    val prop = new Properties()
    prop.setProperty("user", "root")
    prop.setProperty("password", "123456")

    resultDF.write.jdbc("jdbc:mysql://192.168.200.150:3306/spark", "student", prop)

    //  todo:写入mysql时，可以配置插入mode，overwrite覆盖，append追加，ignore忽略，error默认表存在报错
    //resultDF.write.mode(SaveMode.Overwrite).jdbc("jdbc:mysql://192.168.200.150:3306/spark","student",prop)
    spark.stop()
  }
}

6、Spark访问Hbase

object HbaseUtil extends Serializable {
  private val conf = HBaseConfiguration.create()
  private val para = Conf.hbaseConfig // Conf为配置类，获取hbase的配置
  conf.set(HConstants.ZOOKEEPER_CLIENT_PORT, para.get("port").getOrElse("2181"))
  conf.set(HConstants.ZOOKEEPER_QUORUM, para.get("quorum").getOrElse("127-0-0-1"))  // hosts
  private val connection = ConnectionFactory.createConnection(conf)

  def getHbaseConn: Connection = connection
}


//我们以put操作为例，演示将上述设计模式应用到Hbase输出操作当中：

dstream.foreachRDD(rdd => {
  if (!rdd.isEmpty) {
    rdd.foreachPartition(partitionRecords => {
        val connection = HbaseUtil.getHbaseConn // 获取Hbase连接
        partitionRecords.foreach(data => {
            val tableName = TableName.valueOf("tableName")
            val t = connection.getTable(tableName)
            try {
              val put = new Put(Bytes.toBytes(_rowKey_)) // row key
              // column, qualifier, value
              put.addColumn(_column_.getBytes, _qualifier_.getBytes, _value_.getBytes)
              Try(t.put(put)).getOrElse(t.close())
              // do some log（显示在worker上）
            } catch {
              case e: Exception =>
                // log error
                e.printStackTrace()
            } finally {
              t.close()
            }
      })
    })
    // do some log(显示在driver上)
  }
})