删除Apache Iceberg表时Hive Metastore找不到Postgres驱动

环境与操作详情 #

• 已部署Hadoop 3.3.6与HIVE 4.0.0并正常运行
• postgresql-42.7.4.jar已放置于$HIVE_HOME/lib
• 可通过以下命令成功连接Postgres14：

  sudo psql -U hiveuser -p 5432 -h localhost -d metastore_db
  

• 以下PySpark代码可正常完成Iceberg表的读写操作：

  #!/usr/bin/python3.9
  
  from pyspark.sql import SparkSession
  
  iceberg_spark_jar = '/AKE/iceberg-spark-runtime-3.5_2.12-1.9.0.jar'
  warehouse_path = '/user/hive/warehouse'
  catalog = 'ake_catalog'
  
  # Initialize Spark session with Hive support and Iceberg configuration
  spark = SparkSession.builder \
      .appName("PySpark Hive Iceberg Example") \
      .enableHiveSupport() \
      .config(f"spark.sql.catalog.{catalog}", "org.apache.iceberg.spark.SparkCatalog") \
      .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions") \
      .config(f"spark.sql.catalog.{catalog}.type", "hive") \
      .config(f"spark.sql.catalog.{catalog}.uri", "thrift://localhost:9083") \
      .config(f"spark.sql.catalog.{catalog}.warehouse", f"{warehouse_path}") \
      .config("spark.sql.defaultCatalog", f"{catalog}") \
      .config("spark.jars", f"{iceberg_spark_jar}") \
      .getOrCreate()
  
  # Example DataFrame
  data = [("Alice", 34), ("Bob", 45), ("Cathy", 29)]
  columns = ["Name", "Age"]
  df = spark.createDataFrame(data, columns)
  
  # Create the database
  spark.sql("CREATE DATABASE IF NOT EXISTS my_database")
  
  spark.sql("SHOW DATABASES").show()
  spark.sql("SHOW TABLES IN my_database").show()
  
  # Create an Iceberg table directly in the database
  spark.sql("""
  CREATE TABLE IF NOT EXISTS my_database.iceberg_table (
      name STRING, 
      age INT
  )
  USING iceberg
  """)
  
  df.write.format("iceberg").mode("overwrite").saveAsTable(f"my_database.iceberg_table")
  
  iceberg_df = spark.read.format("iceberg").load(f"my_database.iceberg_table")
  iceberg_df.show()
  
  spark.stop()
  

• hive_site.xml配置：

  <configuration>
    <property>
      <name>hive.server2.thrift.bind.host</name>
        <value>0.0.0.0</value> <!-- Bind to all interfaces -->
    </property>
    <property>
      <name>hive.server2.thrift.port</name>
      <value>10000</value> <!-- Default port for HiveServer2 -->
    </property>
    <property>
      <name>hive.server2.authentication</name>
      <value>NONE</value> <!-- Authentication mode (NONE, KERBEROS, etc.) -->
    </property>
    <property>
      <name>hive.server2.enable.doAs</name>
      <value>FALSE</value>
      <description>
        Setting this property to true will have HiveServer2 execute
        Hive operations as the user making the calls to it.
      </description>
    </property> 
    
    <!-- <property>
      <name>hive.metastore.uris</name>
      <value>thrift://0.0.0.0:9083</value>
    </property> -->
  
    <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:postgresql://localhost:5432/metastore_db?createDatabaseIfNotExist=true</value>
      <description>JDBC connect string for a JDBC metastore</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>org.postgresql.Driver</value>
        <description>Driver class name for a JDBC metastore</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hiveuser</value>
        <description>Username to use against metastore database</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>[PASSWORD]</value>
        <description>Password to use against metastore database</description>
    </property>
    <property>
        <name>datanucleus.autoCreateSchema</name>
        <value>false</value>
    </property>
  </configuration>
  

• 添加以下删除表语句后触发异常：

  # Drop an Iceberg table from database
  spark.sql("DROP TABLE IF EXISTS my_database.iceberg_table")
  

  报错信息：

  Exception:
  Caused by: org.datanucleus.exceptions.NucleusException: Attempt to invoke the "BONECP" plugin to create a ConnectionPool gave an error : The specified datastore driver ("org.postgresql.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
          at org.datanucleus.store.rdbms.ConnectionFactoryImpl.generateDataSources(ConnectionFactoryImpl.java:232)
          at org.datanucleus.store.rdbms.ConnectionFactoryImpl.initialiseDataSources(ConnectionFactoryImpl.java:117)
          at org.datanucleus.store.rdbms.ConnectionFactoryImpl.<init>(ConnectionFactoryImpl.java:82)
          ... 164 more
  Caused by: org.datanucleus.store.rdbms.connectionpool.DatastoreDriverNotFoundException: The specified datastore driver ("org.postgresql.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
  

解决方案 #

原因分析 #

删除Iceberg表时，Hive Metastore需要直接连接Postgres元数据库执行元数据删除操作，但当前Spark进程或Hive Metastore进程的类路径中未正确加载Postgres驱动（即使$HIVE_HOME/lib存在驱动，也可能未被目标进程读取）。

具体解决步骤 #

• 方案1：将Postgres驱动添加到Spark类路径
  直接复制驱动到Spark的默认jar目录：

  cp $HIVE_HOME/lib/postgresql-42.7.4.jar $SPARK_HOME/jars/
  

  或者在启动SparkSession时显式指定驱动jar（替换为实际绝对路径）：

  iceberg_spark_jar = '/AKE/iceberg-spark-runtime-3.5_2.12-1.9.0.jar'
  postgres_jar = '/path/to/postgresql-42.7.4.jar'
  # ...
  .config("spark.jars", f"{iceberg_spark_jar},{postgres_jar}") \
  

• 方案2：确保Hive Metastore进程加载驱动
  检查Hive Metastore启动脚本的CLASSPATH配置，确保包含$HIVE_HOME/lib下的所有jar：

  export CLASSPATH=$CLASSPATH:$HIVE_HOME/lib/*
  

  重启Hive Metastore服务：

  hive --service metastore stop
  nohup hive --service metastore > metastore.log 2>&1 &
  

• 方案3：启用Hive Metastore Thrift服务（推荐）
  修改hive_site.xml，取消注释并启用Thrift配置：

  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://localhost:9083</value>
  </property>
  

  重启Hive Metastore服务后，Spark会通过Thrift协议请求Metastore执行删除操作，由Metastore自身处理Postgres连接，无需Spark端加载驱动。

内容的提问来源于stack exchange，提问作者Ahmed Kamal ELSaman