AWS Athena创建存储过程报错：mismatched input 'Begin'求助

报错信息 #

SQL Error [100071] [HY000]: [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. line 1:1: mismatched input 'Begin'

原尝试SQL代码 #

declare basemonth as date;
set basemonth = date(2021-08-01)
While Date_add(month,1,basemonth) < date(2022-07-31);
Begin
select country , plan_validity, count(distinct A.userid) from db.tbl A
where A.plan_validity = '30' and lower(A.country) = 'united kingdom' and A.startdate between Date ('2021-08-01') and Date('2021-10-31') 
and A.userid in
(select distinct userid from db.tbl 
where plan_validity = '30' and lower(country) = 'united kingdom' and startdate between basemonth and Date('2021-08-31'))
group by 1,2
end;

问题原因 #

AWS Athena基于Presto SQL引擎，不支持传统关系型数据库的存储过程语法（如DECLARE/WHILE/BEGIN/END这类过程式语句）。Athena的SQL是无状态的，仅支持纯声明式查询，无法直接实现循环、变量赋值这类逻辑。

解决方案 #

方案1：用纯查询替代循环逻辑 #

通过生成月份序列的方式，一次性完成多月份的统计，避免循环。示例代码如下：

WITH month_series AS (
    -- 生成2021-08到2022-06的所有月份起始日期
    SELECT date_add('month', i, DATE '2021-08-01') AS basemonth
    FROM UNNEST(sequence(0, 10)) AS t(i)
)
SELECT 
    m.basemonth,
    A.country,
    A.plan_validity,
    COUNT(DISTINCT A.userid) AS user_count
FROM db.tbl A
JOIN month_series m 
    ON A.startdate BETWEEN m.basemonth AND last_day(m.basemonth)
WHERE 
    A.plan_validity = '30' 
    AND LOWER(A.country) = 'united kingdom'
    AND A.startdate BETWEEN DATE '2021-08-01' AND DATE '2021-10-31'
    AND EXISTS (
        SELECT 1 FROM db.tbl B
        WHERE 
            B.userid = A.userid
            AND B.plan_validity = '30' 
            AND LOWER(B.country) = 'united kingdom'
            AND B.startdate BETWEEN m.basemonth AND last_day(m.basemonth)
    )
GROUP BY m.basemonth, A.country, A.plan_validity
ORDER BY m.basemonth;

方案2：通过外部服务实现循环逻辑 #

如果必须保留循环逻辑，建议使用AWS Lambda编写代码，在Lambda中实现月份循环，每次调用Athena执行单月份的查询，最后汇总所有查询结果。

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