SQL Parameter in Cloudera Impala query

Question

I have a simple question regarding queries running on Impala (I'm using Pentaho report designer as my reporting tool). The query is:

select a.*, b.sentatmosphere, b.sentgeneral, b.sentnetcharge, b.sentnetqual, b.sentservice, b.senttidiness 
from p_country_town_hotel a left outer join p_hotel_rev_agg  b on (a.hotel = b.key)  
where a.country = ${Cntry} order by a.City   limit 10000

which fails in Pentaho Report designer. The reason is the parameter, because if I change the where clause like: a.country = 'Denmark' then I get back results (bullet quick to be precise and that's why we would like to use this). I know that Pentaho want's it's parameters to be written with ${}, but I haven't found info on Impala using/not using parameters. Any info, ideas, anything?

Raunak Jhawar Raunak Jhawar · Accepted Answer · 2014-04-20T10:50:41

Have you tried to parameterized the HQL with environment variables or hive configuration variables. Can you try...

select a.*, b.sentatmosphere, b.sentgeneral, b.sentnetcharge, b.sentnetqual, b.sentservice, b.senttidiness from p_country_town_hotel a left outer join p_hotel_rev_agg b on (a.hotel = b.key)
where a.country = ${env:Cntry} order by a.City limit 10000

OR

select a.*, b.sentatmosphere, b.sentgeneral, b.sentnetcharge, b.sentnetqual, b.sentservice, b.senttidiness from p_country_town_hotel a left outer join p_hotel_rev_agg b on (a.hotel = b.key)
where a.country = ${hiveconf:Cntry} order by a.City limit 10000

SQL Parameter in Cloudera Impala query

1 Answers