Spark SQL

介绍

结构化数据是指任何有结构信息的数据，所谓结构信息，就是每条记录共用的已知的字段集合。。 当数据符合这样的条件时， Spark SQL 就会使得针对这些数据的读取和查询变得更加简单高效。

Spark SQL 提供了以下三大功能：
1. Spark SQL 可以从各种结构化数据源（例如 JSON、 Hive、 Parquet 等）中读取数据。
2. Spark SQL 不仅支持在 Spark 程序内使用 SQL 语句进行数据查询，也支持从类似商业智能软件 Tableau 这样的外部工具中通过标准数据库连接器（JDBC/ODBC）连接 Spark SQL 进行查询。
3. Spark SQL 支持 SQL 与常规的 Python/Java/Scala 代码高度整合， 包括连接 RDD 与 SQL 表、公开的自定义 SQL 函数接口等。

为了实现这些功能， Spark SQL 提供了一种特殊的 RDD，叫作 SchemaRDD。SchemaRDD 是存放 Row 对象的 RDD，每个 Row 对象代表一行记录，SchemaRDD 还包含记录的结构信息（即数据字段）。

注：将来SchemaRdd会改成DataFrame。

SPARK SQL与Hive

当使用 Spark SQL 进行编程时，根据是否使用 Hive 支持有两个不同的入口。
1. HiveContext：它可以提供 HiveQL 以及其他依赖于 Hive 的功能的支持
2. SQLContext：它是 Spark SQL 功能的一个子集，子集中去掉了需要依赖于 Hive 的功能。

若要把 Spark SQL 连接到一个部署好的 Hive 上，你必须把 hive-site.xml 复制到Spark 的配置文件目录中（$SPARK_HOME/conf）。

如果没有部署好 Hive， Spark SQL 也可以运行，它会在当前的工作目录中创建出自己的Hive 元数据仓库， 叫作 metastore_db。

查询json数据

demo.jl

{"username": "aa", "subject_id": "1", "segment_id": "1"}{"username": "bb", "subject_id": "2", "segment_id": "2"}{"username": "zhexiao", "subject_id": "3", "segment_id": "3"}

sql查询

hive_ctx = HiveContext(sc)json_file = hive_ctx.read.json('demo.jl')# 注册输入的SchemaRDDjson_file.registerTempTable('demojson')name = hive_ctx.sql("""select * from demojson where username='zhexiao'""")print(name.collect()[0].username)

读取数据和执行查询都会返回 SchemaRDD。 SchemaRDD 和传统数据库中的表的概念类似。从内部机理来看， SchemaRDD 是一个由 Row 对象组成的 RDD，附带包含每列数据类型的结构信息。

如果需要对SchemaRDD 进行查询，就需要先把SchemaRDD 注册成为一个临时表。通过调用registerTempTable() 方法完成。

缓存

为了确保使用更节约内存的表示方式进行缓存，应当使用专门的hiveCtx.cacheTable(“tableName”) 方法。

你也可以使用 HiveQL/SQL 语句来缓存表。只需要运行 CACHE TABLE tableName 或UNCACHE TABLE tableName 来缓存表或者删除已有的缓存即可。

读取和存储数据

Spark Sql可以轻松的从Hive 表、 JSON 和 Parquet 文件中读取数据，当使用SQL做查询的时候， Spark SQL 可以智能地只扫描这些用到的字段，而不是像 SparkContext.hadoopFile那样扫描全部数据。

我们也可以在程序中通过指定结构信息，将常规的 RDD 转化为SchemaRDD。还可以将这些 RDD 和来自其他 Spark SQL 数据源的SchemaRDD 进行连接操作。

创建SchemaRDD

d1 = sc.parallelize([Row(username="yyy", age=24)])d1_schemardd = hive_ctx.createDataFrame(d1)d1_schemardd.registerTempTable('mytable')sql_dt = hive_ctx.sql("""select * from mytable where username='yyy'""")print(sql_dt.collect()[0].age)

Spark Sql与JDBC服务器

Spark SQL 也提供 JDBC 连接支持，这对于让商业智能（BI）工具连接到 Spark 集群上以及在多用户间共享一个集群的场景都非常有用。

Spark SQL 的 JDBC 服务器与 Hive 中的 HiveServer2 相一致。由于使用了 Thrift 通信协议，它也被称为“Thrift server”。

服务器可以通过 Spark 目录中的 sbin/start-thriftserver.sh 启动，这个脚本接受的参数选项大多与 spark-submit 相同。默认情况下，服务器会在localhost:10000上监听。

spark自带 beeline 可以用来测试连接是否通了：

> beeline -u jdbc:hive2://localhost:10000> show tables;

beeline

在 Beeline 客户端中，你可以使用标准的 HiveQL 命令来创建、列举以及查询数据表。

创建表：

> CREATE TABLE IF NOT EXISTS mytable (key INT, value STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';> show tables;> select * from mytable;

插入数据：
demo.txt

13,xiaozhe25,xx23,zzz          

把demo.txt的数据加载进去：

> LOAD DATA LOCAL INPATH '/opt/spark/demo.txt' INTO TABLE mytable;> select * from mytable;

如果你想要缓存数据表，使用 CACHE TABLE tableName 语句。缓存之后你可以使用 UNCACHE TABLE tableName 命令取消对表的缓存。 需要注意的是，之前也提到过，缓存的表会在这个 JDBC 服务器上的所有客户端之间共享。

长生命周期的表与查询

使用 Spark SQL 的 JDBC 服务器的优点之一就是我们可以在多个不同程序之间共享缓存下来的数据表。 JDBC Thrift 服务器是一个单驱动器程序，你只需要注册该数据表并对其运行 CACHE 命令，就可以利用缓存了。

用户自定义函数

用户自定义函数，也叫 UDF，可以让我们使用 Python/Java/Scala 注册自定义函数，并在SQL中调用。这种方法很常用，通常用来给机构内的 SQL 用户们提供高级功能支持，这样这些用户就可以直接调用注册的函数而无需自己去通过编程来实现了。

Spark SQL UDF

我们可以使用 Spark 支持的编程语言编写好函数，然后通过 Spark SQL 内建的方法传递进来，非常便捷地注册我们自己的 UDF。

在 Python 和 Java 中， 还需要列出的 SchemaRDD 对应的类型来指定返回值类型。

from pyspark.sql.types import IntegerTypehive_ctx.registerFunction("strLenPython", lambda x: len(x), IntegerType())name = hive_ctx.sql("""select strLenPython('username')  from demojson where username='zhexiao'""")print(name.collect())

Hive UDF

标准的 Hive UDF 已经自动包含在了 Spark SQL 中。如果需要支持自定义的 Hive UDF，我们要确保该 UDF 所在的 JAR 包已经包含在了应用中。如果使用的是 JDBC 服务器，也可以使用–jars 命令行标记来添加 JAR。

Spark SQL性能

Spark SQL 提供的高级查询语言及附加的类型信息可以使 Spark SQL 数据查询更加高效。

Spark SQL 不仅是给熟悉 SQL 的用户使用的。 Spark SQL 使有条件的聚合操作变得非常容易，比如对多个列进行求值。

> SELECT SUM(user.favouritesCount), SUM(retweetCount), user.id FROM tweets GROUP BY user.id

谓词下推可以让 Spark SQL 将查询中的一些部分工作“下移”到查询引擎上。在 Spark SQL中，如果底层的数据存储支持读取键值在一个范围内的记录，或是其他某些限制条件， Spark SQL 就可以把查询语句中的筛选限制条件推到数据存储层，而不需要将整个数据读入到数据库，大大减少需要读取的数据。

Spark SQL中的性能选项

设置方法有2种：
1. 通过beeline，使用set设置。

> beeline> set spark.sql.codegen=true;

1. 在 Spark 配置中设置这些 Spark 属性

conf.set("spark.sql.codegen", "true")

一些选项的配置需要给予特别的考量：
spark.sql.codegen：这个选项可以让Spark SQL 把每条查询语句在运行前编译为 Java 二进制代码。由于生成了专门运行指定查询的代码， codegen 可以让大型查询或者频繁重复的查询明显变快。然而，在运行特别快（1 ～ 2 秒）的即时查询语句时， codegen 有可能会增加额外开销，因为 codegen 需要让每条查询走一遍编译的过程。

spark.sql.inMemoryColumnarStorage.batchSize：在缓存 SchemaRDD 时， Spark SQL 会按照这个选项制定的大小（默认值是 1000）把记录分组，然后分批压缩。如果你表中的记录比较大（包含数百个字段或者包含像网页这样非常大的字符串字段），你就可能需要调低批处理大小来避免内存不够（OOM） 的错误。