如何通过AWS Glue将S3多CSV导入Redshift？能否无代码GUI合并导入？

Hey there! Let's tackle your AWS Glue and Redshift questions one by one—since you've already nailed single-file imports, scaling to multiple CSVs is totally doable, with or without code.

Here's a step-by-step workflow covering both code-based and GUI approaches:

• Step 1: Crawl your S3 CSV folder to create a Glue Table

  • Head over to the AWS Glue Console, navigate to Crawlers, and click Add crawler.
  • Name your crawler, then set the data source as S3. Crucially, point it to the parent folder containing all your CSV files (not a single file—this tells the crawler to include every file under that path).
  • Configure an IAM role with permissions for Glue to access your S3 bucket and Redshift cluster.
  • Pick an existing Glue database or create a new one to store the crawled table.
  • Run the crawler. It’ll auto-detect your CSV schema, but double-check that all CSVs have matching column structures and headers. If not, tweak crawler settings (like "Skip header" or "Update all columns") in the advanced options to avoid schema mismatches.

• Step 2: Load the merged data into Redshift

  • If using PySpark code:
    • Create a new Glue Job with the Spark script editor.
    • Use glueContext.create_dynamic_frame.from_catalog() to pull all files from your crawled table (this handles merging automatically).
    • Write the data to Redshift using glueContext.write_dynamic_frame.from_jdbc_conf(). Here’s a quick snippet:

      from awsglue.context import GlueContext
      from pyspark.context import SparkContext
      
      sc = SparkContext()
      glueContext = GlueContext(sc)
      
      # Read all CSVs from the crawled catalog table
      dynamic_frame = glueContext.create_dynamic_frame.from_catalog(
          database="your-glue-db-name",
          table_name="your-crawled-table-name"
      )
      
      # Write merged data to Redshift
      glueContext.write_dynamic_frame.from_jdbc_conf(
          frame=dynamic_frame,
          catalog_connection="your-redshift-connection-name",
          connection_options={
              "dbtable": "public.your-target-redshift-table",
              "database": "your-redshift-db-name"
          },
          redshift_tmp_dir="s3://your-temp-bucket/tmp-folder/"
      )
      

  • If using GUI (we’ll dive deeper into this for your second question):
    • Use the Visual ETL job type to drag-and-drop nodes for your data source (crawled table) and target (Redshift), then map columns and run the job.

Absolutely! You don’t need any PySpark code to merge and import multiple CSVs—here’s how to adapt your single-file workflow for bulk imports:

• First, confirm your crawler is set up for the entire folder

  • Make sure your crawler points to the parent folder with all CSVs (not a single file). When you run it, the resulting Glue Table will treat all files in that folder as a single merged dataset.

• Create a Visual ETL Job (no code required)

  1. Go to AWS Glue Console > Jobs > Add job.
  2. Under "Job type", select Visual ETL. Name your job, and use the same IAM role you used for your single-file import (or a role with equivalent S3/Redshift permissions).
  3. In the visual editor:
     • Drag a Data source node onto the canvas. Select "Glue Data Catalog" as the source, then choose your crawled table (the one linked to all your CSVs).
     • Drag a Data target node onto the canvas. Select "Amazon Redshift" as the target, pick your existing Redshift connection, and specify the target database and table name.
     • Connect the data source node to the data target node.
     • Click the data target node to configure extra settings:
       • Set the Redshift temporary directory to an S3 bucket/folder Glue can access (this is mandatory for Redshift bulk loads).
       • Under "Write options", choose to append, overwrite, or merge data—pick the option that fits your use case.
  4. Save the job and click Run. Glue will automatically merge all your CSV files and load the combined dataset into Redshift.

• Quick tip for schema consistency

  • If some CSVs have missing columns or slightly different schemas, head to your Glue Table’s schema in the Console and adjust column definitions manually, or re-run the crawler with advanced settings like "Update all new and existing columns" to capture all fields.

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