28

I am getting the below error on creating a hive database

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. com/facebook/fb303/FacebookService$Iface

Hadoop version:**hadoop-1.2.1**

HIVE Version: **hive-0.12.0**

Hadoop path:/home/hadoop_test/data/hadoop-1.2.1
hive path :/home/hadoop_test/data/hive-0.12.0

I have copied hive*.jar ,jline-.jar,antlr-runtime.jar from hive-0.12.0/lib to hadoop-1.2./lib

WoodChopper
  • 4,265
  • 6
  • 31
  • 55
user3579986
  • 281
  • 1
  • 3
  • 4

13 Answers13

47
set hive.msck.path.validation=ignore;
MSCK REPAIR TABLE table_name;

Make sure the location is specified correctly

loneStar
  • 3,780
  • 23
  • 40
6

In the following way, I solved the problem.

set hive.msck.repair.batch.size=1;
set hive.msck.path.validation=ignore;

If you can not set the value, and get the error.Error: Error while processing statement: Cannot modify hive.msck.path.validation at runtime. It is not in list of params that are allowed to be modified at runtime (state=42000,code=1)

add content in hive-site:

key:
hive.security.authorization.sqlstd.confwhitelist.append
value:
hive\.msck\.path\.validation|hive\.msck\.repair\.batch\.size

enter image description here

QiuYi
  • 125
  • 1
  • 4
2

Set hive.metastore.schema.verification property in hive-site.xml to true, by default it is false.

For further details check this link.

Shailvi
  • 105
  • 4
2

Amazon Athena

If you get here because of Amazon Athena errors, you might use this bit below. First check that all you files have the same schema:

If you run an ALTER TABLE ADD PARTITION (or MSCK REPAIR TABLE) statement and mistakenly specify a partition that already exists and an incorrect Amazon S3 location, zero byte placeholder files of the format partition_value_$folder$ are created in Amazon S3. You must remove these files manually.

We removed the files with the awscli.

aws s3 rm s3://bucket/key/table/ --exclude="*" --include="*folder*" --recursive --dryrun

See also the docs with some extra steps included.

Roelant
  • 4,508
  • 1
  • 32
  • 62
2

To proper fix this with MSCK

  1. Remove the older partitions from metastore, if their path not exists, using

    ALTER TABLE dbname.tablename DROP PARTITION IF EXISTS (partition_column_name > 0);

  2. RUN MSCK REPAIR COMMAND

    MSCK REPAIR TABLE dbname.tablename;

Why the step 1 is required because MSCK Repair command will through error if the partition is removed from the file system (HDFS), so by removing all the partitions from the metastore first and then sync with MSCK will properly add the required partitions

Yash
  • 141
  • 1
  • 12
1

The reason why we got this error was we added a new column to the external Hive table. set hive.msck.path.validation=ignore; worked upto fixing hive queries but Impala had additional issues which were solved with below steps:

After doing an invalidate metadata, Impala queries started failing with Error: incompatible Parquet schema for column

Impala error SOLUTION: set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name;

if you're using Cloudera distribution below steps will make the change permanent and you don't have to set the option per session.

Cloudera Manager -> Clusters -> Impala -> Configuration -> Impala Daemon Query Options Advanced Configuration Snippet (Safety Valve)

Add the value: PARQUET_FALLBACK_SCHEMA_RESOLUTION=name

NOTE: do not use SET or semi-colon when setting the parameter in Cloudera Manager

Selwyn Fernandes
  • 21
  • 4
1

open hive cli using "hive --hiveconf hive.root.logger=DEBUG,console" to enable logs and debug from there, in my case a camel case name for partition was written on hdfs and i created hive table with its name fully in lowercase.

musiceni
  • 21
  • 4
1

None of proposed solutions worked for me.

I discovered a 0B file named _$folder$ inside my table location path (at same level of partitions). Removing it allowed me to run a MSCK REPAIR TABLE t without issues.

This file was comming from a s3 restore (roll back to a previous versionned state)

Jérémy
  • 1,790
  • 1
  • 24
  • 40
  • I had the same situation. When changing schema in Hive external table, the underlying files are not removed on table drop. So one has to manually delete the files/partitions before deploying the new schema on the same path. – dsalaj Sep 22 '22 at 09:02
0

I faced the same error. Reason in my case was a directory created in the HDFS warehouse with the same name. When this directory was deleted, it resolved my issue.

Sumit Awkash
  • 1
0

It's probably because your metastore_db is corrubpted. Delete .lck files from metastore_db.

Ashish Chaudhari
  • 1
  • 1
0

hive -e "msck repair table database.tablename" it will repair table metastore schema of table;

dilshad
  • 734
  • 1
  • 10
  • 27
0

setting the below property and then doing msck repair worked for me :

  • set hive.mapred.mode=unstrict;
Rohan Mudaliar
  • 41
  • 6
-2

I faced similar issue when the underlying hdfs directory got updated with new partitions and hence the hive metastore went out of sync.

Solved using the following two steps:

  1. MSCK table table_name showed what all partitions are out of sync.
  2. MSCK REPAIR table table_name added the missing partitions.
Varanasi Sai Bhargav
  • 1
  • 3
  • `MSCK table table_name` returns a syntax error `extraneous input 'msck' expecting {'(', 'SELECT', 'DESC', 'USING', 'WITH', 'VALUES', 'CREATE', 'TABLE', 'INSERT', 'DELETE', 'DESCRIBE', 'GRANT', 'REVOKE', 'EXPLAIN', 'SHOW', 'USE', 'DROP', 'ALTER', 'SET', 'RESET', 'START', 'COMMIT', 'ROLLBACK', 'CALL', 'PREPARE', 'DEALLOCATE', 'EXECUTE'}` and `msck repair table table_name` ends up with `Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. null` – darw Jul 16 '20 at 08:35