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.

Dynamic where condition using UDF

Dynamic where condition using UDF

New Contributor

we want to run a hive query which has a dynamic where condition.

Select * from table where { this condition is dynamic}

 

We have written a dynamic where UDF and dynamic_where() returns us the dynamic where condition string like below -

1 and ndc_cd='002' and generic_nm = 'MEP'

 

But the query doesn't get us the results when we run it using the dynamic_where() udf.

Select * from table where 1 = dynamic_where();

 

the dynamic_where returns the string correctly when we run select dynamic_where ();

 

Can a dynamic where be used in a condition using UDFs. If yes then how to achieve it and what are we doing incorrect.

 

Is there another better way to do it.

 

 

 

2 REPLIES 2

Re: Dynamic where condition using UDF

Guru
Hi Mamta,

- Do you have examples on what dynamic_where() function will return?
- Is your WHERE clause like "1 = dynamic_where()"? Or is it just an example?
- what's the EXPLAIN EXTENDED output for this query?

Cheers
Eric

Re: Dynamic where condition using UDF

New Contributor

Hi Eric,

To answer your questions:-

 1) Do you have examples on what dynamic_where() function will return?

     Answer - the dynamic_where() returns a string , which is a where condition like "1 and ndc_cd='002' and generic_nm = 'MEP'", this return string is dynamic and based on logged-in user,hence dynamic where condition.

 

2) Is your WHERE clause like "1 = dynamic_where()"? Or is it just an example?

     Answer - yes this is the where claus.


3) what's the EXPLAIN EXTENDED output for this query?

    I ran it for the query but couldn't get much info.

 

Basically, I want to execute a query in which the where claus is dynamic and comes from a UDF.