Replace the empty or NULL value with specific value in HIVE query result
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!!!
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.