使用Athena预准备语句执行查询时抛出语法错误异常

问题场景 #

在Athena中执行带参数的预准备语句（S3作为数据存储），尝试在filter函数的lambda表达式中传入两个查询参数时，抛出语法错误：

java.lang.RuntimeException: Query Failed to run with Error Message: SYNTAX_ERROR: line 1:1: 

预准备语句：

PREPARE my_select1 FROM
    
SELECT address.phonenumbers FROM "nested-query-db"."data_with_json1" where 
cardinality(filter(address.phonenumbers,js->js.type = ? and js.number = ?)) >0 and 
cardinality(filter(address.phonenumbers,js->js.type = 'city'and js.number = '4')) > 0 and
firstname ='Emily';

执行语句：

EXECUTE my_select1 USING 'Home', '1';

示例数据：

{"firstname":"Emily","address":{"streetaddress":"101","city":"abc","state":"","phonenumbers":[{"type":"home","number":"11"},{"type":"city","number":"4"}]}}

问题原因 #

Athena基于Presto内核，预准备语句的占位符?无法直接在lambda表达式的箭头函数内部使用——Presto的语法预处理器不能正确解析lambda内部的占位符，从而触发语法错误。

解决方法 #

通过CTE（WITH子句）将参数提前映射为命名变量，让lambda表达式引用外部变量来传递参数，绕开语法限制：

修改后的预准备语句 #

PREPARE my_select1 FROM
WITH params AS (
    SELECT ? AS target_type, ? AS target_number
)
SELECT address.phonenumbers 
FROM "nested-query-db"."data_with_json1", params
WHERE 
    cardinality(filter(address.phonenumbers, js->js.type = params.target_type AND js.number = params.target_number)) > 0
    AND cardinality(filter(address.phonenumbers, js->js.type = 'city' AND js.number = '4')) > 0
    AND firstname = 'Emily';

执行语句保持不变 #

EXECUTE my_select1 USING 'Home', '1';

补充说明 #

• 示例数据中Emily的home号码为11，若执行时传入'1'将匹配不到数据，可根据实际业务需求调整参数值。
• CTE临时表的作用是将占位符转换为可被lambda表达式识别的外部变量，避免预处理器的语法解析冲突。

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