Member since
10-31-2013
4
Posts
0
Kudos Received
1
Solution
My Accepted Solutions
| Title | Views | Posted |
|---|---|---|
| 9431 | 02-20-2015 04:14 PM |
08-29-2017
10:15 AM
One typo correction to Anthony's reply above, the CLI command to retrieve information about a cluster is as 'describe-cluster' and not 'describe-clusters'.
... View more
02-20-2015
04:14 PM
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 more