在火花壳中的多行中拆分行 [英] Splitting row in multiple row in spark-shell

问题描述

我在 spark-shell 的 Spark 数据框中导入了数据.数据填写如下:

I have imported data in Spark dataframe in spark-shell. Data is filled in it like :

Col1 | Col2 | Col3 | Col4
A1   | 11   | B2   | a|b;1;0xFFFFFF
A1   | 12   | B1   | 2
A2   | 12   | B2   | 0xFFF45B

在 Col4 中，值是不同类型的，我想将它们分开(假设a|b"是字母类型，1 或 2"是数字类型，0xFFFFFF 或 0xFFF45B"是十六进制数的类型):

Here in Col4, the values are of different kinds and I want to separate them like (suppose "a|b" is type of alphabets, "1 or 2" is a type of digit and "0xFFFFFF or 0xFFF45B" is a type of hexadecimal no.):

所以，输出应该是:

Col1 | Col2 | Col3 | alphabets | digits | hexadecimal
A1   | 11   | B2   | a         | 1      | 0xFFFFFF
A1   | 11   | B2   | b         | 1      | 0xFFFFFF
A1   | 12   | B1   |           | 2      | 
A2   | 12   | B2   |           |        | 0xFFF45B

希望我已经向您说明了我的疑问，并且我正在使用 spark-shell.提前致谢.

Hope I've made my query clear to you and I am using spark-shell. Thanks in advance.

推荐答案

得到这个answer后编辑关于如何制作regexp_replace 中的反向引用.

Edit after getting this answer about how to make backreference in regexp_replace.

您可以使用带有反向引用的 regexp_replace，然后 split 两次和 explode.它比我原来的解决方案更干净

You can use regexp_replace with a backreference, then split twice and explode. It is, imo, cleaner than my original solution

val df = List(
    ("A1"   , "11"   , "B2"   , "a|b;1;0xFFFFFF"),
    ("A1"   , "12"   , "B1"   , "2"),
    ("A2"   , "12"   , "B2"   , "0xFFF45B")
  ).toDF("Col1" , "Col2" , "Col3" , "Col4")

val regExStr = "^([A-z|]+)?;?(\\d+)?;?(0x.*)?$"
val res = df
  .withColumn("backrefReplace",
       split(regexp_replace('Col4,regExStr,"$1;$2;$3"),";"))
  .select('Col1,'Col2,'Col3,
       explode(split('backrefReplace(0),"\\|")).as("letter"),
       'backrefReplace(1)                      .as("digits"),
       'backrefReplace(2)                      .as("hexadecimal")
  )

+----+----+----+------+------+-----------+
|Col1|Col2|Col3|letter|digits|hexadecimal|
+----+----+----+------+------+-----------+
|  A1|  11|  B2|     a|     1|   0xFFFFFF|
|  A1|  11|  B2|     b|     1|   0xFFFFFF|
|  A1|  12|  B1|      |     2|           |
|  A2|  12|  B2|      |      |   0xFFF45B|
+----+----+----+------+------+-----------+

你仍然需要用 null 替换空字符串...

you still need to replace empty strings by nullthough...

这是一个坚持使用 DataFrames 但也很混乱的解决方案.您可以先使用 regexp_extract 三次(可以用反向引用做的更少?)，最后在|"上使用 split和爆炸.请注意，您需要对 explode 进行合并以返回所有内容(您仍然可能希望在此解决方案中将 letter 中的空字符串更改为 null).

Here is a solution that sticks to DataFrames but is also quite messy. You can first use regexp_extract three times (possible to do less with backreference?), and finally split on "|" and explode. Note that you need a coalesce for explode to return everything (you still might want to change the empty strings in letter to null in this solution).

val res = df
  .withColumn("alphabets",  regexp_extract('Col4,"(^[A-z|]+)?",1))
  .withColumn("digits",     regexp_extract('Col4,"^([A-z|]+)?;?(\\d+)?;?(0x.*)?$",2))
  .withColumn("hexadecimal",regexp_extract('Col4,"^([A-z|]+)?;?(\\d+)?;?(0x.*)?$",3))
  .withColumn("letter",
     explode(
       split(
         coalesce('alphabets,lit("")),
         "\\|"
       )
     )
   )


res.show    

+----+----+----+--------------+---------+------+-----------+------+
|Col1|Col2|Col3|          Col4|alphabets|digits|hexadecimal|letter|
+----+----+----+--------------+---------+------+-----------+------+
|  A1|  11|  B2|a|b;1;0xFFFFFF|      a|b|     1|   0xFFFFFF|     a|
|  A1|  11|  B2|a|b;1;0xFFFFFF|      a|b|     1|   0xFFFFFF|     b|
|  A1|  12|  B1|             2|     null|     2|       null|      |
|  A2|  12|  B2|      0xFFF45B|     null|  null|   0xFFF45B|      |
+----+----+----+--------------+---------+------+-----------+------+

注意:使用反向引用，regexp 部分可能会更好，所以如果有人知道怎么做，请发表评论！

Note: The regexp part could be so much better with backreference, so if somebody knows how to do it, please comment!

这篇关于在火花壳中的多行中拆分行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！