手动在hive中修改表location操作
alter table db_mrk.table set location 'hdfs://hd01-jfs/apps/hive/warehouse/db_mrk.db/table';
2. 这是在mysql中修改分区的location操作
以db_mrk.table变更为例
1. 更新所有partition的location设置
start transaction;
update sds,
(
select tt1.sd_id from sds tt1 right join (
select t1.sd_id, t2.tbl_name from partitions t1 left join TBLS t2 on t1.tbl_id = t2.tbl_id left join dbs t3 on t3.db_id = t2.db_id where t3.name='db_mrk' and t2.tbl_name='table') tt2 on tt1.sd_id=tt2.sd_id
)tt
set location=replace(location, 'hdfs://NameNodeHACluster', 'hdfs://hd01-hdfs') where sds.sd_id =tt.sd_id;
commit; 或 rollback;
2. 查询更新情况
select * from sds tt1 right join (
select t1.sd_id, t2.tbl_name from partitions t1 left join TBLS t2 on t1.tbl_id = t2.tbl_id left join dbs t3 on t3.db_id = t2.db_id where t3.name='db_mrk' and t2.tbl_name='table') tt2 on tt1.sd_id=tt2.sd_id;
3. 查询表更新情况
select * from sds t1 left join TBLS t2 on t1.sd_id = t2.sd_id where t2.tbl_name='table';
4. 更新表location路径
update sds set location='hdfs://hd01-jfs/apps/hive/warehouse/db_mrk.db/table' where sd_id=851093;
