Databricks PySpark加载XML返回Null，如何正确定义Schema？

Hey there, let's dig into why your XML parsing is returning all nulls and fix this up properly!

First, let's break down the core issue here. The Stack Exchange Badges.xml file has a specific structure: a root <badges> tag containing multiple <row> child tags, where all the actual data lives in attributes of each <row> (not nested elements). Your initial code missed a couple critical configuration options, and relying on automatic schema inference (especially in Spark 2.2.1) can fail with attribute-heavy XML.

Step 1: Understand the XML Structure #

Here's a snippet of what your Badges.xml looks like:

Each <row> is a single record, and all fields are stored as attributes of this tag.

Step 2: Define the Explicit Schema #

Spark 2.2.1's automatic schema inference for XML attributes isn't always reliable, so we'll define a schema that matches the attributes in the <row> tags. First, import the necessary types:

from pyspark.sql.types import StructType, StructField, IntegerType, StringType, TimestampType

Then create the schema matching each attribute's name and data type:

badges_schema = StructType([
    StructField("Id", IntegerType(), nullable=True),
    StructField("Name", StringType(), nullable=True),
    StructField("UserId", IntegerType(), nullable=True),
    StructField("Date", TimestampType(), nullable=True),
    StructField("Class", IntegerType(), nullable=True),
    StructField("TagBased", StringType(), nullable=True) # Use BooleanType() if your Spark version can parse "True"/"False" directly
])

Step 3: Update the XML Read Configuration #

You need to tell the spark-xml library two key things:

• The rootTag is <badges> (you had this right)
• The rowTag is <row> (this was missing before—without it, Spark treats the entire <badges> tag as a single row with no data)

Putting it all together, your read code should look like this:

xml_posts = spark.read.format("xml") \
    .option("rootTag", "badges") \
    .option("rowTag", "row") \
    .schema(badges_schema) \
    .load('s3a://%s:%s@%s/Badges.xml'% (ACCESS_KEY, ENCODED_SECRET_KEY, BUCKET_NAME))

Step 4: Verify the Results #

Now run your print and show commands again:

xml_posts.printSchema()
xml_posts.show(5)

You should see the correct schema populated with actual data instead of nulls.

Quick Additional Notes #

• Double-check that your ENCODED_SECRET_KEY is URL-encoded (S3 secrets often have special characters like / or + that need encoding)
• Make sure you're using a version of the spark-xml library compatible with Spark 2.2.1 (e.g., spark-xml_2.11-0.4.1 works well for this version)
• If you want TagBased as a boolean instead of a string, you can switch to BooleanType()—just confirm your Spark version can parse the "True"/"False" string values correctly. If not, read it as a string first and convert it with withColumn.

内容的提问来源于stack exchange，提问作者Kieran White