第五章 Spark-SQL进阶（二）之数据源

Spark系列文章目录

第一章 初识Spark
第二章 Spark-Core核心模型（一）
第二章 Spark-Core核心模型（二）
第三章 Spark-Core编程进阶（一）
第三章 Spark-Core编程进阶（二）
第四章 Spark-SQL基础（一）
第四章 Spark-SQL基础（二）
第五章 Spark-SQL进阶（一）
第五章 Spark-SQL进阶（二）
第五章 Spark-SQL进阶（三）

---

---

第五章 Spark-SQL进阶（二）

2.数据源

数据以各种不同的格式提供

• 电子表格可以用 XML，CSV，TSV 表示
• 应用程序指标可以用原始文本或 JSON 写出
• 每个用例都有针对它定制的特定数据格式

在大数据领域，我们通常会遇到 Parquet、ORC、Avro、JSON、CSV、SQL 和 NoSQL 数据源以及纯文本文件等格式。

我们可以将这些数据格式大致分为三类：结构化、半结构化和非结构化数据。

2.1结构化数据

结构化数据源定义数据的模式。

• 利用有关底层数据的额外信息，结构化数据源可提供有效的存储和性能。
  • 例如，Parquet和ORC等柱状格式使得从列的子集中提取值变得更加容易。
    • 逐行读取每个记录，然后从感兴趣的特定列中提取值可以读取比查询仅对一小部分列感兴趣时所需的更多数据。
• 基于行的存储格式（如Avro）可有效地序列化和存储提供存储优势的数据。
  • 然而，这些优点通常以灵活性为代价。
    • 例如，由于结构的刚性，演变模式可能具有挑战性。

2.2非结构化数据

相比之下，非结构化数据源通常是自由格式文本或二进制对象，其不包含标记或元数据（例如，CSV文件中的逗号），以定义数据的组织。

• 报纸文章，医疗记录，图像blob，应用程序日志通常被视为非结构化数据。
  • 这些类型的源通常要求数据周围的上下文是可解析的。
  • 也就是说，需要知道该文件是图像或是报纸文章。
• 大多数数据源都是非结构化的。
• 非结构化格式的成本是从这些数据源中提取价值，因为需要许多转换和特征提取技术来解释这些数据集。

2.3半结构化数据

半结构化数据源是按记录构建的，但不一定具有跨越所有记录的明确定义的全局模式。

• 结果，每个数据记录都使用其架构信息进行扩充。
• JSON和XML是很受欢迎的例子。
• 半结构化数据格式的好处是，它们在表达数据时提供了最大的灵活性，因为每条记录都是自我描述的。
• 这些格式在许多应用程序中非常常见，因为存在许多用于处理这些记录的轻量级解析器，并且它们还具有人类可读的优点。
• 但是，这些格式的主要缺点是它们会产生额外的解析开销。

2.4SparkSQL数据源

思考，SparkSQL内置支持哪些数据源？

Spark SQL内置支持的数据源有：

• json
• parquet
• jdbc
• orc
• table
• csv
• text
• avro
• etc

认识两个操作数据源的对象：

• DataFrameReader对象

  • 提供了大部分内置数据源读取数据的方法
  • 获取DataFrameReader对象
    • val dfReader:DataFrameReader=spark.read
  • 常见读取数据的方法
    • load,json,parquet,jdbc,table,csv,text…

• DataFrameWriter对象

  • 提供了大部分内置数据源存储数据的方法

  • 获取DataFrameWriter对象

    • val ds:Dataset[T]=....
val dsWriter:DataFrameWriter=ds.write

  • ​ 常见保存数据的方法

    • save,json,parquet,jdbc,table,csv,text…

注意，SparkSQL支持使用通用、特殊的方式读取不同数据源数据。

2.1通用操作

• 在通用加载读取数据的方式中，默认数据源为org.apache.spark.sql.parquet。
• 默认数据源可以由spark.sql.sources.default设置修改。​
• 数据源类型由它们的完全限定名称(例如org.apache.spark.sql.parquet)指定。
• 对于内置源，可以使用简称json, parquet, jdbc, orc, libsvm, csv, text。

2.1.1 读取数据

方法：load(["文件路径"])

核心代码如下：

val usersDF = spark.read.load("examples/src/main/resources/users.parquet")

2.1.2指定数据源

方法：format("数据源类型")

核心代码如下：

val peopleDF = spark.read.format("json").load("examples/src/main/resources/people.json")

2.1.3配置选项

方法：

• option("key","value")
• options(Map集合)

核心代码如下：

//1.使用option方法指定其他配置选项
val peopleDF1 = spark.read.format("csv")
//设置分隔符
.option("sep", ";") 
//设置开启类型推断
.option("inferSchema", "true") 
//设置数据文件中第一行是否是描述信息(类似于表头)
.option("header", "true")
.load("examples/src/main/resources/people.csv")

//2.使用options方法指定其他配置选项
val map=HashMap("sep"->";","inferSchema"->"true","header"->"true")
val peopleDF2 = spark.read.format("csv")
.options(map)
.load("examples/src/main/resources/people.csv")

2.1.4保存数据

方法：save(["文件路径"])

核心代码如下：

//保存为parquet格式
peopleDF1.select("name", "age").write.save("namesAndAges1.parquet")
//等价于
peopleDF1.select("name", "age").write.format("parquet").save("namesAndAges2.parquet")

//保存为orc格式
usersDF.write.format("orc")
  .option("orc.bloom.filter.columns", "favorite_color")
  .option("orc.dictionary.key.threshold", "1.0")
  .option("orc.column.encoding.direct", "name")
  .save("users_with_options.orc")

2.1.5保存模式

方法：mode(args:SaveMode)

Spark支持的保存模式如下：

Scala/Java	Any Language	Meaning
SaveMode.ErrorIfExists(默认)	"error" or "errorifexists"(默认)	When saving a DataFrame to a data source, if data already exists, an exception is expected to be thrown.
SaveMode.Append	"append"	When saving a DataFrame to a data source, if data/table already exists, contents of the DataFrame are expected to be appended to existing data.
SaveMode.Overwrite	"overwrite"	Overwrite mode means that when saving a DataFrame to a data source, if data/table already exists, existing data is expected to be overwritten by the contents of the DataFrame.
SaveMode.Ignore	"ignore"	Ignore mode means that when saving a DataFrame to a data source, if data already exists, the save operation is expected not to save the contents of the DataFrame and not to change the existing data. This is similar to a CREATE TABLE IF NOT EXISTS in SQL.

核心代码如下：

//将数据按照json格式追加到文件中
peopleDF1.write.mode(SaveMode.Append).format("json").save("data/patent/peopleJson.json")

2.1.6执行SQL

可以直接使用SQL查询文件中的数据，如下：

val sqlDF=spark.sql("SELECT * FROM parquet.`examples/src/main/resources/users.parquet`")

2.1.7保存表

方法：saveAsTable("数据库名.表名")

表类型分为：

• 内部表​
  • 不指定path
  • df.write.saveAsTable("dbName.tableName”)
• 外部表
  • 指定path
  • df.write.option("path", "/some/path").saveAsTable("dbName.tableName”)
• 分区表
  • partitionBy("分区字段")
• 分桶表
  • bucketBy(桶的个数,"分桶字段")
  • sortBy("排序字段")

注意：

• sortBy必须与bucketBy一起使用；
• 目前不支持bucketBy+sortBy使用save方法存储；

2.1.8练习

思考1，partitionBy可以和save一块使用吗？

思考2，回顾内部表和外部表的区别？

数据的位置将由metastore控制，内部表被删除时，数据会自动删除。

练习1 设计

根据数据量以及检索数据的常见形式合理搭配选择存储的表结构。

case class Student(clazz:String,name:String,age:Int)

//1.构建伪数据
val seq=(1 to 100).map(x=>Student((x%2).toString,"briup"+x,x))
val ds=seq.toDS		

//2.存储为内部表
ds.write.saveAsTable("test.student1")
//3.存储为分区表
ds.write.partitionBy("clazz").saveAsTable("test.student2")
//4.存储为分桶表
ds.write.bucketBy(3,"name").saveAsTable("test.student3")
//5.存储为分区分桶外部表
ds.write.partitionBy("clazz").bucketBy(3,"name").option("path","file:///home/briup/student6").saveAsTable("test.student4")
//6.存储为分桶排序表
ds.write.bucketBy(3,"name").sortBy("age").saveAsTable("test.student5")
//7.存储为分区分桶排序外部表
ds.write.partitionBy("clazz").bucketBy(3,"name").sortBy("age").saveAsTable("test.student6")

思考3，在分区表和分桶表中如何选择？

练习2 Mysql

使用jdbc方法操作Mysql中的数据

//1.读取数据
val jdbcDF = spark.read .format("jdbc") 
	.option("url", " jdbc:mysql://localhost:3306/hive") 
	.option("dbtable", "schema.tablename") 
	.option("user", "username") 
	.option("password", "password") 
	.load() 
//2.将数据保存到mysql中
jdbcDF.write.format("jdbc") 
	.option("url", "jdbc:mysql://server0.cloud.briup.com/hive") 
	.option("dbtable", "schema.tablename") 
	.option("user", "username") 
	.option("password", "password") 
	.save()

练习3 HBase

操作HBase中的数据

从Hbase中读取数据示例代码如下：

package com.base

import org.apache.hadoop.hbase.client.Result
import org.apache.hadoop.hbase.io.ImmutableBytesWritable
import org.apache.hadoop.hbase.mapreduce.TableOutputFormat
import org.apache.hadoop.hbase.mapreduce.TableInputFormat
import org.apache.hadoop.hbase.{HBaseConfiguration, HConstants}
import org.apache.spark.sql.SparkSession
import org.apache.hadoop.conf.Configuration
import org.junit.Test
import org.apache.hadoop.hbase.client.Put
import org.apache.hadoop.hbase.util.Bytes
import org.apache.spark.rdd.RDD

class SparkReadHbase {

  val spark = SparkSession
  .builder()
  .appName("Spark SQL basic example")
  .config("spark.master", "local[*]")
  .config("spark.hadoop.validateOutputSpecs",false)
  .getOrCreate()
  val sc=spark.sparkContext
  import spark.implicits._


  @Test
  def loadData()={
    def avroCatalog = s"""{
            |"table":{"namespace":"default", "name":"avrotable"},
            |"rowkey":"key",
            |"columns":{
              |"col0":{"cf":"rowkey", "col":"key", "type":"string"},
              |"col1":{"cf":"cf1", "col":"col1", "avro":"avroSchema"}
            |}
          |}""".stripMargin

    def withCatalog(cat: String): DataFrame = {
      sqlContext
      .read
      .options(Map("avroSchema" -> AvroHBaseRecord.schemaString, HBaseTableCatalog.tableCatalog -> avroCatalog))
      .format("org.apache.spark.sql.execution.datasources.hbase")
      .load()
    }
    val df = withCatalog(catalog)
  }

  @Test
  def putData()={
    def catalog = s"""{
                     |"table":{"namespace":"default", "name":"Avrotable"},
                      |"rowkey":"key",
                      |"columns":{
                      |"col0":{"cf":"rowkey", "col":"key", "type":"string"},
                      |"col1":{"cf":"cf1", "col":"col1", "type":"binary"}
                      |}
                      |}""".stripMargin
    object AvroHBaseRecord {
      val schemaString =
      s"""{"namespace": "example.avro",
         |   "type": "record",      "name": "User",
         |    "fields": [
         |        {"name": "name", "type": "string"},
         |        {"name": "favorite_number",  "type": ["int", "null"]},
         |        {"name": "favorite_color", "type": ["string", "null"]},
         |        {"name": "favorite_array", "type": {"type": "array", "items": "string"}},
         |        {"name": "favorite_map", "type": {"type": "map", "values": "int"}}
         |      ]    }""".stripMargin

      val avroSchema: Schema = {
        val p = new Schema.Parser
        p.parse(schemaString)
      }
      
      

      def apply(i: Int): AvroHBaseRecord = {
        val user = new GenericData.Record(avroSchema);
        user.put("name", s"name${"%03d".format(i)}")
        user.put("favorite_number", i)
        user.put("favorite_color", s"color${"%03d".format(i)}")
        val favoriteArray = new GenericData.Array[String](2, avroSchema.getField("favorite_array").schema())
        favoriteArray.add(s"number${i}")
        favoriteArray.add(s"number${i+1}")
        user.put("favorite_array", favoriteArray)
        import collection.JavaConverters._
        val favoriteMap = Map[String, Int](("key1" -> i), ("key2" -> (i+1))).asJava
        user.put("favorite_map", favoriteMap)
        val avroByte = AvroSedes.serialize(user, avroSchema)
        AvroHBaseRecord(s"name${"%03d".format(i)}", avroByte)
      }
    }

    val data = (0 to 255).map { i =>
      AvroHBaseRecord(i)
    }
    sc.parallelize(data).toDF.write.options(
      Map(HBaseTableCatalog.tableCatalog -> catalog, HBaseTableCatalog.newTable -> "5"))
    .format("org.apache.spark.sql.execution.datasources.hbase")
    .save()
    
  }
  def test()={
    def catalog = s"""{
       |"table":{"namespace":"default", "name":"table1"},
       |"rowkey":"key",
       |"columns":{
         |"col0":{"cf":"rowkey", "col":"key", "type":"string"},
         |"col1":{"cf":"cf1", "col":"col1", "type":"boolean"},
         |"col2":{"cf":"cf2", "col":"col2", "type":"double"},
         |"col3":{"cf":"cf3", "col":"col3", "type":"float"},
         |"col4":{"cf":"cf4", "col":"col4", "type":"int"},
         |"col5":{"cf":"cf5", "col":"col5", "type":"bigint"},
         |"col6":{"cf":"cf6", "col":"col6", "type":"smallint"},
         |"col7":{"cf":"cf7", "col":"col7", "type":"string"},
         |"col8":{"cf":"cf8", "col":"col8", "type":"tinyint"}
       |}
     |}""".stripMargin
    case class HBaseRecord(
   col0: String,
   col1: Boolean,
   col2: Double,
   col3: Float,
   col4: Int,       
   col5: Long,
   col6: Short,
   col7: String,
   col8: Byte)

    object HBaseRecord
    {                                                                                                             
      def apply(i: Int, t: String): HBaseRecord = {
        val s = s"""row${"%03d".format(i)}"""       
        HBaseRecord(s,
                    i % 2 == 0,
                    i.toDouble,
                    i.toFloat,  
                    i,
                    i.toLong,
                    i.toShort,  
                    s"String$i: $t",      
                    i.toByte)
      }
    }

    val data = (0 to 255).map { i =>  HBaseRecord(i, "extra")}

    sc.parallelize(data).toDF.write.options(
      Map(HBaseTableCatalog.tableCatalog -> catalog, HBaseTableCatalog.newTable -> "5"))
    .format("org.apache.hadoop.hbase.spark ")
    .save()
    def withCatalog(cat: String): DataFrame = {
      sqlContext
      .read
      .options(Map(HBaseTableCatalog.tableCatalog->cat))
      .format("org.apache.hadoop.hbase.spark")
      .load()
    }
    val df = withCatalog(catalog)
  }
}

将数据保存到Hbase中示例代码如下：

package com.base

import org.apache.hadoop.hbase.client.{Connection, ConnectionFactory, Put}
import org.apache.hadoop.hbase.util.Bytes
import org.apache.hadoop.hbase.{HBaseConfiguration, HConstants, TableName}
import org.apache.spark.sql.SparkSession

object SparkWriteHbase {



  object ConnectionUtil extends Serializable {
    private val conf = HBaseConfiguration.create()
    conf.set(HConstants.ZOOKEEPER_QUORUM,"localhost:2181")
    conf.set(HConstants.ZOOKEEPER_ZNODE_PARENT, "/hbase")
    private val connection = ConnectionFactory.createConnection(conf)
    def getDefaultConn: Connection = connection
  }

  def main(args: Array[String]): Unit = {
    //1.获取SparkSession对象
    val spark = SparkSession
    .builder()
    .appName("Spark SQL basic example")
    .config("spark.master", "local[*]")
    .getOrCreate()
    val sc=spark.sparkContext
    import spark.implicits._
		//2.获取Dataset对象
    val rdd=sc.parallelize(Seq(1,2,4,5,6,7))
    val ds=spark.createDataset(rdd)
    //3.将DS保存到Hbase中
    ds.foreach(line=>{
      val conn = ConnectionUtil.getDefaultConn
      val tableName = TableName.valueOf("spark")
      val t = conn.getTable(tableName)
      try {
        val put = new Put(Bytes.toBytes(line + System.currentTimeMillis()))
        put.addColumn(Bytes.toBytes("test"), Bytes.toBytes("count"),
                      System.currentTimeMillis(), Bytes.toBytes(line))
        t.put(put)
      } finally {
        t.close()
      }
    })
  }
}

练习4 Hive

Spark-on-Hive，操作Hive数仓中的数据

import java.io.File

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

case class Record(key: Int, value: String)

// warehouseLocation points to the default location for managed databases and tables
val warehouseLocation = new File("spark-warehouse").getAbsolutePath

val spark = SparkSession
  .builder()
  .appName("Spark Hive Example")
  .config("spark.sql.warehouse.dir", warehouseLocation)
  .enableHiveSupport()
  .getOrCreate()

import spark.implicits._
import spark.sql

sql("CREATE TABLE IF NOT EXISTS src (key INT, value STRING) USING hive")
sql("LOAD DATA LOCAL INPATH 'examples/src/main/resources/kv1.txt' INTO TABLE src")

// Queries are expressed in HiveQL
sql("SELECT * FROM src").show()
// +---+-------+
// |key|  value|
// +---+-------+
// |238|val_238|
// | 86| val_86|
// |311|val_311|
// ...

// Aggregation queries are also supported.
sql("SELECT COUNT(*) FROM src").show()
// +--------+
// |count(1)|
// +--------+
// |    500 |
// +--------+

// The results of SQL queries are themselves DataFrames and support all normal functions.
val sqlDF = sql("SELECT key, value FROM src WHERE key < 10 ORDER BY key")

// The items in DataFrames are of type Row, which allows you to access each column by ordinal.
val stringsDS = sqlDF.map {
  case Row(key: Int, value: String) => s"Key: $key, Value: $value"
}
stringsDS.show()
// +--------------------+
// |               value|
// +--------------------+
// |Key: 0, Value: val_0|
// |Key: 0, Value: val_0|
// |Key: 0, Value: val_0|
// ...

// You can also use DataFrames to create temporary views within a SparkSession.
val recordsDF = spark.createDataFrame((1 to 100).map(i => Record(i, s"val_$i")))
recordsDF.createOrReplaceTempView("records")

// Queries can then join DataFrame data with data stored in Hive.
sql("SELECT * FROM records r JOIN src s ON r.key = s.key").show()
// +---+------+---+------+
// |key| value|key| value|
// +---+------+---+------+
// |  2| val_2|  2| val_2|
// |  4| val_4|  4| val_4|
// |  5| val_5|  5| val_5|
// ...

// Create a Hive managed Parquet table, with HQL syntax instead of the Spark SQL native syntax
// `USING hive`
sql("CREATE TABLE hive_records(key int, value string) STORED AS PARQUET")
// Save DataFrame to the Hive managed table
val df = spark.table("src")
df.write.mode(SaveMode.Overwrite).saveAsTable("hive_records")
// After insertion, the Hive managed table has data now
sql("SELECT * FROM hive_records").show()
// +---+-------+
// |key|  value|
// +---+-------+
// |238|val_238|
// | 86| val_86|
// |311|val_311|
// ...

// Prepare a Parquet data directory
val dataDir = "/tmp/parquet_data"
spark.range(10).write.parquet(dataDir)
// Create a Hive external Parquet table
sql(s"CREATE EXTERNAL TABLE hive_bigints(id bigint) STORED AS PARQUET LOCATION '$dataDir'")
// The Hive external table should already have data
sql("SELECT * FROM hive_bigints").show()
// +---+
// | id|
// +---+
// |  0|
// |  1|
// |  2|
// ... Order may vary, as spark processes the partitions in parallel.

// Turn on flag for Hive Dynamic Partitioning
spark.sqlContext.setConf("hive.exec.dynamic.partition", "true")
spark.sqlContext.setConf("hive.exec.dynamic.partition.mode", "nonstrict")
// Create a Hive partitioned table using DataFrame API
df.write.partitionBy("key").format("hive").saveAsTable("hive_part_tbl")
// Partitioned column `key` will be moved to the end of the schema.
sql("SELECT * FROM hive_part_tbl").show()
// +-------+---+
// |  value|key|
// +-------+---+
// |val_238|238|
// | val_86| 86|
// |val_311|311|
// ...

spark.stop()