Spark权威指南(中文版)----第10章 Spark SQL

Spark The Definitive Guide(Spark权威指南) 中文版。本书详细介绍了Spark2.x版本的各个模块，目前市面上最好的Spark2.x学习书籍！！！

10.5.   如何运行Spark SQL查询

Spark提供了几个接口来执行SQL查询。

10.5.1.   Spark SQL CLI

Spark SQLCLI是一个方便的工具，您可以使用它从命令行以本地模式进行基本的Spark SQL查询。注意，Spark SQL CLI不能与Thrift JDBC服务器通信。要启动Spark SQL CLI，请在Spark目录中运行以下命令：

./bin/spark-sql

您可以通过放置hive-site.xml, core-site.xml, and hdfs-site.xml文件在conf文件夹下，来配置Hive。对于所有可用选项的完整列表，可以运行如下命令查看

./bin/spark-sql --help.
10.5.2.   Spark的编程SQL接口

除了设置服务器外，还可以通过Spark的任何语言api以特定的方式执行SQL。您可以通过SparkSession对象上的sql方法来实现这一点。这将返回一个DataFrame，我们将在本章后面看到。例如，在Python或Scala中，我们可以运行以下代码:

spark.sql("SELECT 1 + 1").show()

代码中spark.sql("SELECT 1 + 1")会返回一个DataFrame，用于后续的编程计算。这里的sql方法是一个transformation，不会立即执行，这是一个非常强大的接口，因为有些转换在SQL代码中比在DataFrames中更容易表达。通过将多行字符串传递到函数中，可以非常简单地表达多行查询语句。例如，您可以在Python或Scala中执行如下代码:

图片

更强大的是，只要你觉得有必要，可以在SQL和DataFrames之间完全互操作。例如，您可以创建一个DataFrame，使用SQL操作它，然后再次作为DataFrame操作它。这是一个强大的抽象，你可能会发现自己经常使用:

图片

10.5.3.   SparkSQL Thrift JDBC/ODBC Server

Spark提供了一个Java数据库连接(JDBC)接口，程序可以通过该接口连接到Spark Driver程序，从而执行Spark SQL查询。一个常见的用例可能是业务分析师将Tableau之类的业务智能软件连接到Spark。这里实现的Thrift JDBC/Open Database Connectivity (ODBC)服务器对应于Hive 1.2.1中的HiveServer2。您可以使用Spark或Hive1.2.1附带的beeline脚本来测试JDBC服务器。要启动JDBC/ODBC服务器，请在Spark目录中运行以下命令:

./sbin/start-thriftserver.sh

此脚本接受所有bin/spark-submit命令行选项。要查看配置此thrift服务器的所有可用选项，可以运行如下命令：

./sbin/start-thriftserver.sh --help

默认情况下，服务器监听localhost:10000。您可以通过环境变量或系统属性覆盖它。对于环境配置，请使用以下命令:

图片

对于系统属性：

图片

然后，您可以通过运行以下命令来测试这个连接:

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

Beeline会询问您的用户名和密码。在非安全模式下，只需键入操作系统用户名和一个空白密码。对于安全模式，请遵循beeline文档(https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients)中给出的说明。

10.6.   Catalog

Spark SQL中的最高抽象是Catalog。Catalog是存储关于表中存储的数据以及其他有用的东西(如数据库、表、函数和视图)的元数据的抽象。

Catalog位于org.apache.spark.sql.catalog.Catalog包中，包含许多有用的函数，用于列出表、数据库和函数。我们将很快讨论所有这些事情。它对用户来说非常容易理解，因此我们将省略这里的代码示例，但它实际上只是另一个SparkSQL的编程接口。本章只显示正在执行的SQL;因此，如果您正在使用编程接口，请记住您需要将所有内容封装在一个spark.sql()函数中，执行相关代码。

10.7.   Tables

要使用Spark SQL做任何有用的事情，首先需要定义表。表在逻辑上等同于DataFrame，因为它们是运行命令的数据结构。我们可以连接表，过滤它们，聚合它们，并执行我们在前几章中看到的不同操作。表和DataFrame之间的核心区别是:在编程语言的范围内定义DataFrame，而在数据库中定义表。这意味着，当您创建一个表(假设您从未更改过数据库)时，它将属于默认数据库。我们将在本章后面更全面地讨论数据库。需要注意的重要一点是，在Spark 2中。表总是包含数据。没有临时表的概念，只有不包含数据的视图。这一点很重要，因为如果要删除表，那么在删除表时可能会冒数据丢失的风险。

10.7.1.   Spark-Managed Tables(Spark托管表)

一个重要的注意事项是托管表与非托管表的概念。表存储两类重要的信息：表内数据和表的有关数据，也就是元数据。您可以让Spark管理一组文件和数据的元数据。当您从磁盘上的文件定义表时，您正在定义一个非托管表。当您在DataFrame上使用saveAsTable时，您正在创建一个托管表，Spark将跟踪该表所有相关信息。这将读取您的表并将其以Spark格式写入新位置。您可以在新的解释计划中看到这一点。在explain计划中，您还将注意到这将写入到默认的Hive warehouse位置。在创建SparkSession时，可以通过设置spark.sql.warehouse.dir来设置它到选择的目录。默认情况下，Spark将这个设置为/user/hive/warehouse:注意，结果中列出了一个数据库。Spark也有数据库,我们将在本章后面讨论,但现在你应该记住,您还可以看到表在一个特定的数据库通过show tables IN databaseName查询，databaseName代表你想查询的数据库名称,。如果您在一个新的集群或本地模式上运行，则应该返回零结果。

 

10.7.2.   Creating Tables创建表

您可以从各种源创建表。Spark非常独特的一点是能够在SQL中重用整个数据源API。这意味着您不需要定义一个表，然后将数据加载到其中;Spark允许您动态创建一个。您甚至可以在读取文件时指定各种复杂的选项。例如，这里有一个简单的方法来读取我们在前几章中处理过的航班飞行数据:

图片

USING 和 STORED AS

前面示例中的USING语法规范非常重要。如果不指定格式，Spark将默认为Hive SerDe配置。这对将来的读取和写入有性能影响，因为Hive SerDes比Spark的本地序列化慢得多。Hive用户还可以使用STORED AS语法来指定这应该是一个Hive表。

你也可以在表格的某些列添加注释，这可以帮助其他开发人员理解表格中的数据:

图片

也可以从查询中创建一个表:

CREATE TABLE flights_from_select USING parquet AS SELECT * FROM flights

此外，只有在表当前不存在时，才可以指定创建表:

注意

在本例中，我们创建了一个与hive兼容的表，因为我们没有通过USING显式指定格式。我们还可以这样做:

CREATE TABLE IF NOTEXISTS flights_from_select AS SELECT * FROM flights

最后，你可以通过编写一个分区的数据集来控制数据的布局，正如我们在第9章中看到的:

CREATE TABLE partitioned_flights USING parquet PARTITIONED BY (DEST_COUNTRY_NAME)AS SELECT DEST_COUNTRY_NAME, ORIGIN_COUNTRY_NAME, count FROM flights LIMIT 5

这些表甚至可以通过会话在Spark中使用;Spark中目前不存在临时表。您必须创建一个临时视图，本章稍后将对此进行演示。

 

10.7.3.   创建外部表

如本章开头所述，Hive是最早的大数据SQL系统之一，Spark SQL与Hive SQL(HiveQL)语句完全兼容。您可能遇到的一个用例是，将遗留的Hive语句移植到SQL中。幸运的是，在大多数情况下，您可以直接将Hive语句复制粘贴到Spark SQL中。例如，在下面的示例中，我们创建了一个非托管表。Spark将管理表的元数据;然而，这些文件根本不是由Spark管理的。您可以使用create EXTERNAL table语句创建这个表。

您可以通过运行以下命令来使用任何已经定义的文件创建外部表:

图片

您还可以从select子句创建一个外部表:

图片

10.7.4.    插入数据到表

插入遵循标准的SQL语法:

INSERT INTO flights_from_select SELECT DEST_COUNTRY_NAME, ORIGIN_COUNTRY_NAME, count FROM flights LIMIT 20

如果只希望写入某个分区，可以选择提供分区规范。注意，写操作也会遵循分区模式(这可能导致上面的查询运行得非常慢);但是，它只会添加额外的文件到目的分区:

INSERT INTO partitioned_flights PARTITION (DEST_COUNTRY_NAME="UNITED STATES") SELECT count, ORIGIN_COUNTRY_NAME FROM flights WHERE DEST_COUNTRY_NAME='UNITED STATES' LIMIT 12
10.7.5.   查看表元数据

我们在前面看到，可以在创建表时添加注释。你可以通过描述表元数据来查看，它会显示相关的注释:

DESCRIBETABLE flights_csv

 您还可以使用以下方法查看数据的分区方案(但是，请注意，这只适用于分区表):

 

SHOWPARTITIONS partitioned_flights
10.7.6.   刷新表元数据

维护表元数据是确保从最新数据集读取数据的一项重要任务。有两个命令用于刷新表元数据。REFRESH TABLE刷新与该表关联的所有缓存条目(本质上是文件)。如果该表以前缓存过，那么下次扫描时它将被延迟缓存:

另一个相关命令是REPAIR TABLE，它刷新catalog中为给定表维护的分区。这个命令的重点是收集新的分区信息，例如手工写一个新的分区，需要相应地修复表:

MSCKREPAIR TABLE partitioned_flights
10.7.7.   删除表
您不能delete表:您只能“drop”它们。可以使用drop关键字删除表。如果删除托管表(例如flights_csv)，数据和表定义都将被删除:
DROP TABLE flights_csv;

警告

删除表会删除表中的数据，因此在执行此操作时需要非常小心。

如果您试图删除不存在的表，您将收到一个错误。若要仅删除已经存在的表，请使用DROP TABLE IF EXISTS：

DROP TABLE IF EXISTS flights_csv;
10.7.8.   删除非托管表
如果您正在删除一个非托管表(例如，hive_flights)，则不会删除任何数据，但您将不再能够通过表名引用该数据。
10.7.9.   缓存表
就像DataFrames一样，您可以缓存和清除缓存表。您只需使用以下语法指定要使用哪个表:
CACHE TABLE flightsUNCACHE TABLE FLIGHTS

10.8.    视图

创建了表之后，还可以定义视图。视图在现有表的之上定义一组转换(基本上就是保存的查询计划)，这对于组织或重用查询逻辑非常方便。Spark有几个不同的视图概念。视图可以是全局级别的，可以设置为数据库级别，也可以是会话级别。
10.8.1.   创建视图
对于最终用户来说，视图显示为表，只是在查询时对源数据执行转换，而不是将所有源数据重写到新的位置。这可能是一个filter、select，也可能是一个更大的group BY或ROLLUP。例如，在下面的例子中，我们创建了一个目的地为美国的视图，以便只看到这些航班:
CREATE VIEW just_usa_view AS SELECT * FROM flights WHERE dest_country_name = 'United States'

与表一样，您可以创建临时视图，这些视图只在当前会话期间可用，并且不注册到数据库:

CREATE TEMP VIEW just_usa_view_temp AS SELECT * FROM flights WHERE dest_country_name = 'United States'
或者，它可以是一个全局临时视图。全局临时视图的解析与数据库无关，可以在整个Spark应用程序中查看，但在会话结束时将它们删除:
CREATE GLOBAL TEMP VIEW just_usa_global_view_temp AS  SELECT * FROM flights WHERE dest_country_name = 'United States'SHOW TABLES
还可以使用下面示例中所示的关键字指定，如果一个视图已经存在，则希望覆盖该视图。我们可以覆盖临时视图和常规视图:
CREATE OR REPLACE TEMP VIEW just_usa_view_temp AS SELECT * FROM flights WHERE dest_country_name = 'United States'

现在您可以像查询另一个表一样查询这个视图:

SELECT * FROM just_usa_view_temp
视图实际上是一个转换，Spark只在查询时执行它。这意味着它只会在您实际查询表之后应用该过滤器(而不是更早)。实际上，视图相当于从现有DataFrame创建一个新的DataFrame。实际上，您可以通过比较Spark DataFrames和Spark SQL生成的查询计划来了解这一点。在DataFrames中，我们可以这样写:

图片

在SQL中，我们会这样写(从我们的视图查询):

图片

或者,相当于:

图片

由于这个事实，您应该能够轻松地在DataFrames或sql上编写逻辑——无论哪种逻辑对您来说都是最轻松和最容易维护的。

10.8.2.   删除视图

可以像删除表一样删除视图;只需指定要删除的是视图而不是表。删除视图和删除表的主要区别在于，对于视图，不删除底层数据，只删除视图定义本身:

DROPVIEWIFEXISTSjust_usa_view;

 

10.9.    数据库

数据库是组织表的工具。如前所述，如果您不定义一个，Spark将使用default数据库。在Spark中运行的任何SQL语句(包括DataFrame命令)都是在数据库上下文中执行的。这意味着，如果更改数据库，任何用户定义的表都将保留在以前的数据库中，并且需要以不同的方式查询。

警告

这可能会引起混淆，尤其是当您与同事共享相同的上下文或会话时，所以一定要适当地设置数据库。

你可以使用以下命令查看所有数据库:

SHOW DATABASES
10.9.1.   创建数据库
创建数据库的模式与您在本章前面看到的相同;但是，这里使用CREATE DATABASE关键字: 
CREATEDATABASEsome_db
        10.9.2.   设置数据库
 
您可能想要设置一个数据库来执行某个查询。要做到这一点，使用use关键字后面跟着数据库名称: 
USE some_db
设置此数据库之后，所有查询将尝试将表名解析到此数据库。本来运行良好的查询现在可能会失败或产生不同的结果，因为您在一个不同的数据库:

图片

不过，你可以使用正确的前缀查询不同的数据库:

SELECT * FROM default.flights

您可以通过运行以下命令查看当前使用的数据库:

SELECT current_database()

 

当然，您可以切换回default数据库:

USE default;

        10.9.3.   删除数据库

 
删除或移除数据库同样简单:只需使用DROP DATABASE关键字:
DROP DATABASE IF EXISTS some_db;

Spark中的查询支持以下ANSI SQL要求(这里我们列出了SELECT表达式的布局):

图片

图片

10.10.1.   case…when…then语句
通常，您可能需要有条件地替换SQL查询中的值。你可以用一个case…when…then…风格的声明。这基本上相当于编程式的if语句

图片

10.11.    高级主题

既然我们已经定义了数据的位置以及如何组织它，那么让我们继续查询它。SQL查询是要求运行一些命令集的SQL语句。SQL语句可以定义操作、定义或控制。最常见的例子是操作，这也是本书的重点。
10.11.1.   复杂类型
复杂类型背离了标准SQL，并且是标准SQL中不存在的一个非常强大的特性。理解如何在SQL中正确地操作它们是非常重要的。Spark SQL中有三种核心的复杂类型:struct、list和map。

Struct

struct更类似于map。它们提供了在Spark中创建或查询嵌套数据的方法。要创建一个，你只需要用括号括起一组列(或表达式):

图片

现在，您可以查询这些数据，看看它是什么样子的：

图片

你甚至可以在一个struct中查询单独的列——你所需要做的就是使用点语法:

图片

如果愿意，还可以使用struct的名称从struct中选择所有子列。虽然这些并不是真正的子列，但它提供了一种更简单的方法来考虑它们，因为我们可以用它们做任何我们喜欢的事情，就像它们是一列一样:

图片

List

如果您熟悉编程语言中的list，Spark SQL list会让您感到熟悉。有几种方法可以创建数组或值列表。您可以使用collect_list函数，它创建一个值列表。您还可以使用函数collect_set，它创建一个没有重复值的数组。这两个都是聚合函数，因此只能在聚合中指定:

图片

不过，您也可以在列中手动创建数组，如下所示:

图片

您还可以使用python类数组查询语法按位置查询列表:

图片

您还可以将数组转换回行。你可以通过使用explode函数来做到这一点。为了演示，让我们创建一个新的视图作为我们的聚合:

图片

现在让我们将复杂类型分解为数组中每个值对应的结果中的一行。DEST_COUNTRY_NAME将对数组中的每个值进行复制，执行与原始collect完全相反的操作，并返回到原始DataFrame:

图片

10.11.2.   函数
除了复杂类型之外，Spark SQL还提供了各种复杂的函数。您可以在DataFrames函数引用中找到这些函数中的大多数;但是，也有必要了解如何在SQL中找到这些函数。要查看Spark SQL中的函数列表，可以使用SHOW functions语句:SHOWFUNCTIONS您还可以更具体地指示是否希望查看系统函数(即Spark内置函数)以及用户函数:SHOWSYSTEM FUNCTIONS用户函数是由您或其他共享Spark环境的人定义的。这些都是我们在前几章讨论过的相同的用户定义函数(我们将在本章后面讨论如何创建它们):SHOW USERFUNCTIONS您可以通过传递带有通配符(*)的字符串来过滤所有SHOW命令。在这里，我们可以看到所有以“s”开头的函数:SHOWFUNCTIONS "s*";您可以选择包含LIKE关键字，尽管这不是必需的:SHOWFUNCTIONS LIKE "collect*";尽管列出函数当然很有用，但通常您可能想了解更多关于特定函数本身的信息。为此，使用DESCRIBE关键字，它返回特定函数的文档。

用户自定义函数

正如我们在第3章和第4章中看到的，Spark使您能够定义自己的函数并以分布式方式使用它们。您可以像以前一样定义函数，用您选择的语言编写函数，然后正确地注册它:

图片

还可以通过Hive CREATE TEMPORARY FUNCTION语法注册函数。

10.11.3.子查询
使用子查询，您可以在其他查询中指定查询。这使得您可以在SQL中指定一些复杂的逻辑。在Spark中，有两个基本的子查询。关联子查询使用来自查询外部范围的一些信息来补充子查询中的信息。非关联子查询不包含来自外部范围的信息。每个查询都可以返回一个(标量子查询)或多个值。Spark还支持谓词子查询，允许基于值进行过滤。

谓词不相关子查询

例如，让我们看看谓词子查询。在本例中，它由两个不相关的查询组成。第一个查询仅仅是根据我们拥有的数据获得前五个国家的目的地:

图片

这给了我们以下的结果:

图片

现在，我们把这个子查询放在过滤器内，并检查我们的原始国家是否存在于该列表中:

图片

此查询不相关，因为它不包含来自查询外部范围的任何信息。这是一个可以自己运行的查询。

谓词相关子查询

关联谓词子查询允许您在内部查询中使用来自外部范围的信息。举个例子,如果你想看到你的航班是否会把你从你当前所在国家带回来,你可以通过检查是否有这样航班：以当前所在国家为起飞点，以带回国家为目的地:

图片

EXISTS只检查子查询中的一些存在性，如果有值，返回true。你可以通过把NOT运算符放在它前面来翻转它。这就相当于找到了一架飞往你无法返回目的地的航班!

不相关的标量查询

使用不相关的标量查询，您可以引入一些以前可能没有的补充信息。例如，如果您想从整个counts数据集中包含最大值作为它自己的列，您可以这样做:

图片

10.12.  其他的特性

Spark SQL中有一些特性与本章前面的部分不太匹配，所以我们将不按特定的顺序在这里包含它们。在执行优化或调试SQL代码时，这些可能是相关的。

10.12.1.   配置

有几种Spark SQL应用程序配置，如表10-1所示。您可以在应用程序初始化时或在应用程序执行过程中设置这些参数(就像我们在本书中看到的shuffle分区一样)。

属性名称

默认值

含义

spark.sql.inMemoryColumnarStorage.compressed

true

当设置为true时，Spark SQL会根据数据的统计信息为每一列自动选择压缩编解码器。

spark.sql.inMemoryColumnarStorage.batchSize

10000

控制用于列缓存的批的大小。更大的批处理大小可以提高内存利用率和压缩，但是在缓存数据时存在OutOfMemoryErrors (OOMs)的风险。

spark.sql.files.maxPartitionBytes

134217728 (128 MB)

读取文件时要装入单个分区的最大字节数。

spark.sql.files.openCostInBytes

4194304 (4 MB)

打开一个文件的估计成本，通过同时扫描的字节数来衡量。这用于将多个文件放入一个分区中。最好是高估;这样，具有小文件的分区将比具有大文件的分区(优先调度)更快。

spark.sql.broadcastTimeout

300

广播连接中广播等待超时时间 (以秒为单位)。

spark.sql.autoBroadcastJoinThreshold

10485760 (10 MB)

配置将在执行连接时广播给所有工作节点的表的最大字节大小。可以通过将此值设置为-1禁用广播。注意，目前只支持Hive Metastore表的统计信息，其中运行了命令ANALYZE TABLE COMPUTE STATISTICS noscan

spark.sql.shuffle.partitions

200

配置在为连接或聚合shuffle数据时要使用的分区数。
10.12.2.   在SQL中设置配置值

我们将在第15章讨论配置，但作为预览，值得一提的是如何从SQL设置配置。当然，您只能以这种方式设置Spark SQL配置，但是下面是如何设置shuffle分区:SETspark.sql.shuffle.partitions=20

10.13.    结束语

从本章可以清楚地看到，Spark SQL和DataFrames是非常密切相关的，您应该能够使用本书中几乎所有的示例，只需要稍微调整一下语法。本章说明了与Spark sql相关的更多细节。第11章重点介绍一个新概念:允许类型安全的结构化转换的Datasets。

posted @ 2021-08-19 16:11  bluesky1  阅读(642)  评论(0)  编辑  收藏  举报