AWS Glue脚本开发请求：读取S3中多Sheet的Excel文件并转换为Parquet格式

Hey Richard, I’ve put together a complete AWS Glue PySpark script that handles your exact requirement—reading the multi-sheet Excel file from S3, converting each sheet to a DataFrame, and writing them as Parquet files with the sheet name included in the output path. Let’s break this down step by step.

First, a few prerequisites for your Glue Job #

Before running the script, you need to configure your Glue Job with these settings to ensure dependencies are available:

• Glue Version: Use 3.0 or higher (supports modern Spark versions and Python 3.7+)
• Job Parameters: Add these under the "Job parameters" section to include required Python libraries:
  • --additional-python-modules openpyxl==3.1.2,pandas==2.0.3 (openpyxl handles .xlsx files, pandas helps fetch sheet names)
  • --JOB_NAME ExcelToParquetConversion (or any name you prefer)
• IAM Role: Ensure the role attached to your Glue Job has read access to s3://Employee/Data/ and write access to your target Parquet output bucket (e.g., s3://Employee/Parquet_Output/), plus basic Glue job execution permissions.

Complete Glue Script #

import sys
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
from pyspark.sql import SparkSession

# Initialize Glue and Spark contexts
spark = SparkSession.builder.appName("ExcelToParquet").getOrCreate()
glueContext = GlueContext(spark.sparkContext)
job = Job(glueContext)

# Fetch job parameters (makes paths dynamic instead of hardcoding)
args = getResolvedOptions(sys.argv, ['JOB_NAME', 'INPUT_PATH', 'OUTPUT_PATH'])
job.init(args['JOB_NAME'], args)

# Define input/output paths (you can pass these via job parameters too)
input_excel_path = args.get('INPUT_PATH', "s3://Employee/Data/Emp_Data.xlsx")
output_root_path = args.get('OUTPUT_PATH', "s3://Employee/Parquet_Output/")

try:
    # Step 1: Get all sheet names from the Excel file
    # We use pandas here because Spark's Excel connector can't directly list sheets
    import pandas as pd
    excel_file = pd.ExcelFile(input_excel_path)
    sheet_names = excel_file.sheet_names
    print(f"Successfully retrieved sheet names: {sheet_names}")

    # Step 2: Process each sheet one by one
    for sheet in sheet_names:
        print(f"Starting processing for sheet: {sheet}")
        
        # Read the sheet into a Spark DataFrame
        # We use the crealytics Spark Excel connector (pre-installed in Glue 3.0+)
        df = spark.read.format("com.crealytics.spark.excel") \
            .option("header", "true")  # Treat first row as column headers
            .option("inferSchema", "true")  # Auto-detect column data types (adjust if needed)
            .option("dataAddress", f"'{sheet}'!A1")  # Specify the sheet to read
            .load(input_excel_path)
        
        # Clean sheet name for valid S3 path (replace spaces/special characters)
        cleaned_sheet_name = sheet.replace(" ", "_").replace("/", "_").replace("\\", "_")
        
        # Define output path with cleaned sheet name
        output_path = f"{output_root_path}{cleaned_sheet_name}/"
        
        # Write DataFrame to Parquet (overwrite existing files if present)
        df.write.mode("overwrite") \
            .parquet(output_path)
        
        print(f"Successfully wrote sheet '{sheet}' to: {output_path}")

except Exception as e:
    print(f"Error occurred during processing: {str(e)}")
    raise e  # Re-throw to mark the job as failed

finally:
    # Commit the Glue job
    job.commit()

Key Notes & Customization Tips #

• Schema Inference: If your Excel data has complex types (e.g., dates, decimals) or you want strict schema control, replace inferSchema=true with a manually defined schema using .schema(your_schema) to avoid auto-detection errors.
• Write Mode: The script uses mode("overwrite") which replaces existing Parquet files for the sheet. Use mode("append") if you want to add new data to existing files, or mode("ignore") to skip processing if files already exist.
• Sheet Name Cleaning: The script replaces spaces and special characters with underscores to ensure valid S3 paths—adjust this logic if your sheet names have other special characters that need handling.
• Spark Excel Connector: The com.crealytics.spark.excel connector is pre-installed in Glue 3.0+. If you’re using an older Glue version, you’ll need to add the connector JAR via the --additional-jars job parameter (e.g., --additional-jars s3://your-bucket/spark-excel_2.12-0.18.5.jar).

That’s it! Once you configure your Glue Job with the above settings and run the script, each sheet from your Excel file will be saved as a separate Parquet directory in your target S3 bucket.

备注：内容来源于stack exchange，提问作者malcolm richard