Question 19571 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Athena Prepared Statement throws Exception when using Prepared Statements

I have the following query in Athena, I am using S3 as data store. I have 2 query parameters in the lambda expression.

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';

When I execute it using

EXECUTE my_select1 USING 'Home', '1';

It throws the following error.

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

Sample Data:

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

2

Answers


  1. 0

    I think the problem can be caused by the following.

    You are probably using Athena engine version 2, which is based on Presto, explicitly Presto version 0.217.

    The PrestoDB Github repository identifies a very similar problem like the one you described in this issue.

    The issue was addressed in PrestoDB by this pull request, and included in the release 0.256.

    This means the fix is not included in Athena.

    I am not sure if it will work, but to solve the issue you probably could try using Athena engine version 3, which is based on Trino instead.

    Login or Signup to reply.
  2. 0

    @jccampanero explain lambda expression does not support in Athena 2.

    But I have a workaround for you.

    This query works well, according your needs.

    PREPARE my_select1 FROM
SELECT 
    address.phonenumbers
FROM address 
CROSS JOIN UNNEST(address.phonenumbers) as t(phone)
where 
phone.type = ? and phone.number = ? and
cardinality(filter(address.phonenumbers, js -> js.type = 'city' and js.number = '4')) > 0 and
firstname = 'Emily';


EXECUTE my_select1 USING 'home', '11'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search