The Hive query engine has some limitations compared to Hive at this stage. Arm Treasure Data welcomes your feedback and will make improvements based on these.
When two values (of any type) are divided in Hive the result of the division is always automatically upgraded to ‘double’.
To obtain an integer result from a division please cast the result of the division to the desired type. For example:
SELECT CAST((column1 / column2) AS bigint) as division FROM table
|The Presto engine behaves differently with divisions and does not automatically upgrades the result to double.|
Divide By 0
When the dividend of a division is 0, the result will be a ‘NaN’.
This can commonly happen when the dividing the values of 2 columns and the value of the column used as divided is a 0. For example:
SELECT column1, column2, column3 / column4 as division FROM table
Trim Double-Byte Space
TRIM function with Hive can’t handle double-byte space. For example:
SELECT TRIM(' a') as value_keyword FROM table => ' a' (Expected result is 'a')
As a workflow, please use regexp_replace
SELECT regexp_replace(TRIM(' a '), '^ +| +$', '') as value_keyword FROM table