Databricks出现AnalysisException：UNRESOLVED_COLUMN但列存在

核心错误原因 #

你的SQL查询中存在语法错误，导致Spark解析列时出现混乱：在tb_final块的最内层子查询里，count_pages列的定义末尾多了一个冗余逗号，这会让SQL解析器错误地将后续的from关键字当作列名处理，进而破坏整个子查询的列结构，最终引发user_id无法解析的错误。

错误代码片段：

select user_id, pagePath, tag_frequencia_de_acessos_30_dias, count(*) OVER (PARTITION BY tag_frequencia_de_acessos_30_dias, pagePath) as count_pages,
from tb_exit_last_month_join_profile

修正后的完整SQL #

%sql
WITH tb_paginas_saida as
(
  select *, case when lead_user_entrance = 1 or lead_user_entrance is null then 1 else 0 end user_exit
  from
  (
    select *, lead(user_entrance) over(partition by userId order by dateHourMinute, user_entrance desc) as lead_user_entrance
    from refined.vwm_dash_user_activity_granular
    where not outlier_flag and user_entrance = 1 and dateHourMinute is not null and userId is not null
          and pagePath is not null and month(date) = month(DATEADD(day, -1, date('2023-02-01'))) and year(date) = year(DATEADD(day, -1, date('2023-02-01')))
  )
),      
tb_exit_last_month_per_page_and_user as
(
  -- Principal página por usuário
  select userId, pagePath
  from
  (
    select userId, pagePath, row_number() over(partition by userId order by count_pages_exit desc) as user_page_rank
    from
    (
      select userId, pagePath, count(*) as count_pages_exit
      from tb_paginas_saida
      group by userId, pagePath
    )
  )
  where user_page_rank = 1        
),
tb_exit_last_month_join_profile as
(
  select a1.userId as user_id, a1.pagePath, a2.tag_frequencia_de_acessos_30_dias
  from tb_exit_last_month_per_page_and_user as a1
  join temp_table_website_profile_cortex_content as a2 on a1.userId = a2.userId
),
tb_final as
(
  select tag_frequencia_de_acessos_30_dias, page_url, count(distinct user_id) as distinct_users
  from
  (
    select user_id, tag_frequencia_de_acessos_30_dias,
        case when rank() OVER (PARTITION BY tag_frequencia_de_acessos_30_dias order by count_pages desc) <= 10 then pagePath else 'other' end as page_url
    from 
    (
      select user_id, pagePath, tag_frequencia_de_acessos_30_dias, count(*) OVER (PARTITION BY tag_frequencia_de_acessos_30_dias, pagePath) as count_pages
      from tb_exit_last_month_join_profile
    )
  )
  group by tag_frequencia_de_acessos_30_dias, page_url
)

select DATEADD(day, -1, date('2023-02-01')) as date, 0 as id_cortex_view, "LOGADO" as logged, tag_frequencia_de_acessos_30_dias, page_url, distinct_users
from tb_final

额外排查建议 #

如果修正后仍有问题，可确认以下两点：

• 临时视图temp_table_website_profile_cortex_content中的userId列数据类型，是否与tb_exit_last_month_per_page_and_user的userId完全匹配，避免因类型不兼容导致的关联后列解析异常。
• 检查get_website_user_profile(d)函数返回的SQL是否存在隐式的列名修改或过滤逻辑，确保userId列确实被包含在临时视图中。

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