SQL Tips for Hive and Trino(Presto)

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

• SQL Tips for Hive and Trino(Presto)
  • Use of PIVOT and UNPIVOT
  • Standard SQL
  • Hive
  • Trino(Presto)
  • Getting Data from Multiple Databases
    • Example Databases and Tables
    • Use of Fully Qualified Table Names
  • Treasure Data Native Data Types

Use of PIVOT and UNPIVOT

You can use the PIVOT and UNPIVOT operators in standard SQL, Hive, and Trino(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

Trino(Presto)

The following examples show how you can use Trino(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

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
  )

Getting Data from Multiple Databases

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

Example Databases and Tables

• Example of animals table in zoo_a database

• Example of emp animals in zoo_b database

Use of Fully Qualified Table Names

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

Treasure Data Native Data Types

Because Treasure Data supports so many different sources and targets of data, Treasure Data uses a specific set of primitive data types native to our platform.

TD processes queries using different processing engines (Trino(Presto) and Hive). Each engine has its own data type system, and the TD native data types map to types available in the query engine.

When read from and written to the underlying storage layer in TD, which uses MessagePack mpc1 format, these TD types map to MessagePack's formats as well.

Schema relation

Our storage stores data as MessagePack mpc1 format. They support INT as:

For example, the Hive query result for ResultWorker a float data type becomes a double data type.