Hive创建指向MySQL的外部表时出现NullPointerException求助

环境信息 #

• Hive版本：3.1.0.3.1.4.0-315
• Ambari版本：hdp-3.1.4.0

问题描述 #

尝试创建指向远程MySQL表的Hive外部表，使用的DDL语句如下：

sudo beeline -u 'jdbc:hive2://hadoop1.4482.interconnect-hy2:2181,hadoop2.4482.interconnect-hy2:2181,hadoop3.4482.interconnect-hy2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2' --showHeader=false --silent=true --verbose=false -e"
CREATE EXTERNAL TABLE dpyy_test.dim_order_info(
     id bigint,
     order_id string,
     order_status int,
     external_product_id string,
     product_code string,
     product_type int,
     product_name string,
     open_id string,
     custom_id string,
     charge_phone string,
     order_price int,
     order_count int,
     total_order_price int,
     freight int,
     sync_status int,
     remark string,
     app_id string,
     biz_code string,
     province_code string,
     external_order_id string,
     order_type string,
     transact_channel string,
     pay_type string,
     order_date string,
     cancel_date string,
     expand_receiver string,
     order_effect_time string,
     order_expire_time string,
     create_time timestamp,
     update_time timestamp
) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' 
LOCATION 'hdfs://hdp5/dpyy/test/hive/dpyy_test.db/dim_order_info' 
TBLPROPERTIES (
  'hive.sql.databsae.type' = 'MYSQL',
  'hive.sql.jdbc.driver' = 'com.mysql.jdbc.Driver',
  'hive.sql.jdbc.url' = 'jdbc:mysql://172.20.XXX.XXX/dpyy_vas',
  'hive.sql.dbcp.username' = 'dpyy_vas',
  'hive.sql.dbcp.passowrd' = 'XXXXXX',
  'hive.sql.table' = 'order_info',
  'hive.sql.query' = 'select id,order_id,order_status,external_product_id,product_code,product_type,product_name,open_id,custom_id,charge_phone,order_price,order_count,total_order_price,freight,sync_status,remark,app_id,biz_code,province_code,external_order_id,order_type,transact_channel,pay_type,order_date,cancel_date,expand_receiver,order_effect_time,order_expire_time,create_time,update_time from order_info',
  'hive.sql.dbcp.maxActive' = '1'
) 
"

执行后报错：

23/09/15 15:37:21 [main]: INFO jdbc.HiveConnection: Connected to hadoop2.4482.interconnect-hy2:10000
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/3.1.4.0-315/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/3.1.4.0-315/hadoop/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
23/09/15 15:37:26 [main]: INFO jdbc.HiveConnection: Connected to hadoop1.4482.interconnect-hy2:10000
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.NullPointerException (state=08S01,code=1)

查看hiveserver2.log发现关键报错：

ERROR [HiveServer2-Background-Pool: Thread-634534]: metadata.Table (:()) - Unable to get field from serde: org.apache.hive.storage.jdbc.JdbcSerDe

解决方案 #

1. 修复DDL中的拼写错误 #

TBLPROPERTIES存在两处拼写错误，这是引发NullPointerException的核心原因：

• hive.sql.databsae.type 修正为 hive.sql.database.type
• hive.sql.dbcp.passowrd 修正为 hive.sql.dbcp.password

2. 部署必要的依赖Jar包 #

Hive JDBC Storage Handler需要以下Jar包存在于所有Hive节点（Hiveserver2、Metastore）的classpath中：

• hive-jdbc-storage-handler-<对应Hive版本>.jar：JDBC存储处理核心包，HDP环境可从官方仓库获取或编译对应版本
• mysql-connector-java-<5.x版本>.jar：MySQL JDBC驱动包，适配指定的com.mysql.jdbc.Driver

部署步骤：

1. 将上述Jar包复制到/usr/hdp/3.1.4.0-315/hive/lib/目录
2. 通过Ambari重启Hiveserver2和Hive Metastore服务

3. 优化DDL配置（可选） #

• 外部表使用JDBC Storage Handler时，LOCATION参数非必需，可移除（数据实际存储在MySQL，Hive仅作为映射层）
• 同时指定hive.sql.query和hive.sql.table会导致冲突，二者选其一即可

修正后的DDL示例：

sudo beeline -u 'jdbc:hive2://hadoop1.4482.interconnect-hy2:2181,hadoop2.4482.interconnect-hy2:2181,hadoop3.4482.interconnect-hy2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2' --showHeader=false --silent=true --verbose=false -e"
CREATE EXTERNAL TABLE dpyy_test.dim_order_info(
     id bigint,
     order_id string,
     order_status int,
     external_product_id string,
     product_code string,
     product_type int,
     product_name string,
     open_id string,
     custom_id string,
     charge_phone string,
     order_price int,
     order_count int,
     total_order_price int,
     freight int,
     sync_status int,
     remark string,
     app_id string,
     biz_code string,
     province_code string,
     external_order_id string,
     order_type string,
     transact_channel string,
     pay_type string,
     order_date string,
     cancel_date string,
     expand_receiver string,
     order_effect_time string,
     order_expire_time string,
     create_time timestamp,
     update_time timestamp
) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' 
TBLPROPERTIES (
  'hive.sql.database.type' = 'MYSQL',
  'hive.sql.jdbc.driver' = 'com.mysql.jdbc.Driver',
  'hive.sql.jdbc.url' = 'jdbc:mysql://172.20.XXX.XXX/dpyy_vas',
  'hive.sql.dbcp.username' = 'dpyy_vas',
  'hive.sql.dbcp.password' = 'XXXXXX',
  'hive.sql.query' = 'select id,order_id,order_status,external_product_id,product_code,product_type,product_name,open_id,custom_id,charge_phone,order_price,order_count,total_order_price,freight,sync_status,remark,app_id,biz_code,province_code,external_order_id,order_type,transact_channel,pay_type,order_date,cancel_date,expand_receiver,order_effect_time,order_expire_time,create_time,update_time from order_info',
  'hive.sql.dbcp.maxActive' = '1'
) 
"

验证方法 #

执行修正后的DDL后，运行SELECT * FROM dpyy_test.dim_order_info LIMIT 1;测试能否正常读取MySQL数据。

内容的提问来源于stack exchange，提问作者minyan-xiao