hadoop - transformar - transponer tabla dinamica

¿Cómo transponer/pivotar datos en colmena?

Question

Sé que no hay una forma directa de transponer los datos en la colmena. Seguí esta pregunta: ¿Hay alguna forma de transponer datos en Hive? , pero como no hay una respuesta final allí, no se pudo obtener todo el camino.

Esta es la mesa que tengo:

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

Aquí Proc1 puede tener cualquier número de valores. ID, Code & Proc1 juntos forman una clave única para esta tabla. Quiero hacer pivotar / transponer esta tabla para que cada valor único en Proc1 se convierta en una nueva columna, y el valor correspondiente de Proc2 sea el valor en esa columna para la fila correspondiente. En esencia, estoy tratando de conseguir algo como:

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

En la nueva tabla transformada, el ID y el código son la única clave principal. Del boleto que mencioné anteriormente, podría llegar tan lejos usando el UDAF to_map. (Descargo de responsabilidad: esto puede no ser un paso en la dirección correcta, pero mencionando aquí, si lo es)

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

Pero no sé cómo pasar de este paso a la tabla dinámica / transpuesta que quiero. Cualquier ayuda sobre cómo proceder será genial! Gracias.

Answer 1

A continuación es también una forma de pivote

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;

Answer 2

Aquí está la solución que terminé usando:

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;

El UDF to_map se usó del repositorio de brickhouse: https://github.com/klout/brickhouse

Answer 3

En caso de valor numérico, puede utilizar la siguiente consulta de sección:

Data de muestra

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.

Answer 4

Este es el enfoque que utilicé para resolver este problema utilizando la función UDF interna 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" y "map" son hive udf y "collect_list" es un udaf de hive.

Answer 5

No he escrito este código, pero creo que puede usar algunas de las UDF proporcionadas por klouts brickhouse: https://github.com/klout/brickhouse

Específicamente, podría hacer algo como usar su recopilación como se menciona aquí: http://brickhouseconfessions.wordpress.com/2013/03/05/use-collect-to-avoid-the-self-join/

y luego haga explotar los arreglos (tendrán una longitud diferente) utilizando los métodos detallados en este post http://brickhouseconfessions.wordpress.com/2013/03/07/exploding-multiple-arrays-at-the-same-time-with-numeric_ra

Answer 6

Otra solución más.

Pivote usando la función Hivemall to_map .

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

Answer 7

Para Unpivot, simplemente podemos usar la lógica inferior.

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

Answer 8

Puede usar declaraciones de casos y alguna ayuda de collect_set para lograr esto. Usted puede ver esto. Puede consultar la respuesta detallada en: http://www.analyticshut.com/big-data/hive/pivot-rows-to-columns-in-hive/

Aquí está la consulta de referencia,

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;