Databricks SQL设置变量报错：Configuration NumRows不可用

问题详情 #

尝试执行以下SQL设置变量NumRows：

select count(*):
SET NumRows = (
  SELECT 
    count (distinct NBB_CONTRACT_ID) 
  FROM contracts 
  where NBB_T1F2_CP1NAME = 'BNP Paribas Fortis'
)

触发错误：

[_LEGACY_ERROR_TEMP_DBR_0222] org.apache.spark.sql.catalyst.ExtendedAnalysisException: Configuration NumRows is not available.

错误原因 #

1. 语法错误：第一行select count(*):属于冗余代码，末尾的冒号也是无效语法，会干扰后续语句执行。
2. 用法错误：Spark SQL中SET命令用于配置Spark系统参数，而非定义用户自定义变量。直接用SET NumRows = (...)会被识别为尝试修改不存在的系统配置项，因此报错。

解决方法 #

1. 声明用户自定义变量（Databricks等支持环境） #

如果使用Databricks或支持Spark SQL变量声明的环境，用DECLARE定义变量后赋值：

-- 声明变量类型
DECLARE NumRows INT;
-- 赋值
SET NumRows = (
  SELECT COUNT(DISTINCT NBB_CONTRACT_ID) 
  FROM contracts 
  WHERE NBB_T1F2_CP1NAME = 'BNP Paribas Fortis'
);
-- 引用变量
SELECT ${NumRows};

2. 用CTE或临时表存储结果 #

若环境不支持变量声明，可通过公共表表达式（CTE）或临时表存储计算结果，后续查询直接引用：

-- 方式一：CTE
WITH contract_count AS (
  SELECT COUNT(DISTINCT NBB_CONTRACT_ID) AS NumRows
  FROM contracts 
  WHERE NBB_T1F2_CP1NAME = 'BNP Paribas Fortis'
)
-- 后续查询可直接使用contract_count.NumRows
SELECT * FROM contract_count;

-- 方式二：临时表
CREATE TEMPORARY VIEW contract_count AS
SELECT COUNT(DISTINCT NBB_CONTRACT_ID) AS NumRows
FROM contracts 
WHERE NBB_T1F2_CP1NAME = 'BNP Paribas Fortis';

SELECT * FROM contract_count;

3. 编程方式获取结果（Scala/Python） #

如果是在Spark程序中，直接计算并赋值给变量：

Scala

val numRows = spark.sql("""
  SELECT COUNT(DISTINCT NBB_CONTRACT_ID) 
  FROM contracts 
  WHERE NBB_T1F2_CP1NAME = 'BNP Paribas Fortis'
""").head().getLong(0)

Python

num_rows = spark.sql("""
  SELECT COUNT(DISTINCT NBB_CONTRACT_ID) 
  FROM contracts 
  WHERE NBB_T1F2_CP1NAME = 'BNP Paribas Fortis'
""").head()[0]

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