Spark 2.1 -- spark SQL , Dataframe 和DataSet 指南

• Overview
  • SQL
  • Datasets and DataFrames
• Getting Started
  • Starting Point: SparkSession
  • Creating DataFrames
  • Untyped Dataset Operations (aka DataFrame Operations)
  • Running SQL Queries Programmatically
  • Global Temporary View
  • Creating Datasets
  • Interoperating with RDDs
    • Inferring the Schema Using Reflection
    • Programmatically Specifying the Schema
• Data Sources
  • Generic Load/Save Functions
    • Manually Specifying Options
    • Run SQL on files directly
    • Save Modes
    • Saving to Persistent Tables
  • Parquet Files
    • Loading Data Programmatically
    • Partition Discovery
    • Schema Merging
    • Hive metastore Parquet table conversion
      • Hive/Parquet Schema Reconciliation
      • Metadata Refreshing
    • Configuration
  • JSON Datasets
  • Hive Tables
    • Interacting with Different Versions of Hive Metastore
  • JDBC To Other Databases
  • Troubleshooting
• Performance Tuning
  • Caching Data In Memory
  • Other Configuration Options
• Distributed SQL Engine
  • Running the Thrift JDBC/ODBC server
  • Running the Spark SQL CLI
• Migration Guide
  • Upgrading From Spark SQL 2.0 to 2.1
  • Upgrading From Spark SQL 1.6 to 2.0
  • Upgrading From Spark SQL 1.5 to 1.6
  • Upgrading From Spark SQL 1.4 to 1.5
  • Upgrading from Spark SQL 1.3 to 1.4
    • DataFrame data reader/writer interface
    • DataFrame.groupBy retains grouping columns
    • Behavior change on DataFrame.withColumn
  • Upgrading from Spark SQL 1.0-1.2 to 1.3
    • Rename of SchemaRDD to DataFrame
    • Unification of the Java and Scala APIs
    • Isolation of Implicit Conversions and Removal of dsl Package (Scala-only)
    • Removal of the type aliases in org.apache.spark.sql for DataType (Scala-only)
    • UDF Registration Moved to sqlContext.udf (Java & Scala)
    • Python DataTypes No Longer Singletons
  • Compatibility with Apache Hive
    • Deploying in Existing Hive Warehouses
    • Supported Hive Features
    • Unsupported Hive Functionality
• Reference
  • Data Types
  • NaN Semantics

一 综述

spark sql 是spark 中处理格式化数据的模块。不像spark RDD 的API， Spark SQL 接口除了提供结构化数据的信息外，还提供了计算优化的信息。本质上， spark SQL 需要额外信息来优化计算，有多种方式使用Spark SQL , 包括SQL的方式和Dataset API的方式。 spark SQL 的计算结果是和计算引擎（API/ 开发语言）无关地。 换句话说， 只要数据处理的逻辑不变，开发人员可以轻而易举地在各种API之间切换。

本指南中使用的数据来自spark 发布程序，可以使用spark-shell , pyspark 和sparkR  shell 运行。 

1.1 SQL

spark sql 用于支持SQL查询，除此而外， spark SQL可以从hive 中读取数据。了解详细spark sql 与hive  集成，见hive tables : http://spark.apache.org/docs/latest/sql-programming-guide.html#hive-tables 

Spark SQL API 返回结果是Dataset或Dataframe 。 除了API外，开发人员还可以使用命令行，或者ODBC/JDBC。 

1.2 Dataset 和Dataframe

Dataset 是分布式数据集， dataset的API是在spark 1.6版本中添加地，它的初衷是为了提升RDD（强类型限制， 可以使用lambda函数）优化SQL执行引擎。Dataset是JVM中的一个对象，可以作用于其它操作（map,faltMap, filter等）。Dataset API提供Scala /Java 语言支持。 Python 暂不支持Dataset API， 但由于python的动态特性，已经支持 部分dataset API （例如， 用row.columnName来读取行数据的某列），这个例子和R语言的类似。

Dataframe 与dataset 基本类似 ，只是多了列名这些信息。 概念上，它类似于关系数据的表，及R/python 中的dataframe  但在执行效率上进一步优化。 可以从很多数据源中生成dataframe ， 如 结构化数据文件，hive表， 外部数据库，或 RDD。 Dataframe API支持 scala / java / python 和R 。在scala和java 中，dataframe  实际表示为dataset的行数据 。 在scala API 中，Dataframe 实际是Dataset[Row]的别名，但在java API 中， 开发人员需要使用Dataset<Row> 来表示 DataFrame 。 

通篇只scala Dataset行数据表示 DataFrame 

2  开始

2.1 开始之旅 ： SparkSession 

spark 所有功能都始于SparkSession 类， 使用SparkSession.builder()来创建一个SparkSession 。

import org.apache.spark.sql.SparkSession

val spark = SparkSession
  .builder()
  .appName("Spark SQL basic example")
  .config("spark.some.config.option", "some-value")
  .getOrCreate()

// For implicit conversions like converting RDDs to DataFrames

import spark.implicits._

完整例子见： “ examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala” 

Spark 2.0 中SparkSession 内置支持hive特性，包括编写hiveQL ， 使用Hive UDF ， 读取hive 表数据。 并且以上不需要安装hive 。

2.2 创建DataFrame

创建了SparkSession后， 应用程序可以从已存在RDD上创建DataFrame, 从hive表，或其它Spark 数据源。以下例子从json文件中创建DataFrame

val df = spark.read.json("examples/src/main/resources/people.json")

// Displays the content of the DataFrame to stdout

df.show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

 完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.3 非强制类型的Dataset 变换（也就是DataFrame 变换）

DataFrame 提供结构化数据变换DSL（特定领域语言），支持scala/java/python/R。 上面提致， Spark 2.0中scala/java API中， Dataframe 只是Dataset行数据 。 因此，这些操作称为“非强制类型变换”，相对于scala/java 中Dataset 这类“强制类型变换”。

下面是使用Dataset 处理结构化数据的简单例子： 

// This import is needed to use the $-notation

import spark.implicits._

// Print the schema in a tree format

df.printSchema()

// root

// |-- age: long (nullable = true)

// |-- name: string (nullable = true)

// Select only the "name" column

df.select("name").show()

// +-------+

// |   name|

// +-------+

// |Michael|

// |   Andy|

// | Justin|

// +-------+

// Select everybody, but increment the age by 1

df.select($"name", $"age" + 1).show()

// +-------+---------+

// |   name|(age + 1)|

// +-------+---------+

// |Michael|     null|

// |   Andy|       31|

// | Justin|       20|

// +-------+---------+

// Select people older than 21

df.filter($"age" > 21).show()

// +---+----+

// |age|name|

// +---+----+

// | 30|Andy|

// +---+----+

// Count people by age

df.groupBy("age").count().show()

// +----+-----+

// | age|count|

// +----+-----+

// |  19|    1|

// |null|    1|

// |  30|    1|

// +----+-----+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

详细查看DataSet 支持的操作集，详见API 文档 ： http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Dataset

除了简单的列引用和表达式外，Dataset 提供了丰富的功能库，包括 字符串操作，日期计算，通用数据操作等。 详见 DataFrame 函数参考：http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$

2.4 SQL查询编程

SparkSession 上的sql 函数允许程序执行SQL查询， 返回Dataframe 结果

// Register the DataFrame as a SQL temporary view

df.createOrReplaceTempView("people")

val sqlDF = spark.sql("SELECT * FROM people")

sqlDF.show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.5 全局临时视图

spark sql 中临时视图是session范围内有效，当创建视图的session退出后，视图随着消亡。如果想拥有一个临时视图可以在所有session中共享，

并且可以存活直到spark 应用程序退出， 那就需要创建全局临时视图。 全局临时视图保存在数据库 global_temp 中，如果想引用一个全局视图，需要使用全名，如 select * from global_temp.view1。

// Register the DataFrame as a global temporary view

df.createGlobalTempView("people")

// Global temporary view is tied to a system preserved database `global_temp`

spark.sql("SELECT * FROM global_temp.people").show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

// Global temporary view is cross-session

spark.newSession().sql("SELECT * FROM global_temp.people").show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.6  创建datasets

Dataset 和RDD 类似， 除了Dataset没有使用java 序列化类和kryo外，而是引入了特定编码器来序列化计算数据，及在网络中传输数据。 编码器和标准序列化类都会将对象转化为字节， 编码器是动态生成的代码， 这种代码使用的数据格式在进行操作时，如过滤，排序或hash时，不需要将字节反序列为对象，就可直接操作。

// Note: Case classes in Scala 2.10 can support only up to 22 fields. To work around this limit,

// you can use custom classes that implement the Product interface

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

// Encoders are created for case classes

val caseClassDS = Seq(Person("Andy", 32)).toDS()caseClassDS.show()

// +----+---+

// |name|age|

// +----+---+

// |Andy| 32|

// +----+---+

// Encoders for most common types are automatically provided by importing spark.implicits._

val primitiveDS = Seq(1, 2, 3).toDS()primitiveDS.map(_ + 1).collect()

// Returns: Array(2, 3, 4)

// DataFrames can be converted to a Dataset by providing a class. Mapping will be done by name

val path = "examples/src/main/resources/people.json"

val peopleDS = spark.read.json(path).as[Person]peopleDS.show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.7 与RDD的相互操作

spark sql 支持两种方法，将已有RDD转化为Datasets。第一种方法是反射，分析 RDD中所承载的数据的类型，推断RDD转化为Dataset的schema。 这种反射的缺点是需要写很多额外 的代码，并且你需要预先知道spark应用程序所处理数据的schema .

第二种方法是通过程序接口的方式构造一个schema， 将这个schema与RDD关联在一起。此方法虽然繁琐，但却可以实现列数及列类型在运行期才可知的场景。

2.7.1 使用反射推断schema

Spark SQL 的scala API 支持将case class 的RDD，自动转化为Dataframe 。 case class定义表的schema， 使用反射读取case class

的参数， 这些参数最终变成列名。 case class 可以嵌套，可以包含复杂类型,如 Seqs 或 Arrays。 此RDD可以隐式转化为Dataframe，

并注册为一个表，紧接着可以用SQL查询表数据。

import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder

import org.apache.spark.sql.Encoder

// For implicit conversions from RDDs to DataFrames

import spark.implicits._

// Create an RDD of Person objects from a text file, convert it to a Dataframe

val peopleDF = spark.sparkContext

  .textFile("examples/src/main/resources/people.txt")
  .map(_.split(","))
  .map(attributes => Person(attributes(0), attributes(1).trim.toInt))

  .toDF()

// Register the DataFrame as a temporary view

peopleDF.createOrReplaceTempView("people")

// SQL statements can be run by using the sql methods provided by Spark

val teenagersDF = spark.sql("SELECT name, age FROM people WHERE age BETWEEN 13 AND 19")

// The columns of a row in the result can be accessed by field index

teenagersDF.map(teenager => "Name: " + teenager(0)).show()

// +------------+

// |       value|

// +------------+

// |Name: Justin|

// +------------+

// or by field name

teenagersDF.map(teenager => "Name: " + teenager.getAs[String]("name")).show()

// +------------+

// |       value|

// +------------+

// |Name: Justin|

// +------------+

// No pre-defined encoders for Dataset[Map[K,V]], define explicitly

implicit val mapEncoder = org.apache.spark.sql.Encoders.kryo[Map[String, Any]]

// Primitive types and case classes can be also defined as

// implicit val stringIntMapEncoder: Encoder[Map[String, Any]] = ExpressionEncoder()

// row.getValuesMap[T] retrieves multiple columns at once into a Map[String,T]

teenagersDF.map(teenager => teenager.getValuesMap[Any](List("name", "age"))).collect()

// Array(Map("name" -> "Justin", "age" -> 19))

 

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.7.2 编程设置schema

前述方法的缺点：事先需要能知道表数据的列个数和列类型，否则地话，只能通过编程设置schema。需要遵照以下三步：

1 从原始RDD 创建一个RDD的行数据

2 创建一个StructType 类型表示schema ， 需要与1 中行数据对应

3 将schema 应用于RDD行数据 ，通过 SparkSession 的createDataFrame方法

例如： 

import org.apache.spark.sql.types._

// Create an RDD

val peopleRDD = spark.sparkContext.textFile("examples/src/main/resources/people.txt")

// The schema is encoded in a string

val schemaString = "name age"

// Generate the schema based on the string of schema

val fields = schemaString.split(" ")

  .map(fieldName => StructField(fieldName, StringType, nullable = true))

val schema = StructType(fields)

// Convert records of the RDD (people) to Rows

val rowRDD = peopleRDD

  .map(_.split(","))
  .map(attributes => Row(attributes(0), attributes(1).trim))

// Apply the schema to the RDD

val peopleDF = spark.createDataFrame(rowRDD, schema)

// Creates a temporary view using the DataFrame

peopleDF.createOrReplaceTempView("people")

// SQL can be run over a temporary view created using DataFrames

val results = spark.sql("SELECT name FROM people")

// The results of SQL queries are DataFrames and support all the normal RDD operations

// The columns of a row in the result can be accessed by field index or by field name

results.map(attributes => "Name: " + attributes(0)).show()

// +-------------+

// |        value|

// +-------------+

// |Name: Michael|

// |   Name: Andy|

// | Name: Justin|

// +-------------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

3 data source 数据源

spark SQL Dataframe接口支持多种数据源，DataFrame  可以使用关系变换和创建临时视图。将Dataframe注册为临时视图，可以在视图上执行SQL查询。 本章节讲述spark 数据源地通用加载/保存方法，然后深入内置数据源

3.1 通用 load/save 函数 

默认数据源（默认是parquet ， 除非配置spark.sql.sources.default）支持多种操作。

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

usersDF.select("name", "favorite_color").write.save("namesAndFavColors.parquet")

完整例子 ：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.1.1 手动确定参数 

开发人员可以手动设置data source 数据源， 这样可以添加额外的参数到data source中。数据源的名字使用完整修饰名（如 org.apache.spark.sql.parquet），但内置数据源可以使用短名（json , parquet, jdbc, orc , libsvm ,csv , text）。由任意数据源加载到

DataFrame 后，可以转化为其它任意类型。

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

peopleDF.select("name", "age").write.format("parquet").save("namesAndAges.parquet")

完整例子：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.1.2 直接在文件上使用SQL

除了使用API加载 文件到dataframe，并且查询外 ，可以直接对文件使用SQL查询。

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

完整例子：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.1.3 保存模式

保存操作可以使用SaveMode参数，此参数 可以确认如何保存数据。 需要特别注意的是，此处的保存模式并没有附加任务锁机制，也不是原子操作。 除此而外， 将使用 Overwrite 时， 老数据会删除。

Scala/JavaAny LanguageMeaningSaveMode.ErrorIfExists(default)"error"(default)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 to not save the contents of the DataFrame and to not change the existing data. This is similar to a CREATE TABLE IF NOT EXISTS in SQL.

3.1.4 持久化成表

Dataframe 可以持久化成hive 表，使用saveAsTable 可以将表注册到hive metastore中。注意到环境中可以没有安装hive ， spark 会创建一个本地hive metastore(derby保存元数据)。与 createeOrReplaceTempView 接口， saveAsTable 会将Dataframe的数据物化为表，在hive metastore中创建一个真实的表。 持久化的表会持续存在，只要spark使用同一个metastore ，

即使退出spark程序，下次再次启动spark程序时仍然有效。 使用SparkSession 的table 方法，将表名作为参数可以将Dataframe 持久化成表。

默认情况， saveAsTable会创建一个“管理表” ， 意味着Hives metastore 可以管理表数据的路径。当表被drop掉时，表数据相应的也会被删除。 

3.2 Parquet 文件

parquet 文件是列存储格式，很多数据处理系统均支持此类型。 Spark SQL 提供支持读和写parquet文件的同时，将文件源数据的schema保存。当保存parquet文件时，所有列会自动转化为可为空的类型。

3.2.1 程序加载数据

使用上例中数据 

// Encoders for most common types are automatically provided by importing spark.implicits._

import spark.implicits._

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

// DataFrames can be saved as Parquet files, maintaining the schema information

peopleDF.write.parquet("people.parquet")

// Read in the parquet file created above

// Parquet files are self-describing so the schema is preserved

// The result of loading a Parquet file is also a DataFrame

val parquetFileDF = spark.read.parquet("people.parquet")

// Parquet files can also be used to create a temporary view and then used in SQL statements

parquetFileDF.createOrReplaceTempView("parquetFile")

val namesDF = spark.sql("SELECT name FROM parquetFile WHERE age BETWEEN 13 AND 19")

namesDF.map(attributes => "Name: " + attributes(0)).show()

// +------------+

// |       value|

// +------------+

// |Name: Justin|

// +------------+

详细例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.2.2  分区自动发现

表分区是常用的代码手段，就像hive中表分区一样。对于分区表，不同分区的数据会存放到不同的目录，分区列的值转化为分区目录。 parquet 数据源现在支持自动发现和推断分区信息。

例如， 可以将把有人口的信息存储在分区表里，使用如下的目录结构， 除分区列外，还有两个列  性别 和 国别。

path
└── to
    └── table
        ├── gender=male
        │   ├── ...
        │   │
        │   ├── country=US
        │   │   └── data.parquet
        │   ├── country=CN
        │   │   └── data.parquet
        │   └── ...
        └── gender=female
            ├── ...
            │
            ├── country=US
            │   └── data.parquet
            ├── country=CN
            │   └── data.parquet
            └── ...

将表的路径 传给方法SparkSession.read.parquet 或 SparkSession.read.load ， Spark SQL 会自动识别分区信息，返回DataFrame的schema 为: 

root
|-- name: string (nullable = true)
|-- age: long (nullable = true)
|-- gender: string (nullable = true)
|-- country: string (nullable = true)

注意到，分区字段的类型是自动推断地，当前，支持数值型和字符型。有时，开发人员希望可以自动推断分区字段的类型。在这种情况，可以配置自动类型推断功能开关 spark.sql.sources.partitionColumnTypeInference.enabled ，默认此功能开启。  当关闭此功能时，分区字段的类型默认为字符串类型。

从spark 1.6.0之后，只能自动发现给定目录下的分区目录，在上例中， 如果把目录 path/to/table/gender=male 传给SparkSession.read.parquet 或 SparkSession.read.load, 那么gender(性别)

字段会认为是分区列。 如果想手动指定分区自动发现的起始眯眯眼不，需要在数据源中设置basePath 参数 。 例如 ， 假设 path/to/table/gender=male 是数据目录， 设置basePath 为 path/to/table ,

那么分区列是gender

 

3.2.3 schema 合并

和protocolBuffer ，avro ,thrift 一样， parquet 支持schema 演进。 开发人员可以从几个简单的schema 开始，慢慢添加更多的列到schema中。 这样， 开发人员最终可以深化出多个不同的parquet 文件，

便这些Parquet 文件schema 相互兼容。 parquet 数据源现在支持自动检测 这种情况，并自动将所有文件schema合并。

因为schema 合并是很耗时的操作， 因此在大多数情况下没有必要合并，从1.5.0 版本之后，默认关闭parquet 文件地schema自动合并功能，可以通过以下两种方式打开:

1 读取parquet 文件时，设置数据源参数  mergeSchema 为 true 

2 设置全局SQL 参数 spark.sql.parquet.mergeSchema 为 true 

// This is used to implicitly convert an RDD to a DataFrame.

import spark.implicits._

// Create a simple DataFrame, store into a partition directory

val squaresDF = spark.sparkContext.makeRDD(1 to 5).map(i => (i, i * i)).toDF("value", "square")

squaresDF.write.parquet("data/test_table/key=1")

// Create another DataFrame in a new partition directory,

// adding a new column and dropping an existing column

val cubesDF = spark.sparkContext.makeRDD(6 to 10).map(i => (i, i * i * i)).toDF("value", "cube")

cubesDF.write.parquet("data/test_table/key=2")

// Read the partitioned table

val mergedDF = spark.read.option("mergeSchema", "true").parquet("data/test_table")

mergedDF.printSchema()

// The final schema consists of all 3 columns in the Parquet files together

// with the partitioning column appeared in the partition directory paths

// root

//  |-- value: int (nullable = true)

//  |-- square: int (nullable = true)

//  |-- cube: int (nullable = true)

//  |-- key: int (nullable = true)

详细例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.2.4 hive metastore 表与parquet 文件schema转化 

当读取或写数据到hive metastore的parquet 表时， spark SQL 使用自带的parquet 库来操作，为了达到更好的性能而不使用hive SerDe 。 可以使用spark.sql.hive.convertMetastoreParquet 开关，默认是打开。

3.2.4.1 hive/parquet schema 互换

在处理表/schema 的观点来看，hive 的表和parquet 的schema 两者是不同的。

1 hive 是大小写乎略地，parquet 却是大小写敏感

2 hive 认为所有列都是可空的nullable , 但parquet 的nullable却有不同的拿含意。

由于以上原因， 需要在hive 表的schema和 parquet schema 之间的转化 。 当需要将hive metastore parquet表转化为 spark SQL  parquet 表时，互换规则为： 

1 从parquet 文件schema 到hive metastore parquet 表，只要保证同一个字段类型相同即可，可以不必考虑nullable修饰符，但hive metastore parquet 表到parquet schema时，除了同一个字段类型一致，同时要考虑Nullable修饰。

2 当hive metastore  schema中定义如下字段时，parquet 文件schema 也应该作如下处理

1 >  只出现在parquet 文件schema 的字段， 在hive metastore 表中不能出现

2 > 只出现在hive metastore 表中的字段， 应该添加到parquet  文件schema 中，同时定义为nullable 。

3.2.4.2 metadata （元数据）刷新

spark sql 缓存parquet 表数据 metadata ， 当hive metastore  parquet 表转化parquet 文件开启， 转化后的表元数据仍然会缓存。 如果此时hive或其它工具更新了hive metastore 的元数据，那么需要手动更新一下元数据，以保证缓存中的元数据与实际元数据一致。

 

// spark is an existing SparkSession

spark.catalog.refreshTable("my_table")

 

3.2.5 配置信息

通过 SparkSession 的setConf 方法可以配置parquet  信息，或者在SQL中使用set key = value。

Property NameDefaultMeaningspark.sql.parquet.binaryAsStringfalseSome other Parquet-producing systems, in particular Impala, Hive, and older versions of Spark SQL, do not differentiate between binary data and strings when writing out the Parquet schema. This flag tells Spark SQL to interpret binary data as a string to provide compatibility with these systems.spark.sql.parquet.int96AsTimestamptrueSome Parquet-producing systems, in particular Impala and Hive, store Timestamp into INT96. This flag tells Spark SQL to interpret INT96 data as a timestamp to provide compatibility with these systems.spark.sql.parquet.cacheMetadatatrueTurns on caching of Parquet schema metadata. Can speed up querying of static data.spark.sql.parquet.compression.codecsnappySets the compression codec use when writing Parquet files. Acceptable values include: uncompressed, snappy, gzip, lzo.spark.sql.parquet.filterPushdowntrueEnables Parquet filter push-down optimization when set to true.spark.sql.hive.convertMetastoreParquettrueWhen set to false, Spark SQL will use the Hive SerDe for parquet tables instead of the built in support.spark.sql.parquet.mergeSchemafalse

When true, the Parquet data source merges schemas collected from all data files, otherwise the schema is picked from the summary file or a random data file if no summary file is available.

spark.sql.optimizer.metadataOnlytrue

When true, enable the metadata-only query optimization that use the table's metadata to produce the partition columns instead of table scans. It applies when all the columns scanned are partition columns and the query has an aggregate operator that satisfies distinct semantics.

3.3 JSON Datasets

Spark SQL 可以自动推荐json 数据集的schema ,并加载到Dataset[Row] 中。使用SparkSession.read.json() 方法作用于RDD String,或者 JSON文件。

注意，我们说的json 并不是通常讲的json文件， 每行包含一个有分隔符，自完整的有效json 对象。 更多信息，详见：  JSON Lines text format, also called newline-delimited JSON  ： http://jsonlines.org/

结果，  通用的json文件经常会失败

// A JSON dataset is pointed to by path.

// The path can be either a single text file or a directory storing text files

val path = "examples/src/main/resources/people.json"

val peopleDF = spark.read.json(path)

// The inferred schema can be visualized using the printSchema() method

peopleDF.printSchema()

// root

//  |-- age: long (nullable = true)

//  |-- name: string (nullable = true)

// Creates a temporary view using the DataFrame

peopleDF.createOrReplaceTempView("people")

// SQL statements can be run by using the sql methods provided by spark

val teenagerNamesDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13 AND 19")

teenagerNamesDF.show()

// +------+

// |  name|

// +------+

// |Justin|

// +------+

// Alternatively, a DataFrame can be created for a JSON dataset represented by

// an RDD[String] storing one JSON object per string

val otherPeopleRDD = spark.sparkContext.makeRDD(

  """{"name":"Yin","address":{"city":"Columbus","state":"Ohio"}}""" :: Nil)

val otherPeople = spark.read.json(otherPeopleRDD)

otherPeople.show()

// +---------------+----+

// |        address|name|

// +---------------+----+

// |[Columbus,Ohio]| Yin|

// +---------------+----+

完整例子见：   examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.4 hive 表

Spark SQL 支持读取和写数据到hive中， 尽管如此， hive 有很大数量的依赖， 这些依赖在spark发行版本中并不包含。 如果hive依赖包在CLASSPATH中可以找到，spark 会自动加载。注意到，worker节点同样要加载这些依赖， 因为spark executor会从hive 的表中直接读取和写数据，因此依赖于hive 自己的序列化类和反序列化类。

为了使spark 支持hive ,需要将hive-site.xml , core-site.xml 和 hdfs-site.xml  文件放到spark 的conf目录。

为了使用hive ,  需要使用SparkSession 实例， 同时 也可心连接持久化的hive metastore ， 支持Hive序列化， hive用户自定义函数。 环境中如果未部署hive， 仍然可以使用spark 支持hive ，只是没有hive-site.xml 文件时， spark 会自动在本地创建 metastore_db目录来存储元数据 ，创建 spark.sql.warehouse.dir 定义的目录，这个目录默认是在当前目录下spark-warehouse ， 用于spark 应用程序起动后使用。 注意到 hive-site.xml 中hive.metastore.warehouse.dir从spark .2.0.0 后过期， 取而代之， 使用 spark.sql.warehouse.dir 来指定数据仓库默认的数据库。 启动 spark 应用的用户需要给这个目录赋写权限 。

import org.apache.spark.sql.Row

import org.apache.spark.sql.SparkSession

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

// warehouseLocation points to the default location for managed databases and tables

val warehouseLocation = "spark-warehouse"

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)")

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 DaraFrames 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|

// ...

 

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/hive/SparkHiveExample.scala

3.4.1 不同版本的hive metastore 交互

spark SQL 支持 hive 最关键是可以兼容多个版本， 这样spark sql 可以读取Hive 表的数据 。 从spark 1.4.0 开始， 任意一个发行版本可以读取多种hive 版本的数据 ， 详细见下。 hive 每个版本独立访问各版本的hive metastore ， 但是 spark sql 是参考 hive 1.2.1 版本编译， 使用此版本实现诸如序列与反序列化， UDF， UDAF 等。

下面的参数需要在各版本spark 版本中配置

Property NameDefaultMeaningspark.sql.hive.metastore.version1.2.1Version of the Hive metastore. Available options are 0.12.0through 1.2.1.spark.sql.hive.metastore.jarsbuiltinLocation of the jars that should be used to instantiate the HiveMetastoreClient. This property can be one of three options:

1. builtinUse Hive 1.2.1, which is bundled with the Spark assembly when -Phive is enabled. When this option is chosen, spark.sql.hive.metastore.version must be either 1.2.1or not defined.
2. mavenUse Hive jars of specified version downloaded from Maven repositories. This configuration is not generally recommended for production deployments.
3. A classpath in the standard format for the JVM. This classpath must include all of Hive and its dependencies, including the correct version of Hadoop. These jars only need to be present on the driver, but if you are running in yarn cluster mode then you must ensure they are packaged with your application.

spark.sql.hive.metastore.sharedPrefixescom.mysql.jdbc,
org.postgresql,
com.microsoft.sqlserver,
oracle.jdbc

A comma separated list of class prefixes that should be loaded using the classloader that is shared between SparkSQL and a specific version of Hive. An example of classes that should be shared is JDBC drivers that are needed to talk to the metastore. Other classes that need to be shared are those that interact with classes that are already shared. For example, custom appenders that are used by log4j.

spark.sql.hive.metastore.barrierPrefixes(empty)

A comma separated list of class prefixes that should explicitly be reloaded for each version of hive that Spark SQL is communicating with. For example, Hive UDFs that are declared in a prefix that typically would be shared (i.e. org.apache.spark.*).

3.5 通过 JDBC访问其它数据库

 spark SQL 支持通过 JDBC访问其它数据源。spark 使用jdbcRDD 来实现 这个功能。 主要是因为返回的结果集是DataFrame， 可以很容易用spark SQL 处理，或者和其它数据源关联。

java 和python 可以容易使用JDBC数据源， 并且不需要ClassTag 就可以处理数据 。

需要开发人员将JDBC driver放置到 spark classpath 中，例如， 使用spark shell  连接postgres  ， 需要使用以下命令行

bin/spark-shell --driver-class-path postgresql-9.4.1207.jar --jars postgresql-9.4.1207.jar

使用Data Source API 可以加载远程数据库的表数据， 生成DataFrame 或Spark SQL 临时视图。 开发人员可以配置JDBC连接属性， user 和password 是连接数据库的参数 ，除此而外，spark 还支持以下参数：

Property NameMeaningurlThe JDBC URL to connect to. The source-specific connection properties may be specified in the URL. e.g., jdbc:postgresql://localhost/test?user=fred&password=secretdbtableThe JDBC table that should be read. Note that anything that is valid in a FROM clause of a SQL query can be used. For example, instead of a full table you could also use a subquery in parentheses.driverThe class name of the JDBC driver to use to connect to this URL.partitionColumn, lowerBound, upperBound, numPartitionsThese options must all be specified if any of them is specified. They describe how to partition the table when reading in parallel from multiple workers. partitionColumn must be a numeric column from the table in question. Notice that lowerBound and upperBound are just used to decide the partition stride, not for filtering the rows in table. So all rows in the table will be partitioned and returned. This option applies only to reading.fetchsizeThe JDBC fetch size, which determines how many rows to fetch per round trip. This can help performance on JDBC drivers which default to low fetch size (eg. Oracle with 10 rows). This option applies only to reading.batchsizeThe JDBC batch size, which determines how many rows to insert per round trip. This can help performance on JDBC drivers. This option applies only to writing. It defaults to 1000.isolationLevelThe transaction isolation level, which applies to current connection. It can be one of NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, or SERIALIZABLE, corresponding to standard transaction isolation levels defined by JDBC's Connection object, with default of READ_UNCOMMITTED. This option applies only to writing. Please refer the documentation in java.sql.Connection.truncateThis is a JDBC writer related option. When SaveMode.Overwrite is enabled, this option causes Spark to truncate an existing table instead of dropping and recreating it. This can be more efficient, and prevents the table metadata (e.g., indices) from being removed. However, it will not work in some cases, such as when the new data has a different schema. It defaults to false. This option applies only to writing.createTableOptionsThis is a JDBC writer related option. If specified, this option allows setting of database-specific table and partition options when creating a table (e.g., CREATE TABLE t (name string) ENGINE=InnoDB.). This option applies only to writing.

 

代码：

// Note: JDBC loading and saving can be achieved via either the load/save or jdbc methods

// Loading data from a JDBC source

val jdbcDF = spark.read

  .format("jdbc")
  .option("url", "jdbc:postgresql:dbserver")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load()

val connectionProperties = new Properties()

connectionProperties.put("user", "username")

connectionProperties.put("password", "password")

val jdbcDF2 = spark.read

  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties)

// Saving data to a JDBC source

jdbcDF.write

  .format("jdbc")
  .option("url", "jdbc:postgresql:dbserver")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save()

jdbcDF2.write
  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties)

详细代码见：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.6 问题解决

1> 客户端session 和所有executor 的原始加载类支持JDBC。  由于java DriverManager 类对driver 进行安全检查，这样会乎略不属于原始加载类的driver ， 当spark 要打开一个connection。 最好的方法是修改所有worker主机的compute_classpath.sh ， 使得worker会自己加载Driver 类。

2> 一些数据库， 如H2 , 会把所有名字转化为大写， 这样在Spark SQL 中需要使用大写名字。

4 性能优化

在大量负载的情况下，可能通过将数据缓存在内存，以及调整经验参数达到提升性能的目的。

4.1 缓存数据

可以将Spark SQL 的表数据缓存在内存，调用 spark.cacheTable("tableName") 或 dataFrame.cache()  可以保存为列式格式。 这样Spark SQL 只会扫描需要的列， 并将数据压缩以减少内存和GC压力。 可以使用spark.uncacheTable("tableName") 来清除缓存表。

使用SparkSession的setConf 方法可以配置缓存的属性，或者在SQL 中使用SET key=value命令。

Property NameDefaultMeaningspark.sql.inMemoryColumnarStorage.compressedtrueWhen set to true Spark SQL will automatically select a compression codec for each column based on statistics of the data.spark.sql.inMemoryColumnarStorage.batchSize10000Controls the size of batches for columnar caching. Larger batch sizes can improve memory utilization and compression, but risk OOMs when caching data.

4.2 其它配置参数

以下参数也可以提升SQL执行性能。但是这些参数可能未来会过期， 或者默认打开状态。

 

Property Name

DefaultMeaningspark.sql.files.maxPartitionBytes134217728 (128 MB)The maximum number of bytes to pack into a single partition when reading files.spark.sql.files.openCostInBytes4194304 (4 MB)The estimated cost to open a file, measured by the number of bytes could be scanned in the same time. This is used when putting multiple files into a partition. It is better to over estimated, then the partitions with small files will be faster than partitions with bigger files (which is scheduled first).spark.sql.broadcastTimeout300

Timeout in seconds for the broadcast wait time in broadcast joins

spark.sql.autoBroadcastJoinThreshold10485760 (10 MB)Configures the maximum size in bytes for a table that will be broadcast to all worker nodes when performing a join. By setting this value to -1 broadcasting can be disabled. Note that currently statistics are only supported for Hive Metastore tables where the command ANALYZE TABLE <tableName> COMPUTE STATISTICS noscan has been run.spark.sql.shuffle.partitions200Configures the number of partitions to use when shuffling data for joins or aggregations.

5 分布式SQL引擎

Spark SQL 可以作为分布式查询引擎，使用JDBC/ODBC 或命令行接口。在这种模式下， 终端用户或应用开发者，使用Spark SQL 运行SQL查询，而不需要其它代码。

5.1 运行Thrift JDBC/ODBC 服务

hive1.2.1 的HiveServerr2 自带Thrift JDBC/ODBC 服务， 可以使用hive1.2.1 和spark 自带的beeline  测试JDBC服务 。

启动 JDBC/ODBC 服务 ，运行以下命令

./sbin/start-thriftserver.sh

此脚本支持bin/spark-submit 命令的所有参数 ，另外 可以使用 --hiveconf 来配置hive 的 属性。 也可以使用 ./sbin/start-thriftserver.sh --help 来获取完整参数列表。 默认， 服务器监听localhost:10000, 通过以下环境变量可以修改这些参数。

export HIVE_SERVER2_THRIFT_PORT=<listening-port>
export HIVE_SERVER2_THRIFT_BIND_HOST=<listening-host>
./sbin/start-thriftserver.sh \
  --master <master-uri> \

  ...

或者系统属性：

./sbin/start-thriftserver.sh \
  --hiveconf hive.server2.thrift.port=<listening-port> \
  --hiveconf hive.server2.thrift.bind.host=<listening-host> \
  --master <master-uri>
  ...

可以使用beeline 来测试thrift JDBC/ODBC 服务 ： 

./bin/beeline

使用beeline连接 JDBC/ODBC 服务

beeline> !connect jdbc:hive2://localhost:10000

beeline 要求输入用户名和密码， 在非安全模式，  只需要输入用户名，而不用填写密码。 在安全模式下， 需要参考 beeline 文档 ： https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients

配置hive 需要将hive-site.xml , core-site.xml 和hdfs-site.xml 文件放到conf/下。 

也可以使用Hive自带的beeline 

thrift JDBC 服务支持通过 http 发送 thrift RPC 消息, 以下配置项可以开启http模式， 或者添加到 hive-site.xml 配置文件

hive.server2.transport.mode - Set this to value: http
hive.server2.thrift.http.port - HTTP port number fo listen on; default is 10001
hive.server2.http.endpoint - HTTP endpoint; default is cliservice

测试可以使用beeline http协议连接JDBC/ODBC 服务

beeline> !connect jdbc:hive2://<host>:<port>/<database>?hive.server2.transport.mode=http;hive.server2.thrift.http.path=<http_endpoint>

 

5.2 运行Spark SQL CLI

Spark SQL CLI 可以很方便在本地运行hive metastore 服务 ， 执行命令行的SQL查询。 注意到Spark SQL CLI 没法和thrift JDBC服务通信。

启动Spark SQL CLI , 运行以下命令

./bin/spark-sql

hive 配置 在conf/ 下需要以 下三个文件hive-site.xml ， core-site.xml 和 hdfs-site.xml 。 也可以使用 ./bin/spark-sql --help 获取完整帮助

6 版本合并指南

以下内容过于细枝末节，有兴趣的读者可自行参考原文，此处不作翻译

• Overview
  • SQL
  • Datasets and DataFrames
• Getting Started
  • Starting Point: SparkSession
  • Creating DataFrames
  • Untyped Dataset Operations (aka DataFrame Operations)
  • Running SQL Queries Programmatically
  • Global Temporary View
  • Creating Datasets
  • Interoperating with RDDs
    • Inferring the Schema Using Reflection
    • Programmatically Specifying the Schema
• Data Sources
  • Generic Load/Save Functions
    • Manually Specifying Options
    • Run SQL on files directly
    • Save Modes
    • Saving to Persistent Tables
  • Parquet Files
    • Loading Data Programmatically
    • Partition Discovery
    • Schema Merging
    • Hive metastore Parquet table conversion
      • Hive/Parquet Schema Reconciliation
      • Metadata Refreshing
    • Configuration
  • JSON Datasets
  • Hive Tables
    • Interacting with Different Versions of Hive Metastore
  • JDBC To Other Databases
  • Troubleshooting
• Performance Tuning
  • Caching Data In Memory
  • Other Configuration Options
• Distributed SQL Engine
  • Running the Thrift JDBC/ODBC server
  • Running the Spark SQL CLI
• Migration Guide
  • Upgrading From Spark SQL 2.0 to 2.1
  • Upgrading From Spark SQL 1.6 to 2.0
  • Upgrading From Spark SQL 1.5 to 1.6
  • Upgrading From Spark SQL 1.4 to 1.5
  • Upgrading from Spark SQL 1.3 to 1.4
    • DataFrame data reader/writer interface
    • DataFrame.groupBy retains grouping columns
    • Behavior change on DataFrame.withColumn
  • Upgrading from Spark SQL 1.0-1.2 to 1.3
    • Rename of SchemaRDD to DataFrame
    • Unification of the Java and Scala APIs
    • Isolation of Implicit Conversions and Removal of dsl Package (Scala-only)
    • Removal of the type aliases in org.apache.spark.sql for DataType (Scala-only)
    • UDF Registration Moved to sqlContext.udf (Java & Scala)
    • Python DataTypes No Longer Singletons
  • Compatibility with Apache Hive
    • Deploying in Existing Hive Warehouses
    • Supported Hive Features
    • Unsupported Hive Functionality
• Reference
  • Data Types
  • NaN Semantics

一 综述

spark sql 是spark 中处理格式化数据的模块。不像spark RDD 的API， Spark SQL 接口除了提供结构化数据的信息外，还提供了计算优化的信息。本质上， spark SQL 需要额外信息来优化计算，有多种方式使用Spark SQL , 包括SQL的方式和Dataset API的方式。 spark SQL 的计算结果是和计算引擎（API/ 开发语言）无关地。 换句话说， 只要数据处理的逻辑不变，开发人员可以轻而易举地在各种API之间切换。

本指南中使用的数据来自spark 发布程序，可以使用spark-shell , pyspark 和sparkR  shell 运行。 

1.1 SQL

spark sql 用于支持SQL查询，除此而外， spark SQL可以从hive 中读取数据。了解详细spark sql 与hive  集成，见hive tables : http://spark.apache.org/docs/latest/sql-programming-guide.html#hive-tables 

Spark SQL API 返回结果是Dataset或Dataframe 。 除了API外，开发人员还可以使用命令行，或者ODBC/JDBC。 

1.2 Dataset 和Dataframe

Dataset 是分布式数据集， dataset的API是在spark 1.6版本中添加地，它的初衷是为了提升RDD（强类型限制， 可以使用lambda函数）优化SQL执行引擎。Dataset是JVM中的一个对象，可以作用于其它操作（map,faltMap, filter等）。Dataset API提供Scala /Java 语言支持。 Python 暂不支持Dataset API， 但由于python的动态特性，已经支持 部分dataset API （例如， 用row.columnName来读取行数据的某列），这个例子和R语言的类似。

Dataframe 与dataset 基本类似 ，只是多了列名这些信息。 概念上，它类似于关系数据的表，及R/python 中的dataframe  但在执行效率上进一步优化。 可以从很多数据源中生成dataframe ， 如 结构化数据文件，hive表， 外部数据库，或 RDD。 Dataframe API支持 scala / java / python 和R 。在scala和java 中，dataframe  实际表示为dataset的行数据 。 在scala API 中，Dataframe 实际是Dataset[Row]的别名，但在java API 中， 开发人员需要使用Dataset<Row> 来表示 DataFrame 。 

通篇只scala Dataset行数据表示 DataFrame 

2  开始

2.1 开始之旅 ： SparkSession 

spark 所有功能都始于SparkSession 类， 使用SparkSession.builder()来创建一个SparkSession 。

import org.apache.spark.sql.SparkSession

val spark = SparkSession
  .builder()
  .appName("Spark SQL basic example")
  .config("spark.some.config.option", "some-value")
  .getOrCreate()

// For implicit conversions like converting RDDs to DataFrames

import spark.implicits._

完整例子见： “ examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala” 

Spark 2.0 中SparkSession 内置支持hive特性，包括编写hiveQL ， 使用Hive UDF ， 读取hive 表数据。 并且以上不需要安装hive 。

2.2 创建DataFrame

创建了SparkSession后， 应用程序可以从已存在RDD上创建DataFrame, 从hive表，或其它Spark 数据源。以下例子从json文件中创建DataFrame

val df = spark.read.json("examples/src/main/resources/people.json")

// Displays the content of the DataFrame to stdout

df.show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

 完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.3 非强制类型的Dataset 变换（也就是DataFrame 变换）

DataFrame 提供结构化数据变换DSL（特定领域语言），支持scala/java/python/R。 上面提致， Spark 2.0中scala/java API中， Dataframe 只是Dataset行数据 。 因此，这些操作称为“非强制类型变换”，相对于scala/java 中Dataset 这类“强制类型变换”。

下面是使用Dataset 处理结构化数据的简单例子： 

// This import is needed to use the $-notation

import spark.implicits._

// Print the schema in a tree format

df.printSchema()

// root

// |-- age: long (nullable = true)

// |-- name: string (nullable = true)

// Select only the "name" column

df.select("name").show()

// +-------+

// |   name|

// +-------+

// |Michael|

// |   Andy|

// | Justin|

// +-------+

// Select everybody, but increment the age by 1

df.select($"name", $"age" + 1).show()

// +-------+---------+

// |   name|(age + 1)|

// +-------+---------+

// |Michael|     null|

// |   Andy|       31|

// | Justin|       20|

// +-------+---------+

// Select people older than 21

df.filter($"age" > 21).show()

// +---+----+

// |age|name|

// +---+----+

// | 30|Andy|

// +---+----+

// Count people by age

df.groupBy("age").count().show()

// +----+-----+

// | age|count|

// +----+-----+

// |  19|    1|

// |null|    1|

// |  30|    1|

// +----+-----+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

详细查看DataSet 支持的操作集，详见API 文档 ： http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Dataset

除了简单的列引用和表达式外，Dataset 提供了丰富的功能库，包括 字符串操作，日期计算，通用数据操作等。 详见 DataFrame 函数参考：http://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$

2.4 SQL查询编程

SparkSession 上的sql 函数允许程序执行SQL查询， 返回Dataframe 结果

// Register the DataFrame as a SQL temporary view

df.createOrReplaceTempView("people")

val sqlDF = spark.sql("SELECT * FROM people")

sqlDF.show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.5 全局临时视图

spark sql 中临时视图是session范围内有效，当创建视图的session退出后，视图随着消亡。如果想拥有一个临时视图可以在所有session中共享，

并且可以存活直到spark 应用程序退出， 那就需要创建全局临时视图。 全局临时视图保存在数据库 global_temp 中，如果想引用一个全局视图，需要使用全名，如 select * from global_temp.view1。

// Register the DataFrame as a global temporary view

df.createGlobalTempView("people")

// Global temporary view is tied to a system preserved database `global_temp`

spark.sql("SELECT * FROM global_temp.people").show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

// Global temporary view is cross-session

spark.newSession().sql("SELECT * FROM global_temp.people").show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.6  创建datasets

Dataset 和RDD 类似， 除了Dataset没有使用java 序列化类和kryo外，而是引入了特定编码器来序列化计算数据，及在网络中传输数据。 编码器和标准序列化类都会将对象转化为字节， 编码器是动态生成的代码， 这种代码使用的数据格式在进行操作时，如过滤，排序或hash时，不需要将字节反序列为对象，就可直接操作。

// Note: Case classes in Scala 2.10 can support only up to 22 fields. To work around this limit,

// you can use custom classes that implement the Product interface

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

// Encoders are created for case classes

val caseClassDS = Seq(Person("Andy", 32)).toDS()caseClassDS.show()

// +----+---+

// |name|age|

// +----+---+

// |Andy| 32|

// +----+---+

// Encoders for most common types are automatically provided by importing spark.implicits._

val primitiveDS = Seq(1, 2, 3).toDS()primitiveDS.map(_ + 1).collect()

// Returns: Array(2, 3, 4)

// DataFrames can be converted to a Dataset by providing a class. Mapping will be done by name

val path = "examples/src/main/resources/people.json"

val peopleDS = spark.read.json(path).as[Person]peopleDS.show()

// +----+-------+

// | age|   name|

// +----+-------+

// |null|Michael|

// |  30|   Andy|

// |  19| Justin|

// +----+-------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.7 与RDD的相互操作

spark sql 支持两种方法，将已有RDD转化为Datasets。第一种方法是反射，分析 RDD中所承载的数据的类型，推断RDD转化为Dataset的schema。 这种反射的缺点是需要写很多额外 的代码，并且你需要预先知道spark应用程序所处理数据的schema .

第二种方法是通过程序接口的方式构造一个schema， 将这个schema与RDD关联在一起。此方法虽然繁琐，但却可以实现列数及列类型在运行期才可知的场景。

2.7.1 使用反射推断schema

Spark SQL 的scala API 支持将case class 的RDD，自动转化为Dataframe 。 case class定义表的schema， 使用反射读取case class

的参数， 这些参数最终变成列名。 case class 可以嵌套，可以包含复杂类型,如 Seqs 或 Arrays。 此RDD可以隐式转化为Dataframe，

并注册为一个表，紧接着可以用SQL查询表数据。

import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder

import org.apache.spark.sql.Encoder

// For implicit conversions from RDDs to DataFrames

import spark.implicits._

// Create an RDD of Person objects from a text file, convert it to a Dataframe

val peopleDF = spark.sparkContext

  .textFile("examples/src/main/resources/people.txt")
  .map(_.split(","))
  .map(attributes => Person(attributes(0), attributes(1).trim.toInt))

  .toDF()

// Register the DataFrame as a temporary view

peopleDF.createOrReplaceTempView("people")

// SQL statements can be run by using the sql methods provided by Spark

val teenagersDF = spark.sql("SELECT name, age FROM people WHERE age BETWEEN 13 AND 19")

// The columns of a row in the result can be accessed by field index

teenagersDF.map(teenager => "Name: " + teenager(0)).show()

// +------------+

// |       value|

// +------------+

// |Name: Justin|

// +------------+

// or by field name

teenagersDF.map(teenager => "Name: " + teenager.getAs[String]("name")).show()

// +------------+

// |       value|

// +------------+

// |Name: Justin|

// +------------+

// No pre-defined encoders for Dataset[Map[K,V]], define explicitly

implicit val mapEncoder = org.apache.spark.sql.Encoders.kryo[Map[String, Any]]

// Primitive types and case classes can be also defined as

// implicit val stringIntMapEncoder: Encoder[Map[String, Any]] = ExpressionEncoder()

// row.getValuesMap[T] retrieves multiple columns at once into a Map[String,T]

teenagersDF.map(teenager => teenager.getValuesMap[Any](List("name", "age"))).collect()

// Array(Map("name" -> "Justin", "age" -> 19))

 

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

2.7.2 编程设置schema

前述方法的缺点：事先需要能知道表数据的列个数和列类型，否则地话，只能通过编程设置schema。需要遵照以下三步：

1 从原始RDD 创建一个RDD的行数据

2 创建一个StructType 类型表示schema ， 需要与1 中行数据对应

3 将schema 应用于RDD行数据 ，通过 SparkSession 的createDataFrame方法

例如： 

import org.apache.spark.sql.types._

// Create an RDD

val peopleRDD = spark.sparkContext.textFile("examples/src/main/resources/people.txt")

// The schema is encoded in a string

val schemaString = "name age"

// Generate the schema based on the string of schema

val fields = schemaString.split(" ")

  .map(fieldName => StructField(fieldName, StringType, nullable = true))

val schema = StructType(fields)

// Convert records of the RDD (people) to Rows

val rowRDD = peopleRDD

  .map(_.split(","))
  .map(attributes => Row(attributes(0), attributes(1).trim))

// Apply the schema to the RDD

val peopleDF = spark.createDataFrame(rowRDD, schema)

// Creates a temporary view using the DataFrame

peopleDF.createOrReplaceTempView("people")

// SQL can be run over a temporary view created using DataFrames

val results = spark.sql("SELECT name FROM people")

// The results of SQL queries are DataFrames and support all the normal RDD operations

// The columns of a row in the result can be accessed by field index or by field name

results.map(attributes => "Name: " + attributes(0)).show()

// +-------------+

// |        value|

// +-------------+

// |Name: Michael|

// |   Name: Andy|

// | Name: Justin|

// +-------------+

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SparkSQLExample.scala

3 data source 数据源

spark SQL Dataframe接口支持多种数据源，DataFrame  可以使用关系变换和创建临时视图。将Dataframe注册为临时视图，可以在视图上执行SQL查询。 本章节讲述spark 数据源地通用加载/保存方法，然后深入内置数据源

3.1 通用 load/save 函数 

默认数据源（默认是parquet ， 除非配置spark.sql.sources.default）支持多种操作。

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

usersDF.select("name", "favorite_color").write.save("namesAndFavColors.parquet")

完整例子 ：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.1.1 手动确定参数 

开发人员可以手动设置data source 数据源， 这样可以添加额外的参数到data source中。数据源的名字使用完整修饰名（如 org.apache.spark.sql.parquet），但内置数据源可以使用短名（json , parquet, jdbc, orc , libsvm ,csv , text）。由任意数据源加载到

DataFrame 后，可以转化为其它任意类型。

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

peopleDF.select("name", "age").write.format("parquet").save("namesAndAges.parquet")

完整例子：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.1.2 直接在文件上使用SQL

除了使用API加载 文件到dataframe，并且查询外 ，可以直接对文件使用SQL查询。

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

完整例子：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.1.3 保存模式

保存操作可以使用SaveMode参数，此参数 可以确认如何保存数据。 需要特别注意的是，此处的保存模式并没有附加任务锁机制，也不是原子操作。 除此而外， 将使用 Overwrite 时， 老数据会删除。

Scala/JavaAny LanguageMeaningSaveMode.ErrorIfExists(default)"error"(default)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 to not save the contents of the DataFrame and to not change the existing data. This is similar to a CREATE TABLE IF NOT EXISTS in SQL.

3.1.4 持久化成表

Dataframe 可以持久化成hive 表，使用saveAsTable 可以将表注册到hive metastore中。注意到环境中可以没有安装hive ， spark 会创建一个本地hive metastore(derby保存元数据)。与 createeOrReplaceTempView 接口， saveAsTable 会将Dataframe的数据物化为表，在hive metastore中创建一个真实的表。 持久化的表会持续存在，只要spark使用同一个metastore ，

即使退出spark程序，下次再次启动spark程序时仍然有效。 使用SparkSession 的table 方法，将表名作为参数可以将Dataframe 持久化成表。

默认情况， saveAsTable会创建一个“管理表” ， 意味着Hives metastore 可以管理表数据的路径。当表被drop掉时，表数据相应的也会被删除。 

3.2 Parquet 文件

parquet 文件是列存储格式，很多数据处理系统均支持此类型。 Spark SQL 提供支持读和写parquet文件的同时，将文件源数据的schema保存。当保存parquet文件时，所有列会自动转化为可为空的类型。

3.2.1 程序加载数据

使用上例中数据 

// Encoders for most common types are automatically provided by importing spark.implicits._

import spark.implicits._

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

// DataFrames can be saved as Parquet files, maintaining the schema information

peopleDF.write.parquet("people.parquet")

// Read in the parquet file created above

// Parquet files are self-describing so the schema is preserved

// The result of loading a Parquet file is also a DataFrame

val parquetFileDF = spark.read.parquet("people.parquet")

// Parquet files can also be used to create a temporary view and then used in SQL statements

parquetFileDF.createOrReplaceTempView("parquetFile")

val namesDF = spark.sql("SELECT name FROM parquetFile WHERE age BETWEEN 13 AND 19")

namesDF.map(attributes => "Name: " + attributes(0)).show()

// +------------+

// |       value|

// +------------+

// |Name: Justin|

// +------------+

详细例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.2.2  分区自动发现

表分区是常用的代码手段，就像hive中表分区一样。对于分区表，不同分区的数据会存放到不同的目录，分区列的值转化为分区目录。 parquet 数据源现在支持自动发现和推断分区信息。

例如， 可以将把有人口的信息存储在分区表里，使用如下的目录结构， 除分区列外，还有两个列  性别 和 国别。

path
└── to
    └── table
        ├── gender=male
        │   ├── ...
        │   │
        │   ├── country=US
        │   │   └── data.parquet
        │   ├── country=CN
        │   │   └── data.parquet
        │   └── ...
        └── gender=female
            ├── ...
            │
            ├── country=US
            │   └── data.parquet
            ├── country=CN
            │   └── data.parquet
            └── ...

将表的路径 传给方法SparkSession.read.parquet 或 SparkSession.read.load ， Spark SQL 会自动识别分区信息，返回DataFrame的schema 为: 

root
|-- name: string (nullable = true)
|-- age: long (nullable = true)
|-- gender: string (nullable = true)
|-- country: string (nullable = true)

注意到，分区字段的类型是自动推断地，当前，支持数值型和字符型。有时，开发人员希望可以自动推断分区字段的类型。在这种情况，可以配置自动类型推断功能开关 spark.sql.sources.partitionColumnTypeInference.enabled ，默认此功能开启。  当关闭此功能时，分区字段的类型默认为字符串类型。

从spark 1.6.0之后，只能自动发现给定目录下的分区目录，在上例中， 如果把目录 path/to/table/gender=male 传给SparkSession.read.parquet 或 SparkSession.read.load, 那么gender(性别)

字段会认为是分区列。 如果想手动指定分区自动发现的起始眯眯眼不，需要在数据源中设置basePath 参数 。 例如 ， 假设 path/to/table/gender=male 是数据目录， 设置basePath 为 path/to/table ,

那么分区列是gender

 

3.2.3 schema 合并

和protocolBuffer ，avro ,thrift 一样， parquet 支持schema 演进。 开发人员可以从几个简单的schema 开始，慢慢添加更多的列到schema中。 这样， 开发人员最终可以深化出多个不同的parquet 文件，

便这些Parquet 文件schema 相互兼容。 parquet 数据源现在支持自动检测 这种情况，并自动将所有文件schema合并。

因为schema 合并是很耗时的操作， 因此在大多数情况下没有必要合并，从1.5.0 版本之后，默认关闭parquet 文件地schema自动合并功能，可以通过以下两种方式打开:

1 读取parquet 文件时，设置数据源参数  mergeSchema 为 true 

2 设置全局SQL 参数 spark.sql.parquet.mergeSchema 为 true 

// This is used to implicitly convert an RDD to a DataFrame.

import spark.implicits._

// Create a simple DataFrame, store into a partition directory

val squaresDF = spark.sparkContext.makeRDD(1 to 5).map(i => (i, i * i)).toDF("value", "square")

squaresDF.write.parquet("data/test_table/key=1")

// Create another DataFrame in a new partition directory,

// adding a new column and dropping an existing column

val cubesDF = spark.sparkContext.makeRDD(6 to 10).map(i => (i, i * i * i)).toDF("value", "cube")

cubesDF.write.parquet("data/test_table/key=2")

// Read the partitioned table

val mergedDF = spark.read.option("mergeSchema", "true").parquet("data/test_table")

mergedDF.printSchema()

// The final schema consists of all 3 columns in the Parquet files together

// with the partitioning column appeared in the partition directory paths

// root

//  |-- value: int (nullable = true)

//  |-- square: int (nullable = true)

//  |-- cube: int (nullable = true)

//  |-- key: int (nullable = true)

详细例子见：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.2.4 hive metastore 表与parquet 文件schema转化 

当读取或写数据到hive metastore的parquet 表时， spark SQL 使用自带的parquet 库来操作，为了达到更好的性能而不使用hive SerDe 。 可以使用spark.sql.hive.convertMetastoreParquet 开关，默认是打开。

3.2.4.1 hive/parquet schema 互换

在处理表/schema 的观点来看，hive 的表和parquet 的schema 两者是不同的。

1 hive 是大小写乎略地，parquet 却是大小写敏感

2 hive 认为所有列都是可空的nullable , 但parquet 的nullable却有不同的拿含意。

由于以上原因， 需要在hive 表的schema和 parquet schema 之间的转化 。 当需要将hive metastore parquet表转化为 spark SQL  parquet 表时，互换规则为： 

1 从parquet 文件schema 到hive metastore parquet 表，只要保证同一个字段类型相同即可，可以不必考虑nullable修饰符，但hive metastore parquet 表到parquet schema时，除了同一个字段类型一致，同时要考虑Nullable修饰。

2 当hive metastore  schema中定义如下字段时，parquet 文件schema 也应该作如下处理

1 >  只出现在parquet 文件schema 的字段， 在hive metastore 表中不能出现

2 > 只出现在hive metastore 表中的字段， 应该添加到parquet  文件schema 中，同时定义为nullable 。

3.2.4.2 metadata （元数据）刷新

spark sql 缓存parquet 表数据 metadata ， 当hive metastore  parquet 表转化parquet 文件开启， 转化后的表元数据仍然会缓存。 如果此时hive或其它工具更新了hive metastore 的元数据，那么需要手动更新一下元数据，以保证缓存中的元数据与实际元数据一致。

 

// spark is an existing SparkSession

spark.catalog.refreshTable("my_table")

 

3.2.5 配置信息

通过 SparkSession 的setConf 方法可以配置parquet  信息，或者在SQL中使用set key = value。

Property NameDefaultMeaningspark.sql.parquet.binaryAsStringfalseSome other Parquet-producing systems, in particular Impala, Hive, and older versions of Spark SQL, do not differentiate between binary data and strings when writing out the Parquet schema. This flag tells Spark SQL to interpret binary data as a string to provide compatibility with these systems.spark.sql.parquet.int96AsTimestamptrueSome Parquet-producing systems, in particular Impala and Hive, store Timestamp into INT96. This flag tells Spark SQL to interpret INT96 data as a timestamp to provide compatibility with these systems.spark.sql.parquet.cacheMetadatatrueTurns on caching of Parquet schema metadata. Can speed up querying of static data.spark.sql.parquet.compression.codecsnappySets the compression codec use when writing Parquet files. Acceptable values include: uncompressed, snappy, gzip, lzo.spark.sql.parquet.filterPushdowntrueEnables Parquet filter push-down optimization when set to true.spark.sql.hive.convertMetastoreParquettrueWhen set to false, Spark SQL will use the Hive SerDe for parquet tables instead of the built in support.spark.sql.parquet.mergeSchemafalse

When true, the Parquet data source merges schemas collected from all data files, otherwise the schema is picked from the summary file or a random data file if no summary file is available.

spark.sql.optimizer.metadataOnlytrue

When true, enable the metadata-only query optimization that use the table's metadata to produce the partition columns instead of table scans. It applies when all the columns scanned are partition columns and the query has an aggregate operator that satisfies distinct semantics.

3.3 JSON Datasets

Spark SQL 可以自动推荐json 数据集的schema ,并加载到Dataset[Row] 中。使用SparkSession.read.json() 方法作用于RDD String,或者 JSON文件。

注意，我们说的json 并不是通常讲的json文件， 每行包含一个有分隔符，自完整的有效json 对象。 更多信息，详见：  JSON Lines text format, also called newline-delimited JSON  ： http://jsonlines.org/

结果，  通用的json文件经常会失败

// A JSON dataset is pointed to by path.

// The path can be either a single text file or a directory storing text files

val path = "examples/src/main/resources/people.json"

val peopleDF = spark.read.json(path)

// The inferred schema can be visualized using the printSchema() method

peopleDF.printSchema()

// root

//  |-- age: long (nullable = true)

//  |-- name: string (nullable = true)

// Creates a temporary view using the DataFrame

peopleDF.createOrReplaceTempView("people")

// SQL statements can be run by using the sql methods provided by spark

val teenagerNamesDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13 AND 19")

teenagerNamesDF.show()

// +------+

// |  name|

// +------+

// |Justin|

// +------+

// Alternatively, a DataFrame can be created for a JSON dataset represented by

// an RDD[String] storing one JSON object per string

val otherPeopleRDD = spark.sparkContext.makeRDD(

  """{"name":"Yin","address":{"city":"Columbus","state":"Ohio"}}""" :: Nil)

val otherPeople = spark.read.json(otherPeopleRDD)

otherPeople.show()

// +---------------+----+

// |        address|name|

// +---------------+----+

// |[Columbus,Ohio]| Yin|

// +---------------+----+

完整例子见：   examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.4 hive 表

Spark SQL 支持读取和写数据到hive中， 尽管如此， hive 有很大数量的依赖， 这些依赖在spark发行版本中并不包含。 如果hive依赖包在CLASSPATH中可以找到，spark 会自动加载。注意到，worker节点同样要加载这些依赖， 因为spark executor会从hive 的表中直接读取和写数据，因此依赖于hive 自己的序列化类和反序列化类。

为了使spark 支持hive ,需要将hive-site.xml , core-site.xml 和 hdfs-site.xml  文件放到spark 的conf目录。

为了使用hive ,  需要使用SparkSession 实例， 同时 也可心连接持久化的hive metastore ， 支持Hive序列化， hive用户自定义函数。 环境中如果未部署hive， 仍然可以使用spark 支持hive ，只是没有hive-site.xml 文件时， spark 会自动在本地创建 metastore_db目录来存储元数据 ，创建 spark.sql.warehouse.dir 定义的目录，这个目录默认是在当前目录下spark-warehouse ， 用于spark 应用程序起动后使用。 注意到 hive-site.xml 中hive.metastore.warehouse.dir从spark .2.0.0 后过期， 取而代之， 使用 spark.sql.warehouse.dir 来指定数据仓库默认的数据库。 启动 spark 应用的用户需要给这个目录赋写权限 。

import org.apache.spark.sql.Row

import org.apache.spark.sql.SparkSession

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

// warehouseLocation points to the default location for managed databases and tables

val warehouseLocation = "spark-warehouse"

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)")

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 DaraFrames 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|

// ...

 

完整例子见：  examples/src/main/scala/org/apache/spark/examples/sql/hive/SparkHiveExample.scala

3.4.1 不同版本的hive metastore 交互

spark SQL 支持 hive 最关键是可以兼容多个版本， 这样spark sql 可以读取Hive 表的数据 。 从spark 1.4.0 开始， 任意一个发行版本可以读取多种hive 版本的数据 ， 详细见下。 hive 每个版本独立访问各版本的hive metastore ， 但是 spark sql 是参考 hive 1.2.1 版本编译， 使用此版本实现诸如序列与反序列化， UDF， UDAF 等。

下面的参数需要在各版本spark 版本中配置

Property NameDefaultMeaningspark.sql.hive.metastore.version1.2.1Version of the Hive metastore. Available options are 0.12.0through 1.2.1.spark.sql.hive.metastore.jarsbuiltinLocation of the jars that should be used to instantiate the HiveMetastoreClient. This property can be one of three options:

1. builtinUse Hive 1.2.1, which is bundled with the Spark assembly when -Phive is enabled. When this option is chosen, spark.sql.hive.metastore.version must be either 1.2.1or not defined.
2. mavenUse Hive jars of specified version downloaded from Maven repositories. This configuration is not generally recommended for production deployments.
3. A classpath in the standard format for the JVM. This classpath must include all of Hive and its dependencies, including the correct version of Hadoop. These jars only need to be present on the driver, but if you are running in yarn cluster mode then you must ensure they are packaged with your application.

spark.sql.hive.metastore.sharedPrefixescom.mysql.jdbc,
org.postgresql,
com.microsoft.sqlserver,
oracle.jdbc

A comma separated list of class prefixes that should be loaded using the classloader that is shared between Spark SQL and a specific version of Hive. An example of classes that should be shared is JDBC drivers that are needed to talk to the metastore. Other classes that need to be shared are those that interact with classes that are already shared. For example, custom appenders that are used by log4j.

spark.sql.hive.metastore.barrierPrefixes(empty)

A comma separated list of class prefixes that should explicitly be reloaded for each version of Hive that Spark SQL is communicating with. For example, Hive UDFs that are declared in a prefix that typically would be shared (i.e. org.apache.spark.*).

3.5 通过 JDBC访问其它数据库

 spark SQL 支持通过 JDBC访问其它数据源。spark 使用jdbcRDD 来实现 这个功能。 主要是因为返回的结果集是DataFrame， 可以很容易用spark SQL 处理，或者和其它数据源关联。

java 和python 可以容易使用JDBC数据源， 并且不需要ClassTag 就可以处理数据 。

需要开发人员将JDBC driver放置到 spark classpath 中，例如， 使用spark shell  连接postgres  ， 需要使用以下命令行

bin/spark-shell --driver-class-path postgresql-9.4.1207.jar --jars postgresql-9.4.1207.jar

使用Data Source API 可以加载远程数据库的表数据， 生成DataFrame 或Spark SQL 临时视图。 开发人员可以配置JDBC连接属性， user 和password 是连接数据库的参数 ，除此而外，spark 还支持以下参数：

Property NameMeaningurlThe JDBC URL to connect to. The source-specific connection properties may be specified in the URL. e.g., jdbc:postgresql://localhost/test?user=fred&password=secretdbtableThe JDBC table that should be read. Note that anything that is valid in a FROM clause of a SQL query can be used. For example, instead of a full table you could also use a subquery in parentheses.driverThe class name of the JDBC driver to use to connect to this URL.partitionColumn, lowerBound, upperBound, numPartitionsThese options must all be specified if any of them is specified. They describe how to partition the table when reading in parallel from multiple workers. partitionColumn must be a numeric column from the table in question. Notice that lowerBound and upperBound are just used to decide the partition stride, not for filtering the rows in table. So all rows in the table will be partitioned and returned. This option applies only to reading.fetchsizeThe JDBC fetch size, which determines how many rows to fetch per round trip. This can help performance on JDBC drivers which default to low fetch size (eg. Oracle with 10 rows). This option applies only to reading.batchsizeThe JDBC batch size, which determines how many rows to insert per round trip. This can help performance on JDBC drivers. This option applies only to writing. It defaults to 1000.isolationLevelThe transaction isolation level, which applies to current connection. It can be one of NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, or SERIALIZABLE, corresponding to standard transaction isolation levels defined by JDBC's Connection object, with default of READ_UNCOMMITTED. This option applies only to writing. Please refer the documentation in java.sql.Connection.truncateThis is a JDBC writer related option. When SaveMode.Overwrite is enabled, this option causes Spark to truncate an existing table instead of dropping and recreating it. This can be more efficient, and prevents the table metadata (e.g., indices) from being removed. However, it will not work in some cases, such as when the new data has a different schema. It defaults to false. This option applies only to writing.createTableOptionsThis is a JDBC writer related option. If specified, this option allows setting of database-specific table and partition options when creating a table (e.g., CREATE TABLE t (name string) ENGINE=InnoDB.). This option applies only to writing.

 

代码：

// Note: JDBC loading and saving can be achieved via either the load/save or jdbc methods

// Loading data from a JDBC source

val jdbcDF = spark.read

  .format("jdbc")
  .option("url", "jdbc:postgresql:dbserver")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .load()

val connectionProperties = new Properties()

connectionProperties.put("user", "username")

connectionProperties.put("password", "password")

val jdbcDF2 = spark.read

  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties)

// Saving data to a JDBC source

jdbcDF.write

  .format("jdbc")
  .option("url", "jdbc:postgresql:dbserver")
  .option("dbtable", "schema.tablename")
  .option("user", "username")
  .option("password", "password")
  .save()

jdbcDF2.write
  .jdbc("jdbc:postgresql:dbserver", "schema.tablename", connectionProperties)

详细代码见：  examples/src/main/scala/org/apache/spark/examples/sql/SQLDataSourceExample.scala

3.6 问题解决

1> 客户端session 和所有executor 的原始加载类支持JDBC。  由于java DriverManager 类对driver 进行安全检查，这样会乎略不属于原始加载类的driver ， 当spark 要打开一个connection。 最好的方法是修改所有worker主机的compute_classpath.sh ， 使得worker会自己加载Driver 类。

2> 一些数据库， 如H2 , 会把所有名字转化为大写， 这样在Spark SQL 中需要使用大写名字。

4 性能优化

在大量负载的情况下，可能通过将数据缓存在内存，以及调整经验参数达到提升性能的目的。

4.1 缓存数据

可以将Spark SQL 的表数据缓存在内存，调用 spark.cacheTable("tableName") 或 dataFrame.cache()  可以保存为列式格式。 这样Spark SQL 只会扫描需要的列， 并将数据压缩以减少内存和GC压力。 可以使用spark.uncacheTable("tableName") 来清除缓存表。

使用SparkSession的setConf 方法可以配置缓存的属性，或者在SQL 中使用SET key=value命令。

Property NameDefaultMeaningspark.sql.inMemoryColumnarStorage.compressedtrueWhen set to true Spark SQL will automatically select a compression codec for each column based on statistics of the data.spark.sql.inMemoryColumnarStorage.batchSize10000Controls the size of batches for columnar caching. Larger batch sizes can improve memory utilization and compression, but risk OOMs when caching data.

4.2 其它配置参数

以下参数也可以提升SQL执行性能。但是这些参数可能未来会过期， 或者默认打开状态。

 

Property Name

DefaultMeaningspark.sql.files.maxPartitionBytes134217728 (128 MB)The maximum number of bytes to pack into a single partition when reading files.spark.sql.files.openCostInBytes4194304 (4 MB)The estimated cost to open a file, measured by the number of bytes could be scanned in the same time. This is used when putting multiple files into a partition. It is better to over estimated, then the partitions with small files will be faster than partitions with bigger files (which is scheduled first).spark.sql.broadcastTimeout300

Timeout in seconds for the broadcast wait time in broadcast joins

spark.sql.autoBroadcastJoinThreshold10485760 (10 MB)Configures the maximum size in bytes for a table that will be broadcast to all worker nodes when performing a join. By setting this value to -1 broadcasting can be disabled. Note that currently statistics are only supported for Hive Metastore tables where the command ANALYZE TABLE <tableName> COMPUTE STATISTICS noscan has been run.spark.sql.shuffle.partitions200Configures the number of partitions to use when shuffling data for joins or aggregations.

5 分布式SQL引擎

Spark SQL 可以作为分布式查询引擎，使用JDBC/ODBC 或命令行接口。在这种模式下， 终端用户或应用开发者，使用Spark SQL 运行SQL查询，而不需要其它代码。

5.1 运行Thrift JDBC/ODBC 服务

hive1.2.1 的HiveServerr2 自带Thrift JDBC/ODBC 服务， 可以使用hive1.2.1 和spark 自带的beeline  测试JDBC服务 。

启动 JDBC/ODBC 服务 ，运行以下命令

./sbin/start-thriftserver.sh

此脚本支持bin/spark-submit 命令的所有参数 ，另外 可以使用 --hiveconf 来配置hive 的 属性。 也可以使用 ./sbin/start-thriftserver.sh --help 来获取完整参数列表。 默认， 服务器监听localhost:10000, 通过以下环境变量可以修改这些参数。

export HIVE_SERVER2_THRIFT_PORT=<listening-port>
export HIVE_SERVER2_THRIFT_BIND_HOST=<listening-host>
./sbin/start-thriftserver.sh \
  --master <master-uri> \

  ...

或者系统属性：

./sbin/start-thriftserver.sh \
  --hiveconf hive.server2.thrift.port=<listening-port> \
  --hiveconf hive.server2.thrift.bind.host=<listening-host> \
  --master <master-uri>
  ...

可以使用beeline 来测试thrift JDBC/ODBC 服务 ： 

./bin/beeline

使用beeline连接 JDBC/ODBC 服务

beeline> !connect jdbc:hive2://localhost:10000

beeline 要求输入用户名和密码， 在非安全模式，  只需要输入用户名，而不用填写密码。 在安全模式下， 需要参考 beeline 文档 ： https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients

配置hive 需要将hive-site.xml , core-site.xml 和hdfs-site.xml 文件放到conf/下。 

也可以使用Hive自带的beeline 

thrift JDBC 服务支持通过 http 发送 thrift RPC 消息, 以下配置项可以开启http模式， 或者添加到 hive-site.xml 配置文件

hive.server2.transport.mode - Set this to value: http
hive.server2.thrift.http.port - HTTP port number fo listen on; default is 10001
hive.server2.http.endpoint - HTTP endpoint; default is cliservice

测试可以使用beeline http协议连接JDBC/ODBC 服务

beeline> !connect jdbc:hive2://<host>:<port>/<database>?hive.server2.transport.mode=http;hive.server2.thrift.http.path=<http_endpoint>

 

5.2 运行Spark SQL CLI

Spark SQL CLI 可以很方便在本地运行hive metastore 服务 ， 执行命令行的SQL查询。 注意到Spark SQL CLI 没法和thrift JDBC服务通信。

启动Spark SQL CLI , 运行以下命令

./bin/spark-sql

hive 配置 在conf/ 下需要以 下三个文件hive-site.xml ， core-site.xml 和 hdfs-site.xml 。 也可以使用 ./bin/spark-sql --help 获取完整帮助

6 版本合并指南

以下内容过于细枝末节，有兴趣的读者可自行参考原文，此处不作翻译

原文链接  http://blog.csdn.net/hopeatme/article/details/68252446