技术咨询：如何从外部网络独立Hive表加载数据至Amazon Redshift

Hey there, let's break down the practical, actionable ways to load data from an external Hive table into Amazon Redshift. I’ve worked through this exact scenario a few times, so here are the most reliable approaches along with step-by-step instructions and pro tips:

Approach 1: S3 Transit (Most Efficient & Widely Used) #

Redshift integrates seamlessly with S3, and Hive can easily export data to S3—this is the go-to method for large datasets.

Step-by-Step: #

1. Export Hive data to S3
   Use Hive's INSERT OVERWRITE DIRECTORY or EXPORT TABLE command to dump your Hive table into S3. Choose a format Redshift supports (CSV, Parquet, ORC—Parquet is preferred for compression and speed).
   Example for CSV:

   INSERT OVERWRITE DIRECTORY 's3://your-s3-bucket/hive-exported-data/'
   FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
   WITH SERDEPROPERTIES ('field.delim' = ',')
   SELECT * FROM your_external_hive_table;
   

   For partitioned Hive tables, add a WHERE clause to export specific partitions, or export all partitions to separate S3 prefixes.

2. Create the target Redshift table
   Match the schema to your Hive table, but adjust data types for Redshift compatibility:

   • Hive STRING → Redshift VARCHAR(n)
   • Hive BIGINT → Redshift BIGINT
   • Hive TIMESTAMP → Redshift TIMESTAMP (ensure date formats align)
     Example:

   CREATE TABLE target_redshift_table (
       user_id BIGINT,
       full_name VARCHAR(100),
       signup_date TIMESTAMP,
       account_balance DECIMAL(18,2)
   );
   

3. Load data into Redshift with COPY command
   The COPY command is Redshift's fastest loading method—use it to pull data from S3.
   Example for CSV:

   COPY target_redshift_table
   FROM 's3://your-s3-bucket/hive-exported-data/'
   IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-S3-Access-Role'
   FORMAT AS CSV
   DELIMITER ','
   IGNOREHEADER 0
   MAXERROR 10; -- Skip up to 10 error rows (adjust as needed)
   

   For Parquet, replace FORMAT AS CSV with FORMAT AS PARQUET.

Pro Tips: #

• Keep your S3 bucket and Redshift cluster in the same AWS region to cut latency and data transfer costs.
• Use Parquet for large datasets—it’s columnar, compressed, and loads much faster than CSV.
• Split large exports into multiple S3 objects (e.g., 1GB each) to parallelize the COPY operation.

Approach 2: AWS Glue ETL (For Automation & Complex Transformations) #

If you need to clean, transform, or automate data syncs, AWS Glue is perfect. It connects directly to Hive and Redshift, handling heavy lifting for you.

Step-by-Step: #

1. Register Hive as a Glue data source

   • If Hive is on EMR or an external cluster, configure Glue to connect to your Hive Metastore via JDBC.
   • Alternatively, use Glue Crawlers to scan Hive's data storage (HDFS or S3) and auto-discover table schemas.

2. Set up a Redshift connection in Glue
   Configure a connection with your Redshift cluster endpoint, database name, and authentication (IAM role is recommended over hardcoded credentials).

3. Build a Glue ETL job
   Use Python or Scala to write a script that:

   • Reads the Hive table into a Glue DynamicFrame.
   • Applies transformations (e.g., filter invalid rows, rename fields, cast data types).
   • Writes the cleaned data to Redshift.
     Example Python snippet for writing to Redshift:

   from awsglue.context import GlueContext
   from pyspark.context import SparkContext
   
   sc = SparkContext()
   glueContext = GlueContext(sc)
   
   # Read Hive table
   hive_dyf = glueContext.create_dynamic_frame.from_catalog(
       database="hive_db",
       table_name="your_hive_table"
   )
   
   # Write to Redshift
   glueContext.write_dynamic_frame.from_jdbc_conf(
       frame=hive_dyf,
       catalog_connection="redshift_connection",
       connection_options={
           "dbtable": "target_redshift_table",
           "database": "redshift_db"
       },
       redshift_tmp_dir="s3://your-s3-bucket/glue-temp/"
   )
   

4. Schedule the job
   Use Glue's built-in scheduler or CloudWatch Events to run the job on a recurring basis (e.g., daily for incremental syncs).

Pro Tips: #

• Use larger Glue worker types (G.2X or higher) for big datasets to speed up processing.
• For incremental syncs, filter Hive data using a timestamp or ID column (e.g., WHERE last_updated > '2024-01-01') to avoid reprocessing all data.

Approach 3: Direct JDBC Connection (For Small Datasets) #

If you’re dealing with small datasets (a few GB or less), you can directly pull data from Hive to Redshift via JDBC. This is simpler but slower for large volumes.

Step-by-Step: #

1. Ensure network connectivity
   Make sure Redshift can reach your Hive cluster:

   • If Hive is in a private VPC, set up VPC peering or a NAT gateway.
   • Open Hive's JDBC port (default 10000) to Redshift's security group.

2. Write a script to transfer data
   Use tools like Python (with pyhive and psycopg2) or Java to connect to both databases, pull data in batches, and insert into Redshift.
   Example Python snippet:

   from pyhive import hive
   import psycopg2
   
   # Connect to Hive
   hive_conn = hive.Connection(host="hive-cluster-ip", port=10000, database="default")
   hive_cursor = hive_conn.cursor()
   hive_cursor.execute("SELECT user_id, full_name, signup_date FROM your_hive_table")
   
   # Connect to Redshift
   redshift_conn = psycopg2.connect(
       dbname="redshift_db",
       user="admin",
       password="your-password",
       host="redshift-cluster-endpoint",
       port="5439"
   )
   redshift_cursor = redshift_conn.cursor()
   
   # Batch insert (1000 rows at a time)
   batch_size = 1000
   rows = []
   for row in hive_cursor:
       rows.append(row)
       if len(rows) == batch_size:
           redshift_cursor.executemany(
               "INSERT INTO target_redshift_table VALUES (%s, %s, %s)",
               rows
           )
           redshift_conn.commit()
           rows = []
   # Insert remaining rows
   if rows:
       redshift_cursor.executemany(
           "INSERT INTO target_redshift_table VALUES (%s, %s, %s)",
           rows
       )
       redshift_conn.commit()
   
   # Clean up connections
   hive_cursor.close()
   hive_conn.close()
   redshift_cursor.close()
   redshift_conn.close()
   

Pro Tips: #

• Always use batch inserts to avoid overwhelming either database.
• This method isn’t recommended for large datasets—JDBC transfers are single-threaded and slow compared to S3/Glue.

General Best Practices #

• Validate data: After loading, compare row counts and key metrics (e.g., COUNT(*), SUM(account_balance)) between Hive and Redshift to ensure no data was lost.
• Handle data types carefully: Hive's ARRAY or MAP types can be converted to Redshift's SUPER type for semi-structured data.
• Use IAM roles: Avoid hardcoding credentials—use IAM roles for S3, Glue, and Redshift access to improve security.
• Incremental syncs: For ongoing data updates, use timestamp or ID filters to sync only new/changed data instead of full table loads.

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