Scala Spark指定DataFrame Schema时遇重复列问题

Hey there, let's sort out this schema problem you're facing! It's clear Spark automatically inferred every column as a string type, which isn't right—columns like Weekly_Sales, Temperature should be numeric, Date needs to be a date type, and IsHoliday should be a boolean. Here's how to set the correct schema explicitly, and why this happened in the first place.

Why Did This Happen? #

Spark's automatic schema inference can get tripped up by common issues like:

• Missing values (your MarkDown columns have nulls, which makes Spark play it safe and default to string)
• Non-standard date formats that Spark can't parse automatically
• Rows with unexpected entries (e.g., a non-numeric value in a column that should be numeric)

Solution 1: Define a Custom Schema Before Reading Data #

This is the most reliable approach, as it forces Spark to use your specified types from the start. Here's how to do it in PySpark:

First, import the necessary data types:

from pyspark.sql.types import StructType, StructField, IntegerType, FloatType, DateType, BooleanType

Then define your schema to match the correct data types for each column:

custom_schema = StructType([
    StructField("Store", IntegerType(), nullable=True),
    StructField("Date", DateType(), nullable=True),
    StructField("IsHoliday", BooleanType(), nullable=True),
    StructField("Dept", IntegerType(), nullable=True),
    StructField("Weekly_Sales", FloatType(), nullable=True),
    StructField("Temperature", FloatType(), nullable=True),
    StructField("Fuel_Price", FloatType(), nullable=True),
    StructField("MarkDown1", FloatType(), nullable=True),
    StructField("MarkDown2", FloatType(), nullable=True),
    StructField("MarkDown3", FloatType(), nullable=True),
    # Add MarkDown4 and MarkDown5 here if they exist in your full dataset
])

When reading your data (e.g., from a CSV), pass this schema directly to the reader:

df = spark.read.csv("your_data_file.csv", header=True, schema=custom_schema)

Now if you run df.printSchema(), you'll see the correct types instead of all strings.

Solution 2: Convert Types After Reading #

If you already have the DataFrame loaded and need to fix it post-read, use cast() and date/boolean conversion functions:

from pyspark.sql.functions import col, to_date, to_boolean

# Adjust the date format to match what's in your Date column (e.g., "MM/dd/yyyy" if that's your format)
df_clean = df.withColumn("Store", col("Store").cast(IntegerType())) \
             .withColumn("Date", to_date(col("Date"), "yyyy-MM-dd")) \
             .withColumn("IsHoliday", to_boolean(col("IsHoliday"))) \
             .withColumn("Dept", col("Dept").cast(IntegerType())) \
             .withColumn("Weekly_Sales", col("Weekly_Sales").cast(FloatType())) \
             .withColumn("Temperature", col("Temperature").cast(FloatType())) \
             .withColumn("Fuel_Price", col("Fuel_Price").cast(FloatType())) \
             .withColumn("MarkDown1", col("MarkDown1").cast(FloatType())) \
             # Repeat for MarkDown2, MarkDown3, etc.

Quick Tip #

Make sure the date format you use in to_date() matches exactly what's in your Date column—if your dates look like 12/31/2023, use "MM/dd/yyyy" instead of "yyyy-MM-dd".

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