# SQL Tips for Hive and Trino

For query engine specific performance tuning tips, refer to [Hive Performance Tuning](/products/customer-data-platform/data-workbench/queries/hive/performance_tuning) and [Trino Performance Tuning](/products/customer-data-platform/data-workbench/queries/trino/performance_tuning).

## Use of PIVOT and UNPIVOT

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)

| **uid** | **key** | **value** |
|  --- | --- | --- |
| 101 | c1 | 11 |
| 101 | c2 | 12 |
| 101 | c3 | 13 |
| 102 | c1 | 21 |
| 102 | c2 | 22 |
| 102 | c3 | 23 |


Example of horizontal table (htable)

| **uid** | **c1** | **c2** | **c3** |
|  --- | --- | --- | --- |
| 101 | 11 | 12 | 13 |
| 102 | 21 | 22 | 23 |


## 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
```

| **uid** | **c1** | **c2** | **c3** |
|  --- | --- | --- | --- |
| 101 | 11 | 12 | 13 |
| 102 | 21 | 22 | 23 |


**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
```

| **uid** | **key** | **value** |
|  --- | --- | --- |
| 101 | c1 | 11 |
| 102 | c1 | 21 |
| 101 | c2 | 12 |
| 102 | c2 | 22 |
| 101 | c3 | 13 |
| 102 | c3 | 23 |


## 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
```

| **uid** | **c1** | **c2** | **c3** |
|  --- | --- | --- | --- |
| 101 | 11 | 12 | 13 |
| 102 | 21 | 22 | 23 |


**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
```

| **uid** | **key** | **value** |
|  --- | --- | --- |
| 101 | c1 | 11 |
| 101 | c2 | 12 |
| 101 | c3 | 13 |
| 102 | c1 | 21 |
| 102 | c2 | 22 |
| 102 | c3 | 23 |


## Trino

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

| **uid** | **c1** | **c2** | **c3** |
|  --- | --- | --- | --- |
| 101 | 11 | 12 | 13 |
| 102 | 21 | 22 | 23 |


**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
  )
```

| **uid** | **key** | **value** |
|  --- | --- | --- |
| 101 | c1 | 11 |
| 101 | c2 | 12 |
| 101 | c3 | 13 |
| 102 | c1 | 21 |
| 102 | c2 | 22 |
| 102 | c3 | 23 |


## Getting Data from Multiple Databases

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 Databases and Tables

- Example of animals table in zoo_a database


| **id** | **name** | **sex** |
|  --- | --- | --- |
| 1 | Lion | m |
| 2 | Gorilla | m |
| 3 | Zebra | f |
| 4 | Giraffe | f |


- Example of emp animals in zoo_b database


| **animal_id** | **name** | **sex** |
|  --- | --- | --- |
| 101 | Lion | m |
| 102 | Bear | f |
| 103 | Elephant | f |
| 104 | Gorilla | f |
| 105 | Tiger | m |
| 106 | Monkey | m |
| 107 | Rhinoceros | f |


### 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
```

| **name** | **sex** |
|  --- | --- |
| Lion | m |
| Giraffe | f |
| Bear | f |
| Monkey | m |
| Gorilla | m |
| Gorilla | f |
| Elephant | f |
| Rhinoceros | f |
| Tiger | m |
| Zebra | f |