CODEWITHSUNDEEP
hadoophive
catalino
catalino

Reputation: 39

Cannot create Hive external table using 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'
);

But 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: Property hive.sql.query is required.)

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?

Upvotes: 2

Views: 1410

Answers (1)

Sanchit Grover
Sanchit Grover

Reputation: 1008

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

  1. Download the latest jar from this link: http://repo1.maven.org/maven2/org/apache/hive/hive-jdbc-handler/3.1.2/hive-jdbc-handler-3.1.2.jar

  2. Put this downloaded jar into a hdfs location.

  3. Run hive normally.
  4. Run command: add jar ${HDFS_PATH_TO_DOWNLOADED_JAR}
  5. Run your create table command

Upvotes: 2

Related Questions