question Re: Error while running query on HIVE; in Support Questions

Error while running query on HIVE;

sayaeWajood — Fri, 16 Sep 2022 10:46:57 GMT

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]

Re: Error while running query on HIVE;

Romainr — Tue, 08 Nov 2016 02:23:42 GMT

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

Re: Error while running query on HIVE;

sayaeWajood — Tue, 08 Nov 2016 06:47:48 GMT

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

Re: Error while running query on HIVE;

saranvisa — Mon, 14 Nov 2016 18:25:45 GMT

Pls try this and see any luck

ANALYZE TABLE Table_Name COMPUTE STATISTICS;

Re: Error while running query on HIVE;

sayaeWajood — Tue, 15 Nov 2016 09:50:53 GMT

Hi,

Nops, no luck...

BR,

Salman

Re: Error while running query on HIVE;

sayaeWajood — Wed, 16 Nov 2016 13:55:21 GMT

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...

Re: Error while running query on HIVE;

saranvisa — Fri, 18 Nov 2016 05:09:59 GMT

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

Re: Error while running query on HIVE;

saranvisa — Fri, 18 Nov 2016 05:26:30 GMT

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

Re: Error while running query on HIVE;

sayaeWajood — Tue, 23 May 2017 08:01:46 GMT

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

Re: Error while running query on HIVE;

Hadoop_Spark — Sun, 20 May 2018 11:03:14 GMT

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?

Re: Error while running query on HIVE;

NaveenGangam — Mon, 21 May 2018 15:01:54 GMT

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

Re: Error while running query on HIVE;

Hadoop_Spark — Tue, 22 May 2018 01:56:35 GMT

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

<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.

Re: Error while running query on HIVE;

shashisingh — Tue, 16 Oct 2018 16:02:55 GMT

Can you please check database name, which are being used while creating table. As you are not using any database name in the query, might be its pointing to some other database on which you might not have access.

Re: Error while running query on HIVE;

Former Member — Fri, 16 Nov 2018 17:15:13 GMT

Please check DB name and make sure Table is not existed already

Re: Error while running query on HIVE;

yasmin — Mon, 25 Feb 2019 21:10:06 GMT

Hi All,

Facing below error while running the hive command.

MSCK REPAIR TABLE flexdto_standin_avro;

Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

Your inputs is much appreciated.

Thanks

Yasmin