Sqoop / Hive for CCA175: getting table AlreadyExistsException

Question

I am trying to prepare my CCA175 exam. I am trying to import mysql database retail_db to hive but getting this error. I tried few times and also removed directories / files from these folders;

/user/hive/warehouse
/user/cloudera

Still, I am getting this following error, after running this sqoop job:

sqoop import-all-tables \
--num-mappers 1 \   
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \  
--username=retail_dba \   
--password=cloudera \   
--hive-import \   
--hive-overwrite \   
--create-hive-table \   
--compress \   
--compression-codec org.apache.hadoop.io.compress.SnappyCodec \   
--outdir java_files

Logging initialized using configuration in jar:file:/usr/jars/hive-common-1.1.0-cdh5.7.0.jar!/hive-log4j.properties FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. AlreadyExistsException(message:Table categories already exists)

Any help would be highly appreciated.

Add --verbose in the end of your command and tell me the hive create table command generated before this error. Ideally it should remove Hive table as overwrite is used — Dev

Nikhil Bhide Nikhil Bhide · Accepted Answer · 2016-12-30T16:54:05

It seems that you already have categories table in default database of hive. You need to drop tables from Hive. Following are the steps :

Login onto mysql and check how many tables does retail_db consists of.
```
mysql> use retail_db;   
mysql> show tables;
```
Login on hive and check which tables of retail_dba (mysql) do exist? Drop tables which exist in hive. In you case, its categories
```
hive> show tables; 

hive> drop table categories;
```
Navigate to cloudera home and check whether do you already have directories corresponding to these tables. Because as a part of Sqoop import to Hive, data is temporarily copied onto HDFS under /user/cloudera/ before moving to hive warehouse location. For example, check for categories directory.
```
[cloudera@quickstart /]$ hadoop fs -ls /user/cloudera/categories
```

Sqoop / Hive for CCA175: getting table AlreadyExistsException

2 Answers