9.1. Migrating From Hive

The subscript operator in SQL supports full expressions, unlike Hive (which only supports constants). Therefore you can write queries like:

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 as follows:

SELECT IF(CARDINALITY(my_array) >= 3, my_array[3], NULL)
FROM ...

Use ANSI SQL syntax for arrays

Arrays are indexed starting from 1, not from 0:

FROM ...

Construct arrays with ANSI syntax:

SELECT ARRAY[1, 2, 3] AS my_array

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 ...

The following standard types are supported for CAST targets:

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

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 clause:

  SELECT substr(name, 1, 3) x
  FROM ...
)
SELECT *
FROM a
WHERE x = 'foo'

Presto supports UNNEST for expanding arrays and maps.Use instead of LATERAL VIEW explode().

Hive query:

SELECT student, score
FROM tests
LATERAL VIEW explode(scores) t AS score;

Presto query:

SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score);