Replace the empty or NULL value with specific value in HIVE query result
Solution 1
Use LENGTH()
to check the length of the column value. It returns > 0, if there is some value else return 0 for empty or NULL value.
Also frame the column value in CASE WHEN ... END
block
The final query may look like:
SELECT country, CASE WHEN LENGTH(os(agent)) > 0 THEN os(agent) ELSE 'Others' END AS SO, COUNT(*)
FROM clicks_data
WHERE country IS NOT NULL AND os(agent) IS NOT NULL
GROUP BY country, os(agent);
Hope this help you!!!
Solution 2
COALESCE will be the best suitable and optimum solution for your case
Syntax: COALESCE(VALUE,DEFAULT_VALUE): Function returns default value when values is null else VALUE;
Query
SELECT country, COALESCE(os(agent),'Others') AS SO, COUNT(*)
FROM clicks_data
WHERE country IS NOT NULL AND os(agent) IS NOT NULL
GROUP BY country, os(agent);
Hope this would be the efficient solution for your problem.
Solution 3
=''
maybe the easiest way to go.
e.g.
CASE WHEN col='' THEN xxx ELSE yyy END
AS col_new;
Sebastian Loeb Sucre
Updated on March 22, 2020Comments
-
Sebastian Loeb Sucre about 4 years
I'm trying to show a default value, "Others", when the query does not return any result for one of the selected columns. I'll show you the example.
This query returns an empty value for
os(agent) SO
(in the first row):select country, os(agent) SO, count(*) from clicks_data where country is not null and os(agent) is not null group by country, os(agent);
Output:
ZA 4 ZA Android 4 ZA Mac 8 ZA Windows 5
Instead, I would like to get this result:
ZA Others 4 ZA Android 4 ZA Mac 8 ZA Windows 5
My next attempt was this query, but it's not really working, either:
select country, regexp_replace(os(agent),'','Others') SO, count(*) from clicks_data where country is not null and os(agent) is not null group by country, os(agent);
This is the result:
ZA Others 4 ZA OthersAOthersnOthersdOthersrOthersoOthersiOthersdOthers 4 ZA OthersMOthersaOtherscOthers 8 ZA OthersWOthersiOthersnOthersdOthersoOtherswOtherssOthers 5