如何从具有属性的多个嵌套 XML 文件转换以激发数据框数据 [英] How to transform to spark Data Frame data from multiple nested XML files with attributes

问题描述

如何将下面的值从多个 XML 文件转换为火花数据框:

How to transform values below from multiple XML files to spark data frame :

• 属性 Id0 来自 Level_0
• 日期/值来自Level_4

• attribute Id0 from Level_0
• Date/Value from Level_4

所需输出:

+----------------+-------------+---------+
|Id0             |Date         |Value    |
+----------------+-------------+---------+
|Id0_value_file_1|  2021-01-01 |   4_1   |
|Id0_value_file_1|  2021-01-02 |   4_2   |
|Id0_value_file_2|  2021-01-01 |   4_1   |
|Id0_value_file_2|  2021-01-02 |   4_2   |
+----------------+-------+---------------+

file_1.xml:

file_1.xml:

<Level_0 Id0="Id0_value_file1">
  <Level_1 Id1_1 ="Id3_value" Id_2="Id2_value">
    <Level_2_A>A</Level_2_A>
    <Level_2>
      <Level_3>
        <Level_4>
          <Date>2021-01-01</Date>
          <Value>4_1</Value>
        </Level_4>
        <Level_4>
          <Date>2021-01-02</Date>
          <Value>4_2</Value>
        </Level_4>
      </Level_3>
    </Level_2>
  </Level_1>
</Level_0>

file_2.xml:

file_2.xml:

<Level_0 Id0="Id0_value_file2">
  <Level_1 Id1_1 ="Id3_value" Id_2="Id2_value">
    <Level_2_A>A</Level_2_A>
    <Level_2>
      <Level_3>
        <Level_4>
          <Date>2021-01-01</Date>
          <Value>4_1</Value>
        </Level_4>
        <Level_4>
          <Date>2021-01-02</Date>
          <Value>4_2</Value>
        </Level_4>
      </Level_3>
    </Level_2>
  </Level_1>
</Level_0>

当前代码示例:

files_list = ["file_1.xml", "file_2.xml"]
df = (spark.read.format('xml')
           .options(rowTag="Level_4")
           .load(','.join(files_list))

当前输出:(Id0 列缺少属性)

Current Output:(Id0 column with attributes missing)

+-------------+---------+
|Date         |Value    |
+-------------+---------+
|  2021-01-01 |     4_1 |
|  2021-01-02 |     4_2 |
|  2021-01-01 |     4_1 |
|  2021-01-02 |     4_2 |
+-------+---------------+

有一些例子，但没有一个能解决问题:-我正在使用数据块 spark_xml - https://github.com/databricks/spark-xml- 有一个示例，但没有属性读取，在 spark 中读取 XML，使用sparkxml从xml中提取标签属性 .

There are some examples, but non of them solve the problem: -I'm using databricks spark_xml - https://github.com/databricks/spark-xml -There is an examample but not with attribute reading, Read XML in spark, Extracting tag attributes from xml using sparkxml .

正如@mck 正确指出的那样 A 不是正确的 XML 格式.我的示例中有一个错误(现在 xml 文件已更正)，它应该是 A.之后，建议的解决方案甚至适用于多个文件.

As @mck pointed out correctly <Level_2>A</Level_2> is not correct XML format. I had a mistake in my example(now xml file is corrected), it should be <Level_2_A>A</Level_2_A>. After that , proposed solution works even on multiple files.

注意:为了加速加载大量 xml 定义架构，如果没有定义架构，火花在创建数据帧时会读取每个文件以干扰架构...更多信息:https://szczeles.github.io/Reading-JSON-CSV-and-XML-files-efficiently-in-Apache-Spark/

NOTE: To speedup loading of large number of xmls define schema, if no schema is defined spark is reading each file when creating dataframe to interfere schema... for more info: https://szczeles.github.io/Reading-JSON-CSV-and-XML-files-efficiently-in-Apache-Spark/

步骤 1):

 files_list = ["file_1.xml", "file_2.xml"]
 # for schema seem NOTE above

 df = (spark.read.format('xml')
               .options(rowTag="Level_0")
               .load(','.join(files_list),schema=schema))
df.printSchema()

root
 |-- Level_1: struct (nullable = true)
 |    |-- Level_2: struct (nullable = true)
 |    |    |-- Level_3: struct (nullable = true)
 |    |    |    |-- Level_4: array (nullable = true)
 |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |-- Date: string (nullable = true)
 |    |    |    |    |    |-- Value: string (nullable = true)
 |    |-- Level_2_A: string (nullable = true)
 |    |-- _Id1_1: string (nullable = true)
 |    |-- _Id_2: string (nullable = true)
 |-- _Id0: string (nullable = true

第 2 步)见下方@mck 解决方案:

STEP 2) see below @mck solution:

推荐答案

您可以使用 Level_0 作为 rowTag，并分解相关的数组/结构:

You can use Level_0 as the rowTag, and explode the relevant arrays/structs:

import pyspark.sql.functions as F

df = spark.read.format('xml').options(rowTag="Level_0").load('line_removed.xml')

df2 = df.select(
    '_Id0', 
    F.explode_outer('Level_1.Level_2.Level_3.Level_4').alias('Level_4')
).select(
    '_Id0',
    'Level_4.*'
)

df2.show()
+---------------+----------+-----+
|           _Id0|      Date|Value|
+---------------+----------+-----+
|Id0_value_file1|2021-01-01|  4_1|
|Id0_value_file1|2021-01-02|  4_2|
+---------------+----------+-----+

这篇关于如何从具有属性的多个嵌套 XML 文件转换以激发数据框数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！