Spark SQL and DataFrames Version 1.6

编者前述：

       Spark 中的RDD 数据结构较为抽象，同时RDD上的处理方法对于新手不太容易理解，自从spark 1.4中添加了dataframe数据结构后，不仅丰富了spark的数据表达形式，同时，作为一名开发人员可以像理解SQL 一样来理解数据处理逻辑，编写代码。随着学习的深入，你会发现dataframe在ML 中也有应用，可以说，dataframe是一样通用的数据结构。

一 总述

  Spark SQL 是Spark处理结构化数据的模块，不同于Spark RDD API , 它对数据实体和计算优化提供了更多的结构属性，通过这些属性可以提升计算效率。Spark SQL支持Sql , Dataframes API , Dataset API三种方式交互，在同种执行引擎上，这三种交互方式都可以很好的被执行引擎解析和运行。这种一致性保证了开发人员可以在三种交互方式之前来回切换，或者在不同的变换方式下使用列更便利的交互方式。

本部分的例子使用spark 分布式示例数据，可以在spark-shell, pyspark shell or sparkR shell 中运行 。

1.1 SQL

Spark SQL 主要用于执行SQL 的场景，包括基本SQL 语法或HiveQL语法。它也可以直接读取hive 上的数据，关于此部分的配置实现请参看 Hive Tables章节。使用其它语言运行 SQL 会返回DataFrame数据结构，当然也可以通过 JDBC/ODBC , 命令行方式 运行SQL。

1.2  DataFrames
  DataFrame是一种分布式的数据集，这个数据集按列组织在一起。基本上可以等价于关系数据库中表，或R/Python中的dataframe,只是处理性能上更优化。DataFrames可以从多种数据源中构造出来： 结构化数据文件，Hive表，外部数据库，或已有RDD。提供scala,java,python和R 的API。

1.3 DataSets

Spark 1.6 中首次尝试添加了DataSet数据集 ,这个数据集提供的数据接口，可以更进一步优化RDD在Spark SQL执行引擎上的效率。Dataset可以从JVM对象上构造，紧接着可以使用变换函数(map , flatMap, filter等)。

DataSet API 支持scala, java , 尽管对python的支持还不完全，但是已经有部分python的API 可以使用，后期会完全开发python API.

2 开始发现之旅

2.1 第一站：SQLContext
  Spark SQL所有功能是从SQLContext衍生的。我们可以借助SparkContext创建SQLContext.
---------------------------------------------------------

val sc: SparkContext // An existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

// this is used to implicitly convert an RDD to a DataFrame.
import sqlContext.implicits._

---------------------------------------------------------
除了创建SQLContext外，还可以直接创建一个HiveContext，HiveContext扩展了SQLContext的所有功能，同时支持HiveQL解释查询语句，调用Hive UDF，和读取
Hive表数据。HiveContext不依赖于Hive的安装，同时也支持SQLContext的多种数据源。为了避免把hive所有的依赖包引入spark,HiveContext需要单独打包，当
然如果引入所有hive依赖包不会带来编译问题，建议使用spark1.3的HiveContext包。后续版本的SQLContext逐渐引入了HiveContext中部分特性。

  SQL解释器可以通过spark.sql.dialect选项来设置，使用SQLContext的setConf方法，或在SQL中SET key=value来修改SQL解释器。对于SQLContext，提供唯一的SQL解释器是"sql",而HiveContext默认使用"hiveql"，同时"sql"仍然可用。鉴于hiveql更完善，我们推荐使用hiveql.

2.1 创建DataFrames

有了SQLContext, 应用程序可以从已有RDD，从HiveTable 或从其它数据源来创建DataFrames, 下例是使用JSON 文件创建DataFrame.

---------------------------------------------------------

val sc: SparkContext // An existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

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

// Displays the content of the DataFrame to stdout
df.show()

---------------------------------------------------------

2.3 操作DataFrame

下例中使用scala api操作dataframe

val sc: SparkContext // An existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

// Create the DataFrame
val df = sqlContext.read.json("examples/src/main/resources/people.json")

// Show the content of the DataFrame
df.show()
// age  name
// null Michael
// 30   Andy
// 19   Justin

// 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(df("name"), df("age") + 1).show()
// name    (age + 1)
// Michael null
// Andy    31
// Justin  20

// Select people older than 21
df.filter(df("age") > 21).show()
// age name
// 30  Andy

// Count people by age
df.groupBy("age").count().show()
// age  count
// null 1
// 19   1
// 30   1

全部dataframe的方法请参见 API Documentation.

除此而外，针对列的数据类型和列数据的表达式，还可以使用其它函数库，如字符串函数，数值计算，数学计算和其它，可参见DataFrame Function Reference.

2.4 运行 SQL 查询

SQLContext 的sql函数，使程序可以运行SQL查询并返回DataFrame.

val sqlContext = ... // An existing SQLContext
val df = sqlContext.sql("SELECT * FROM table")

2.5创建Datasets

Datasets和RDD类似，除了使用新的编码器序列化对象（不使用java的序列化类或Kryo)，便于序列化后对象的计算和在网络上传输。虽然新的编码器和标准的序列化类都是将对象转换成字节流，但新的编码器是动态生成编码，这种格式可以在事先不反序列化为对象的前提下，对对象进行多种操作，如filter, sort 和hash等。

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

val ds = Seq(1, 2, 3).toDS()
ds.map(_ + 1).collect() // Returns: Array(2, 3, 4)

// Encoders are also created for case classes.
case class Person(name: String, age: Long)
val ds = Seq(Person("Andy", 32)).toDS()

// 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 people = sqlContext.read.json(path).as[Person]

2.6 与RDD间互操作

Spark SQL支持两种方式将RDD转换为DataFrames。第一种方式是使用反射机制推断RDD中schema和类型信息。此方式编写需要额外代码，且只适用于已知schema的应用程序中。第二种方式是通过程度接口构造数据的schema,将schema应用于RDD.此方式适用于程序运行期才能确定数据schema和类型的场景。

2.6.1 反射推断schema

Scala接口支持从case class的RDD自动转化为Dataframe. Case class定义表数据的schema，case class中参数的名称和类型自动转化为dataframe的schema和字段类型。case class 还可以嵌套或包含复杂类型，如序列和数组。RDD可以隐式转化为dataframe,并注册成表及在表上使用SQL语句。

// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
// this is used to implicitly convert an RDD to a DataFrame.
import sqlContext.implicits._

// Define the schema using a case class.
// 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: Int)

// Create an RDD of Person objects and register it as a table.
val people = sc.textFile("examples/src/main/resources/people.txt").map(_.split(",")).map(p => Person(p(0), p(1).trim.toInt)).toDF()
people.registerTempTable("people")

// SQL statements can be run by using the sql methods provided by sqlContext.
val teenagers = sqlContext.sql("SELECT name, age FROM people WHERE age >= 13 AND age <= 19")

// 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:
teenagers.map(t => "Name: " + t(0)).collect().foreach(println)

// or by field name:
teenagers.map(t => "Name: " + t.getAs[String]("name")).collect().foreach(println)

// row.getValuesMap[T] retrieves multiple columns at once into a Map[String, T]
teenagers.map(_.getValuesMap[Any](List("name", "age"))).collect().foreach(println)
// Map("name" -> "Justin", "age" -> 19)

2.6.2 程序定义schema

对于程序运行期才能确认类型的数据集，只能通过程序三步转化为dataframe

1 创建RDD行数据

2 对第一步的行数据定义类型为StructType的schema

3 通过SQLContext的createDataFrame方法绑定schema到行数据上

例如

// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

// Create an RDD
val people = sc.textFile("examples/src/main/resources/people.txt")

// The schema is encoded in a string
val schemaString = "name age"

// Import Row.
import org.apache.spark.sql.Row;

// Import Spark SQL data types
import org.apache.spark.sql.types.{StructType,StructField,StringType};

// Generate the schema based on the string of schema
val schema =
  StructType(
    schemaString.split(" ").map(fieldName => StructField(fieldName, StringType, true)))

// Convert records of the RDD (people) to Rows.
val rowRDD = people.map(_.split(",")).map(p => Row(p(0), p(1).trim))

// Apply the schema to the RDD.
val peopleDataFrame = sqlContext.createDataFrame(rowRDD, schema)

// Register the DataFrames as a table.
peopleDataFrame.registerTempTable("people")

// SQL statements can be run by using the sql methods provided by sqlContext.
val results = sqlContext.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(t => "Name: " + t(0)).collect().foreach(println)

三 数据源

通过将数据源转化为dataframe，spark sql可以操作多种数据源。可以像操作RDD那样操作dataframe，也可以注册为临时表执行SQL操作。本章讲解加载和保存spark数据源的通用方法，并深入探讨系统内置数据源的参数。

3.1 加载/保存函数

为了简单，用默认数据源作各种操作（默认数据源是parquet,通过修改spark.sql.source.default可以指定其它数据源）

val df = sqlContext.read.load("examples/src/main/resources/users.parquet")

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

3.1.1 手动设置参数

除了可以手动设置数据源外，还可以设置数据源的其它参数。需要注意，数据源需要使用正式的全称(i.e.,org.apache.spark.sql.parquet ),对于系统内置的数据源可以使用简称(json,parquet,jdbc)。 不同类型的dataframe之间可以相互转化。

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

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

3.1.2 直接运行SQL

除了可以调动read API读取文件至dataframe外，可以用SQL直接查询文件

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

3.1.3 保存模式

保存操作需要定义如何处理已存在数据，这个参数就是SaveMode。需要注意保存模式并不支持锁机制和原子操作。另外，覆盖模式会先删除数据，之后再写入新的数据。

Scala/Java	Any Language	Meaning
SaveMode.ErrorIfExists (default)	"error" (default)	每次保存dataframe时，如果数据源已存在，见会抛出异常
SaveMode.Append	"append"	每次保存dataframe时，如果数据源已存在，则会把新数据追加到已有数据之后
SaveMode.Overwrite	"overwrite"	每次保存dataframe时,如果数据源/表数据已经存在，老数据会被新数据覆盖
SaveMode.Ignore	"ignore"	每次保存dataframe时,如果数据源已经存在，见保存操作会被乎略

3.1.4 持久化表

程序中使用HiveContext时，Dataframes可以使用saveAsTable来持久化，不像registerTempTable，saveAsTable会实体化内容（本人说法：registerTempTable只是在程序运行内存中建立了一个逻辑表名，并用这个表名和dataframe关联，一旦程序退出，这个表将无法访问，这也是名字中间'Temp'的由来)，并在HiveMetastore中创建一个指针指向数据DataFrame。

只要保证连接到同一个metastore,持久化的表会一直存在即使重启spark。持久化表的dataframe可以通过SQLContext的table方法创建，只需要将表名作为参数传给table方法即可。

默认saveAsTable会创建一个”受管理表”,意味着表数据的存储路径会受metastore管理，drop受管理表会删除表数据。

3.2 Parquet文件

parquet是一种列式存储文件，spark sql操作对Parquet的读写，会自动保存原始数据的schema信息。

3.2.1 程序加载数据

见例子

// sqlContext from the previous example is used in this example.

// This is used to implicitly convert an RDD to a DataFrame.
import sqlContext.implicits._

val people: RDD[Person] = ... // An RDD of case class objects, from the previous example.

// The RDD is implicitly converted to a DataFrame by implicits, allowing it to be stored using Parquet.
people.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 parquetFile = sqlContext.read.parquet("people.parquet")

//Parquet files can also be registered as tables and then used in SQL statements.
parquetFile.registerTempTable("parquetFile")
val teenagers = sqlContext.sql("SELECT name FROM parquetFile WHERE age >= 13 AND age <= 19")
teenagers.map(t => "Name: " + t(0)).collect().foreach(println)

3.2.2 分区发现

表分区技术和hive一样，是一种数据处理的优化技术。在分区表中，数据通常分别存放在不同的目录下，分区字段的值会出现在分区目录上。parquet数据源可以自动发现或推断分区信息。例如，可以把实验数据按下面目录结构存放在分区表中，分区字段gender , country 。

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

把 path/to/table传递给SQLContext.read.parquet或SQLContext.read.load后，SparkSQL会自动从路径中抽取分区信息，此时的dataframe返回值变成

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

注意到分区列数据类型是自动推断的。当前版本只支持数值类型和字符串类型。有时我们不希望程序自动推断分区列的数据类型，配置spark.sql.sources.partitionColumnTypeInferene.enabled参数来关闭自动推断。关闭自动推断后，分区列的数据类型只能是字符串。

从spark1.6之后，分区发现默认只在给定目录起作用。例如，把path/to/table/gender=male传递给SQLContext.read.parquet或SQLContext.read.load, 但gender并不会被认为是分区列，只有配置了basePath为分区发现的起始目录后，gender才会被认为是分区列。

3.2.3 schema合并

类似ProtocolBuffer,Avro, Thrift , parquet支持schema推衍。用户可以从简单schema开始，渐渐添加需要的列到schema中。这样导致每个parquet文件的schema，看上去不同但很相近，parquet数据源现在支持自动检测到此种情况，并将合并所有文件的schema。

考虑到schema合并是费时，并且在大多数情况是无意义的，因此从spark1.5.0开始默认关闭此特性。有两种方式开启

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

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

// sqlContext from the previous example is used in this example.

// This is used to implicitly convert an RDD to a DataFrame.
import sqlContext.implicits._

// Create a simple DataFrame, stored into a partition directory
val df1 = sc.makeRDD(1 to 5).map(i => (i, i * 2)).toDF("single", "double")
df1.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 df2 = sc.makeRDD(6 to 10).map(i => (i, i * 3)).toDF("single", "triple")
df2.write.parquet("data/test_table/key=2")

// Read the partitioned table
val df3 = sqlContext.read.option("mergeSchema", "true").parquet("data/test_table")
df3.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
// |-- single: int (nullable = true)
// |-- double: int (nullable = true)
// |-- triple: int (nullable = true)    译者注： 原文档中有误，运行结果应该没有此行
// |-- key : int (nullable = true)

3.4  parquet 表转化为兼容Hive metastore

当读取或写入hivemetastore parquet表时，spark sql为了提高性能，会使用spark的parquet类，取代Hiveparquet序列化和反序列化类。通过修改参数spark.sql.hive.convertMetastoreParquet，默认on支持转化。

3.4.1 hive/parquet schema 一致性

在处理表schema时，hive和parquet存在两个关键差别

1 hive 是大小写不敏感，而parquet是大小写敏感

2 hive认为所有列是nullable（可为空的），而parquet并不会轻意作这个假设

由于以上原因，当我们把hivemetastore parquet table转化为spark sql parquet table时，需要兼容hivemetastore schema 和parquet schema。原则如下：

1两种schema中相同的字段的类型必须一致，而不管是否是nullable(可为空)。只有当兼容字段用于spark sql parquet schema时，才需要考虑是否定义nullable。

2 当Hivemetastore schema中字段需要兼容时

I > parquet schema中独有的字段不需要兼容处理

II> hive metastore schema中独有字段兼容处理后为nullable字段

3.4.2 metadata刷新

为了提升性能，spark sql缓存parquet metadata。当hivemetastore parquet表转化启动后，从hivemetastore parquet表转化出的metadata也会缓存.如果表数据被Hive或其它外部工具更新，则需要手动刷新缓存中的数据。

// sqlContext is an existing HiveContext

sqlContext.refreshTable("my_table")

3.5 配置

parquet 配置可以在程序中使用SQLContext的setConf方法设置，或在SQL中使用SET key=value方式设置。

Property Name	Default	Meaning
spark.sql.parquet.binaryAsString	false	有些使用parquet的系统，特别是Impala,hive和老 版本spark sql ,并不区分二进制数组和字符串， 这个flag告诉spark sql把二进制数据解析成 字符串，以兼容两者。
spark.sql.parquet.int96AsTimestamp	true	有些使用parquet的系统，特别是impala,hive把 timestamp存为int96 ,这个flag告诉spark sql把 int96 解析成timestamp
spark.sql.parquet.cacheMetadata	true	打开parquet schema metadata缓存,可以加速查询
spark.sql.parquet.compression.codec	gzip	设置parquet文件压缩编码器，可选参数 uncompressed,snappy, gzip , lzo
spark.sql.parquet.filterPushdown	true	设置true允许parquet过滤
spark.sql.hive.convertMetastoreParquet	true	设置false，spark sql使用Hive序列化和反序列化 类解析parquet表，否则用spark内置parquet
spark.sql.parquet.output.committer.class	org.apache.parquet.hadoop. ParquetOutputCommitter	Parquet 使用输出committer类，此类必须是 org.apache.hadoop.mapreduce.OutputCommitter的子类，尤其 是org.apache.parquet.hadoop.ParquetOutputCommitter的子类 注意： 1 如果spark.speculation (spark 推断执行任务）为on,此配置项自动忽略 2 hadoop configuration 设置此参数，而不要用spark的 SQLConf 3 此参数会覆盖spark.sql.sources.outputCommitterClass spark sql使用内置org.apache.spark.sql.parquet.DirectParquetOutputCommitter ，此类比默认parquet输出committer ，在写入数据到S3时效率更高
spark.sql.parquet.mergeSchema	false	设置为true,parquet数据源合并所有数据文件的schema，否则schema从summary file， 如果summary file不存在，则使用任意数据文件schema

3.3 JSON Datasets

使用SQLContext.read.json()作用于RDD[String]或JSON文件，Spark SQL可以从json datasets中推断出schema信息，然后加载到dataframe中。

注意这个json文件并不是通常的JSON文件，每行必须包含分隔符，自包含有效JSON对象。如果使用通常的JSON文件 ，这个转化过程肯定会失败。

// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.SQLContext(sc)

// 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 people = sqlContext.read.json(path)

// The inferred schema can be visualized using the printSchema() method.
people.printSchema()
// root
//  |-- age: integer (nullable = true)
//  |-- name: string (nullable = true)

// Register this DataFrame as a table.
people.registerTempTable("people")

// SQL statements can be run by using the sql methods provided by sqlContext.
val teenagers = sqlContext.sql("SELECT name FROM people WHERE age >= 13 AND age <= 19")

// Alternatively, a DataFrame can be created for a JSON dataset represented by
// an RDD[String] storing one JSON object per string.
val anotherPeopleRDD = sc.parallelize(
  """{"name":"Yin","address":{"city":"Columbus","state":"Ohio"}}""" :: Nil)
val anotherPeople = sqlContext.read.json(anotherPeopleRDD)

3.4 hive表

Spark SQL 支持读写hive数据。然而，hive有很多依赖包，spark默认不包含这些依赖包。要想让spark完整支持hive,需要在打包编译时添加-Phive和-Phive-thriftserver两个参数。这样编译出来的spark才会包含hive及依赖的所有JAR包。需要注意这些JAR还要在所有worker节点的lib中，因为只有获取hive序列化和反序列化类库才能访问Hive中数据。

hive 配置包含在hive-site.xml,core-site.xml (安全配置),hdfs-site.xml (HDFS配置).注意要想运行在yarn集群配置，driver和executors 必须运行在yarn 集群模式，需要把datanucleus jar 放在lib_managed/jars目录下，hive-site.xml放到conf/目录下,更简单的方式是通过 --jars选项和 --file选项添加到spark-submit命令中去。

为了运行hive操作metastore中表和执行hiveql查询，需要继承自SQLContext的HiveContext。即使没有部署Hive仍然可以创建HiveContext对象。当没有配置hive-site.xml文件时，HiveContext自动创建metastore_db在当前目录(本人注解：此时hive metastore 使用derby数据库)，创建warehouse目录在HiveConf中指定的路径，默认是/user/hive/warehouse.需要注意/user/hive/warehouse目录要给spark用户赋权。

// sc is an existing SparkContext.
val sqlContext = new org.apache.spark.sql.hive.HiveContext(sc)

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

// Queries are expressed in HiveQL
sqlContext.sql("FROM src SELECT key, value").collect().foreach(println)

3.4.1 和不同版本的hive metastore交互

spark sql 最重要的是可以直接访问Hive metastore来获取hive表信息，进而读取hive表数据。从spark1.4起，单独的spark sql发布版本可以访问多种版本Hive metastore 。注意到，因为内部spark sql 编译支持hive1.2.1 ，所有spark sql可以访问各版本的hive metastore,并使用hive的类执行UDF，UDAF等 。

以下是配置metadata抽取数据的hive版本参数

Property Name	Default	Meaning
spark.sql.hive.metastore.version	1.2.1	Hive metastore 版本，从0.12.0到1.2.1
spark.sql.hive.metastore.jars	builtin	实例化HiveMetastoreClient类的jar的位置，有三个值可选 builtin spark 编译命令行中添加-Phive时会使用hive 1.2.1 , 使用此选项后，spark.sql.hive.metastore.version＝1.2.1， 或为空值 maven 不常用，使用maven仓库中下载的hive jar对应版本 JVM 的classpath路径，此路径要包含所有hive以及 依赖包，和相应hadoop包等。虽然只是用于driver， 但是如果运行在yarn集群模式，这些jar也要一起打包
spark.sql.hive.metastore.sharedPrefixes	com.mysql.jdbc, org.postgresql, com.microsoft.sqlserver, oracle.jdbc	以逗号分隔的包用于类加载器，这些包需要在sparkSQL 和Hive中共存。一个必须的类是用于连接metastore的 JDBC 驱动，其它还有像log4j等
spark.sql.hive.metastore.barrierPrefixes	(empty)	Spark SQL和hive通信时需要加载的类，用逗号分隔。 如 hive UDFs定义的jar

3.5 JDBC到其它数据库

Spark SQL可以使用JDBC去访问其它数据库类数据源，这个是通过JdbcRDD来实现的。因为返回的结果是DataFrame，

这样可以很方便在spark sql中处理或关联其它数据源，JDBC数据源可以轻松使用java ,python来开发而不需要ClassTag(scala专用) .

首先，我们需要在spark classpath中包含数据库的JDBC驱动，例如通过spark shell访问postgres

SPARK_CLASSPATH=postgresql-9.3-1102-jdbc41.jar bin/spark-shell

使用Data Source API，可以从远程数据库中加载DataFrame或Spark sql临时表，以下参数需要配置

Property Name	Meaning
url	JDBC URL连接串
dbtable	需要读取的JDBC表名，注意可以使用有效的SQL查询中的FROM子句，如使用子查询的结果集
driver	JDBC URL连接串使用的驱动类，在driver连接JDBC系统之前，master和所有workers需要加载这个驱动类
partitionColumn, lowerBound, upperBound,numPartitions	它们描述了多个workers并行分片读取表数据的方式。即使只用到四个选项其中一个，也需要把全部选项配置。partitionColumn必须是表中数值型列，lowerBoundand upperBound用于定义分区的跨度，所以表的全部行数据会分片并返回，而不会过被滤掉部分行
fetchSize	用于决定每次请求最多获取多少行，为了提升性能每次获取很少的行，如oracle10行

val jdbcDF = sqlContext.read.format("jdbc").options(

  Map("url" -> "jdbc:postgresql:dbserver",
  "dbtable" -> "schema.tablename")).load()

3.6 解决问题

1 JDBC驱动类需要在各个客户端和executor节点可见。因为那些在driver打开连接时不可见的类，javaDriverManager类的安全检查会乎略。一个简单的办法是修改所有workes节点的comput_classpath.sh脚本，以使能包含所有driver JARs。

2 有些数据库如H2， 会将所有名字转化为大写，因此在spark sql中需要同时使用大写

四 性能优化

为了减轻负载，应该尽可能将数据缓存在内存，或根据经难优化某些参数

4.1 将数据缓存在内存

Spark SQL 可以调用sqlContext.cacheTable(“tableName”)或dataFrame.cache()按列格式将表数据缓存于内存，然后SparkSQL只扫描需要的列，并将数据压缩至最小的内存消耗和GC负载。

可以调用sqlContext.uncacheTable(“tableName”)去移除内存中表数据

可以用SQLContext的setConf方法， 或在SQL命令行中使用SET key=value的方式，配置内存缓存参数。

Property Name	Default	Meaning
spark.sql.inMemoryColumnarStorage.compressed	true	设置true, Spark SQL会依据数据统计选择一种压缩类对列编码
spark.sql.inMemoryColumnarStorage.batchSize	10000	每批数据中列缓存的长度，更大的长度可以提高内存的使用和压缩，但会有OOM风险

4.2 其它配置项

以下配置也可以用于提升性能，可能后续版本会停用这些配置参数，或引入其它优化参数

Property Name	Default	Meaning
spark.sql.autoBroadcastJoinThreshold	10485760 (10 MB)	当运行关联操作时，允许表数据广播给所有workers的最大字节，当设置为-1时，广播特性关闭，注意hive中统计命令只在hivemetastore表中支持。ANALYZETABLE <tableName> COMPUTE STATISTICS noscan has beenrun.
spark.sql.tungsten.enabled	true	True,使用优化的Tungsten执行支撑内存管理和动态生成表达式运算的字节码
spark.sql.shuffle.partitions	200	当执行关联和聚合时，配置最大可使用的分区数

五 分布式SQL引擎

spark sql 可以作为分布式查询引擎，通过JDBC/ODBC,或命令行接口。这种模式下，终端用户或应用程序直接运行sparksql的SQL查询，而不需要写更多代码。

5.1 运行thriftJDBC/ODBC服务

HiveServer2 中集成了thriftJDBC/ODBC服务，可以使用beeline去连接spark或hive 1.2.1 的JDBC服务。

开启JDBC/ODBC服务，使用下面的脚本

./sbin/start-thriftserver.sh

bin/spark-submit命令行可以添加参数，--hiveconf选项可以读取hive属性。需要运行/sbin/start-thriftserver.sh --help调出所有选项。默认服务监听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去测试JDBC/ODBC thrift服务

./bin/beeline

在beeline中连接JDBC/ODBC服务

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

beeline会询问用户名和密码,在非安全模式下只需要输入用户名，不需要写密码，但在安全模式下，需要按这个文档配置beeeline documentation。

只需要把hive-site.xml, core-site.xml和hdfs-site.xml文件放到conf/目录下，就不用去配置Hive.

也可以把beeline和Hive一起使用。thrift jdbc服务支持通过http发送thrift rpc消息,通过配置以下属性或修改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 可以方便本地运行hivemetastore服务，或者通过命令行执行查询。注意sparksql cli不能直接去连接thrift jdbc服务

开启sparksql cli ,在spark目录下运行

./bin/spark-sql

将hive-site.xml , core-site.xml ,hdfs-site.xml文件放置到conf/下，可以配置Hive。运行bin/spark-sql –help去获取更多参考

六 版本迁移

6.1 spark sql 1.5 升级到spark sql 1.6

spark 1.6 默认thrift server运行多session模式。意味着，每个jdbc/odbc连接保持一份sql配置和临时函数，然而缓存表数据是共享的。如果想运行老版本的单session模式，需要设置spark.sql.hive.thriftServer.singleSession= true， 可以将此配置添加到spark-defaults.conf或start-thriftserver.sh时使用—conf添加配置。

./sbin/start-thriftserver.sh \
     --conf spark.sql.hive.thriftServer.singleSession=true \
     ...

6.2 spark sql 1.4升级到spark sql1.5

1默认支持使用手动内存管理(Tungsten)的执行优化，外加针对表达式计算的代码生成，这些特性可以设置spark.sql.tungsten.enabled=false禁用

2 parquet schema合并默认不再开启，需要配置spark.sql.parquet.mergeSchema = true

3 字符串解析可以使用点(.)来定义列名，或访问列中嵌套元素。例如df['table.column.nestedField'] .然而，当列或中包含点(.)时，需要使用反引号，如table.`column.with.dots`.nested

4默认列式存储中分区裁剪开启，配置spark.sql.inMemoryColumnarStorage.partitionPruning＝false

5 不在支持无限有效位decimal列，spark sql最大允许38位有效位，当使用BigDecimal对像时，

对像的值是38位有效位且18位小数位，当在DDL中没有定义有效位和小数位时，默认Decimal(10,0)

6 timestamp 存储到1毫秒，而不再支持1纳秒

7 在sparksql , float和decimal等价，但hiveql仍然区分两者。

8 SQL / Dataframe 的函数使用小写

9 在hadoop推断模式下，使用DirectOutputCommitter并不安全，因此在hadoop推断模式下，即使配置此committer,但此committer也不在使用。

10 JSON数据源不会自己加载其它应用创建的新文件，为了持久化新文件到JSON表中，需要使用REFRESHTABLES的SQL命令，或HiveContext的refreshTable方法。如JSONData的DataFrame，需要重新创建DataFrame时，DataFrame才会包含新的文件。

6.3 spark sql 1.3 升级到sparksql 1.4

6.3.1 Dataframe 数据读取和写入接口

依据用户反馈，我们新添加了SQLContext.read接口用户读取文件，DataFrame.write接口用于写入数据，并停用老的API， 如 SQLContext.parquetFile ,SQLContext.jsonFile

参见API文档SQLContext.read ( scala, java,python)和DataFrame.write (scala ,java,python )

6.3.2 DataFrame groupBy 保持分组列

基于用户反馈，修改默认DataFrame.groupBy().agg()方法，将分组列保存在DataFrame里，想使用原来老的处理方式，修改spark.sql.retainGroupColumns=false

// In 1.3.x, in order for the grouping column "department" to show up,

// it must be included explicitly as part of the agg function call.
df.groupBy("department").agg($"department", max("age"), sum("expense"))

// In 1.4+, grouping column "department" is included automatically.
df.groupBy("department").agg(max("age"), sum("expense"))

// Revert to 1.3 behavior (not retaining grouping column) by:
sqlContext.setConf("spark.sql.retainGroupColumns", "false")

6.4 spark sql 1.0-1.2 升级到1.3

spark 1.3 移除sparksql 'Alpha”标签,并清理了API，spark 1.3之后,sparksql的发布包兼容1.X系列，这种兼容处理保证API的稳定性。

6.4.1 SchemaRDD改名DataFrame

用户发现在spark1.3最大的改变是把schemaRDD改名DataFrame。这主要是因为DataFrame并不是继承自RDD，并且将RDD的方法在Dataframe类上重新实现。保证调用DataFrame的.rdd方法，仍然可以转换为RDD。

在ScalaAPI中，仍然保留schemaRDD这个名称，保证在用户早期的例子中数据源的兼容，但还是建议用户升级使用Dataframe。java和python用户不再支持SchemaRDD这个名称。

6.4.2 统一Java和scalaAPI

spark 1.3之前，javaAPI中有两个兼容的类JavaSQLContext和JavaSchemaRDD，ScalaAPI中也有类似情况 。在Spark1.3中，Java API和ScalaAPI统一使用SQLContext和DataFrame。通常这些类使用java和scala语言中的都支持的类型（如Array），但有些情况没有两种语言都支持的类型（如闭包和Maps)，这种情况下某些函数需要重载。

另外，有些java特有的类型已不支持，scala和java开发都需要使用org.apache.spark.sql.types来定义schema。

6.4.3 隔离隐式转换和删除DSL包（scalaonly)

spark 1.3之前很多示例代码import sqlContext._，这会把SQLContext中所有函数引入到当前代码中。自spark1.3，从RDD到DataFrame的转换函数，转移到SQLContext的implicits对象中，因此用户需要importsqlContext.implicits._

另外，这个隐式转换函数只支持products构造的RDD,需要调用toDF函数实现。

当使用DSL（被DataFrameAPI取代）中的函数时，用户使用import org.apache.spark.sql.catalyst.dsl ,而DataFrame函数接口中使用importorg.apache.spark.sql.functions._

6.4.4 删除org.apache.spark.sql中DataType(scala only)

spark 1.3中删除了sql包中DataType这个别名，用户需要importorg.apache.spark.sql.types

6.4.5 UDF注册移动到sqlContext.udf（JAVA＆SCALA）

DataFrame DSL和SQL中注册UDF的函数，都移动到SQLContext的udf对象中。

sqlContext.udf.register("strLen", (s: String) => s.length())

python 的UDF注册函数没有改动。

6.4.6 python 中DataTypes不在是单例

在python中使用DataTypes需要事先构造，而不能引用单例。

6.5 shark迁移

6.5.1 调度

用样设置JDBC客户端的公平调度池(Fairscheduler)，设置spark.sql.thriftserver.scheduler.pool

SET spark.sql.thriftserver.scheduler.pool=accounting;

6.5.2 reduce数

shark中默认reduce是1，可以配置mapred.reduce.tasks来修改此默认值，sparksql停用此参数，而使用spark.sql.shuffle.partitions=200

SET spark.sql.shuffle.partitions=10;
SELECT page, count(*) c
FROM logs_last_month_cached
GROUP BY page ORDER BY c DESC LIMIT 10;

也可以在hive-site.xml中配置此参数来修改默认值。现在mapred.reduce.tasks参数仍然可用，只是会自动转化为spark.sql.shuffle.partitions.

6.5.3 缓存

缓存表的属性spark.cache不在使用，同时表名后缀是_cached也不在自动缓存。新添加两个命令CACHETABLE或UNCACHE TABLE ，允许用户显示控制缓存。

CACHE TABLE logs_last_month;
UNCACHE TABLE logs_last_month;

注意：CACHE TABLE tbl默认不是lazy， 因此不需要手动触发缓存实体化。

CACHE [LAZY] TABLE [AS SELECT] ...

几个缓存相关的特性不再支持：

1 用户自定义分区上数据的缓存回收机制

2 RDD 重新加载

3 内存缓存的写入机制

6.6 apache hive 相容

在设计上，sparksql兼容hive metastore，SerDes和UDFs ,而现在的SerDes，UDFs都是基于hive1.2.1开发的，因此spark sql可以连接到不同版本的metastore（0.12.0到1.2.1）

6.6.1 在已有hive仓库上部署

spark sql thrift jdbc服务可以兼容已有hive，而不需要修改已有hivemetastore ,数据存放位置和表的分区

6.6.2 支持hive特性

spark sql 支持主要hive特性

Hive query statements, including:

• • SELECT

  • GROUP BY

  • ORDER BY

  • CLUSTER BY

  • SORT BY

• All Hive operators, including:

  • Relational operators (=,⇔,==,<>,<,>,>=,<=, etc)

  • Arithmetic operators (+,-,*,/,%, etc)

  • Logical operators (AND,&&,OR,||, etc)

  • Complex type constructors

  • Mathematical functions (sign,ln,cos,etc)

  • String functions (instr,length,printf,etc)

• User defined functions (UDF)

• User defined aggregation functions(UDAF)

• User defined serialization formats(SerDes)

• Window functions

• Joins

  • JOIN

  • {LEFT|RIGHT|FULL}OUTER JOIN

  • LEFT SEMIJOIN

  • CROSS JOIN

• Unions

• Sub-queries

  • SELECT colFROM ( SELECT a + b AS col from t1) t2

• Sampling

• Explain

• Partitioned tables includingdynamic partition insertion

• View

• All Hive DDL Functions, including:

  • CREATETABLE

  • CREATETABLE AS SELECT

  • ALTER TABLE

• Most Hive Data types, including:

  • TINYINT

  • SMALLINT

  • INT

  • BIGINT

  • BOOLEAN

  • FLOAT

  • DOUBLE

  • STRING

  • BINARY

  • TIMESTAMP

  • DATE

  • ARRAY<>

  • MAP<>

  • STRUCT<>

6.6.3 不支持hive功能

以下Hive特性暂不支持，并且很多这些特性在hive中很少使用。

Major Hive Features

分桶的hive表：分桶是hash分区上进行hash分片的技术，sparksql暂不支持

Esoteric Hive Features

1 union 语法

2 join 语法

3 列统计：sparksql暂不支持扫描来收集列统计，只支持填入hivemetastore的sizeInBytes字段

Hive Input/Output Formats

1cli文件格式：sparksql只支持textoutputformat

2hadoop 打包

Hive Optimizations

很多hive的优化项spark暂时不支持，有些在sparksql内存计算的模式下不起作用，有些在后续版本会支持。

1块级bitmap索引和虚拟列

2join和groupby中自动确认reduce数：可以配置参数设置shuffle的并行度，SETspark.sql.shuffle.partitions=[num_tasks];

3查询元数据：查询操作只需要元数据，sparksql会单起一个任务计算结果

4数据偏斜标识：sparksql不使用hive的数据偏斜标识

5join中STREAMTABLE提示：sparksql不使用STREAMTABLE提示

6查询时合并多个小文件：如果查询多个小文件，hive会先将小文件合并成较大的文件，以防止hdfs元数据记录太多，sparksql不支持。

七参考

7.1数据类型

sparksql 和dataframe支持以下数据类型。

Numeric types

• • ByteType:Represents 1-byte signed integer numbers. The range of numbers isfrom-128 to127.

  • ShortType:Represents 2-byte signed integer numbers. The range of numbers isfrom-32768 to32767.

  • IntegerType:Represents 4-byte signed integer numbers. The range of numbers isfrom-2147483648 to2147483647.

  • LongType:Represents 8-byte signed integer numbers. The range of numbers isfrom-9223372036854775808 to9223372036854775807.

  • FloatType:Represents 4-byte single-precision floating point numbers.

  • DoubleType:Represents 8-byte double-precision floating point numbers.

  • DecimalType:Represents arbitrary-precision signed decimal numbers. Backedinternally byjava.math.BigDecimal. ABigDecimal consists of an arbitraryprecision integer unscaled value and a 32-bit integer scale.

• String type

  • StringType:Represents character string values.

• Binary type

  • BinaryType:Represents byte sequence values.

• Boolean type

  • BooleanType:Represents boolean values.

• Datetime type

  • TimestampType:Represents values comprising values of fields year, month, day,hour, minute, and second.

  • DateType:Represents values comprising values of fields year, month, day.

• Complex types

  • ArrayType(elementType,containsNull): Represents values comprising a sequence ofelements with the type ofelementType.containsNull is used to indicate ifelements in aArrayType value can havenull values.

  • MapType(keyType,valueType, valueContainsNull): Represents values comprisinga set of key-value pairs. The data type of keys are described bykeyType and the data type of valuesare described by valueType. For aMapType value, keys are not allowed tohavenull values.valueContainsNullis used to indicate if values of aMapTypevalue can havenull values.

  • StructType(fields):Represents values with the structure described by a sequence ofStructFields (fields).

    • StructField(name, dataType,nullable): Represents a field in aStructType.The name of a field is indicated byname.The data type of a field is indicated bydataType.nullable is used to indicate ifvalues of this fields can havenullvalues.

Sparksql 所有支持的数据类型在org.apache.spark.sql.types，使用前需要import进来

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

Data type	Value type in Scala	API to access or create a data type
ByteType	Byte	ByteType
ShortType	Short	ShortType
IntegerType	Int	IntegerType
LongType	Long	LongType
FloatType	Float	FloatType
DoubleType	Double	DoubleType
DecimalType	java.math.BigDecimal	DecimalType
StringType	String	StringType
BinaryType	Array[Byte]	BinaryType
BooleanType	Boolean	BooleanType
TimestampType	java.sql.Timestamp	TimestampType
DateType	java.sql.Date	DateType
ArrayType	scala.collection.Seq	ArrayType(elementType, [containsNull]) Note:The default value of containsNull is true.
MapType	scala.collection.Map	MapType(keyType, valueType,[valueContainsNull]) Note: The default value ofvalueContainsNull is true.
StructType	org.apache.spark.sql.Row	StructType(fields) Note: fields is aSeq of StructFields. Also, two fields with the same name are notallowed.
StructField	The value type in Scala of the data type of this field (Forexample, Int for a StructField with the data type IntegerType)	StructField(name, dataType, nullable)

7.2NaN (Not a number)

当float和double不完全适用浮点数语义时，会有特殊的处理非数值类数据的方法。

1NaN = NaN 返回真

2在聚合时，所有NaN聚合为一个值

3NaN在关联中做key时，作为一个值来看待

4 当按长序排列时，NaN作为数值型值中最大的排在最后面。