如何在Spark 2.0 Dataframe中转换字符串日期格式

Got it, let's work through converting those messy Excel date strings into clean YYYY-MM-DD dates in Spark 2.0. Your data has two variations of date strings—some are just dates like 2/20/18, others include a timestamp like 12/4/17 13:45—plus null values we need to leave as-is. Here's a step-by-step solution:

First: Read the Excel Data #

Spark 2.0 doesn't have built-in support for Excel files, so you'll need to use the spark-excel library. Add this dependency when submitting your job or in your build file:

• For Scala/SBT: libraryDependencies += "com.crealytics" %% "spark-excel" % "0.13.5"
• For PySpark: Submit with --packages com.crealytics:spark-excel_2.11:0.13.5

Then read the Excel file, making sure to keep columns as strings (we'll handle date parsing manually):

Scala #

import org.apache.spark.sql.SparkSession

val spark = SparkSession.builder()
  .appName("ExcelDateFix")
  .master("local[*]") // Remove this line for cluster mode
  .getOrCreate()

val rawDF = spark.read
  .format("com.crealytics.spark.excel")
  .option("header", "true")
  .option("inferSchema", "false") // Keep all columns as strings
  .load("path/to/your/file.xlsx")

PySpark #

from pyspark.sql import SparkSession

spark = SparkSession.builder \
    .appName("ExcelDateFix") \
    .master("local[*]") \
    .getOrCreate()

raw_df = spark.read \
    .format("com.crealytics.spark.excel") \
    .option("header", "true") \
    .option("inferSchema", "false") \
    .load("path/to/your/file.xlsx")

Second: Convert Date Strings to YYYY-MM-DD #

We'll use Spark's to_date function with coalesce to handle both date-only and date-with-time formats. coalesce will pick the first valid parsed date (since one format will return null for each string type), and leave null values untouched.

Scala #

import org.apache.spark.sql.functions._

val cleanedDF = rawDF
  // Convert modified column
  .withColumn("modified_date", coalesce(
      to_date(col("modified"), "MM/dd/yy"),
      to_date(col("modified"), "MM/dd/yy HH:mm")
  ))
  // Convert created column
  .withColumn("created_date", coalesce(
      to_date(col("created"), "MM/dd/yy"),
      to_date(col("created"), "MM/dd/yy HH:mm")
  ))

// Optional: If you need the date as a string in YYYY-MM-DD format instead of Date type
val cleanedDFWithStringDates = cleanedDF
  .withColumn("modified_yyyy_mm_dd", date_format(col("modified_date"), "yyyy-MM-dd"))
  .withColumn("created_yyyy_mm_dd", date_format(col("created_date"), "yyyy-MM-dd"))

PySpark #

from pyspark.sql.functions import col, to_date, coalesce, date_format

cleaned_df = raw_df \
    .withColumn("modified_date", coalesce(
        to_date(col("modified"), "MM/dd/yy"),
        to_date(col("modified"), "MM/dd/yy HH:mm")
    )) \
    .withColumn("created_date", coalesce(
        to_date(col("created"), "MM/dd/yy"),
        to_date(col("created"), "MM/dd/yy HH:mm")
    ))

# Optional: Convert to string format
cleaned_df_with_string_dates = cleaned_df \
    .withColumn("modified_yyyy_mm_dd", date_format(col("modified_date"), "yyyy-MM-dd")) \
    .withColumn("created_yyyy_mm_dd", date_format(col("created_date"), "yyyy-MM-dd"))

How This Works #

• to_date(col, format) parses the string column into a Spark Date type using the specified format. If the string doesn't match the format, it returns null.
• coalesce(a, b) returns the first non-null value from the two inputs—so it handles both your date-only and date-with-time strings automatically.
• date_format converts the Date type to a string in your desired YYYY-MM-DD format, if you need a string instead of the native date type.

Testing this with your sample data will turn 12/4/17 13:45 into 2017-12-04, 2/20/18 into 2018-02-20, and leave null values as null.

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