无法使用jdbcStorageHandler创建Hive外部表 [英] Cannot create Hive external table using jdbcStorageHandler

问题描述

我正在Amazon EMR中运行一个小型集群，以便与Apache Hive 2.3.5一起玩.据我了解，Apache Hive可以从远程数据库导入数据，并使群集运行查询.我正在遵循Apache Hive网站文档中提供的示例( https://cwiki.apache.org/confluence/display/Hive/JdbcStorageHandler )，并创建了以下代码:

I am running a small cluster in Amazone EMR in order to play with Apache Hive 2.3.5. It is my understanding that Apache Hive can import data from a remote database and have the cluster to run queries. I was following an example that is provided in Apache Hive web documentation (https://cwiki.apache.org/confluence/display/Hive/JdbcStorageHandler) and created the following code:

CREATE EXTERNAL TABLE hive_table
(
  col1 int,
  col2 string,
  col3 date
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    'hive.sql.database.type'='POSTGRES',
    'hive.sql.jdbc.driver'='org.postgresql.Driver',
    'hive.sql.jdbc.url'='jdbc:postgresql://<url>/<dbname>',
    'hive.sql.dbcp.username'='<username>',
    'hive.sql.dbcp.password'='<password>',
    'hive.sql.table'='<dbtable>',
    'hive.sql.dbcp.maxActive'='1'
);

但是出现以下错误:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException java.lang.IllegalArgumentException: Property hive.sql.query is required.)

根据文档，我需要指定"hive.sql.table"或"hive.sql.query"以告诉如何从jdbc数据库获取数据.但是，如果我将hive.sql.table替换为hive.sql.query，则会出现以下错误:

According to the documentation, I need to specify either "hive.sql.table" or "hive.sql.query" to tell how to get data from jdbc database. But if I replace hive.sql.table with hive.sql.query I get the following error:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException java.lang.IllegalArgumentException: No enum constant org.apache.hive.storage.jdbc.conf.DatabaseType.POSTGRES)

我尝试在网络上寻找解决方案，但似乎没有人遇到与我同样的问题.我需要修改配置文件还是我的代码中缺少重要内容?

I tried looking in the web for a solution and it doesn't look like anyone experience the same issues that I am having. Do I need to modify a config file or am I missing something critical in my code?

推荐答案

我认为您使用的是不支持POSTGRES的jar版本.

I think you are using a version of the jar which doesn't support POSTGRES.

1. 从此链接下载最新的jar:将此下载的jar放入hdfs位置.

   Put this downloaded jar into a hdfs location.

   这篇关于无法使用jdbcStorageHandler创建Hive外部表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！