SQL、Pandas和Spark:常用数据查询操作对比

简介: 当今信息时代,数据堪称是最宝贵的资源。沿承系列文章,本文对SQL、Pandas和Spark这3个常用的数据处理工具进行对比,主要围绕数据查询的主要操作展开。

640.png


本文首先介绍SQL查询操作的一般流程,对标SQL查询语句的各个关键字,重点针对Pandas和Spark进行介绍,主要包括10个常用算子操作。


01 SQL标准查询


谈到数据,必会提及数据库;而提及数据库,则一般指代关系型数据库(R DB),操作关系型数据库的语言则是SQL(Structured Query Language)。SQL本质上仍然属于一种编程语言,并且有着相当悠久的历史,不过其语法特性却几乎没怎么变更过,从某种意义上讲这也体现了SQL语言的过人之处。


640.png


在最新TIOBE排行榜中,SQL位居第10位


一般而言,一句标准的SQL语句按照书写顺序通常含有如下关键词:

  • select:指定查询字段
  • distinct:对查询结果字段进行去重
  • from:明确查询的数据库和表
  • join on:指定查询数据源自多表连接及条件
  • where:设置查询结果过滤条件
  • group by:设置分组聚合统计的字段
  • having:依据聚合统计后的字段进一步过滤
  • order by:设置返回结果排序依据
  • limit:限定返回结果条数


这是一条SQL查询语句中所能涉及的主要关键字,经过解析器和优化器之后,最后的执行过程则又与之差别很大,执行顺序如下:

  • from:首先找到待查询的表
  • join on:如果目标数据表不止一个,则对多表建立连接关系
  • where:根据查询条件过滤数据记录
  • group by:对过滤结果进行分组聚合
  • having:对分组聚合结果进行二次过滤
  • select:对二次过滤结果抽取目标字段
  • distinct:根据条件进行去重处理
  • order by:对去重结果进行排序
  • limit:仅返回排序后的指定条数记录

曾经,个人一度好奇为何不将SQL语句的书写顺序调整为与执行顺序一致,那样更易于理解其中的一些技术原理,但查询资料未果后,就放弃了……


当然,本文的目的不是介绍SQL查询的执行原理或者优化技巧,而仅仅是对标SQL查询的几个关键字,重点讲解在Pandas和Spark中的实现。


02 Pandas和Spark实现SQL对应操作


1)from。Pandas和Spark中无需from,执行df.xxx操作的过程本身就蕴含着from的含义。


2)join on。join on在SQL多表查询中是很重要的一类操作,常用的连接方式有inner join、left join、right join、outer join以及cross join五种,在Pandas和Spark中也都有相应关键字。


Pandas:Pandas实现join操作有两个主要的API:mergejoin。其中merge是Pandas的顶层接口(即可直接调用pd.merge方法),也是DataFrame的API,支持丰富的参数设置,主要介绍如下:


def merge(
    left,  # 左表
    right,  # 右表
    how: str = "inner",  # 默认连接方式:inner
    on=None,  # SQL中on连接一段,要求左表和右表中 公共字段
    left_on=None,  # 设置左表连接字段
    right_on=None,  # 设置右表连接字段
    left_index: bool = False,  # 利用左表索引作为连接字段
    right_index: bool = False,  # 利用右表索引作为连接字段
    sort: bool = False,  # join结果排序
    suffixes=("_x", "_y"),  # 非连接字段有重名时,可s何止后缀
    copy: bool = True, 
    indicator: bool = False,
    validate=None,
) -> "DataFrame":


上述参数中,可以设置on连接条件的方式主要有3种:即若连接字段为两表共有字段,则可直接用on设置;否则可分别通过left_on和right_on设置;当一个表的连接字段是索引时,可设置left_index为True。


与merge操作类似,join可看做是merge的一个简化版本,默认以索引作为连接字段,且仅可通过DataFrame来调用,不是Pandas的顶级接口(即不存在pd.join方法)。


另外,concat也可通过设置axis=1参数实现横向两表的横向拼接,但更常用于纵向的union操作。


Spark:相较于Pandas中有多种实现两个DataFrame连接的方式,Spark中接口则要单一许多,仅有join一个关键字,但也实现了多种重载方法,主要有如下3种用法:


// 1、两个DataFrame有公共字段,且连接条件只有1个,直接传入连接列名
df1.join(df2, "col")
// 2、有多个字段,可通过Seq传入多个字段
df1.join(df2, Seq("col1", "col2")
// 3、两个DataFrame中连接字段不同名,此时需传入判断连接条件
df1.join(df2, df1("col1")===df2("col2"))
// 注意,上述连接条件中,等于用===,不等于用=!=


3)where。数据过滤在所有数据处理流程中都是重要的一环,在SQL中用关键字where实现,在Pandas和Spark中也有相应的接口。


Pandas。Pandas中实现数据过滤的方法有多种,个人常用的主要是如下3类:

  • 通过loc定位操作符+逻辑判断条件实现筛选过滤。loc是用于数据读取的方法,由于其也支持传入逻辑判断条件,所以自然也可用于实现数据过滤,这也是日常使用中最为频繁一种;
  • 通过query接口实现,提起query,首先可能想到的便是SQL中Q,实际上pandas中的query实现的正是对标SQL中的where语法,在实现链式筛选查询中非常好用,具体可参考Pandas用了一年,这3个函数是我的最爱……
  • where语句,Pandas以API丰富而著称,所以自然是不会放过where关键字的,不过遗憾的是Pandas中的where和Numpy中的where一样,都是用于对所有列的所有元素执行相同的逻辑判断,可定制性较差。


Spark。Spark中实现数据过滤的接口更为单一,有wherefilter两个关键字,且二者的底层实现是一致的,所以实际上就只有一种用法。但在具体使用中,where也支持两种语法形式,一种是以字符串形式传入一个类SQL的条件表达式,类似于Pandas中query;另一种是显示的以各列对象执行逻辑判断,得到一组布尔结果,类似于Pandas中loc操作。


4)group by。group by关键字用于分组聚合,实际上包括了分组和聚合两个阶段,由于这一操作属于比较规范化的操作,所以Pandas和Spark中也都提供了同名关键字,不同的是group by之后所接的操作算子不尽相同。


Pandas:Pandas中groupby操作,后面可接多个关键字,常用的其实包括如下4类:

Spark:Spark中的groupBy操作,常用的包括如下3类:

  • 直接接聚合函数,如sum、avg等;
  • agg函数,并传入多个聚合算子,与Pandas中类似;
  • pivot函数,实现特定的数据透视表功能。


5)having。在SQL中,having用于实现对聚合统计后的结果进行过滤筛选,与where的核心区别在于过滤所用的条件是聚合前字段还是聚合后字段。而这在Pandas和Spark中并不存在这一区别,所以与where实现一致。


6)select。选择特定查询结果,详见Pandas vs Spark:获取指定列的N种方式


7)distinct。distinct在SQL中用于对查询结果去重,在Pandas和Spark中,实现这一操作的函数均为drop_duplicates/dropDuplicates


8)order by。order by用于根据指定字段排序,在Pandas和Spark中的实现分别如下:


Pandas:sort_indexsort_values,其中前者根据索引排序,后者根据传入的列名字段排序,可通过传入ascending参数控制是升序还是降序。


Spark:orderBysort,二者也是相同的底层实现,功能完全一致。也是通过传入的字段进行排序,可分别配合asc和desc两个函数实现升序和降序。


// 1、指定列+desc
df.orderBy(df("col").desc)
// 2、desc函数加指定列
df.orderBy(desc("col"))


9)limit。limit关键字用于限制返回结果条数,这是一个功能相对单一的操作,二者的实现分别如下:

Pandas:可分别通过head关键字和iloc访问符来提取指定条数的结果;

Spark:直接内置了limit算子,用法更接近SQL中的limit关键字。


10)Union。SQL中还有另一个常用查询关键字Union,在Pandas和Spark中也有相应实现:

Pandas:concatappend,其中concat是Pandas 中顶层方法,可用于两个DataFrame纵向拼接,要求列名对齐,而append则相当于一个精简的concat实现,用于在一个DataFrame尾部追加另一个DataFrame;


Spark:Spark中直接模仿SQL语法,分别提供了unionunionAll两个算子实现两个DataFrame的纵向拼接,且含义与SQL中完全类似。


03 小节


对标SQL标准查询语句中的常用关键字,重点对Pandas和Spark中相应操作进行了介绍,总体来看,两个计算框架均可实现SQL中的所有操作,但Pandas实现的接口更为丰富,传参更为灵活;而Spark则接口更为统一,但一般也支持多种形式的方法重载。另外,Spark中的算子命名与SQL更为贴近,语法习惯也与其极为相似,这对于具有扎实SQL基础的人快速学习Spark来说会更加容易。


640.png



目录
相关文章
|
2月前
|
SQL 数据管理 数据库
SQL语句实例教程:掌握数据查询、更新与管理的关键技巧
SQL(Structured Query Language,结构化查询语言)是数据库管理和操作的核心工具
|
29天前
|
SQL JSON 分布式计算
【赵渝强老师】Spark SQL的数据模型:DataFrame
本文介绍了在Spark SQL中创建DataFrame的三种方法。首先,通过定义case class来创建表结构,然后将CSV文件读入RDD并关联Schema生成DataFrame。其次,使用StructType定义表结构,同样将CSV文件读入RDD并转换为Row对象后创建DataFrame。最后,直接加载带有格式的数据文件(如JSON),通过读取文件内容直接创建DataFrame。每种方法都包含详细的代码示例和解释。
|
29天前
|
SQL 监控 安全
员工上网行为监控软件:SQL 在数据查询监控中的应用解析
在数字化办公环境中,员工上网行为监控软件对企业网络安全和管理至关重要。通过 SQL 查询和分析数据库中的数据,企业可以精准了解员工的上网行为,包括基础查询、复杂条件查询、数据统计与分析等,从而提高网络管理和安全防护的效率。
28 0
|
2月前
|
SQL 分布式计算 大数据
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(一)
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(一)
57 0
|
2月前
|
SQL 分布式计算 算法
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(二)
大数据-97 Spark 集群 SparkSQL 原理详细解析 Broadcast Shuffle SQL解析过程(二)
86 0
|
2月前
|
SQL 分布式计算 Java
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
50 0
|
2月前
|
SQL 分布式计算 大数据
大数据-94 Spark 集群 SQL DataFrame & DataSet & RDD 创建与相互转换 SparkSQL
大数据-94 Spark 集群 SQL DataFrame & DataSet & RDD 创建与相互转换 SparkSQL
68 0
|
2月前
|
SQL 存储 分布式计算
大数据-93 Spark 集群 Spark SQL 概述 基本概念 SparkSQL对比 架构 抽象
大数据-93 Spark 集群 Spark SQL 概述 基本概念 SparkSQL对比 架构 抽象
45 0
|
4月前
|
SQL 存储 分布式计算
|
4月前
|
SQL 关系型数据库 数据挖掘
SQL 基础入门简直太重要啦!从零开始,带你轻松掌握数据查询与操作,开启数据世界大门!
【8月更文挑战第31天】在数字化时代,数据无处不在,而 SQL(Structured Query Language)则是开启数据宝藏的关键钥匙。无论你是编程新手还是数据处理爱好者,掌握 SQL 都能帮助你轻松提取和分析信息。SQL 简洁而强大,像一位魔法师,能从庞大数据库中迅速找到所需数据。从查询、条件筛选到排序、分组,SQL 功能多样,还能插入、更新和删除数据,助你在数据海洋中畅游无阻。
47 0