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.

CDH 5.1 Problem with Hive test metastore

CDH 5.1 Problem with Hive test metastore

Contributor

Hi all,

 

I 'm using a cluster with CDH5.1 and I set Oracle as Hive metastore.

 

When I check the available databases present in the Hive metastore, there are many entries like the following ones:

 

cloudera_manager_metastore_canary_test_db_hive_hivemetastore_ad877ceb5c63524a48e429715bdb9368_2014_09_26_16_28_18

 

This should be the test database created by Hive to check metastore but Hive cannot delete them.

 

How can I solve this?

 

Thanks

Alessio

3 REPLIES 3

Re: CDH 5.1 Problem with Hive test metastore

Expert Contributor

my backed database is MySQL, but i can delete these databases.

 

what's errors when you deleted these database ?

Re: CDH 5.1 Problem with Hive test metastore

Contributor

These cloudera_manager_metastore_canary_* databases are created by Cloudera Manager Service Monitor to test whether Hive MetaStore works properly. Some error happens in the progress and makes these databases left in MetaStore.

 

A common reason of this kind of failure is that the timeout value for Service Monitor to read Hive MetaStore is not big enough. To prevent this from happending again, please go to Cloudera Manager --> Hive service --> Configuration --> Service-Wide --> Montoring --> Service Monitor Client Config Overrides, and then modify the value of "hive.metastore.client.socket.timeout" from the default value 20 to a fairly big value such as 300. 

 

To clear these unwanted databases, please stop the Hive service totally and then run the following queries in Oracle:

delete from PARTITION_PARAMS where PART_ID in (
select p.PART_ID from TBLS t, DBS d, PARTITIONS p
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID);

delete from PARTITION_KEY_VALS where PART_ID in (
select p.PART_ID from TBLS t, DBS d, PARTITIONS p
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID);

delete from SORT_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from BUCKETING_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SERDE_PARAMS where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SERDES where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SKEWED_VALUES where SD_ID_OID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SKEWED_COL_NAMES where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SDS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from PARTITIONS where PART_ID in (
select p.PART_ID from TBLS t, DBS d, PARTITIONS p
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID
);




delete COLUMNS_V2 where CD_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete CDS where CD_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);


delete SORT_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete BUCKETING_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete SERDE_PARAMS where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SERDES where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SKEWED_VALUES where SD_ID_OID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SKEWED_COL_NAMES where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SDS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete PARTITION_KEYS where TBL_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete TABLE_PARAMS where TBL_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete from TBLS where TBL_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete from DBS where NAME like 'cloudera_manager_metastore_canary_test%';

commit;

 Hope this helps!

Highlighted

Re: CDH 5.1 Problem with Hive test metastore

Contributor

The unwanted databases (cloudera_manager_metastore_canary_test_db_hive_hivemetastore_*) were created by Cloudera Manager Service Monitor, which periodically create databases/tables in Hive MetaStore and then delete them to check whether Hive MetaStore work properly or not. It looks like that some exceptions happened during this process and thus the created databases/tables were left in Hive MetaStore not cleared.

This problem usually happens because the timeout value for Service Monitor to read Hive MetaStore is too low. To prevent it from happending again, please go to Cloudera Manager --> Hive service --> Configuration --> Service-Wide --> Montoring --> Service Monitor Client Config Overrides, and then modify the value of "hive.metastore.client.socket.timeout" from the default value 20 to a fairly big one, such as 600.

 

To clear the existing unwanted databases, please stop Hive service in Cloudera Manager and then run the following queries in Oracle:

 

delete from PARTITION_PARAMS where PART_ID in (
select p.PART_ID from TBLS t, DBS d, PARTITIONS p
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID);

delete from PARTITION_KEY_VALS where PART_ID in (
select p.PART_ID from TBLS t, DBS d, PARTITIONS p
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID);

delete from SORT_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from BUCKETING_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SERDE_PARAMS where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SERDES where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SKEWED_VALUES where SD_ID_OID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SKEWED_COL_NAMES where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from SDS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, PARTITIONS p, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID and s.SD_ID = p.SD_ID);

delete from PARTITIONS where PART_ID in (
select p.PART_ID from TBLS t, DBS d, PARTITIONS p
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and p.TBL_ID = t.TBL_ID
);




delete COLUMNS_V2 where CD_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete CDS where CD_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);


delete SORT_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete BUCKETING_COLS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete SERDE_PARAMS where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SERDES where SERDE_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SKEWED_VALUES where SD_ID_OID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SKEWED_COL_NAMES where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete from SDS where SD_ID in (
select s.SD_ID from TBLS t, DBS d, SDS s
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID and s.SD_ID = t.SD_ID
);

delete PARTITION_KEYS where TBL_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete TABLE_PARAMS where TBL_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete from TBLS where TBL_ID in (
select t.TBL_ID from TBLS t, DBS d
where d.NAME like 'cloudera_manager_metastore_canary_test%' and t.DB_ID = d.DB_ID
);

delete from DBS where NAME like 'cloudera_manager_metastore_canary_test%';

commit;

 

 Hope this helps!

 

 

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