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

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

    Use ANSI SQL syntax for arrays

    Arrays are indexed starting from 1, not from 0:

    1. FROM ...

    Construct arrays with ANSI syntax:

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

    1. SELECT *
    2. FROM "7day_active"

    Use the standard string concatenation operator

    Use the ANSI SQL string concatenation operator:

    1. SELECT a || b || c
    2. FROM ...

    The following standard types are supported for CAST targets:

    1. SELECT
    2.   CAST(x AS varchar)
    4. , CAST(x AS double)
    5. , CAST(x AS boolean)
    6. FROM ...

    In particular, use VARCHAR instead of STRING.

    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:

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

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

    Hive query:

    1. SELECT student, score
    2. FROM tests
    3. LATERAL VIEW explode(scores) t AS score;

    Presto query:

    1. SELECT student, score
    2. FROM tests
    3. CROSS JOIN UNNEST(scores) AS t (score);