Databricks Spark查询报错Key not found: industry_code#xxxx咨询

子查询访问外部表别名的合法性 #

你写的子查询访问外部表别名al的语法本身是合法的，Spark支持关联子查询引用外部查询的列。出现Key not found: industry_code#15252错误，大概率是Spark查询解析器在处理隐式连接（逗号分隔多表）时，对关联子查询的外部列解析出现了异常。

修复方案 #

方案1：替换隐式连接为显式INNER JOIN #

把原来的逗号分隔表的写法改成显式的INNER JOIN，让Spark的解析器更清晰地识别表之间的关联关系，从而正确解析子查询中的外部列：

SELECT 
  bu.tenant_id,
  bu.service_location_id,
  bu.account_id,
  bu.commodity_type,
  bu.commodity_usage,
  bu.commodity_units,
  bu.charges,
  bu.billed_usage_start,
  bu.billed_usage_end,
  CASE 
    WHEN al.industry_code_type IS NOT NULL AND al.industry_code IS NOT NULL AND al.industry_code_type= 'sic' 
    THEN (
      SELECT sics_name 
      FROM dev_silver.trend_calculator_poc.mapping_business_type_codes_results 
      WHERE sics_code=CAST(al.industry_code AS BIGINT) 
      LIMIT 1
    ) 
  END AS business_type,  
  bu.created AS created_at, 
  bu.updated AS updated_at 
FROM dev_silver.trend_calculator_poc.billed_usage_temp bu
INNER JOIN dev_silver.trend_calculator_poc.account_location_temp al 
  ON al.tenant_id=bu.tenant_id 
  AND al.service_location_id=bu.service_location_id 
  AND al.account_id=bu.account_id;

方案2：将关联子查询改为JOIN方式（更推荐） #

关联子查询在某些场景下性能不如JOIN，而且可以完全改写为LEFT JOIN的方式，彻底避免子查询解析问题：

SELECT 
  bu.tenant_id,
  bu.service_location_id,
  bu.account_id,
  bu.commodity_type,
  bu.commodity_usage,
  bu.commodity_units,
  bu.charges,
  bu.billed_usage_start,
  bu.billed_usage_end,
  CASE 
    WHEN al.industry_code_type IS NOT NULL AND al.industry_code IS NOT NULL AND al.industry_code_type= 'sic' 
    THEN map.sics_name
  END AS business_type,  
  bu.created AS created_at, 
  bu.updated AS updated_at 
FROM dev_silver.trend_calculator_poc.billed_usage_temp bu
INNER JOIN dev_silver.trend_calculator_poc.account_location_temp al 
  ON al.tenant_id=bu.tenant_id 
  AND al.service_location_id=bu.service_location_id 
  AND al.account_id=bu.account_id
LEFT JOIN dev_silver.trend_calculator_poc.mapping_business_type_codes_results map
  ON map.sics_code=CAST(al.industry_code AS BIGINT)
  AND al.industry_code_type= 'sic'
  AND al.industry_code IS NOT NULL
  AND al.industry_code_type IS NOT NULL;

这种方式不仅解决了解析错误，还可能提升查询性能，因为Spark对JOIN的优化通常比关联子查询更成熟。

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