AWS ATHENA: user-defined variables

Question

I need to create a row number by group. In mysql I use "user-defined variables" for that. But in Athena is gives me an error: no viable alternative at input '@'

Here is my query:

SELECT  @row_num := IF(@prev_value=concat_ws('',t.user_id,t.campaign_id,t.placement_id, t.creative_id),@row_num+1,1) AS RowNumber
     ,t.user_id 
     ,t.campaign_id
     ,t.placement_id
     ,t.creative_id
     , t.imp_clk_event_dt
     ,@prev_value := concat_ws('',t.user_id,t.campaign_id,t.placement_id, t.creative_id)
FROM e2c_conv t,
     (SELECT @row_num := 1) x,
     (SELECT @prev_value := '') y ORDER BY t.user_id,t.campaign_id,t.placement_id, t.creative_id, t.imp_clk_event_dt

Any suggestions on how to resolve this problem will be highly appreciated.

Answer 1

TLDR: AWS Athena does not support variables.

As per their documentation, AWS Athena is based on Presto 0.172 with some limitations. Presto currently has an open issue where the addition of variables is being discussed. PR-5918

Answer 2

with Mandeep as 
              ( 
               select 123 as m_1
                    , 234 as m_2
                ) -- Variable_table (Define Variables here)
select * 
from (
select 123 as a
      , 'abc' as v) as b 
left join Mandeep as m on 1 = 1 - mapping with rows
where a = m.m_1 -- using variables

Answer 3

Athena now supports Prepared Statements for parameterized queries:

You can use the Athena parameterized query feature to prepare statements for repeated execution of the same query with different query parameters. A prepared statement contains parameter placeholders whose values are supplied at execution time. Prepared statements enable Athena queries to take parameters directly and help to prevent SQL injection attacks.

Answer 4

Query :

with man as ( select ('123') as m_number  
         ) 
select *
from table_name as t 
left join man as m on 1=1
where t.number = m.m_number