17.1. Migrating From Hive
Presto uses ANSI SQL syntax and semantics, whereas Hive uses a SQL-like language called HiveQL which is loosely modeled after MySQL (which itself has many differences from ANSI SQL).
Use subscript for accessing a dynamic index of an array instead of a udf
The subscript operator in SQL supports full expressions, unlike Hive (which only supports constants). Therefore you can write queries like:
SELECT my_array[CARDINALITY(my_array)] as last_element FROM ...
Avoid out of bounds access of arrays
Accessing out of bounds elements of an array will result in an exception. You can avoid this with an
if as follows:
SELECT IF(CARDINALITY(my_array) >= 3, my_array, NULL) FROM ...
Use ANSI SQL syntax for arrays
Arrays are indexed starting from 1, not from 0:
SELECT my_array AS first_element FROM ...
Construct arrays with ANSI syntax:
SELECT ARRAY[1, 2, 3] AS my_array
Use ANSI SQL syntax for identifiers and strings
Strings are delimited with single quotes and identifiers are quoted with double quotes, not backquotes:
SELECT name AS "User Name" FROM "7day_active" WHERE name = 'foo'
Quote identifiers that start with numbers
Identifiers that start with numbers are not legal in ANSI SQL and must be quoted using double quotes:
SELECT * FROM "7day_active"
Use the standard string concatenation operator
Use the ANSI SQL string concatenation operator:
SELECT a || b || c FROM ...
Use standard types for CAST targets
The following standard types are supported for
SELECT CAST(x AS varchar) , CAST(x AS bigint) , CAST(x AS double) , CAST(x AS boolean) FROM ...
In particular, use
VARCHAR instead of
Use CAST when dividing integers
Presto follows the standard behavior of performing integer division when dividing two integers. For example, dividing
2 will result in
To perform floating point division on two integers, cast one of them to a double:
SELECT CAST(5 AS DOUBLE) / 2
Use WITH for complex expressions or queries
When you want to re-use a complex output expression as a filter, use either an inline subquery or factor it out using the
WITH a AS ( SELECT substr(name, 1, 3) x FROM ... ) SELECT * FROM a WHERE x = 'foo'
Use UNNEST to expand arrays and maps
Presto supports UNNEST for expanding arrays and maps.
UNNEST instead of
LATERAL VIEW explode().
SELECT student, score FROM tests LATERAL VIEW explode(scores) t AS score;
SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t (score);