Spark SQL 查询：使用 `spark.sql()` 和临时视图

Spark SQL 查询：使用 spark.sql() 和临时视图

在 Spark 中，除了使用 DataFrame DSL 外，你还可以直接执行标准 SQL 查询，这对于熟悉 SQL 的用户或复杂查询场景特别有用。以下是核心概念和使用方法：

一、spark.sql() - 执行 SQL 查询的核心方法

作用：执行标准的 ANSI SQL 查询并返回 DataFrame 结果
特点：

• 支持完整的 SQL 语法（SELECT, JOIN, WHERE, GROUP BY 等）
• 查询结果自动转换为 DataFrame
• 与 DataFrame DSL 完全兼容

# Python 示例
result = spark.sql("""
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    WHERE age > 30
    GROUP BY department
    HAVING avg_salary > 5000
    ORDER BY avg_salary DESC
""")
result.show()

// Scala 示例
val result = spark.sql("""
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    WHERE age > 30
    GROUP BY department
    HAVING avg_salary > 5000
    ORDER BY avg_salary DESC
""")
result.show()

二、临时视图（Temporary Views） - SQL 查询的基础

要在 SQL 查询中引用数据，需要先将 DataFrame 注册为临时视图：

1. 创建临时视图的方法

方法	作用	生命周期	可见范围
createOrReplaceTempView()	创建或替换临时视图	会话级别	当前 SparkSession
createTempView()	创建临时视图（已存在时报错）	会话级别	当前 SparkSession
createOrReplaceGlobalTempView()	创建或替换全局临时视图	应用级别	所有 SparkSession
createGlobalTempView()	创建全局临时视图（已存在时报错）	应用级别	所有 SparkSession

2. 使用示例

# 创建普通临时视图
df = spark.read.parquet("employees.parquet")
df.createOrReplaceTempView("employees")

# 创建全局临时视图
department_df.createOrReplaceGlobalTempView("departments")

# 跨视图查询
spark.sql("""
    SELECT e.name, e.salary, d.dept_name
    FROM employees e
    JOIN global_temp.departments d 
    ON e.dept_id = d.id
""")

// Scala 示例
val df = spark.read.parquet("employees.parquet")
df.createOrReplaceTempView("employees")

val globalResult = spark.sql("SELECT * FROM employees WHERE salary > 80000")

3. 视图命名空间

视图类型	访问方式	示例
普通临时视图	直接使用视图名	SELECT * FROM employees
全局临时视图	使用 global_temp 数据库前缀	SELECT * FROM global_temp.departments

三、SQL 与 DataFrame DSL 的互操作性

1. SQL → DataFrame

所有 spark.sql() 查询结果都是 DataFrame，可直接使用 DSL 操作：

sql_result = spark.sql("SELECT * FROM employees WHERE salary > 50000")
dsl_result = sql_result.filter("bonus > 1000").groupBy("dept").count()

2. DataFrame → SQL

DataFrame 操作可转换为 SQL 查询：

# 查看 DataFrame 对应的 SQL 查询计划
df.filter(df.salary > 50000).explain()
# == Physical Plan ==
# *(1) Filter (salary#10 > 50000)

四、完整工作流程示例

# 1. 创建 SparkSession
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("SQLExample").getOrCreate()

# 2. 从文件创建 DataFrame
df = spark.read.csv("data.csv", header=True, inferSchema=True)

# 3. 注册为临时视图
df.createOrReplaceTempView("sales_data")

# 4. 执行复杂 SQL 查询
result = spark.sql("""
    SELECT 
        product_category,
        YEAR(sale_date) AS sale_year,
        SUM(amount) AS total_sales,
        AVG(amount) AS avg_sale
    FROM sales_data
    WHERE sale_date BETWEEN '2020-01-01' AND '2022-12-31'
    GROUP BY product_category, YEAR(sale_date)
    HAVING total_sales > 100000
    ORDER BY sale_year DESC, total_sales DESC
""")

# 5. 将结果保存为新表
result.write.saveAsTable("annual_sales_summary")

# 6. 直接查询保存的表
spark.sql("SELECT * FROM annual_sales_summary WHERE sale_year = 2022").show()

五、最佳实践与注意事项

1. 视图生命周期管理

   # 删除临时视图
   spark.catalog.dropTempView("employees")
   
   # 删除全局临时视图
   spark.catalog.dropGlobalTempView("departments")
   

2. 查看所有注册的视图

   # 列出所有临时视图
   spark.catalog.listTables()
   
   # 列出所有全局临时视图
   spark.catalog.listTables("global_temp")
   

3. 参数化查询（避免 SQL 注入）

   # 安全方式：使用参数化查询
   min_salary = 50000
   result = spark.sql("SELECT * FROM employees WHERE salary > ?", (min_salary,))
   

4. 性能优化

   • 对常用视图进行缓存：spark.sql("CACHE TABLE employees")
   • 使用分区和分桶优化大表查询
   • 避免 SELECT *，只选择需要的列

5. 跨会话共享数据

   # 创建全局视图
   df.createOrReplaceGlobalTempView("shared_data")
   
   # 在新会话中访问
   new_spark = SparkSession.newInstance()
   new_spark.sql("SELECT * FROM global_temp.shared_data")
   

六、适用场景对比

场景	推荐使用
简单数据转换	DataFrame DSL
复杂多表关联	SQL 查询
即席分析（Ad-hoc）	SQL 查询
重用现有 SQL 代码	SQL 查询
类型安全操作	Dataset API (Scala/Java)
流处理	Structured Streaming SQL

七、高级特性

1. UDF 在 SQL 中的使用

   from pyspark.sql.functions import udf
   from pyspark.sql.types import IntegerType
   
   # 注册 UDF
   squared_udf = udf(lambda x: x*x, IntegerType())
   spark.udf.register("squared", squared_udf)
   
   # 在 SQL 中使用
   spark.sql("SELECT squared(age) FROM employees")
   

2. 跨数据库查询

   SELECT * 
   FROM default.employees e
   JOIN global_temp.departments d
   ON e.dept_id = d.id
   

3. CTE（公用表表达式）

   WITH high_earners AS (
     SELECT * FROM employees WHERE salary > 100000
   )
   SELECT dept, COUNT(*) 
   FROM high_earners 
   GROUP BY dept
   

总结

Spark SQL 提供了：

1. 通过 spark.sql() 执行标准 SQL 的能力
2. 使用临时视图（createTempView）作为查询基础
3. 与 DataFrame API 的无缝互操作
4. 完整的 SQL 功能支持（JOIN, GROUP BY, 窗口函数等）

关键工作流程：
数据源 → DataFrame → 注册为临时视图 → 执行 SQL 查询 → 结果作为新 DataFrame → 进一步处理或输出

这种集成方式使开发者可以根据需求灵活选择 SQL 或 DataFrame API，充分利用 Spark 的强大功能。