Redshift通过Hive Metastore创建外部表遇连接错误求助

Let's break down this issue step by step—Default TException is a generic Thrift error from the Hive Metastore, which usually points to connectivity, service health, or configuration mismatches. Since your external schema was created successfully, the basic IAM role setup for Redshift <-> S3 is likely okay, so we'll focus on the Hive Metastore connection itself.

1. Verify Network Connectivity Between Redshift and Hive Metastore #

First, confirm Redshift can actually reach your Hive Metastore's IP and port (9083):

• Check Redshift connection logs: Run this query to see if there are failed connection attempts to your Metastore:

  SELECT * FROM svv_connection_log 
  WHERE remote_host = 'XX.XXX.XXX.XX' AND remote_port = 9083 
  ORDER BY recordtime DESC LIMIT 10;
  

  Look for entries with connection_status = 'failed' or error messages like "connection refused".
• Validate security group/NACL rules:
  • Ensure your Redshift cluster's security group allows outbound traffic to the Metastore's IP on port 9083.
  • Ensure the Metastore server's security group allows inbound traffic from Redshift's cluster IP (or security group) on port 9083.
• Test from a proxy EC2: Launch an EC2 instance in the same VPC as Redshift, then run telnet XX.XXX.XXX.XX 9083 or nc -zv XX.XXX.XXX.XX 9083 to confirm the port is reachable.

2. Check Hive Metastore Service Health #

If the network is open, confirm the Metastore itself is running properly:

• Check the Metastore process: On the Metastore server, run:

  ps aux | grep hivemetastore
  

  You should see a running Java process for the metastore. If not, restart the service (e.g., systemctl restart hive-metastore).
• Verify port listening:

  netstat -tulpn | grep 9083
  

  This should show the port is being listened on by the hivemetastore process.
• Inspect Metastore logs: Look for errors in the Metastore's log file (typically /var/log/hive/hivemetastore.log):

  tail -n 50 /var/log/hive/hivemetastore.log
  

  Common issues here include failed database connections (if the Metastore uses MySQL/PostgreSQL) or Thrift initialization errors.

3. Validate External Schema Configuration #

Even though the schema was created, double-check for subtle misconfigurations:

• Confirm URI and database name: Ensure the URI in your CREATE EXTERNAL SCHEMA statement uses the correct protocol (thrift://) and matches the Metastore's IP/port. Also, verify the DATABASE value matches an existing database in your Hive Metastore (default is default).
• SSL compatibility: If your Hive Metastore is configured to use SSL for Thrift connections, you must add the SSL true parameter when creating the external schema:

  CREATE EXTERNAL SCHEMA hive_schema
  FROM HIVE METASTORE
  DATABASE 'default'
  URI 'thrift://XX.XXX.XXX.XX:9083'
  IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3AccessRole'
  SSL true;
  

4. Check Thrift Version Compatibility #

Redshift has specific compatibility requirements for Hive Metastore versions. If you're using a newer Hive version (e.g., Hive 3.x), there might be Thrift protocol mismatches with Redshift.

• Check Redshift's supported Hive Metastore versions and ensure your Metastore aligns with that.
• If you're on an incompatible version, consider downgrading the Metastore or using a version that's known to work with Redshift.

5. Rule Out Authentication Issues #

If your Hive Metastore uses Kerberos or LDAP authentication, Redshift needs additional parameters to connect:

• For Kerberos, add KERBEROS_PRINCIPAL and KERBEROS_KEYTAB (if required) to your CREATE EXTERNAL SCHEMA statement.
• Ensure the Redshift cluster has access to the necessary Kerberos tickets or credentials to authenticate with the Metastore.

Start with the network and service health checks first—those are the most common culprits for this error. Once you've narrowed down the root cause, adjust your configuration or infrastructure accordingly.

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