SQL Tips for Hive and Presto

For tips specifically concerning Presto performance, refer to Presto Performance Tuning.

• Use of PIVOT / UNPIVOT
  • Standard SQL
  • Hive
  • Presto
• Getting Data from Multiple Databases
  • Example Databases and Tables
  • Use of Fully Qualified Table Names

Use of PIVOT and UNPIVOT

You can use the PIVOT and UNPIVOT operators in standard SQL, Hive, and Presto.

The PIVOT operator transforms rows into columns. The UNPIVOT operator transforms columns into rows.

Example Tables

Example of vertical table (vtable)

Example of horizontal table (htable)

Standard SQL

The following examples show how you can use an SQL syntax query language.

PIVOT

SELECT 
  uid,
  kv['c1'] AS c1,
  kv['c2'] AS c2,
  kv['c3'] AS c3
FROM (
    SELECT 
      uid,
      to_map(
        KEY,
        VALUE
      ) kv
    FROM
      vtable
    GROUP BY
      uid
  ) t

UNPIVOT

SELECT uid, 'c1' AS key, c1 AS value FROM htable
UNION ALL
SELECT uid, 'c2' AS key, c2 AS value FROM htable
UNION ALL
SELECT uid, 'c3' AS key, c3 AS value FROM htable

Hive

The following examples show how you can use Hive.

PIVOT

Hive on Treasure Data supports to_map UDAF, which can generate Map type, and then transforms rows into columns. The general Hive function doesn’t offer the same support.

SELECT
  uid,
  kv['c1'] AS c1,
  kv['c2'] AS c2,
  kv['c3'] AS c3
FROM (
  SELECT uid, to_map(key, value) kv
  FROM vtable
  GROUP BY uid
) t

UNPIVOT

LATERAL VIEW explode function transforms columns into rows.

SELECT 
  t1.uid,
  t2.key,
  t2.value
FROM
  htable t1
LATERAL VIEW
  explode(
    MAP(
      'c1',
      c1,
      'c2',
      c2,
      'c3',
      c3
    )
  ) t2 AS KEY,
  VALUE

Presto

The following examples show how you can use Presto.

PIVOT

This SQL transforms rows into columns by map_agg function.

SELECT 
  uid,
  kv['c1'] AS c1,
  kv['c2'] AS c2,
  kv['c3'] AS c3
FROM (
    SELECT 
      uid,
      map_agg(
        KEY,
        VALUE
      ) kv
    FROM
      vtable
    GROUP BY
      uid
  ) t