Query Porting to Impala

user_4836324 0
Viewed 45

I am trying to understand the small snippet from Query which must be adapted by Impala.

Select
.
.

from ${ENV_PREFIX}private_datalap_storage_customer_v1 cus
lateral view explode(adresses) address as addr
where year = substr(${REF_DATE}, 1, 4)
and month = substr(${REF_DATE}, 5, 2)

Can someone please help to understand what's happening infrom and Where ?

Also, I will appreciate if someone can explain Why I have the below error when Tryin to Run the Query on Impala

ParseException line 35:20 cannot recognize input near ',' ''1'' ',' in function specification

impala hiveql hive sql variables
1 Answers

substr() receives string, start position and length in chars to extract from start position. substr('2021-02-20', 1, 4) should extract 2021.

Most probable, the variable is not resolved and you get substr(, 1, 4) instead of for example substr('2021-02-20', 1, 4).In Impala, variables are in this form ${var:var_name}, check how are you passing it and how is it getting resolved using select '${var:var_name}'

Also I do not know how are you passing variable in Hive but string literal should be quoted, if variable itself does not contain quotes, this substr(${REF_DATE}, 1, 4) gets resolved as substr(2021-02-20, 1, 4), which is wrong, so double check do you need to put ${REF_DATE} in quotes or it already contains quotes.

user_27003440
Related