Comment transposer / pivoter des données dans hive?

je sais qu'il n'y a aucun moyen direct de transposer des données dans hive. J'ai suivi cette question: Existe-t-il un moyen de transposer les données dans Hive?, mais comme il n'y a pas de réponse définitive là-bas, ne pouvait pas obtenir tout le chemin.

C'est la table que j'ai:

 | ID   |   Code   |  Proc1   |   Proc2 | 
 | 1    |    A     |   p      |   e     | 
 | 2    |    B     |   q      |   f     |
 | 3    |    B     |   p      |   f     |
 | 3    |    B     |   q      |   h     |
 | 3    |    B     |   r      |   j     |
 | 3    |    C     |   t      |   k     |

ici Proc1 peut avoir n'importe quel nombre de valeurs. ID, Code & Proc1 forment ensemble une clé unique pour cette table. Je veux pivoter/ transposer cette table de sorte que chaque valeur unique dans Proc1 devienne une nouvelle colonne, et valeur correspondante de Proc2 est la valeur dans la colonne concernée de la ligne correspondante. En essence, je vais essayer d'obtenir quelque chose comme:

 | ID   |   Code   |  p   |   q |  r  |   t |
 | 1    |    A     |   e  |     |     |     |
 | 2    |    B     |      |   f |     |     |
 | 3    |    B     |   f  |   h |  j  |     |
 | 3    |    C     |      |     |     |  k  |

dans la nouvelle table transformée, ID et code sont les seules clés primaires. D'après le billet que j'ai mentionné ci-dessus, je pourrais aller aussi loin en utilisant l'UDAF de to_map. (Avertissement - ce ne peut être une étape dans la bonne direction, mais le simple fait de mentionner ici, si c'est)

 | ID   |   Code   |  Map_Aggregation   | 
 | 1    |    A     |   {p:e}            |
 | 2    |    B     |   {q:f}            |
 | 3    |    B     |   {p:f, q:h, r:j } |  
 | 3    |    C     |   {t:k}            |

mais ne sait pas comment passer de cette étape au pivot / transposé le tableau que je veux. Toute aide sur la façon de procéder sera grande! Grâce.

16
demandé sur Community 2014-04-12 06:25:13

8 réponses

Voici l'approche que j'ai utilisée pour résoudre ce problème en utilisant la fonction UDF interne de hive, "map":

select
    b.id,
    b.code,
    concat_ws('',b.p) as p,
    concat_ws('',b.q) as q,
    concat_ws('',b.r) as r,
    concat_ws('',b.t) as t
from 
    (
        select id, code,
        collect_list(a.group_map['p']) as p,
        collect_list(a.group_map['q']) as q,
        collect_list(a.group_map['r']) as r,
        collect_list(a.group_map['t']) as t
        from (
            select
              id,
              code,
              map(proc1,proc2) as group_map 
            from 
              test_sample
        ) a
        group by
            a.id,
            a.code
    ) b;

"concat_ws" et "map" sont ruche udf et "collect_list" est une ruche à l'udaf, nous.

10
répondu Shakti Garg 2017-03-22 19:47:47

Voici la solution que j'ai finalement utilisée:

add jar brickhouse-0.7.0-SNAPSHOT.jar;
CREATE TEMPORARY FUNCTION collect AS 'brickhouse.udf.collect.CollectUDAF';

select 
    id, 
    code,
    group_map['p'] as p,
    group_map['q'] as q,
    group_map['r'] as r,
    group_map['t'] as t
    from ( select
        id, code,
        collect(proc1,proc2) as group_map 
        from test_sample 
        group by id, code
    ) gm;

Le to_map UDF a été utilisé à partir de la brickhouse repo: https://github.com/klout/brickhouse

5
répondu Sunny 2014-07-01 17:21:34

Encore une autre solution.

Pivot en utilisant Hivemallto_map fonction.

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

4
répondu myui 2016-03-30 01:28:37

Je n'ai pas écrit ce code, mais je pense que vous pouvez utiliser certains UDFs fournis par klouts brickhouse: https://github.com/klout/brickhouse

spécifiquement, vous pouvez faire quelque chose comme utiliser leur collection comme mentionné ici: http://brickhouseconfessions.wordpress.com/2013/03/05/use-collect-to-avoid-the-self-join/

et ensuite faire exploser les matrices (elles seront de longueur différente) en utilisant les méthodes détaillées dans ce post http://brickhouseconfessions.wordpress.com/2013/03/07/exploding-multiple-arrays-at-the-same-time-with-numeric_ra

1
répondu user2726995 2014-04-14 19:12:06

en cas de valeur numérique, vous pouvez utiliser la requête ci-dessous:

données D'échantillon

ID  cust_freq   Var1    Var2    frequency
220444  1   16443   87128   72.10140547
312554  6   984 7339    0.342452643
220444  3   6201    87128   9.258396518
220444  6   47779   87128   2.831972441
312554  1   6055    7339    82.15209213
312554  3   12868   7339    4.478333954
220444  2   6705    87128   15.80822558
312554  2   37432   7339    13.02712127

select id, sum(a.group_map[1]) as One, sum(a.group_map[2]) as Two, sum(a.group_map[3]) as Three, sum(a.group_map[6]) as Six from
( select id, 
 map(cust_freq,frequency) as group_map 
 from table
 ) a group by a.id having id in 
( '220444',
'312554');

ID  one two three   six
220444  72.10140547 15.80822558 9.258396518 2.831972441
312554  82.15209213 13.02712127 4.478333954 0.342452643

In above example I have't used any custom udf. It is only using in-built hive functions.
Note :For string value in key write the vale as sum(a.group_map['1']) as One.
0
répondu Suman 2017-09-12 01:19:57

pour Unpivot, nous pouvons simplement utiliser la logique ci-dessous.

SELECT Cost.Code, Cost.Product, Cost.Size
, Cost.State_code, Cost.Promo_date, Cost.Cost, Sales.Price
FROM
(Select Code, Product, Size, State_code, Promo_date, Price as Cost
FROM Product
Where Description = 'Cost') Cost
JOIN
(Select Code, Product, Size, State_code, Promo_date, Price as Price
FROM Product
Where Description = 'Sales') Sales
on (Cost.Code = Sales.Code
and Cost.Promo_date = Sales.Promo_date);
0
répondu Anjib Rajkhowa 2018-02-19 11:16:14

ci-Dessous est aussi une façon de Pivot

SELECT TM1_Code, Product, Size, State_code, Description
  , Promo_date
  , Price
FROM (
SELECT TM1_Code, Product, Size, State_code, Description
   , MAP('FY2018Jan', FY2018Jan, 'FY2018Feb', FY2018Feb, 'FY2018Mar', FY2018Mar, 'FY2018Apr', FY2018Apr
        ,'FY2018May', FY2018May, 'FY2018Jun', FY2018Jun, 'FY2018Jul', FY2018Jul, 'FY2018Aug', FY2018Aug
        ,'FY2018Sep', FY2018Sep, 'FY2018Oct', FY2018Oct, 'FY2018Nov', FY2018Nov, 'FY2018Dec', FY2018Dec) AS tmp_column
FROM CS_ME_Spirits_30012018) TmpTbl
LATERAL VIEW EXPLODE(tmp_column) exptbl AS Promo_date, Price;
0
répondu Anjib Rajkhowa 2018-02-19 12:23:03

Vous pouvez utiliser des énoncés de cas et de l'aide de collect_set pour y arriver. Vous pouvez vérifier cela. Vous pouvez vérifier les détails de répondre à http://www.analyticshut.com/big-data/hive/pivot-rows-to-columns-in-hive/

Voici la requête de renvoi,

SELECT resource_id,
CASE WHEN COLLECT_SET(quarter_1)[0] IS NULL THEN 0 ELSE COLLECT_SET(quarter_1)[0] END AS quarter_1_spends,
CASE WHEN COLLECT_SET(quarter_2)[0] IS NULL THEN 0 ELSE COLLECT_SET(quarter_2)[0] END AS quarter_2_spends,
CASE WHEN COLLECT_SET(quarter_3)[0] IS NULL THEN 0 ELSE COLLECT_SET(quarter_3)[0] END AS quarter_3_spends,
CASE WHEN COLLECT_SET(quarter_4)[0] IS NULL THEN 0 ELSE COLLECT_SET(quarter_4)[0] END AS quarter_4_spends
FROM (
SELECT resource_id,
CASE WHEN quarter='Q1' THEN amount END AS quarter_1,
CASE WHEN quarter='Q2' THEN amount END AS quarter_2,
CASE WHEN quarter='Q3' THEN amount END AS quarter_3,
CASE WHEN quarter='Q4' THEN amount END AS quarter_4
FROM billing_info)tbl1
GROUP BY resource_id;
0
répondu Mahesh Mogal 2018-06-17 12:47:33