[Solved-2 Solutions] Manipulating a data structure in Pig/Hive ?
Problem:
We have a data structure like this:
| Item | Year | Jan | Feb | Mar |
|---|---|---|---|---|
| A | 2000 | 3.2 | 4.1 | 2.8 |
| B | 2000 | 4.3 | 5.0 | 3.9 |
| A | 2001 | 7.2 | 6.8 | 9.2 |
We need the data look like this:
| Item | Year | Month | Value |
|---|---|---|---|
| A | 2000 | Jan | 3.2 |
| A | 2000 | Feb | 4.1 |
| A | 2000 | Mar | 2.8 |
| B | 2000 | Jan | 4.3 |
| B | 2000 | Feb | 5.0 |
| B | 2000 | Mar | 3.9 |
| A | 2001 | Jan | 7.2 |
| A | 2001 | Feb | 6.8 |
| A | 2001 | Mar | 9.2 |
Here we would like to be able to do this in Pig, but knowing how to do it in Hive, R, Python, or Excel/LibreCalc ?
Solution 1:
You can try this:
CREATE TABLE myDatabase.newTable STORED AS TEXTFILE AS
SELECT item, year, 'jan' AS Month, jan AS Value FROM myDatabase.myTable UNION ALL
SELECT item, year, 'feb' AS Month, feb AS Value FROM myDatabase.myTable UNION ALL
SELECT item, year, 'mar' AS Month, mar AS Value FROM myDatabase.myTable;Solution 2:
This solution will work in Hive. but it is pretty similar to SQL.
select item, year,
'Jan' as Month,
Jan as value
from yourtable
UNION
select item, year,
'Feb' as Month,
Feb as value
from yourtable
UNION
select item, year,
'Mar' as Month,
Mar as value
from yourtable