Spark SQL，DataFrame 和Datasets 指南--Spak2.4.3

Spark SQL,DataFrame,Dataset

Spark SQL是一个结构化数据处理模块。不像Spark RDD API，Spark SQL提供的接口使Spark更加熟悉数据和执行的计算的结构。在内部，Spark SQL使用额外的信息来执行额外的优化。可以通过SQL和Dataset API来和Spark SQL交互。不管使用什么API/语言来表达计算，都是用的一个计算引擎。这种统一给了开发者很大的自由度来在不同的API之间选择最容易表达计算转换方式的方式。
本页中所有的采样数据使用的都是采样数据，可以在spark-shell,pyspark,sparKR中运行。

SQL

Spark SQL用处之一就是执行SQL查询，还可以从HIve中读取数据。如果想直到如何配置这些功能请查阅Hive Tables部分。当使用另一种编程语言来运行SQL时返回结果时Dataset/DataFrame。还可以采用命令行甚至JDBC/ODBC来和SQL接口交互。

Datasets和DataFrame

Dataset是分布式数据集合，从Spark1.6开始支持，它拥有RDD的优势（强类型，使用lambda函数）以及Spark SQL优化过的执行引擎。Dataset可以来源于JVM对象，可以采用转换函数操作（map.flatMap,filter等）。Scala和Python支持Dataset，而Python不支持。但由于Python动态的特性很多优势已经有了（可以通过row.columnName获取某行的某列值）。R也类似。
DataFrame就是有命名列的Dataset。概念上它等价于关系型数据库中的表或Python/R中的data frame，而其中包含了更多的优化。DataFrame的构建来源很广：结构化数据文件，Hive表，外部数据库或者RDD。四种语言都支持DataFrame。Scala和Java中DataFrame由Row构成的Dataset。Scala中DataFrame就是Dataset[Row]，Java中则是Dataset。
这篇文档中就把Row组成的Dataset当作DataFrame。

Start

出发吧：SparkSession

Spark中所有功能的入口是SparkSession类。要想创建基本的SparkSession可以直接使用SparkSession.builder：

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("Python Spark SQL basic example").config("spark.some.config.option","some-value").getOrCreate()

所有的样例代码位于Sprak repo中的examples/src/main/python/sql/basic.py。Spark2.0的SparkSession内置支持使用HiveQL写查询，使用Hive UDF以及从Hive表中读取数据的功能。有了这些特性你就不必非得启动Hive了。

创建DataFrame

有了SparkSession，应用就可以由现存的RDD，Hive表或Spark数据源中创建DataFrame。
下面的例子是基于JSON文件创建DataFrame:

# spark is an existing SparkSession
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|
# +----+-------+

全部代码在Spark repo中的examples/src/main/python/sql/basic.py。

非泛型 Dataset操作（即DataFrame操作）

DataFrame实际是为Scala,Java,Python,R提供了处理结构化数据的领域内的语言。
如上所述，Spark2.0中，Java和Scala的DataFrame就是Row组成的Dataset。Scala/Java Dataset属于强泛型，而这种DataFrame就是非泛型操作了。
这里展示一些使用Dataset完成的结构化数据处理样例。
Python中可以通过属性或索引来获取DataFrame的列。尽管前者很方便，但是仍建议使用后者

# spark, df are from the previous example
# 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|
# +----+-----+
# |  19|    1|
# |null|    1|
# |  30|    1|
# +----+-----+

DataFrame全部操作可见于API Document。
除了上面的列引用和表达式，DataFrame有丰富的库函数用于处理字符串操作，日期计算，常用的数学运算等。完整列表见于DataFrame Function Reference。

SQL查询

SparkSession的sql函数使应用能够运行SQL查询并返回DataFrame。

# Register the DataFrame as a SQL temporary view
df.createOrReplaceTempView("people")

sqlDF = spark.sql("SELECT * FROM people")
sqlDF.show()
# +----+-------+
# | age|   name|
# +----+-------+
# |null|Michael|
# |  30|   Andy|
# |  19| Justin|
# +----+-------+

全局临时视图

Spark SQL的临时视图属于会话期间有效，一旦会话结束则消失地无影无踪。如果想要创建一个会话间共享甚至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|
# +----+-------+

创建Dataset

Dataset类似于RDD，但不是使用Java序列化或Kryo，Dataset使用特殊的编码器来序列化对用从而用于处理或网络间传输。尽管这种编码器和标准序列化都会将对象转换为字节，但编码器动态编码并且允许Spark在没有将字节反序列化回对象的时候就执行很多像filtering,sorting,hashing这样的操作。

import java.util.Arrays;
import java.util.Collections;
import java.io.Serializable;

import org.apache.spark.api.java.function.MapFunction;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.Encoder;
import org.apache.spark.sql.Encoders;

public static class Person implements Serializable {
  private String name;
  private int age;

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public int getAge() {
    return age;
  }

  public void setAge(int age) {
    this.age = age;
  }
}

// Create an instance of a Bean class
Person person = new Person();
person.setName("Andy");
person.setAge(32);

// Encoders are created for Java beans
Encoder<Person> personEncoder = Encoders.bean(Person.class);
Dataset<Person> javaBeanDS = spark.createDataset(
  Collections.singletonList(person),
  personEncoder
);
javaBeanDS.show();
// +---+----+
// |age|name|
// +---+----+
// | 32|Andy|
// +---+----+

// Encoders for most common types are provided in class Encoders
Encoder<Integer> integerEncoder = Encoders.INT();
Dataset<Integer> primitiveDS = spark.createDataset(Arrays.asList(1, 2, 3), integerEncoder);
Dataset<Integer> transformedDS = primitiveDS.map(
    (MapFunction<Integer, Integer>) value -> value + 1,
    integerEncoder);
transformedDS.collect(); // Returns [2, 3, 4]

// DataFrames can be converted to a Dataset by providing a class. Mapping based on name
String path = "examples/src/main/resources/people.json";
Dataset<Person> peopleDS = spark.read().json(path).as(personEncoder);
peopleDS.show();
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+

与RDD交互

Spark SQL支持两种不同的方法将现存的RDD转换为Dataset。其一是使用反射机制来推理RDD中具体对象类型的schema。如果编写Spark应用代码时已经知道了schema那这种反射机制就很不错。
其二是通过接口创建schema应用到RDD上来创建Dataset。尽管比第一种冗长，但可以直到运行时才知道列和类型进而构建Dataset。

使用反射机制进行推理schema

Spark SQL会将Row对象组成的RDD转换为DataFrame,并自动推理数据类型。Row对象就是由Row类构造，其参数是一系列键值对。一系列键代表表中的列名，泛型由对整个数据集采样而推理得来，这很像JSON文件上的推理过程。

from pyspark.sql import Row

sc = spark.sparkContext

# Load a text file and convert each line to a Row.
lines = sc.textFile("examples/src/main/resources/people.txt")
parts = lines.map(lambda l: l.split(","))
people = parts.map(lambda p: Row(name=p[0], age=int(p[1])))

# Infer the schema, and register the DataFrame as a table.
schemaPeople = spark.createDataFrame(people)
schemaPeople.createOrReplaceTempView("people")

# SQL can be run over DataFrames that have been registered as a table.
teenagers = spark.sql("SELECT name FROM people WHERE age >= 13 AND age <= 19")

# The results of SQL queries are Dataframe objects.
# rdd returns the content as an :class:`pyspark.RDD` of :class:`Row`.
teenNames = teenagers.rdd.map(lambda p: "Name: " + p.name).collect()
for name in teenNames:
    print(name)
# Name: Justin

编程指定schema

当参数字典不能提前指定时(例如，记录的结构编码进了字符串，或者文本数据集解析之后其域对于不同用户则不同)可以根据下列三个步骤采用编程的方式来构造DataFrame:

1. 由原始RDD创建元组或列表RDD
2. 由StructType类创建shema对象，来匹配步骤1的元组或列表RDD的结构。
3. 通过SparkSession的createDataFrame方法应用shema来创建DataFrame。

# Import data types
from pyspark.sql.types import *

sc = spark.sparkContext

# Load a text file and convert each line to a Row.
lines = sc.textFile("examples/src/main/resources/people.txt")
parts = lines.map(lambda l: l.split(","))
# Each line is converted to a tuple.
people = parts.map(lambda p: (p[0], p[1].strip()))

# The schema is encoded in a string.
schemaString = "name age"

fields = [StructField(field_name, StringType(), True) for field_name in schemaString.split()]
schema = StructType(fields)

# Apply the schema to the RDD.
schemaPeople = spark.createDataFrame(people, schema)

# Creates a temporary view using the DataFrame
schemaPeople.createOrReplaceTempView("people")

# SQL can be run over DataFrames that have been registered as a table.
results = spark.sql("SELECT name FROM people")

results.show()
# +-------+
# |   name|
# +-------+
# |Michael|
# |   Andy|
# | Justin|
# +-------+

聚合

内置DataFrame函数提供常用的聚合操作，例如count(),countDistinct(),avg(),max(),min()等。尽管这些函数是专为DataFrame设计的，Spark SQL在Java和Scala中也有泛型安全的这些函数来work with Dataset。而且用户不必限于预定义的聚合函数可以自定义的。

非泛型用户自定义聚合函数

用户需要继承UserDefinedAggregateFunction抽象类，实现其中一个非泛型聚合方法。例如用户自定义平均值：

import java.util.ArrayList;
import java.util.List;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.expressions.MutableAggregationBuffer;
import org.apache.spark.sql.expressions.UserDefinedAggregateFunction;
import org.apache.spark.sql.types.DataType;
import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StructField;
import org.apache.spark.sql.types.StructType;

public static class MyAverage extends UserDefinedAggregateFunction {

  private StructType inputSchema;
  private StructType bufferSchema;

  public MyAverage() {
    List<StructField> inputFields = new ArrayList<>();
    inputFields.add(DataTypes.createStructField("inputColumn", DataTypes.LongType, true));
    inputSchema = DataTypes.createStructType(inputFields);

    List<StructField> bufferFields = new ArrayList<>();
    bufferFields.add(DataTypes.createStructField("sum", DataTypes.LongType, true));
    bufferFields.add(DataTypes.createStructField("count", DataTypes.LongType, true));
    bufferSchema = DataTypes.createStructType(bufferFields);
  }
  // Data types of input arguments of this aggregate function
  public StructType inputSchema() {
    return inputSchema;
  }
  // Data types of values in the aggregation buffer
  public StructType bufferSchema() {
    return bufferSchema;
  }
  // The data type of the returned value
  public DataType dataType() {
    return DataTypes.DoubleType;
  }
  // Whether this function always returns the same output on the identical input
  public boolean deterministic() {
    return true;
  }
  // Initializes the given aggregation buffer. The buffer itself is a `Row` that in addition to
  // standard methods like retrieving a value at an index (e.g., get(), getBoolean()), provides
  // the opportunity to update its values. Note that arrays and maps inside the buffer are still
  // immutable.
  public void initialize(MutableAggregationBuffer buffer) {
    buffer.update(0, 0L);
    buffer.update(1, 0L);
  }
  // Updates the given aggregation buffer `buffer` with new input data from `input`
  public void update(MutableAggregationBuffer buffer, Row input) {
    if (!input.isNullAt(0)) {
      long updatedSum = buffer.getLong(0) + input.getLong(0);
      long updatedCount = buffer.getLong(1) + 1;
      buffer.update(0, updatedSum);
      buffer.update(1, updatedCount);
    }
  }
  // Merges two aggregation buffers and stores the updated buffer values back to `buffer1`
  public void merge(MutableAggregationBuffer buffer1, Row buffer2) {
    long mergedSum = buffer1.getLong(0) + buffer2.getLong(0);
    long mergedCount = buffer1.getLong(1) + buffer2.getLong(1);
    buffer1.update(0, mergedSum);
    buffer1.update(1, mergedCount);
  }
  // Calculates the final result
  public Double evaluate(Row buffer) {
    return ((double) buffer.getLong(0)) / buffer.getLong(1);
  }
}

// Register the function to access it
spark.udf().register("myAverage", new MyAverage());

Dataset<Row> df = spark.read().json("examples/src/main/resources/employees.json");
df.createOrReplaceTempView("employees");
df.show();
// +-------+------+
// |   name|salary|
// +-------+------+
// |Michael|  3000|
// |   Andy|  4500|
// | Justin|  3500|
// |  Berta|  4000|
// +-------+------+

Dataset<Row> result = spark.sql("SELECT myAverage(salary) as average_salary FROM employees");
result.show();
// +--------------+
// |average_salary|
// +--------------+
// |        3750.0|
// +--------------+

泛型安全的用户自定义聚合函数

强泛型Dataset的用户自定义聚合需要实现Aggregator抽象类。例如一个泛型安全的用户自定义平均值：

import java.io.Serializable;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Encoder;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.TypedColumn;
import org.apache.spark.sql.expressions.Aggregator;

public static class Employee implements Serializable {
  private String name;
  private long salary;

  // Constructors, getters, setters...

}

public static class Average implements Serializable  {
  private long sum;
  private long count;

  // Constructors, getters, setters...

}

public static class MyAverage extends Aggregator<Employee, Average, Double> {
  // A zero value for this aggregation. Should satisfy the property that any b + zero = b
  public Average zero() {
    return new Average(0L, 0L);
  }
  // Combine two values to produce a new value. For performance, the function may modify `buffer`
  // and return it instead of constructing a new object
  public Average reduce(Average buffer, Employee employee) {
    long newSum = buffer.getSum() + employee.getSalary();
    long newCount = buffer.getCount() + 1;
    buffer.setSum(newSum);
    buffer.setCount(newCount);
    return buffer;
  }
  // Merge two intermediate values
  public Average merge(Average b1, Average b2) {
    long mergedSum = b1.getSum() + b2.getSum();
    long mergedCount = b1.getCount() + b2.getCount();
    b1.setSum(mergedSum);
    b1.setCount(mergedCount);
    return b1;
  }
  // Transform the output of the reduction
  public Double finish(Average reduction) {
    return ((double) reduction.getSum()) / reduction.getCount();
  }
  // Specifies the Encoder for the intermediate value type
  public Encoder<Average> bufferEncoder() {
    return Encoders.bean(Average.class);
  }
  // Specifies the Encoder for the final output value type
  public Encoder<Double> outputEncoder() {
    return Encoders.DOUBLE();
  }
}

Encoder<Employee> employeeEncoder = Encoders.bean(Employee.class);
String path = "examples/src/main/resources/employees.json";
Dataset<Employee> ds = spark.read().json(path).as(employeeEncoder);
ds.show();
// +-------+------+
// |   name|salary|
// +-------+------+
// |Michael|  3000|
// |   Andy|  4500|
// | Justin|  3500|
// |  Berta|  4000|
// +-------+------+

MyAverage myAverage = new MyAverage();
// Convert the function to a `TypedColumn` and give it a name
TypedColumn<Employee, Double> averageSalary = myAverage.toColumn().name("average_salary");
Dataset<Double> result = ds.select(averageSalary);
result.show();
// +--------------+
// |average_salary|
// +--------------+
// |        3750.0|
// +--------------+

数据源

Spark SQL支持通过DataFrame接口处理多种不同的数据源。DataFrame可以用于关系型转换操作和创建临时视图。将DataFrame注册为临时视图可以通过SQL来查询其中数据。这部分主要讲解Spark Data Source常用的加载和保存数据的方法，以及内置数据源的具体细节。

内置加载/保存函数

最简单的情况下，默认数据源（即parquet，除非修改了配置saprk.sql.sources.default）可以用于所有操作。

df = spark.read.load("examples/src/main/resources/users.parquet")
df.select("name", "favorite_color").write.save("namesAndFavColors.parquet")

完整代码详见"examples/src/main/python/sql/datasource.py。

手动设定参数

你也可以通过传递其他参数来设定数据源。数据源由全名（例如org.apache.spark.sql.parquet）指定，对于内置的源可以使用缩写（json,parquet,jdbc,orc,libsvm,csv,text）。这样就可以从一种数据源加载成DataFrame然后转换为另一种。
要加载JSON可以

df = spark.read.load("example/src/main/resources/people.json",format="json")
df.select("name","age").write.save("namesAndAges.parquet",format="parquet")

加载CSV可以

df = spark.read.load("examples/src/main/resources/people.csv",format="csv",sep=":",inferSchema="true",header="true")

额外的参数也可以用于写操作上。例如，可以为ORC数据源指定布隆过滤器和字典编码。下面的样例就是在favorite_color上创建布隆过滤器以及设置name和favorite_color的字典编码。对于Parquet，也有parquet.enable.dictionary。查看官方Apache ORC/Parquet网站来了解其他选项的细节。

df = spark.read.orc("examples.src/main/resources/users.orc")
(df.write.format("orc").option("orc.bloom.filter.columns","favorite_color").option("orc.dictionary.key.threhold","1.0").sve("users_with_options.orc"))

直接在文件上运行SQL

不需要读取文件成DataFrame再去查询，你可以直接在文件上运行SQL查询啊！！！

df = spark.sql("SELECT * FROM parquet.'examples/src/main/resources/users/parquet'")

保存Modes

保存可以使用SaveMode，这需要指定如何处理现存数据。一定注意这种方法没有锁，非原子性。所以多个writer写同一个位置不安全。另外，执行Overwrite的时候写之前旧数据会被删除的。

Scala/Java	任何语言	意义
SaveMode.ErrorIfExists(default)	“error"或"errorifexists”(default)	当保存DataFrame到某个数据源时发现有数据了那就抛异常吧
SaveMode.Append	“append”	追加写，保留原数据在后面追加新的DataFrame内容
SaveMode.Overwrite	“overwrite”	覆盖写，覆盖掉原来的内容
SaveMode.Ignore	“ignore”	忽略意味着如果保存DataFrame的时候如果有了数据，那就忽略掉这次保存的，不变动原来的

保存到持久化表

使用saveAsTable命令可以将DataFrame保存成Hive metastore。注意这不意味着真部署了Hive，Spark会使用Derby创建默认的本地Hive metastore。不像createOrReplaceTempView命令，saveAsTable物化DataFrame的数据并创建指向Hive metastore数据。主要你维持着到相同的metastore的连接那持久表即使Spark 程序重启了依然存在。调用SparkSession的table方法传入表名就可以根据DataFrame创建持久表。
对于基于文件的数据源例如text,parquet,json等，可以通过path参数指定自定标路径，例如df.write.option(“path”,"/some/path").saveAsTable(“t”)。当表被dropped自定义表的路径不会清除。如果没有指定自定义表路径，则Spark将数据写到数据仓库目录中默认位置。表被dropped时默认表路径也被清除。
从Spark2.1开始，持久化的数据源表在Hive metastore中每个分区一个metastore。这有几点优势：

• 只需要查询涉及到的必要的分区即可，不必都得找到表中所有的分区再查询。
• Hive DDL（ALTER TABLE PARTITION…SET LOCATION）现在支持Datasource API创建的表了。

注意创建外部数据源表时默认不会收集分区信息，要想在metastore中同步分区信息使用MSCK REPAIR TABLE.

Bucketing,Sorting以及Partitioning

对于基于文件的数据源，可能会对输出partition或bucket-sort。Bucketing和sorting只对持久表管用。

df.write.bucketBy(42,"name").sortBy("age").saveAsTable("people_bucketed")

但partition可以在使用Dataset API时用于save和saveAsTable

df.write.partitionBy("favorite_color").format("parquet").save("namesPartByColor.parquet")

也可能对一个表又partition又bucketing

df =  spark.read.parquet("examples/src/main/resources/users.parquet")
(df
.write
.partitionBy("favorite_color")
.bucketBy(42,"name")
.saveAsTable("people_partitioned_bucketed"))

partitionBy创建了Partition Directory一节中说的目录结构。因此partitionBy不适用于列值非常多的情况。而bucketBy将数据分到固定数目的桶中，可以用于列中唯一值有很多的情况。

Parquet文件

Parquet支持很多其他数据处理系统的列式格式。Spark SQL支持自动解析schema的读写Parquet文件。当写Parquet文件时，所有的列值都会为了兼容而自动转换为nullable（？？）。

加载数据

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.
parquetFile = spark.read.parquet("people.parquet")

# Parquet files can also be used to create a temporary view and then used in SQL statements.
parquetFile.createOrReplaceTempView("parquetFile")
teenagers = spark.sql("SELECT name FROM parquetFile WHERE age >= 13 AND age <= 19")
teenagers.show()
# +------+
# |  name|
# +------+
# |Justin|
# +------+

分区发现

表分区是Hive这种系统常用的优化方法。分区表的数据通常存储在不同目录中，分区列值则体现在每个分区目录的路径上。所以内置的文件数据源（包括Text/CSV/JSON/ORC/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给SparkSession.read.parquet或SparkSession.read.load，Spark SQL将自动从路径中提取分区信息。注意返回的DataFrame是

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

注意分区列的数据类型自动推理，现在Spark SQL支持数值型，日期型，时间型，字符串型自动推理。有时不想用这种自动推理机制，可以把spark.sql.sources.partitionColumnTypeInference.enable设置为false。此时分区列都会被认为是字符串类型。
从Spark1.6开始，分区发现默认找到给定路径下的分区。对于上面的例子，如果用户将path/to/table/gender=male传递给SparkSession.read.parquet或SparkSession.read.load，那么gender不会被视为分区列。如果用户需要指定分区发现开始的基路径，可以在数据源参数中设置basePath，例如当把path/to/table/gender=male设置为数据路径并且设置basePath为path/to/table，则gender成为分区列。

schema合并

类似Protocol Buffer，Avro和Thrift，Parquet也支持schema演化。用户可以以简单的schema开始逐渐加入需要的schema。最终可能有多个Parquet文件，他们不同但相互兼容schema。Parquet能够原子性地发现这种情况然后合并schema。
由于schema合并代价很高而且多数情况下不必要所以Spark1.5默认关闭。可以这样打开它：

1. 读取Parquet文件时设置mergeSchema为true。
2. 设置全局SQL参数spark.sql.parquet.mergeSchema为true

from pyspark.sql import Row

# spark is from the previous example.
# Create a simple DataFrame, stored into a partition directory
sc = spark.sparkContext

squaresDF = spark.createDataFrame(sc.parallelize(range(1, 6))
                                  .map(lambda i: Row(single=i, double=i ** 2)))
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
cubesDF = spark.createDataFrame(sc.parallelize(range(6, 11))
                                .map(lambda i: Row(single=i, triple=i ** 3)))
cubesDF.write.parquet("data/test_table/key=2")

# Read the partitioned table
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
#  |-- double: long (nullable = true)
#  |-- single: long (nullable = true)
#  |-- triple: long (nullable = true)
#  |-- key: integer (nullable = true)

Hive metastore Parquet 表变换

当读取或写入Hive metastore Parquet表时，Spark SQL使用自己的Parquet机制而非Hive SerDe以求更好的性能。这种方式由spark.sql.hive.convertMetastoreParquet配置设置默认打开。

协调Hive/Parquet schema

Hive和Parquet之间从表schema处理角度有两点关键区别：

• Hive大小写不敏感，Parquet敏感
• Hive所有的列都可以为空，而Parquet不是

所以当在把Hive metastore Parquet表转为Spark SQL Parquet表时一定需要协调Hive metastore schema和Parquet schema。调整原则时：

1. 两个schema内有相同名称的field除了可否为空之外得有相同得数据类型。调整得field数据类型依照Parquet一边，从而遵守空值规则。
2. 调整的schema具体包含这些定义在Hive metastore schema的filed：所有仅出现在Parquet schema中的filed都被删掉；所有仅出现在Hive metastore schema中的field作为nullable filed加入。

metadata刷新

当Hive metastore Parquet 表转换开启时Spark SQL缓存Parquet metadata以得到更佳性能，这些转换表的metadata也会被缓存。当这些表由Hive或其他外部工具更新了就必须手动刷新这些metadata从而保证metadata一致性。

# spark is an existing SparkSession
spark.catalog.refreshTable("my_table")

配置

Parquet的配置信息可以通过SparkSession的setConf方法或使用SQL 命令SET key=value

属性名	默认值	解释说明
spark.sql.parquet.binaryAsString	false	一些其他Parquet生成系统例如Impala,Hive,旧版本的Spark SQL在写Parquet schema的时候不区分二进制数据和字符串。该属性使Spark SQL将二进制数据解释为字符串以兼容这些系统
spark.sql.parquet.int96AsTimestamp	true	一些Parquet生成系统特别是Impala,Hive，将Timestamp存储为INT96。该属性让Spark SQL将INT96解释为时间戳类型以兼容这些系统
spark.sql.parquet.compression.codec	snappy	设置写parquet文件时的压缩方法。
spark.sql.parquet.filterPushdown	true	设置为true时使Parquet过滤器优化下推
spark.sql.hive.convertMetastoreParquet	true	false时使Spark SQL对parquet表的序列化方法使用Hive SerDe而不是内置方法
spark.sql.parquet.mergeSchema	false	true时使Parquet数据源合并所有收集到的数据文件的schema，除非schema来自于summary 文件或随机数据文件
spark.sql.parquet.writeLegacyFormat	false	true使用Spark1.4及之前的方法写出数据。

ORD文件

自Spark2.3依赖Spark采用新的ORC文件格式支持向量化的ORC读取器。下面的配置就是新添加的选项。当spark.sql.orc.impl设置为native和spark.sql.orc.enableVectorizedReader设置为true时向量化读取器用于本地ORC表。当spark.sql.hive.convertMetastoreOrc设置为true时它才用于Hive ORC serde表。

属性名	默认值	解释说明
spark.sql.orc.impl	native	ORC实现名称，“native”和 “hive.native”
spark.sql.orc.enableVectorizedReader	true

JSON文件

Spark SQL可以自动推到JSON数据集的schema并加载为DataFrame。使用SparkSession.read.json实现该转换。
注意这里的json file不是一般意义上的JSON 文件。每行必须包含一个独立的自包含的JSON对象。查看JSON Lines text format,also called newline-delimited JSON了解更多。
对于标准的多行JSON文件设置multiLine参数为true

# spark is from the previous example.
sc = spark.sparkContext

# A JSON dataset is pointed to by path.
# The path can be either a single text file or a directory storing text files
path = "examples/src/main/resources/people.json"
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
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
jsonStrings = ['{"name":"Yin","address":{"city":"Columbus","state":"Ohio"}}']
otherPeopleRDD = sc.parallelize(jsonStrings)
otherPeople = spark.read.json(otherPeopleRDD)
otherPeople.show()
# +---------------+----+
# |        address|name|
# +---------------+----+
# |[Columbus,Ohio]| Yin|
# +---------------+----+

Hive表

Spark SQL也是支持读写Apache Hive数据。但Hive有很多依赖倒置默认Spark包不包含这些依赖。如果Hive依赖位于类路径下那么Spark会加载他们。注意所有worker上都得有这些Hive依赖库因为每个节点都需要Hive 序列化和反序列化（SerDes）从而获取Hive数据。
将hive-site.xml,core-site.xml.hdfs-site.xml配置文件放在Spark的conf/目录下就可配置Hive。
要想与Hive交互用户必须实例化Hive支持的SparkSession，包括与持久化Hive metastore的连接，Hive serdes支持和Hive用户自定义函数。即使没有部署Hive也可以支持Hive。如果没有配置hive-site.xml（说明没有部署Hive），那context会自动在当前目录创建metastore_db根据spark.sql.warehouse.dir配置创建目录，spark-warehouse默认值时Spark应用启动的当前目录。注意Spark2.0.0之后取消了hive-site.xml中的hive.metastore.warehouse.dir属性，取而代之的是用spark.sql.awrehouse.dir来指定数据仓库中数据库的默认位置。需要赋予Spark应用所属用户写权限才行。

from os.path import expanduser, join, abspath

from pyspark.sql import SparkSession
from pyspark.sql import Row

# warehouse_location points to the default location for managed databases and tables
warehouse_location = abspath('spark-warehouse')

spark = SparkSession \
    .builder \
    .appName("Python Spark SQL Hive integration example") \
    .config("spark.sql.warehouse.dir", warehouse_location) \
    .enableHiveSupport() \
    .getOrCreate()

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

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

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

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

# The items in DataFrames are of type Row, which allows you to access each column by ordinal.
stringsDS = sqlDF.rdd.map(lambda row: "Key: %d, Value: %s" % (row.key, row.value))
for record in stringsDS.collect():
    print(record)
# 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.
Record = Row("key", "value")
recordsDF = spark.createDataFrame([Record(i, "val_" + str(i)) for i in range(1, 101)])
recordsDF.createOrReplaceTempView("records")

# Queries can then join DataFrame data with data stored in Hive.
spark.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|
# ...

为Hive表指定存储格式

当创建Hive表时应该定义表如何读写，例如输入输出格式。也要定义如何将表中的数据反序列化为行，如何将行序列化为数据，比如serde方法。下面的配置指定存储格式（serde,input format,output format），例如CREATE TABLE src(id int) USING hive OPTIONS(fileFormat ‘parquet’)。默认以文本文件读取。注意创建表时还不支持Hive存储handler，可以在Hive端使用存储handler创建表然后在Spark SQL中读取。

属性名	说明
fileFormat	fileFormat是存储格式细则包括serde,input format,output format。当前支持6种格式：sequencefile,rcfile,orc,parquet,textfile,avro
inputFoamat,outputFormat	这两个参数使用字符串格式指定输入格式和输出格式的名称，例如“org.apache.hadoop.hive.ql.io.orc.OrcInputFormat”。这两个参数必须成对儿出现，如果制订了fileFormat就不要再指定他们了。
serde	该参数指定serde类名。如果指定了fileFormat且给定的fileFormat包含了serde信息就不要再指定了。当前sequencefile,textfile,rcfile不包含，所以可以用这三个作为参数。
fieldDelim,escapeDelim,collectionDelim,mapkeyDelim,lineDelim	这些参数仅用于“textfile” fileFormat.他们定义将将分隔文件读成一行一行的。

其他OPTIONS属性被视为serde属性。

和不同版本的Hive metastore交互

Hive支持Spark SQL最重要的特性之一是获取Hive metastore，这使Spark SQL获取Hive 表的metastore。

用JDBC连接其他数据库

Spark SQL还包括一个通过JDBC从其他数据库读取数据的data source。这应优先于jdbcRDD，因为使用JDBC的返回值是DataFrame，并且容易使用Spark SQL处理以及和其他data source进行连接。Java或Python也容易使用JDBC因为不需要提供ClassTag。（注意这不同于Spark SQL JDBC 服务器，它允许其他应用使用Spark SQL运行查询）
首先得在spark类路径下包含数据库的JDBC驱动。例如要从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临时视图。用户可以在data source属性中指定JDBC连接属性，user和password是常见的连接属性用以登陆data source。除了连接属性Spark还支持下列大小写不敏感的选项：

属性名	解释
url	l要连接的JDBC URL。可能是jdbc:postgresql://localhost/test?user=fred&password=secret
dbtable	读取或写入的JDBC表。注意将其作为读入表路径时SQL查询中的FROM子句中所有都可用，例如圆括号括起来的子查询。不能同时指定dbtable和query
query	把数据读入Spark的查询操作，可以用圆括号括起来的子查询，Spark会为子查询附一个别名，例如SELECT FROM (<user_specified_query>) spark_gen_alias。不要同时指定dbtable和query，query和partitionColumn
driver	连接URL所使用的JDBC驱动
partitionColumn,lowerBound,upperBound	必须一块设置，而且还得带上numPartitions。他们说明了从多个worker上并行读取数据的方式。
numPartitions	并行读写表时最大的分区数。也说明最大的并发JDBC连接数。
queryTimeout	驱动系欸但等待Statement对象执行的秒数
fetchsize	JDBC每round trip取回数据的大小即行数。
batchsize	决定JDBC每round trip插入多少行，批量插入提高效率
isolationLevel	事务隔离级别，包括NONE,READ_COMMITTED,READ_UNCOMMITTED,REPEATABLE_READ,SERIALIZABLE
sessionInitStatement	在远程数据库会话建立之后开始读数据之前该参数会执行一句SQL来初始化会话。
truncate	当使用SaveMode.Overwrite时，该参数使Spark截断现存表而非直接丢弃再重建。
cascadeTruncate
createTableOptions
createTableColumnTypes
customSchema
pushDownPredicate

# Note: JDBC loading and saving can be achieved via either the load/save or jdbc methods
# Loading data from a JDBC source
jdbcDF = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:postgresql:dbserver") \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .load()

jdbcDF2 = spark.read \
    .jdbc("jdbc:postgresql:dbserver", "schema.tablename",
          properties={"user": "username", "password": "password"})

# Specifying dataframe column data types on read
jdbcDF3 = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:postgresql:dbserver") \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .option("customSchema", "id DECIMAL(38, 0), name STRING") \
    .load()

# 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",
          properties={"user": "username", "password": "password"})

# Specifying create table column data types on write
jdbcDF.write \
    .option("createTableColumnTypes", "name CHAR(64), comments VARCHAR(1024)") \
    .jdbc("jdbc:postgresql:dbserver", "schema.tablename",
          properties={"user": "username", "password": "password"})

问题

• 在所有执行器和客户端会话中JDBC driver必须对原生类加载器可见。因为Java 的DriverManager类有安全检查，导致当打开连接时那些对原生类加载器不可见的驱动被忽略。简单的方式是在所有worker上修改compute_classpath.sh来包含驱动JAR
• 某些数据库，像H2这种把所有列名转成了大写，所以还得在Spark SQL中用大写来引用。
• 用户可以在data source 参数中指定具体供应商JDBC连接属性。

性能调优

对于某些应用，通过缓存数据在内存中，或打开一些实验性质的参数选项可能会提高性能。

内存中缓存数据

通过调用spark.catalog.cacheTable(“tableName”)或dataFrame.cache()可以让Spark SQL在内存中以列格式缓存表。Spark SQL仅扫描需要的列且自动地打开压缩来减小内存消耗以及GC压力。调用spark.catalog.uncacheTable(“tableName”)来删除缓存。
使用SparkSession的setConfig()方法或运行SQL命令SET key=value来配置内存中缓存参数

属性名	默认值	说明
spark.sql.inMemoryColumnarStorage.compressed	true	压缩数据
spark.sql.inMemoryColumnarStorage.batchSize	10000	批次列缓存的大小。越大则内存利用率和压缩更好，但是更有可能导致OOM

其他配置参数

下面的参数控制查询执行的性能。未来有可能会删除掉这些配置因为很多优化会自动化。

属性名	默认值	说明
spark.sql.files.maxPartitionBytes	128MB	读取文件时单个分区最大字节数
spark.sql.files.openCostInBytes	4MB	打开一个文件的代价，由同时能够扫描的字节数而计算。
spark.sql.broadcastTimeout	300	广播join时广播的等待时间
spark.sql.autoBroadcastJoinThreshold	10MB	执行join时想把表广播到每台worker上时最大的表的大小。-1代表不用广播手段。
spark.sql.shuffle.partitions	200	配置当shuffle数据来join或聚合时使用的分区数

用于SQL查询的广播方法

BROADCAST使得Spark在进行表与表或视图之间join时将每个具体的表进行广播的方式。当使用join方法时优先考虑broadcast hash join，即使通信量优于配置spark.sql.autoBroadcastJoinThrehold。join的两边都指定之后Spark会广播其中一个来降低通信量。Spark不保证所有情况下都选择BHJ因为有的情况不支持。when the broadcast nested loop join is selected,we still respect the hint

from pyspark.sql.functions import broadcast
broadcast(spark.table("src")).join(spark.table("records"), "key").show()

分布式SQL引擎

Spark SQL也可以使用其JDBC/ODBC或命令行接口执行分布式查询引擎。这种情况下终端用户或应用可以直接与Spark SQL交互运行SQL查询不用写代码

运行Thrift JDBC/ODBC服务器

Thrift JDBC/ODBC服务器由对应的Hive1.2.1的HiveServer2实现。可以进行测试。
在Spark目录中运行该命令启动JDBC/ODBC服务器

./sbin/start-thriftserver.sh