将其组成俱乐部多列值，以蜂巢为一列

Question

我试图根据基于键的组将多列值击中一个列。基本上，我将使用它使用SPARK 1.6 DataFrame API创建嵌套JSON。

样品输入表ABC： -

a     b     c       d       e       f       g
---------------------------------------------
aa    bb    cc      dd      ee      ff      gg
aa    bb    cc1     dd1     ee1     ff1     gg1
aa    bb    cc2     dd2     ee2     ff2     gg2
aa1   bb1   cc3     dd3     ee3     ff3     gg3
aa1   bb1   cc4     dd4     ee4     ff4     gg4

最终输出组由a，b： -

aa      bb      {{cc,dd,ee,ff,gg},{cc1,dd1,ee1,ff1,gg1},{cc2,dd2,ee2,ff2,gg2}}
aa1     bb1     {{cc3,dd3,ee3,ff3,gg3},{cc4,dd4,ee4,ff4,gg4}}

我尝试使用collect_list，但它只能将一列分组。不知道如何将多列分组在一起。我尝试使用Concat String，然后在上面使用Collect，但是我会放松模式映射，因为我必须最终以JSON格式转储它。以地图或结构的形式与其结合也将起作用。请针对此问题提出一些优雅的方法/解决方案。谢谢注意：使用Spark 1.6

Answer 1

两个查询都与sqlContext.sql ("select ...");

一起使用

---

select      a,b
           ,collect_list(array(c,d,e,f,g))
from        abc
group by    a,b
;

---

+-----+-----+----------------------------------------------------------------------------------------------+
| aa  | bb  | [["cc","dd","ee","ff","gg"],["cc1","dd1","ee1","ff1","gg1"],["cc2","dd2","ee2","ff2","gg2"]] |
+-----+-----+----------------------------------------------------------------------------------------------+
| aa1 | bb1 | [["cc3","dd3","ee3","ff3","gg3"],["cc4","dd4","ee4","ff4","gg4"]]                            |
+-----+-----+----------------------------------------------------------------------------------------------+

---

select      a,b
           ,collect_list(struct(c,d,e,f,g))
from        abc
group by    a,b
;

---

+-----+-----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aa  | bb  | [{"col1":"cc","col2":"dd","col3":"ee","col4":"ff","col5":"gg"},{"col1":"cc1","col2":"dd1","col3":"ee1","col4":"ff1","col5":"gg1"},{"col1":"cc2","col2":"dd2","col3":"ee2","col4":"ff2","col5":"gg2"}] |
+-----+-----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aa1 | bb1 | [{"col1":"cc3","col2":"dd3","col3":"ee3","col4":"ff3","col5":"gg3"},{"col1":"cc4","col2":"dd4","col3":"ee4","col4":"ff4","col5":"gg4"}]                                                               |
+-----+-----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

spark demo

[cloudera@quickstart ~]$ spark-shell --version
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _ / _ / _ `/ __/  '_/
   /___/ .__/_,_/_/ /_/_   version 1.6.0
      /_/
Type --help for more information.
[cloudera@quickstart ~]$ 

---

[cloudera@quickstart ~]$ spark-shell
scala> sqlContext.sql("select * from abc").show;
+---+---+---+---+---+---+---+
|  a|  b|  c|  d|  e|  f|  g|
+---+---+---+---+---+---+---+
| aa| bb| cc| dd| ee| ff| gg|
| aa| bb|cc1|dd1|ee1|ff1|gg1|
| aa| bb|cc2|dd2|ee2|ff2|gg2|
|aa1|bb1|cc3|dd3|ee3|ff3|gg3|
|aa1|bb1|cc4|dd4|ee4|ff4|gg4|
+---+---+---+---+---+---+---+

scala> sqlContext.sql("select a,b,collect_list(array(c,d,e,f,g)) from abc group by a,b").show;
+---+---+--------------------+                                                  
|  a|  b|                 _c2|
+---+---+--------------------+
|aa1|bb1|[[cc3, dd3, ee3, ...|
| aa| bb|[[cc, dd, ee, ff,...|
+---+---+--------------------+

scala> sqlContext.sql("select a,b,collect_list(struct(c,d,e,f,g)) from abc group by a,b").show;
+---+---+--------------------+                                                  
|  a|  b|                 _c2|
+---+---+--------------------+
|aa1|bb1|[[cc3,dd3,ee3,ff3...|
| aa| bb|[[cc,dd,ee,ff,gg]...|
+---+---+--------------------+