Support Questions

jldb · ‎02-16-2015

Hi there,

Frequently, I'm finding phantom tables (for example: cloudera_manager_metastore_canary_test_db_hive_hivemetastore_4fc2919535135f1bc562814ffdaad5f4_2015_02_11_16_56_30) in our Hive metastore. Some of this databases contain nothing which means I can drop the database, but more often than not they contain a table called "cm_test_table".

I have tried to delete this with the CASCADE parameter as obviously hive believes there's a table still in the database, but I get a weird error ([Error 10001]: Table not found cm_test_table) when clearly the table is found, so I imagine it may be a lock of some kind or a corrupt table.

In the past the only way I've been able to get rid of these phantom tables (it messes up some of our other systems - Presto for example with prestogres (https://github.com/facebook/presto/issues/2317)) is to rummage through the hive metastore and issue SQL commands one by one to remove the phantom tables and then databases but this seems highly inefficient.

Is there anything I'm missing which anyone can help with?

Many thanks in advance,

J

giladwolff · ‎02-20-2015

I believe that the underlying problem is with the hive metastore canary timeout. Do the following to change it and remove the databases:

Instructions	

Increase the Cloudera Manager timeout value for reading the Hive MetaStore by navigating to, Hive service > Configuration > Service-Wide > Monitoring > Service Monitor Client Config Overrides.

Modify the value of "hive.metastore.client.socket.timeout" from 20 to 1800.

Manually clear the database used by the Hive MetaStore. The following gives an example of SQL queries to clear the Oracle database for the Hive Metastore [other databases (MySQL/PostgreSQL) are similar].

 
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;

For MySQL databases:

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 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 from 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 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 from 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 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_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 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 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 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 PARTITIONS where PART_ID in 
  (
  select PART_ID from 
    (
    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
    ) as sub_query
);

delete from TBLS where TBL_ID in 
  (
  select TBL_ID from 
    (
    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
    ) as sub_query
  );

delete from SDS where SD_ID in
  (
  select SD_ID from
    (
    select s.SD_ID from DBS d, SERDES sd, SDS s, TBLS t where d.NAME like 'cloudera_manager_metastore_canary_test%' 
    and s.SERDE_ID = sd.SERDE_ID and sd.NAME like 'CM_TEST_TABLE%'
    ) as sub_query
  );
  
delete from SERDES where SERDE_ID in  
  (
  select SERDE_ID from
    (
    select sd.SERDE_ID from DBS d, CDS c, SERDES sd
    where d.NAME like 'cloudera_manager_metastore_canary_test%' and d.DB_ID = c.CD_ID and sd.NAME like 'CM_TEST_TABLE%'
    ) as sub_query
  );
  
delete from CDS where CD_ID in 
  (
  select CD_ID from
    (
    select distinct(c.CD_ID) from DBS d, CDS c
    where d.NAME like 'cloudera_manager_metastore_canary_test%' and c.CD_ID not in (select TBL_ID from TBLS)
    ) as sub_query
  );

delete from DBS where NAME like 'cloudera_manager_metastore_canary_test%';

In the latest cm version (cm5.3.1) we reuse the database for the hive metastore canary so we won't leave an orphan database per failed canary run.

G.

View solution in original post

DaveB_ORCL · ‎06-03-2015

Yes, I was able to fix this by using gilad's script to manually drop the hive table by doing DML against the underlying metastore database. Do note that I found a few issues with a couple of the SQL statements (as I documented above), so be sure to backup your metastore database first if you attempt this.

Good luck!

Cheers,

dave

View solution in original post

ilet · ‎02-20-2015

Hi,

bumping this thread as I'm seeing similar errors.

What is happening is these cloudera_manager_metastore_canary_test_db_hive_hivemetastore_**** databases keep propping up in our hive list, and we can't drop them directly in hive. If you attempt to drop them, the error message is "

Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. must specify a non-null serDe".

What I need then to do is manually enter the metastore db and physically drop the tables with psql. This behaviour seems to be consistent and won't go away.

Are there any tests in CM that can be disabled to avoid this?

giladwolff · ‎02-20-2015

I believe that the underlying problem is with the hive metastore canary timeout. Do the following to change it and remove the databases:

Instructions	

Increase the Cloudera Manager timeout value for reading the Hive MetaStore by navigating to, Hive service > Configuration > Service-Wide > Monitoring > Service Monitor Client Config Overrides.

Modify the value of "hive.metastore.client.socket.timeout" from 20 to 1800.

Manually clear the database used by the Hive MetaStore. The following gives an example of SQL queries to clear the Oracle database for the Hive Metastore [other databases (MySQL/PostgreSQL) are similar].

 
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;

For MySQL databases:

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 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 from 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 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 from 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 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_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 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 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 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 PARTITIONS where PART_ID in 
  (
  select PART_ID from 
    (
    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
    ) as sub_query
);

delete from TBLS where TBL_ID in 
  (
  select TBL_ID from 
    (
    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
    ) as sub_query
  );

delete from SDS where SD_ID in
  (
  select SD_ID from
    (
    select s.SD_ID from DBS d, SERDES sd, SDS s, TBLS t where d.NAME like 'cloudera_manager_metastore_canary_test%' 
    and s.SERDE_ID = sd.SERDE_ID and sd.NAME like 'CM_TEST_TABLE%'
    ) as sub_query
  );
  
delete from SERDES where SERDE_ID in  
  (
  select SERDE_ID from
    (
    select sd.SERDE_ID from DBS d, CDS c, SERDES sd
    where d.NAME like 'cloudera_manager_metastore_canary_test%' and d.DB_ID = c.CD_ID and sd.NAME like 'CM_TEST_TABLE%'
    ) as sub_query
  );
  
delete from CDS where CD_ID in 
  (
  select CD_ID from
    (
    select distinct(c.CD_ID) from DBS d, CDS c
    where d.NAME like 'cloudera_manager_metastore_canary_test%' and c.CD_ID not in (select TBL_ID from TBLS)
    ) as sub_query
  );

delete from DBS where NAME like 'cloudera_manager_metastore_canary_test%';

In the latest cm version (cm5.3.1) we reuse the database for the hive metastore canary so we won't leave an orphan database per failed canary run.

G.

DaveB_ORCL · ‎04-10-2015

Great script.

But I wanted to point out that some of your SQL for mySQL looks like it might be missing some where clauses.

For instance, this query is missing a join between C and 😧

delete from CDS where CD_ID in 
  (
  select CD_ID from
    (
    select distinct(c.CD_ID) from DBS d, CDS c
    where d.NAME like 'cloudera_manager_metastore_canary_test%' and c.CD_ID not in (select TBL_ID from TBLS)
    ) as sub_query
  );

Also, these other queries look suspect for being cartesian joins:

delete from SERDES where SERDE_ID in  
  (
  select SERDE_ID from
    (
    select sd.SERDE_ID from DBS d, CDS c, SERDES sd
    where d.NAME like 'cloudera_manager_metastore_canary_test%' and d.DB_ID = c.CD_ID and sd.NAME like 'CM_TEST_TABLE%'
    ) as sub_query
  );

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

TylerHale · ‎06-03-2015

DaveB - Were you ever able to solve this? I'm experiencing the same issue with cloudera_manager_metastore_canary_test_db_hive_hivemetastore* databases accumulating in Hive. When I try to drop the database with cascade parameter or the cm_test_table I see the same errors as you did.

DaveB_ORCL · ‎06-03-2015

Yes, I was able to fix this by using gilad's script to manually drop the hive table by doing DML against the underlying metastore database. Do note that I found a few issues with a couple of the SQL statements (as I documented above), so be sure to backup your metastore database first if you attempt this.

Good luck!

Cheers,

dave

Cloudera Community

Support Questions

HDFS Cloudera hive metastore phantom tables