Azure Databricks中Spark SQL调用R变量失败问题排查及替代方案

1. 变量插值机制不匹配：Databricks SQL单元格的${}是笔记本级别的变量插值，仅识别通过dbutils.widgets定义的笔记本变量，或%sql SET设置的Hive会话变量。而sparkR.conf是配置SparkSession的参数，不属于笔记本插值变量的读取范围，导致${db.dt1}无法获取到正确值，查询条件无效。
2. 类型转换隐患：dt_1是R数值类型，通过sparkR.conf传递时可能未被正确转为字符串格式，即使能读取，SQL中加引号后也可能生成不符合日期格式的无效值。

方案一：使用笔记本Widget变量（推荐） #

在R单元格中通过dbutils.widgets创建字符串类型的笔记本变量，SQL单元格可直接通过${}插值引用：

# R单元格：定义并设置Widget变量
dt_1 <- "20230101"
dt_2 <- "20230131"
dbutils.widgets.text("db_dt1", dt_1)
dbutils.widgets.text("db_dt2", dt_2)

-- SQL单元格：直接引用变量
select *
from tbl
where date between '${db_dt1}' and '${db_dt2}'

用完变量后可执行dbutils.widgets.remove("db_dt1")清理Widget。

方案二：在R单元格直接生成并执行SQL #

无需切换到SQL单元格，直接在R中拼接查询语句并执行：

dt_1 <- 20230101
dt_2 <- 20230131
# 拼接SQL字符串
query <- sprintf("select * from tbl where date between '%s' and '%s'", dt_1, dt_2)
# 执行查询并展示结果
result <- sql(query)
display(result)

方案三：使用Spark SQL会话变量 #

在R单元格中通过sql()执行SET命令设置会话变量，SQL单元格通过hiveconf前缀引用：

# R单元格：设置会话变量
dt_1 <- 20230101
dt_2 <- 20230131
sql(paste0("SET db.dt1 = '", dt_1, "'"))
sql(paste0("SET db.dt2 = '", dt_2, "'"))

-- SQL单元格：引用会话变量
select *
from tbl
where date between '${hiveconf:db.dt1}' and '${hiveconf:db.dt2}'

也可直接在SQL中调用spark.conf.get读取配置：

select *
from tbl
where date between spark.conf.get('db.dt1') and spark.conf.get('db.dt2')

内容的提问来源于stack exchange，提问作者rfs