How to drop a parition if the partition value has a empty character in it.

[Priya]

Hi All,

Please Help.How can I drop a partition if the partition value has a empty
character in it.

Eg:/user/hive/warehouse/xyz/table_name/dt=20161017/profile_id=Profile ID

-- 
Regards,
Usha Priya Ramanathan

Re: Hive on Tez CTAS query breaks

[Gopal Vijayaraghavan]

> Thanx for the suggestion. It works with the setting you suggested.
> 
> What does this mean? Do I need to special case this query.

You need to report a bug on https://issues.apache.org/jira/browse/HIVE

Because, this needs to get fixed.

> Turning off CBO cluster-wide won't be the right thing to do, would it?

Nope, CBO is a good thing - a workaround would be to try what Sergey suggested, 
the '$f0' is probably the auto-generated name for the count(0).

Naming that column explicitly on both branches of the UNION ALL, might get CBO 
back up.

Cheers,
Gopal

Re: Hive Runtime Error processing row

[Sergey Shelukhin]

Hi. Can you file a JIRA with exception callstack? Seems to be a bug. Thanks!

From: George Liaw mailto:george.a.l...@gmail.com>>
Reply-To: "user@hive.apache.org" 
mailto:user@hive.apache.org>>
Date: Thursday, November 10, 2016 at 17:27
To: "user@hive.apache.org" 
mailto:user@hive.apache.org>>
Subject: Re: Hive Runtime Error processing row

Gopal,

Tested it out and that seemed to resolve the issue. Guess it'll have to be kept 
disabled for the time being.

Thanks!

On Thu, Nov 10, 2016 at 3:58 PM, George Liaw 
mailto:george.a.l...@gmail.com>> wrote:
I'll give it a try. This is Hive 2.0.1

On Thu, Nov 10, 2016 at 3:26 PM, Gopal Vijayaraghavan 
mailto:gop...@apache.org>> wrote:

> I'm running into the below error occasionally and I'm not 100% certain what's 
> going on. Does anyone have a hunch what might be happening here or where we 
> can dig for more ideas? Removed row contents but there are multiple columns.

You can try a repro run by doing

set hive.mapjoin.hybridgrace.hashtable=false;

to dig into the issue.

> at 
> org.apache.hadoop.hive.ql.exec.persistence.HybridHashTableContainer$ReusableRowContainer.setFromOutput(HybridHashTableContainer.java:844)
> at 
> org.apache.hadoop.hive.ql.exec.persistence.HybridHashTableContainer$GetAdaptor.setFromRow(HybridHashTableContainer.java:725)

Which version of Hive is this?

Cheers,
Gopal





--
George A. Liaw

(408) 318-7920
george.a.l...@gmail.com
LinkedIn



--
George A. Liaw

(408) 318-7920
george.a.l...@gmail.com
LinkedIn

RE: Hive Left Join inequality condition

[Markovitz, Dudu]

My pleasure ☺

Dudu

From: Goden Yao [mailto:goden@gmail.com]
Sent: Friday, November 11, 2016 1:26 AM
To: user@hive.apache.org
Subject: Re: Hive Left Join inequality condition

This worked!! Thanks so much Dudu!!

On Sat, Nov 5, 2016 at 1:03 PM Markovitz, Dudu 
mailto:dmarkov...@paypal.com>> wrote:
Ugly as hell, but should work.

Dudu



SELECT r_id,
   CASE WHEN table1.property_value = 'False' THEN FALSE
WHEN table1.property_value = 'True' THEN TRUE
WHEN r.rea <  rg.laa THEN FALSE
WHEN r.rea >= rg.laa THEN TRUE
ELSE FALSE END AS flag
  FROM rs r
  LEFT JOIN public.di_re rg
ON r.re = rg.re
  LEFT JOIN (selectr.r_id, table1.property_value
fromrs r
join public.tbl table1
ON r.re = table1.re
where  table1.property_name = ''
   AND r.rea BETWEEN table1.begin_time AND table1.end_time
) table1

   on r.r_id = table1.r_id

From: Goden Yao [mailto:goden...@apache.org]
Sent: Saturday, November 05, 2016 9:22 AM
To: user@hive.apache.org
Subject: Hive Left Join inequality condition


Hello!

Lately we have ran into the need to implement inequality JOIN in Hive, and we 
could have easily done that with WHERE clause, if it was not the LEFT join.
Basically, we wonder how people implement LEFT/RIGHT JOIN with inequality 
conditions in Hive without loss of efficiency.
Thank you.
Example:

SELECT r_id,

   CASE WHEN table1.property_value = 'False' THEN FALSE

WHEN table1.property_value = 'True' THEN TRUE

WHEN r.rea <  rg.laa THEN FALSE

WHEN r.rea >= rg.laa THEN TRUE

ELSE FALSE END AS flag

  FROM rs r

  LEFT JOIN public.di_re rg

ON r.re = rg.re

  LEFT JOIN public.tbl table1

ON r.re = table1.re

   AND table1.property_name = ''

   AND r.rea BETWEEN table1.begin_time AND table1.end_time

Error:

FAILED: SemanticException Line 0:-1 Both left and right aliases encountered in 
JOIN ...

Ways to resolve:
• Move inequality condition in WHERE clause:

• WHERE r.rea BETWEEN table1.begin_time AND table1.end_time

• WARNING: Affects query logic - filters all the table instead of filtering 
LEFT JOIN clause only;
• Move condition into SELECT field with CASE statement (if possible):

• SELECT r_id,

•  CASE WHEN table1.property_value = 'False'

•AND r.rea BETWEEN table1.begin_time AND  table1.end_time 
THEN FALSE

•   WHEN table1.property_value = 'True'

•AND r.rea BETWEEN table1.begin_time AND table1.end_time 
THEN TRUE
Not possible in every case;
• Divide queries into two separate statements and UNION them: one query 
with WHERE filter and another query totally omitting the JOIN to table that 
needed inequality as well as omitting the ids from the first query:

• WITH stage AS (

• SELECT r_id,

•  CASE WHEN table1.property_value = 'False' THEN FALSE

•   WHEN table1.property_value = 'True' THEN TRUE

•   WHEN r.rea <  rg.laa THEN FALSE

•   WHEN r.rea >= rg.laa THEN TRUE

•   ELSE FALSE END as flag

• FROM rs r

• LEFT JOIN public.di_re rg

•   ON r.re = rg.re

• LEFT JOIN public.tbl table1

•   ON r.region = table1.region

•  AND table1.property_name = ''

• WHERE r.rea BETWEEN table1.begin_time AND table1.end_time

• )

• SELECT * FROM stage

• UNION

• SELECT r_id,

•  CASE WHEN r.rea <  rg.laa THEN FALSE

•   WHEN r.rea >= rg.laa THEN TRUE

•   ELSE FALSE END as flag

• FROM rs r

• LEFT JOIN public.di_re rg

•   ON r.re = rg.re

• WHERE r_id NOT IN (SELECT DISTINCT r_id from stage)
Very expensive in terms of calculation, but in some cases inevitable.
​
--
Goden