schematool failing metastore validation for Hive 1.1.0

Question

I just deployed Cloudera 5.12 and I'm installing Hive. Following the instructions, I run

\i /usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-1.1.0.postgres.sql

as a final step, to create the metastore. When I run the schematool validation

schematool -dbType postgres -validate

I get an error:

Validating metastore schema tables
Table(s) [ [compaction_queue, completed_txn_components, hive_locks, next_compaction_queue_id, next_lock_id, next_txn_id, txn_components, txns] ] are missing from the metastore database schema.
Failed in schema table validation.
[FAIL]

Everything else is SUCCESS and I can access the Hive databases without problems. How do I fix this error?

Are you referring to this instructions? https://www.cloudera.com/documentation/enterprise/latest/topics/cdh_ig_hive_schema_tool.html — mazaneicha, Sep 08 '17 at 15:53
Did you run scm_prepare_database.sh? If yes, can you share `schematool -info` output? — mazaneicha, Sep 08 '17 at 19:26

score 1 · Answer 1 · answered Sep 08 '17 at 22:31

I believe this is a bug. Here is what I found:

$ find . -name "*.sql" -print |xargs grep compaction_queue
./postgres/hive-txn-schema-0.14.0.postgres.sql:CREATE TABLE "compaction_queue" (
./postgres/hive-txn-schema-0.14.0.postgres.sql:CREATE TABLE "next_compaction_queue_id" (
./postgres/hive-txn-schema-0.14.0.postgres.sql:INSERT INTO "next_compaction_queue_id" VALUES(1);
./postgres/hive-schema-0.14.0.postgres.sql:CREATE TABLE "compaction_queue" (
./postgres/hive-schema-0.14.0.postgres.sql:CREATE TABLE "next_compaction_queue_id" (
./postgres/hive-schema-0.14.0.postgres.sql:INSERT INTO "next_compaction_queue_id" VALUES(1);

As you can see, the table next_compaction_queue_id only exists in schema version 0.14 for postgresql. It does not exist in any of other versions or any other database type. I do not believe these are used. If you have Cloudera Support, please create a support case and ask support to create a jira.

A workaround would be to find the table create statements of the CLAIMED missing tables in the hive-schema-0.14.0.postgres.sql file and add these tables to your Hive metastore database. Since they are not used, it won't harm anything, but it will get rid of the error in your schematool command.

score 1 · Accepted Answer · answered Sep 15 '17 at 11:58

I made it work recreating the Hive metastore using schematool. I had to first drop the current metastore on postgreSQL, using the information from here:

> su - postgres
> psql

REVOKE CONNECT ON DATABASE thedb FROM public;
SELECT pid, pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = current_database() AND pid <> pg_backend_pid();
drop metastore;

and then recreating using

/usr/lib/hive/bin/schematool -dbType postgres -initSchema -verbose -userName hiveuser -passWord thepassword

Validation worked afterwards:

> /usr/lib/hive/bin/schematool -dbType postgres -validate
Starting metastore validation

Validating schema version
Succeeded in schema version validation.
[SUCCESS]

Validating sequence number for SEQUENCE_TABLE
Succeeded in sequence number validation for SEQUENCE_TABLE.
[SUCCESS]

Validating metastore schema tables
Succeeded in schema table validation.
[SUCCESS]

Validating DFS locations
Succeeded in DFS location validation.
[SUCCESS]

Validating columns for incorrect NULL values.
Succeeded in column validation for incorrect NULL values.
[SUCCESS]

Done with metastore validation: [SUCCESS]
schemaTool completed

So I think that if there's a bug, it's on the metastore creation step:

\i /usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-1.1.0.postgres.sql

detailed on the Cloudera installation manual.

schematool failing metastore validation for Hive 1.1.0

2 Answers2