I was working on Hive data migration from one host to another host. source host hive metastore was configured with derby, and after we moved metastore_db folder into destination server, still hive tables had reference to old hdfs host location.
I followed below steps to replace old hdfs server reference with new host
launch the derby client and download and install the jar.
wget http://archive.apache.org/dist/commons/lang/binaries/commons-lang3-3.9-bin.zip
unzip commons-lang3-3.9-bin.zip
./ij
connect 'jdbc:derby:;databaseName=/data/hive/metastore_db/';
call SQLJ.INSTALL_JAR('file:/var/tmp/commons-lang3-3.9/commons-lang3-3.9.jar', 'APP.commons_lang', 0);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'APP.commons_lang');
create function replace (sourceString varchar(8000), searchString varchar(8000), replaceString varchar(8000)) returns varchar(8000) parameter style java no sql language java external name 'org.apache.commons.lang3.StringUtils.replace';
Find out the existing old host matching records
select sd_id,LOCATION from sds where LOCATION like '%test.cloudapp.net%';
create sql file to export the matching tables like below
vi /var/tmp/exportid.sql
-----------------------------------------------------------------------------
connect 'jdbc:derby:;databaseName=/data/hive/metastore_db/';
select sd_id from sds where LOCATION like '%test.cloudapp.net%';
-----------------------------------------------------------------------------
for x in `cat /var/tmp/output.txt`;do echo "UPDATE sds SET LOCATION=replace(LOCATION,'test.cloudapp.net','dummy.test.com') WHERE SD_ID=$x;>>/var/tmp/newsql.sql";done
append below line in top of the newsql.sql file and finale sql file should be like below
------------------------------------------------------------------------------------------------------------------
connect 'jdbc:derby:;databaseName=/data/hive/metastore_db/';
call SQLJ.INSTALL_JAR('file:/var/tmp/commons-lang3-3.9/commons-lang3-3.9.jar', 'APP.commons_lang', 0);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'APP.commons_lang');
create function replace (sourceString varchar(8000), searchString varchar(8000), replaceString varchar(8000)) returns varchar(8000) parameter style java no sql language java external name 'org.apache.commons.lang3.StringUtils.replace';
java -cp /home/derby/lib/*:. org.apache.derby.tools.ij /var/tmp/newsql.sql
ij> select replace(LOCATION,'test.cloudapp.net', 'dummy.test.com') from sds;
1
-----------------------------------------------------------------------------------------------------------------------------
hdfs://dummy.test.com:9000/data/hive/warehouse/students
hdfs://dummy.test.com:9000/data/hive/warehouse/students1
2 rows selected
ij> UPDATE sds SET LOCATION=replace(LOCATION, 'test.cloudapp.net', 'dummy.test.com') WHERE SD_ID=1;
1 row inserted/updated/deleted
