Hive: Misc

(Last Updated On: )

This tutorial will show you some misc usage for working with Hive. If you have no installed Hive yet please follow this tutorial.

NVL:
Check if value is null then substitute other value.

  1. SELECT NVL(columnA, 'was null')
  2. FROM test;

CAST:
If columnE was a date and you wanted it to be a string.

  1. SELECT CAST(columnE AS STRING)
  2. FROM test;

Concat:
This will concat the strings together giving “Test the code!”

  1. SELECT CONCAT('Test', ' the ', 'code!');

CONCAT_WS:
This will concat the strings together starting at “Test” and use the first position as the seperator giving “Test the code”

  1. SELECT CONCAT_WS(' ', 'Test', 'the', 'code');

MIN:
This will give the minimum value of columnC

  1. SELECT MIN(columnC) AS Max_ColumnC
  2. FROM test;

MAX:
This will give the maximum value of columnC

  1. SELECT MAX(columnC) AS Max_ColumnC
  2. FROM test;

DISTINCT:
This will select the distinct columnA and columnB

  1. SELECT DISTINCT columnA, columnB
  2. FROM test;

CASE:

  1. SELECT CASE WHEN columnA = 'val' THEN 'Is Val' ELSE 'Not Val' END
  2. FROM test;

COLLECT_SET:
This will collect all values in columnC and select the first index.

  1. SELECT COLLECT_SET(columnC)[0]
  2. FROM test;

CURRENT_DATE:
This will give you the current date in the format YYYY-MM-dd.

  1. SELECT CURRENT_DATE();

UNIX_TIMESTAMP:
This will give you the current timestamp from EPOCH. IE: 1549591492

  1. SELECT UNIX_TIMESTAMP();

FROM_UNIXTIME:
This will take a timestamp and display it in the format YYYY-MM-dd HH:MM:SS.

  1. SELECT FROM_UNIXTIME(1549591492);

TO_DATE:
This will convert a date to YYYY-MM-dd.

  1. SELECT TO_DATE('2019-02-01 01:01:01');

YEAR:

  1. SELECT YEAR(columnE)
  2. FROM test;

MONTH:

  1. SELECT MONTH(columnE)
  2. FROM test;

DAY:

  1. SELECT DAY(columnE)
  2. FROM test;

DATE_ADD:

  1. SELECT DATE_ADD('2019-01-01', 4);

UPPER:
This will upper case columnA.

  1. SELECT UPPER(columnA)
  2. FROM test;

LOWER:
This will lower case columnA.

  1. SELECT LOWER(columnA)
  2. FROM test;

TRIM:
This will trim leading and trailing spaces in columnA.

  1. SELECT TRIM(columnA)
  2. FROM test;

LITERALS:
If a column contains a space you will need to use literal in order to use the AS keyword or when you are defining it in the create table command. Although I don’t recommend this it is possible.

  1. SELECT columnA AS `test column`
  2. FROM test;