Spark SQL 是 Apache Spark 处理结构化数据的模块。
一、初始化 SparkSession
SparkSession 用于创建数据框,将数据框注册为表,执行 SQL 查询,缓存表及读取 Parquet 文件。
from pyspark.sql import SparkSession
spark = SparkSession \
.builder \
.appName("Python Spark SQL basic example") \
.config("spark.some.config.option", "some-value") \
.getOrCreate()
sc = spark.sparkContext
二、创建数据框
1、从 RDD 创建
from pyspark.sql.types import * 包括:
__all__ = [
"DataType", "NullType", "StringType", "BinaryType", "BooleanType", "DateType",
"TimestampType", "DecimalType", "DoubleType", "FloatType", "ByteType", "IntegerType",
"LongType", "ShortType", "ArrayType", "MapType", "StructField", "StructType"]
from pyspark.sql import Row
from pyspark.sql.types import *
from collections import namedtuple
(1) 推断 Schema
lines = sc.textFile("people.txt")
parts = lines.map(lambda l: l.split(","))
people = parts.map(lambda p: Row(name=p[0], age=int(p[1])))
peopledf = spark.createDataFrame(people)
peopledf.show()
+---------+---+
| name|age|
+---------+---+
| Danae| 27|
| Claudia| 26|
|Desdemona| 25|
| Chloe| 30|
| Felicia| 25|
+---------+---+
(2)指定 Schema
people = parts.map(lambda p: Row(name=p[0], age=int(p[1].strip())))
schemaString = "name age"
fields = [StructField(field_name, StringType(), True) for field_name in schemaString.split()]
schema = StructType(fields)
spark.createDataFrame(people, schema)
print(type(df))
<class 'pyspark.sql.dataframe.DataFrame'>
2、从 Spark 数据源创建
(1)JSON
df = spark.read.json("customer.json")
df.show()
# +-------+---+---------+--------+-----------+
# |address|age|firstName|lastName|phoneNumber|
# +-------+---+---------+--------+-----------+
# |BeiJing| 29| Tom| Smith|13911112222|
# |NanJing| 30| July| Doe|13922223333|
# +-------+---+---------+--------+-----------+
df2 = spark.read.load("people.json", format="json")
df2.show()
# +---+---------+
# |age| name|
# +---+---------+
# | 27| Danae|
# | 26| Claudia|
# | 25|Desdemona|
# | 30| Chloe|
# | 25| Felicia|
# +---+---------+
(2)Parquet 文件
df3 = spark.read.load("people.parquet")
df3.show()
# +---------+---+
# | name|age|
# +---------+---+
# | Danae| 27|
# | Claudia| 26|
# |Desdemona| 25|
# | Chloe| 30|
# | Felicia| 25|
# +---------+---+
(3)文本文件
df4 = spark.read.text("people.txt")
df4.show()
# +------------+
# | value|
# +------------+
# | Danae,27|
# | Claudia,26|
# |Desdemona,25|
# | Chloe,30|
# | Felicia,25|
# +------------+
3、查阅数据信息
df.dtypes # 返回 df 的列名与数据类型
df.show() # 显示 df 的内容
df.head() # 返回前 n 行数据
df.first() # 返回第 1 行数据
df.take(2) # 返回前 n 行数据
df.schema # 返回 df 的 Schema
df.describe().show() # 汇总统计数据
df.columns # 返回 df 的列名
df.count() # 返回 df 的行数
df.distinct().count() # 返回 df 中不重复的行数
df.printSchema() # 返回 df的 Schema
df.explain() # 返回逻辑与实体方案
返回 df 的列名与数据类型:
[('address', 'string'), ('age', 'bigint'), ('firstName', 'string'), ('lastName', 'string'), ('phoneNumber', 'string')]
显示 df 的内容:
+-------+---+---------+--------+-----------+
|address|age|firstName|lastName|phoneNumber|
+-------+---+---------+--------+-----------+
|BeiJing| 29| Tom| Smith|13911112222|
|NanJing| 30| July| Doe|13922223333|
+-------+---+---------+--------+-----------+
返回前 n 行数据:
[Row(address='BeiJing', age=29, firstName='Tom', lastName='Smith', phoneNumber='13911112222'), Row(address='NanJing', age=30, firstName='July', lastName='Doe', phoneNumber='13922223333')]
返回前 1 行数据:
Row(address='BeiJing', age=29, firstName='Tom', lastName='Smith', phoneNumber='13911112222')
返回前 n 行数据:
[Row(address='BeiJing', age=29, firstName='Tom', lastName='Smith', phoneNumber='13911112222'), Row(address='NanJing', age=30, firstName='July', lastName='Doe', phoneNumber='13922223333')]
返回 df 的 Schema:
StructType(List(StructField(address,StringType,true),StructField(age,LongType,true),StructField(firstName,StringType,true),StructField(lastName,StringType,true),StructField(phoneNumber,StringType,true)))
汇总统计数据:
+-------+-------+------------------+---------+--------+-----------------+
|summary|address| age|firstName|lastName| phoneNumber|
+-------+-------+------------------+---------+--------+-----------------+
| count| 2| 2| 2| 2| 2|
| mean| null| 29.5| null| null| 1.39166677775E10|
| stddev| null|0.7071067811865476| null| null|7856741.934616441|
| min|BeiJing| 29| July| Doe| 13911112222|
| max|NanJing| 30| Tom| Smith| 13922223333|
+-------+-------+------------------+---------+--------+-----------------+
返回 df 的列名: ['address', 'age', 'firstName', 'lastName', 'phoneNumber']
返回 df 的行数: 2
返回 df 的 Schema:
root
|-- address: string (nullable = true)
|-- age: long (nullable = true)
|-- firstName: string (nullable = true)
|-- lastName: string (nullable = true)
|-- phoneNumber: string (nullable = true)
返回逻辑与实体方案:
== Physical Plan ==
*(1) FileScan json [address#6,age#7L,firstName#8,lastName#9,phoneNumber#10] Batched: false, Format: JSON, Location: InMemoryFileIndex[file:/D:/Mypython/spark/customer.json], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<address:string,age:bigint,firstName:string,lastName:string,phoneNumber:string>
4、重复值
df = df.dropDuplicates()
5、查询
(1)Select
df.select("firstName", "lastName").show() # 显示 firstName、age 的所有条目
+---------+--------+
|firstName|lastName|
+---------+--------+
| Tom| Smith|
| July| Doe|
+---------+--------+
df.select("firstName", "age", F.explode("phoneNumber").alias("contactInfo"))\
.select("contactInfo.type", "firstName", "age")\
.show() # 显示 firstName、age 的所有条目和类型
df.select(df["firstName"], df["age"] + 1).show() # 显示 firstName 和 age 列的所有记录,并对 age 记录添加1
+---------+---------+
|firstName|(age + 1)|
+---------+---------+
| Tom| 30|
| July| 31|
+---------+---------+
df.select(df['age'] > 29).show() # 显示所有小于24岁的记录
+----------+
|(age > 29)|
+----------+
| false|
| true|
+----------+
(2)When
df.select("firstName", F.when(df.age > 29, 1).otherwise(0)).show()
+---------+--------------------------------------+
|firstName|CASE WHEN (age > 29) THEN 1 ELSE 0 END|
+---------+--------------------------------------+
| Tom| 0|
| July| 1|
+---------+--------------------------------------+
df[df.firstName.isin("Tom", "July")].show()
+-------+---+---------+--------+-----------+
|address|age|firstName|lastName|phoneNumber|
+-------+---+---------+--------+-----------+
|BeiJing| 29| Tom| Smith|13911112222|
|NanJing| 30| July| Doe|13922223333|
+-------+---+---------+--------+-----------+
(3)Like
df.select("firstName", df.lastName.like("Smith")).show()
+---------+-------------------+
|firstName|lastName LIKE Smith|
+---------+-------------------+
| Tom| true|
| July| false|
+---------+-------------------+
(4)Startswith & Endswith
df.select("firstName", df.lastName.startswith("Sm")).show()
+---------+------------------------+
|firstName|startswith(lastName, Sm)|
+---------+------------------------+
| Tom| true|
| July| false|
+---------+------------------------+
df.select(df.lastName.endswith("th")).show()
+----------------------+
|endswith(lastName, th)|
+----------------------+
| true|
| false|
+----------------------+
(5)Substring
df.select(df.firstName.substr(1, 3).alias("name")).show() # alias 对列进行重命名
+----+
|name|
+----+
| Tom|
| Jul|
+----+
(6)Between
df.select(df.age.between(22, 29)).show()
+-----------------------------+
|((age >= 22) AND (age <= 29))|
+-----------------------------+
| true|
| false|
+-----------------------------+
6、列操作
(1)添加列
df = df.withColumn('telePhoneNumber', F.explode(df.phoneNumber.number))
(2)修改列
df = df.withColumnRenamed('phoneNumber', 'callNumber')
df.show()
+-------+---+---------+--------+-----------+
|address|age|firstName|lastName| callNumber|
+-------+---+---------+--------+-----------+
|BeiJing| 29| Tom| Smith|13911112222|
|NanJing| 30| July| Doe|13922223333|
+-------+---+---------+--------+-----------+
(3)删除列
df = df.drop("address", "callNumber")
df = df.drop(df.address).drop(df.callNumber)
df.show()
+---+---------+--------+
|age|firstName|lastName|
+---+---------+--------+
| 29| Tom| Smith|
| 30| July| Doe|
+---+---------+--------+
7、分组
df.groupBy("age").count().show() # 按 age 列分组,统计每组人数
+---+-----+
|age|count|
+---+-----+
| 29| 1|
| 30| 1|
+---+-----+
8、筛选
df.filter(df["age"] > 29).show() # 按 age 列筛选,保留年龄大于29岁的
+---+---------+--------+
|age|firstName|lastName|
+---+---------+--------+
| 30| July| Doe|
+---+---------+--------+
9、排序
df.sort(peopledf.age.desc()).show()
df.sort("age", ascending=False).show()
df.orderBy(["age", "city"], ascending=[0, 1]).show()
+---+---------+--------+
|age|firstName|lastName|
+---+---------+--------+
| 30| July| Doe|
| 29| Tom| Smith|
+---+---------+--------+
10、替换缺失值
df.na.fill(50).show() # 用一个值替换空值
df.na.drop().show() # 去除 df 中为空值的行
df.na.replace(10, 20).show() # 用一个值替换另一个值
11、重分区
df.repartition(10).rdd.getNumPartitions() # 将 df 拆分为10个分区
df.coalesce(1).getNumPartitions() # 将 df 合并为1个分区
12、运行 SQL 查询
(1)将数据框注册为视图
df.createGlobalTempView("people")
df.createTempView("customer")
df.createOrReplaceTempView("customer")
(2)查询视图
df5 = spark.sql("SELECT * FROM customer").show()
peopledf2 = spark.sql("SELECT * FROM global_temp.people").show()
13、输出
(1)数据结构
rdd1 = df.rdd
df.toJSON().first()
df.toPandas()
(2)保存至文件
df.select("firstName", "address").write.save("nameAndAddress.parquet")
df.select("firstName", "age").write.save("namesAndAges.json", format="json")
14、终止 SparkSession
spark.stop()
摘自DataCamp
Learn Python for Data Science Interactively