Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Unable to fetch records from table using where in hive and not showing any erorrs

Highlighted

Unable to fetch records from table using where in hive and not showing any erorrs

HI,

 

I am using Cloudera CDH 5.9.0.While i am trying to fetch the records from hive table uisng where clause i am unable to get the results .It is continuously running.

 

Shall i need to configure any parameters to use Hive in ClouderaManager?

 

Thanks

SudheerKota






Thanks
SudheerKota
15 REPLIES 15
Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Champion

There could be so many reasons, can you provide more hints like

1. Are you getting result without 'where' clause?
2. Are you getting this isseue for only a particular table (or) for all the hive tables?
3. Is the table created on Hive (or) on Impala?
4. Was that a working table (before)?
5. Did you upgrade your CDH from previous version?
6. Also if possible, share your query

Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Hi,

1)I am able to fetch the records without where clause

2)i am getting the issue for all tables created in hive

3)tables are created in hive\

4)yes it was.and i also tried by creating and inserting sample table aslo,but no result.

5)Yes.This is the latest version only

6) below is the query and resonse

 

hive> select * from movies where movieid=50;
Query ID = rabbit-hyd_20170103123737_3c64070c-b4f6-4446-834b-60731418658d
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1483425764083_0003, Tracking URL = http://server1:8088/proxy/application_1483425764083_0003/
Kill Command = /opt/cloudera/parcels/CDH-5.9.0-1.cdh5.9.0.p0.23/lib/hadoop/bin/hadoop job -kill job_1483425764083_0003

 

 

Thanks

SudheerKota

 






Thanks
SudheerKota
Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Champion

@SudheerHadoop

 

Your answer is yes for the question related to upgrade

 

Can you run the below command and get the location 

hive>describe formatted dbname.movies

 

and make sure the clustername and HDFS path looks good. 

hdfs://clustername:8020/user/hive/warehouse/dbname.db/movies

 

Also come out from hive and check you can access the file directly in HDFS

hadoop fs -ls /user/hive/warehouse/dbname.db/movies

hadoop fs -cat /user/hive/warehouse/dbname.db/movies/filename....

 

Thanks

Kumar

Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Hi Kumar,

 

Thanks for your reply.one morething is i have directly installed cdh 5.9.0 not updated from old version.

I have checked the directory by using provided commands ,i can able to access but i didnt found any file there.

Not for this table ,this is happening for every table and i noticed that all DML commands are not working (cant get any results,running continuously)through Hue and Command prompt.

 

Thanks

SudheerKota






Thanks
SudheerKota
Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Champion

@SudheerHadoop

 

You cannot do DML If you don't find file in the location mentioned using describe formatted table command

 

1. Can you share the location mentioned from the describe command and location where you check the data.

2. Now, can you create a new table and insert some data. Repeat the steps that i've mentioned previously and confirm you can see the files for the new table. if so, try DML on new table

 

Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Hi Kumar,

 

i have tried by creating new table also but not effected.I was created the table using Hive prompt in Hue.

The location i was used to store is "/user/local/hive/ " and Hive supported by Mysql instead of  Postgre Sql.

 

 

Thanks

SudheerKota






Thanks
SudheerKota
Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Champion

@SudheerHadoop

 

 

 

 

1. Go to CM -> Hive -> Configuration -> search for hive.metastore.warehouse.dir and share me the path

2. Get the location of the existing & new tables using describe formatted table and share the same. 

3. You mentioned that you used to store in /user/local/hive... Is your hive warehouse path configured to this location?

4. what do you mean by not effected when you create new table? Pls share more details as much as you can.. giving one line hint will not help to debug the issue from remote

 

 

 

Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Hi ,

 

Thanks for your reply.Please find the details as below

1)/usr/local/hive/warehouse

3)Same path only configured

4)Not affected means...i  created a new table and inserted some data to it and performed to fetch the data using where clause,but the result remains same (still processing request) and found that no dml comand is working in hive.

2)Please find the screenshot attached below.Screenshot from 2017-01-09 17_52_44.png

 

One small request from me.If you are free can we have a session regarding Hive Database?

 

Thanks

SudheerKota

 






Thanks
SudheerKota
Highlighted

Re: Unable to fetch records from table using where in hive and not showing any erorrs

Champion

@SudheerHadoop

 

From your screen shot the table parameter shows only one parameter, which means your table is empty... that is the reason you are not getting any result. So it is working as expected...

 

Table Parameters:
        transient_lastDdlTime   1483083212

To make it more clear.... To get result from your table, your table parameter should look like below. It says that the table tied up with one file and it has 20 records. 

Table Parameters:
        COLUMN_STATS_ACCURATE   true
        numFiles                1
        numRows                 20
        rawDataSize             440
        totalSize               460
        transient_lastDdlTime   1466515270

Hope this helps!

 

Thanks

Kuma

 

 

Don't have an account?
Coming from Hortonworks? Activate your account here