Hive - Not able to query the tables

Big Data Apache Hive
,
#1

Hi,

I imported the tables to the path /apps/hive/warehouse/shitansu.db by using Sqoop import-all-tables command (Not with hive import).

Import went successful and I can see the table but not able to query those tables from HIVE. Please find the below code:

Import command:

[shubhaprasadsamal@gw01 ~]$ sqoop import-all-tables --connect “jdbc:mysql://nn01.itversity.com:3306/retail_db” --username=retail_dba --password=itversity --warehouse-dir=/apps/hive/warehouse/shitansu.db

hive> dfs -ls /apps/hive/warehouse/shitansu.db;
Found 6 items
drwxr-xr-x - shubhaprasadsamal hdfs 0 2017-01-05 14:51 /apps/hive/warehouse/shitansu.db/categories
drwxr-xr-x - shubhaprasadsamal hdfs 0 2017-01-05 14:51 /apps/hive/warehouse/shitansu.db/customers
drwxr-xr-x - shubhaprasadsamal hdfs 0 2017-01-05 14:51 /apps/hive/warehouse/shitansu.db/departments
drwxr-xr-x - shubhaprasadsamal hdfs 0 2017-01-05 14:52 /apps/hive/warehouse/shitansu.db/order_items
drwxr-xr-x - shubhaprasadsamal hdfs 0 2017-01-05 14:52 /apps/hive/warehouse/shitansu.db/orders
drwxr-xr-x - shubhaprasadsamal hdfs 0 2017-01-05 14:52 /apps/hive/warehouse/shitansu.db/products
hive> use shitansu;
OK
Time taken: 0.027 seconds
hive> show tables;
OK
Time taken: 0.021 seconds
hive> select count(1) from categories;
FAILED: SemanticException [Error 10001]: Line 1:21 Table not found 'categories’
hive>

Thanks,
Shitansu.

#2

Another similar type of issue, but little different:

  1. created a DB under HIVE:

hive> create database shitansu1

  1. Imported all table by sqoop and hive import:

sqoop import-all-tables -m 1 --connect “jdbc:mysql://nn01.itversity.com:3306/retail_db” --username=retail_db --password=itversity --hive-import --hive-overwrite --warehouse-dir=/apps/hive/warehouse/shitansu1.db --compress --compression-codec org.apache.hadoop.io.compress.SnappyCodec --outdir /apps/hive/warehouse/shitansu1

and execution went fine.

  1. Only one table got imported under /apps/hive/warehoue/shitansu1.db i.e. “Orders”

Question:

1.Not able to view the tables under the data base “shitansu1” in HIVE

hive> use shitansu1;
OK
Time taken: 0.021 seconds
hive> show tables;
OK
Time taken: 0.02 seconds
hive>
> dfs -ls /apps/hive/warehouse/shitansu1.db
> ;
Found 1 items
drwxr-xr-x - shubhaprasadsamal hdfs 0 2017-01-05 17:18 /apps/hive/warehouse/shitansu1.db/orders
hive> show tables;
OK
Time taken: 0.028 seconds

  1. Why only one table got imported?

Thanks,
Shitansu.

#3

Hi,

During sqoop import, a new table would not be created in hive without the --hive-import and --create-hive-table clauses. Even though the sqoop import is done to the hive default directory which is /apps/hive/warehouse, it would only be considered a normal import and not a hive import without the above mentioned clauses. As a result, the data would be successfully imported to the specified directory but the respective table would not be created in the Hive metastore.

This is the reason you are unable to view the tables in hive even though the data is imported to the hive default directory in HDFS.

Thanks.

#4

In your second case as well, you have specified --hive-import and not --create-hive-table. All tables would have got imported if --create-hive-table was specified.

#5

use the --hive-database database_name , clause.
for example , try
sqoop import-all-tables
-m 1
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db” --username=retail_db --password=itversity
–hive-import --hive-overwrite
–create-hive-table
–hive-database shitansu

#6

When you are importing tables into Hive with --hive-import, sqoop stages the files in hdfs user space. When you imported the tables, it imported one table and while importing second which ever it may be, there must be that table file in hdfs user space i.e
/user/shubhaprasadsamal,
so sqoop could not stage the file for Hive import, so it stopped import process and didn’t import other tables also even though there may not be other tables file in hdfs user space.
So you check your user space, and delete any dir with the name as mysql tables and import again. Delete order table in hive before importing again.

#7

Thank you all for your valuable info.

The issue is resolved.

Thanks,
Shitansu.