Error while running query on HIVE;

[#1]

HI All,

I am unable to run the simple query on HIVE i.e. describe tablename; and getting the below error. Also when I tried to check it on hue it also give me the same error. Even Ive tried to do the same with admin user and it gives me the same error. can anybody help me here.

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. cannot find field lbs_retail_celleliste_jakntest from [0:id, 1:timestamp, 2:lid, 3:kundenr, 4:kundenavn, 5:gadenavn, 6:husnr, 7:postnr, 8:postdistrikt, 9:kisnr, 10:kismoder, 11:kismoder_navn, 12:sys34_x, 13:sys34_y, 14:celle_01_y, 15:celle_02, 16:celle_03, 17:celle_04, 18:celle_05, 19:celle_06, 20:celle_07, 21:celle_08, 22:celle_09, 23:celle_10, 24:utm_e, 25:utm_n]

[#2]

You Hive table metadata seems corrupted. Any change you could drop the
table and recreate it?

[#3]

Thanks Romainr,

Actually, when I tried to execute the describe query in HUE-Hive Editor, it executed fine. But when I tried to click table (on left panel of the HUE-Hive editor, in which we have to select database and below that we got list of tables), then it gives me the error.

Other tables in databases works fine when I tried to execute the same query (Both in query editor and left table list panel). 

I hope It will clear it more.

Best Regards

Salman 

[#4]

Hi,

I have tried to drop and recreate the same table. but still I am facing the same issue.

I have made same tables like issue database in temp database that is pointing to the same file directory as in issue database table. This table worked fine without any error and able to show data, describe data etc. Then I have made the same table in issue database with the same configuration but it gives us the same error that I am facing.

So I am facing this issue only in one database. 

Any Help!!!

BR,

Salman...

[#5]

You can try this and let me know...

Note:
If you are using cloudera then use /user/hive/warehouse/
in case of Hortonworks distribution then replace it as follows: /apps/hive/warehouse/

Step1: Run the below command to see the file available in your hive table. Replace mydb with your dbname and mytable with your tablename
# hadoop fs -ls /user/hive/warehouse/mydb.db/mytable

Step2: Ex: if the above commands returns as follows: /user/hive/warehouse/mydb.db/mytable/000000_0
then run the below command. it will return last 10 records from your file.
# hadoop fs -tail /user/hive/warehouse/mydb.db/mytable/000000_0

Step3: You mentioned that you have create script. so compare the list of column from create script with one record from the above list

Step4: Make sure columns in create script matches with data (especially the problematic column lbs_retail_celleliste_jakntest)

Thanks

Kumar

[#6]

I am giving one more option to you...

Note: You can hard code $ with actual path/file/table

• Step1: take a backup & delete the data & table from the DB where you have issue
• Step2: hive -S -e "export table $schema_file1.$tbl_file1 to '$HDFS_DATA_PATH/$tbl_file1';"  Note: Execute from HDFS and export the working db.table
• Step2: # --It contains both data and metadata. 
• Step3: hive -S -e "import table $schema_file1.$tbl_file1 from '$HDFS_DATA_PATH/$tbl_file1';"  Note: First import will through an error as table doesn't exist in target DB, but automatically create a table. Import to the target DB where you need the data
• Step4: hive -S -e "import table $schema_file1.$tbl_file1 from '$HDFS_DATA_PATH/$tbl_file1';" Note: Second import will import the data without any error as table available now

Thanks

Kumar

[#7 · Accepted solution]

Hi,

Apologies for the late response. I've tried the above solution but they didt worked for me. 

An after number of times I have found the resolution.

As database has a table with same name, due to that the problem is occuring.

For example:
We have a database test and in the same database we have a table test so Ive rename the table to test_2. 

We have successfully recreated this problem and also resolved the same on in test cluster.

So thanks for the help and response. 

BR

[#8]

Hi,

I still not getting resolved with given resolution.

hive> create table departments(department_id int,department_name string)
> ;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS `NUCLEUS_TYPE`,`A0`.`CREATE_TIME`,`A0`.`LAST_ACCESS_TIME`,`A0`.`OWNER`,`A0`.`RETENTION`,`A0`.`IS_REWRITE_ENABLED`,`A0`.`TBL_NAME`,`A0`.`TBL_TYPE`,`A0`.`TBL_ID` FROM `TBLS` `A0` LEFT OUTER JOIN `DBS` `B0` ON `A0`.`DB_ID` = `B0`.`DB_ID` WHERE `A0`.`TBL_NAME` = ? AND `B0`.`NAME` = ?)
hive>

Could you please help me to resolve this?

[#9]

Could you please post the HMS log snippet please showing the full exception stack? Could you also post the output of select * from SEQUENCE_TABLE from your HMS metastore DB? Thanks

[#10]

Hi Naveen,

Thanks for your reply.

As verified i could not find HMS logs.

and while executing command got same error as below.

hive> select * from SEQUENCE_TABLE
> ;
FAILED: SemanticException Unable to fetch table sequence_table. Exception thrown when executing query : SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS `NUCLEUS_TYPE`,`A0`.`CREATE_TIME`,`A0`.`LAST_ACCESS_TIME`,`A0`.`OWNER`,`A0`.`RETENTION`,`A0`.`IS_REWRITE_ENABLED`,`A0`.`TBL_NAME`,`A0`.`TBL_TYPE`,`A0`.`TBL_ID` FROM `TBLS` `A0` LEFT OUTER JOIN `DBS` `B0` ON `A0`.`DB_ID` = `B0`.`DB_ID` WHERE `A0`.`TBL_NAME` = ? AND `B0`.`NAME` = ?
hive>

AND MY HIVE-SITE.XML Configuration is as below

<configuration>

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/hive?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>

<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>

<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
</property>
</configuration>

can u please help me if these are any more tags needs to be added to my xml.