Support Questions

Find answers, ask questions, and share your expertise

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Cloudera Community
- :
- Support
- :
- Support Questions
- :
- Dynamic where condition using UDF

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Dynamic where condition using UDF

Labels:

New Contributor

Created 07-08-2019 12:19 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

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

Guru

Created 07-15-2019 12:23 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- 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

Highlighted
##

Re: Dynamic where condition using UDF

New Contributor

Created 07-16-2019 12:01 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.