Hive query (tez/mr) hangs due to empty string operation.

[#1]

PROBLEM:

Hive queries were getting stuck/hang on both mr and tez engines while selecting from a table containing few csv files.

While the query was working fine for few csv files, for others it just hangs. Nothing in the logs also.

I was using Hive 1.2.1 on HDP 2.5.3.0

After some investigation, I found out that those files have some empty values '' in the fields where an rpad function was getting used.

You can easily reproduce the issue by firing:

select rpad('',1,''); 

You will see that the query just hangs.

The reason is it goes to an infinite loop.

More details here: HIVE-15792

RESOLUTION:

nvl will not work in this case.

That is

select nvl('','D'); --will return ''

I resolved using a query like this:

SELECT rpad(CASE WHEN LENGTH(nvl(COLUMN_NAME,null)) > 0 THEN COLUMN_NAME ELSE null END, 1, ''); 

In this case the query will return null for both null and empty string values occurring in COLUMN_NAME.

Hope this helps.

Thanks,

Rajdeep