How to query Vertical Column of Hive table in Horizontal output

user_13911667 0
Viewed 310

I have a table contains data vertically (attr_name and attr_value columns) like below but I want to print the data vertically. Is it possible in Hive?

+------------------------+--------+--------+---------------+------------+
|relation                |object  |instance|attr_name      |attr_value  |
+------------------------+--------+--------+---------------+------------+
|Summary~>Disk~>Disk-1   |Disk    |Disk-1  |Size_MB        |7726        |
|Summary~>Disk~>Disk-1   |Disk    |Disk-1  |Write_MB       |694         |
|Summary~>Disk~>Disk-1   |Disk    |Disk-1  |Time_Pct       |4           |
|Summary~>Disk~>Disk-1   |Disk    |Disk-1  |Disk           |DISK0       |
|Summary~>Disk~>Disk-2   |Disk    |Disk-2  |Size_MB        |476937      |
|Summary~>Disk~>Disk-2   |Disk    |Disk-2  |Write_MB       |0           |
|Summary~>Disk~>Disk-2   |Disk    |Disk-2  |Time_Pct       |4           |
|Summary~>Disk~>Disk-2   |Disk    |Disk-2  |Disk           |DISK1       |
+------------------------+--------+--------+---------------+------------+

I can do a normal query to get attr_value of a attr_name. But I want to get all the output rows which has relation with same instance.

like I want to get all the attr_name and attr_value group by instance where attr_value='DISK1', while query reference will be attr_value only.

If I query like select relation, all attr_name as column, all attr_value as value from table name where attr_value IN (DISK1) for the related instance. for this query below should be output. I don't want to group by instance because query needs to be based upon attr_value.

Can I get this value?

+------------------------+--------+--------+----------+----------+---------+------+
|relation                |object  |instance|Size_MB   |Write_MB  |Time_Pct |DISK  | 
+------------------------+--------+--------+----------+----------+---------+------+
|Summary~>Disk~>Disk-2   |Disk    |Disk-2  |476937    |0         |4        |DISK1 |
+------------------------+--------+--------+----------+----------+---------+------+
hive vertical-alignment hql sql
1 Answers

You can use conditional aggregation:

select relation, object, instance,
       max(case when attr_name = 'Size_MB' then attr_value end) as Size_MB,
       max(case when attr_name = 'Write_MB' then attr_value end) as Write_MB,
       max(case when attr_name = 'Time_Pct' then attr_value end) as Time_Pct,
       max(case when attr_name = 'Disk' then attr_value end) as Disk
from t
group by relation, object, instance;

Add a where clause before the group by if you want to filter for a particular object or instance.

Note that this has a fixed number of columns that you need to specify explicitly. If you have a variable number of columns and do not know what the result looks like, then you would need to use dynamic SQL.

In Hive, this would typically mean using an application language (such as python or java or whatever) to construct the query in a string, by reading the columns from the data).

user_11440350
Related