SQL Tips for Hive and Trino

For query engine specific performance tuning tips, refer to Hive Performance Tuning and Trino Performance Tuning.

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

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)

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

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

The following examples show how you can use Trino.

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

UNPIVOT

CROSS JOIN unnest function is similar to LATERAL VIEW explode function. It also transforms columns into rows.

SELECT
  t1.uid,
  t2.key,
  t2.value
FROM
  htable t1 CROSS
JOIN
  unnest(
    array['c1',
    'c2',
    'c3'],
    array[c1,
    c2,
    c3]
  ) t2(
    KEY,
    VALUE
  )

From within in Hive and Trino, you can create a single query to obtain data from several databases or analyze data in different databases.

• Example of animals table in zoo_a database

• Example of emp animals in zoo_b database

If you use the fully qualified name of a table in the format database.table, you can query and process data from multiple databases.

SELECT
  name,
  sex
FROM
  zoo_a.animals
UNION
SELECT
name,
sex
FROM
zoo_b.animals