Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Predicate push down working for the same join query on hive-cli but not when run as a hive action through oozie

Predicate push down working for the same join query on hive-cli but not when run as a hive action through oozie

New Contributor

Hi,

I have a partitioned hive table that has multiple partition columns.

My query is of the form:

select audf(column1) , sum(column2)

from table1

join

table2

on (table1.id = table2.id)

where ((table1.partition_column1 = 2016 and table1.partition_column2 = 08 and table1.partition_column3 = 09) or (table1.partition_column1 = 2016 and table1.partition_column2 = 08 and table1.partition_column3 = 10))

and

((table2.partition_column1 = 2016 and table2.partition_column2 = 08 and table2.partition_column3 = 09) or (table2.partition_column1 = 2016 and table2.partition_column2 = 08 and table2.partition_column3 = 10))

group by audf(column1);

Predicate push down happens when this query is run through hive-cli and input splits that are added are significantly low in number as compared to when this query is run as part of oozie hive action.

All the 4 configuration properties related to ppd are set to true in both, hive as well as oozie configurations.

I am using HDP 2.4.

5 REPLIES 5

Re: Predicate push down working for the same join query on hive-cli but not when run as a hive action through oozie

Super Guru

@Suyash Agarwal the only thing that stands out to me is the fact you might have your hive session params invoke dvia CLI which may not be invoked when you run with oozie. check your .hiverc and port those default params into your hive action on oozie.

Re: Predicate push down working for the same join query on hive-cli but not when run as a hive action through oozie

New Contributor

Hi @Sunile Manjee, thanks for the response. I expect .hiverc to be available in hive conf directory but couldn't find it there. Where can I find it? Also, does this mean that the output of `set -v;` for cli and oozie action will differ? I have the output of this command for cli and oozie but I couldn't find any property that was different and relevant to predicate push down. Would you like me to share it if that helps?

Re: Predicate push down working for the same join query on hive-cli but not when run as a hive action through oozie

New Contributor

Okay, there are a few properties like `hive.optimize.point.lookup` that are being set in oozie but are undefined in hive-cli. I think that means there is a version mis-match. But all the jars in oozie share lib have the same version (1.2.1).

Re: Predicate push down working for the same join query on hive-cli but not when run as a hive action through oozie

Super Guru

@Suyash Agarwal check

$HIVE_HOME/bin/.hiverc

when you launch hive cli, do the -i option. this will avoid running the .hiverc ini files. then you can test again to determine if fucntionality is similar to when you run in oozie.

Re: Predicate push down working for the same join query on hive-cli but not when run as a hive action through oozie

New Contributor

@Sunile Manjee There's no .hiverc in the directory. I tried with an empty initialize file with -i and yet, predicate pushdown works fine with hive-cli but it doesn't work with oozie.