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.
SELECT NVL(columnA, 'was null') FROM test;
CAST:
If columnE was a date and you wanted it to be a string.
SELECT CAST(columnE AS STRING) FROM test;
Concat:
This will concat the strings together giving “Test the code!”
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”
SELECT CONCAT_WS(' ', 'Test', 'the', 'code');
MIN:
This will give the minimum value of columnC
SELECT MIN(columnC) AS Max_ColumnC FROM test;
MAX:
This will give the maximum value of columnC
SELECT MAX(columnC) AS Max_ColumnC FROM test;
DISTINCT:
This will select the distinct columnA and columnB
SELECT DISTINCT columnA, columnB FROM test;
CASE:
SELECT CASE WHEN columnA = 'val' THEN 'Is Val' ELSE 'Not Val' END FROM test;
COLLECT_SET:
This will collect all values in columnC and select the first index.
SELECT COLLECT_SET(columnC)[0] FROM test;
CURRENT_DATE:
This will give you the current date in the format YYYY-MM-dd.
SELECT CURRENT_DATE();
UNIX_TIMESTAMP:
This will give you the current timestamp from EPOCH. IE: 1549591492
SELECT UNIX_TIMESTAMP();
FROM_UNIXTIME:
This will take a timestamp and display it in the format YYYY-MM-dd HH:MM:SS.
SELECT FROM_UNIXTIME(1549591492);
TO_DATE:
This will convert a date to YYYY-MM-dd.
SELECT TO_DATE('2019-02-01 01:01:01');
YEAR:
SELECT YEAR(columnE) FROM test;
MONTH:
SELECT MONTH(columnE) FROM test;
DAY:
SELECT DAY(columnE) FROM test;
DATE_ADD:
SELECT DATE_ADD('2019-01-01', 4);
UPPER:
This will upper case columnA.
SELECT UPPER(columnA) FROM test;
LOWER:
This will lower case columnA.
SELECT LOWER(columnA) FROM test;
TRIM:
This will trim leading and trailing spaces in columnA.
SELECT TRIM(columnA) 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.
SELECT columnA AS `test column` FROM test;