Databricks AnalysisException报错：homepage_url列不存在排查请求

错误原因 #

1. 交叉连接的子查询中直接使用homepage_url，未指定所属表别名organizations，SQL引擎无法定位该列。
2. 采用LEFT OUTER JOIN关联organizations表，会存在部分行的homepage_url为NULL的情况，直接调用字符串处理函数会触发异常。
3. 部分SQL引擎（如Spark SQL）不支持普通CROSS JOIN引用前置关联表的列，需使用CROSS JOIN LATERAL（或方言等价语法）实现依赖前置列的横向计算。

修正后的SQL代码 #

SELECT DISTINCT
  account.xpd_relationshipstatus AS CRM_xpd_relationshipstatus
 ,REPLACE(owneridname,'Data.Import #','') AS xxxxxxx
 ,account.ts_montaguoffice AS Montagu_Office
 ,CAST(account.ts_reminderdatesetto AS DATE) AS testname
 ,CAST(account.ts_lastdatestatuschanged AS DATE) AS YearofCRMtslastdatestatuschanged
 ,organizations.name AS nameCB
 ,organizations.homepage_url
 ,IFF(e.e LIKE 'www.%', e.e, 'www.' + e.e) AS website
 ,LEFT(category_list,CHARINDEX(',',category_list  +','  )-1) AS category_CB
 ,organizations.category_groups_list AS category_groups_CB

FROM basecrmcbreport.account
LEFT OUTER JOIN basecrmcbreport.CRM2CBURL_Lookup
  ON account.Id = CRM2CBURL_Lookup.Key
LEFT OUTER JOIN basecrmcbreport.organizations
  ON CRM2CBURL_Lookup.CB_URL_KEY = organizations.cb_url
-- 使用LATERAL JOIN替代普通CROSS JOIN，确保能引用organizations表的列
CROSS JOIN LATERAL (VALUES (CHARINDEX('://', COALESCE(organizations.homepage_url, '')))) a(a)
CROSS JOIN LATERAL (VALUES (IFF(a.a = 0, 1, a.a + 3))) b(b)
CROSS JOIN LATERAL (VALUES (CHARINDEX('/', COALESCE(organizations.homepage_url, ''), b.b))) c(c)
CROSS JOIN LATERAL (VALUES (IFF(c.c = 0, LENGTH(COALESCE(organizations.homepage_url, '')) + 1, c.c))) d(d)
CROSS JOIN LATERAL (VALUES (SUBSTRING(COALESCE(organizations.homepage_url, ''), b.b, d.d - b.b))) e(e)

关键修改点 #

• 所有引用homepage_url的地方都加上表别名organizations.，并通过COALESCE处理NULL值，避免空字符串报错。
• 将普通CROSS JOIN替换为CROSS JOIN LATERAL，确保SQL引擎能识别前置关联表的列（不同引擎可能有差异，比如PostgreSQL用LATERAL，Spark SQL支持该语法）。
• 给每个横向子查询的结果列加上明确的别名（如a.a、e.e），避免列名冲突。

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