Apache derby Replace function installation for Hive metastore host migration

 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%';

-----------------------------------------------------------------------------

Then run below command to export the output

java -cp /home/derby/lib/*:. org.apache.derby.tools.ij /var/tmp/exportid.sql >/var/tmp/output.txt

edit the out output.txt and remove the lines except SD_ID.

Create Update SQL file script  to update the records

This shell script create the Update sql file

 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';

UPDATE sds SET LOCATION=replace(LOCATION,'test.cloudapp.net','dummy.test.com') WHERE SD_ID=150;

UPDATE sds SET LOCATION=replace(LOCATION,'test.cloudapp.net','dummy.test.com') WHERE SD_ID=151;

UPDATE sds SET LOCATION=replace(LOCATION,'test.cloudapp.net','dummy.test.com') WHERE SD_ID=152;

UPDATE sds SET LOCATION=replace(LOCATION,'test.cloudapp.net','dummy.test.com') WHERE SD_ID=153;

UPDATE sds SET LOCATION=replace(LOCATION,'test.cloudapp.net','dummy.test.com') WHERE SD_ID=154;

------------------------------------------------------------------------------------------------------------------

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