The intention of this blog is to outline the process that can be taken to clone a Big SQL database from one cluster to another. The procedure outlined here relies on taking an offline backup of the Big SQL database on one cluster and restoring it to another. The backup of the Big SQL database should be scheduled in off peak hours as it requires a database deactivation.
Important – The two clusters must be identical with the same version of Big SQL, OS level, number of nodes and Big SQL configuration.
At the same time the Big SQL database backup is taken a Hive meta-store backup is required. When the Big SQL database and Hive meta-store is restored on the 2nd cluster, only the meta-data is cloned. The HDFS files need to be shipped to the 2nd cluster as well.
There is a Big SQL Backup and Restore utility that can be used for cloning the Big SQL database available from Big SQL 5.0.2. Along with these utility commands, the backup and restoration of the hive metastore and transfer of the data needs to be done on the second cluster:
1. Offline Backup of the Big SQL database on all nodes of the cluster and transfer to the second cluster bar.py -m backup --offline -d /backup/bigsql_dbbackup/offline --onallnodes -r 'Head node of 2nd cluster' 2. Take Hive Backups and transfer to the 2nd cluster (sections 1.2 and 2.2 below) 3. Copy any custom configs from Cluster1 to Cluster2 (section 2.3 below) 4. Restore big sql backup from Cluster 2's head node bar.py -m restore -d /backup/bigsql_dbbackup/offline 5. Restore the Hive meta-store backup (section 3.2 below) 6. Execute the steps in sections 4-7 below
If not using the utility the following steps need to be taken to clone the database:
1. Database Backups
No workloads should be running or clients connected when taking this backup so schedule the backup in off peak hours. The backup needs to be taken when all the physical Big SQL workers are active. If YARN is enabled for Big SQL or logical Big SQL workers are configured, disable these features before taking the backup.
1.1 Big SQL Database Backup
Use the mkdir command to create the backup directory on the Big SQL Head node and ALL worker nodes. For example:
Prerequisite: Ensure that archive logging is not enabled, this is the default for Big SQL. Issue the following command to check:
db2 get db cfg for bigsql | grep LOGARCHMETH1 First log archive method (LOGARCHMETH1) = OFF
If archive logging is enabled it should be turned off before proceeding using this command:
db2 update db cfg for bigsql using LOGARCHMETH1 OFF
Issue the following commands to take the backup which require DB2 administrator (DBADMIN) privileges as the bigsql user from the Big SQL head node:
db2_all “db2 force applications all” db2 terminate db2 deactivate db bigsql db2 "backup db bigsql on all dbpartitionnums to /backup/bigsql_dbbackup" -- Take a Hive Metastore backup before proceeding db2 activate db bigsql -- and continue with day to day activity
1.2 Hive meta-store Backup
More information on Hive Backup and restore can be found in HDP Documentation on backup. For convenience, some commands are given for the default hive mysql database.
First, login to Ambari UI, go to Hive –> Config –> Advanced, check for the property “Database Type” – make sure it is MySQL and get the database name e.g. HIVE. Note the database host (Cluster1’s hive database host). Login via shell to cluster1’s hive meta-store host as root user and issue the following commands:
mkdir /hive_backup mysqldump hive > /hive_backup/hive.sql
2. Copy the backups and configs to Cluster2
2.1. Copy the Big SQL Backup on Cluster2
Use the mkdir command to create a directory on the Big SQL Head node and ALL worker nodes on Cluster2 to store the Big SQL backups from Cluster1. For example:
There will be a backup file for the Big SQL node. It is important that these backups are copied to cluster2 in the correct order. For example, the backup image corresponding to node 0 must be copied to node 0 on the 2nd cluster. The /home/bigsql/sqllib/db2nodes.cfg file shows which host corresponds to which node. Use the scp commands to copy the backup images from Cluster1 to Cluster2.
In this example db2nodes.cfg file, styes2 is node 0 on the 1st cluster and blab2 is node 0 on the 2nd cluster. styes6 is node 1 on the 1st cluster a blab6 is node 1 on the second cluster. 0 styes2.fyre.ibm.com 0 0 blab2.fyre.ibm.com 0 1 styes6.fyre.ibm.com 0 1 blab6.fyre.ibm.com 0 From styes2 as the bigsql user from /home/bigsql/backup/bigsql_dbbackup issue: scp BIGSQL.0.bigsql.DBPART000.20170927071532.001 bigsql@blab2:/home/bigsql/backup/bigsql_dbbackup/BIGSQL.0.bigsql.DBPART000.20170927071532.001 From sytes6 as bigsql user from /home/bigsql/backup/bigsql_dbbackup issue: scp BIGSQL.0.bigsql.DBPART001.20170927071532.001 bigsql@blab6:/home/bigsql/backup/bigsql_dbbackup/BIGSQL.0.bigsql.DBPART001.20170927071532.001
2.2. Copy the Hive Meta-store Backup on Cluster1 to Cluster2
On cluster2, first, login to Ambari UI, go to Hive –> Config –> Advanced, check for the property “Database Type” – make sure it is MySQL and get the database name e.g. HIVE. Note the hive meta-store host (Cluster2’s hive meta-store host).
Login via shell to Cluster2’s hive meta-store host as root user and issue the following commands:
Copy the hive meta-store from Cluster1 to Cluster2’s hive meta-store host in the directory created in the previous step using the scp command.
For example, from styes4 (cluster1) as root user issue this command to copy the backup to blab4 (cluster2):
scp /hive_backup/hive.sql root@blab4:/hive_backup/hive.sql
2.3. Copy any custom configs from Cluster1 to Cluster2
If any custom database configs, custom UDFs, custom SerDes or custom spark PTFs are used in cluster1, these need to be applied to the 2nd cluster. Ranger authorizations would also need to be replicated to the new cluster. The same users and groups must also exist on both clusters.
3. Restore the databases on Cluster2
After restoring the Big SQL database from Cluster1 on Cluster2, restore the Hive catalog from Cluster1 to Cluster2. If Big SQL HA is enabled, HA would need to be disabled on cluster2 before the restore (and then it can be re-enabled afterwards which will in turn propagate the Big SQL catalog to the standby head node).
3.1 Big SQL Restore on Cluster2
When the backup is taken, a file with a timestamp is created on each of the Big SQL nodes. Example of a valid timestamp is 20170927071532. Go to the backup directory copied over to Cluster2 for example /backup/bigsql_dbbackup and determine the timestamp of the backup image.
Restore the Big SQL db with the timestamp noted in the previous step using the following commands as the bigsql user on the Big SQL head node on Cluster2:
db2_all “db2 force applications all” db2 terminate db2 deactivate db bigsql db2_all "db2 restore db bigsql from /backup/bigsql_dbbackup taken at 'timestamp' replace existing without prompting" Messages such as “SQL2540W Restore is successful, however a warning "2539" was encountered during Database Restore while processing in No Interrupt mode” is normal //Restore the Hive meta-store before activating the database db2 activate db bigsql
3.2 Restore the Hive meta-store on Cluster2
When a restore of the offline Big SQL database backup is done the Hive meta-store also should be restored so that the Big SQL and Hive catalogs are in sync. The next set of steps should be done from the Cluster2’s hive meta-store host as root user. Edit the hive.sql file copied over to Cluster2. Since the backup was taken on Cluster1, all hdfs path references to Cluster1 need to be replaced with Cluster2 hdfs paths. Replace ALL the hdfs:// entries for cluster1 with entries for Cluster2.
For example, replace hdfs://styes4.fyre.ibm.com:8020/apps/hive/warehouse with hdfs://blab4.fyre.ibm.com:8020/apps/hive/warehouse'
More information can be found in HDP Documentation on restore. For convenience, some commands are given for restoration of the Hive meta-store for the default hive mysql database:
mysql hive < /hive_backup/hive.sql
4. Validate that the Big SQL db is identical on the 2 clusters
From Cluster2’s Big SQL head node issue some commands and validate that the outputs are similar on both clusters.
For example, issue some commands such as: db2 connect to bigsql db2 "select schemaname from syscat.schemata" db2 "select tabname from syscat.tables where tabschema ='BIGSQL'"
Note that since the data has not been transferred over, any count(*) queries from Hive or Big SQL will always return 0 rows.
5. Copy the Hive warehouse directory from Cluster1 to Cluster2
Transfer the data present in the Hive warehouse directory (/apps/hive/warehouse) to the new Hadoop cluster using the hadoop distcp
Use the hadoop distcp command to copy the hive warehouse directory from Cluster1 to Cluster2. Note that the target directory does not have ‘warehouse’ specified because we want to replace the contents of the warehouse directory on Cluster2.
For example: where styes is the 1st cluster and blab is the 2nd cluster hadoop distcp hdfs://styes4.fyre.ibm.com:8020/apps/hive/warehouse hdfs://blab4.fyre.ibm.com:8020/apps/hive/
6. Copy HDFS files for the tables to Cluster2
Copy the HDFS files for each table and then do some sanity checking to make sure that the table can be accessed and the table counts are the same on both clusters. Repeats steps 6.1 and 6.2 for all tables that need to be replicated. This can also be done in parallel to speed up the copying of files.
6.1 Determine the HDFS location of the table on Cluster1
The HDFS location of a specific table can be determined from Hive or Big SQL. The hive commands are:
hive> use bigsql; hive> describe extended 'table_name'; The Big SQL commands to retrieve the HDFS path of the table is: db2 "VALUES(SYSHADOOP.HCAT_DESCRIBETAB(‘schema_name', ‘table_name'))" Save the path after the LOCATION keyword.
6.2 Use hadoop distcp to copy the files to Cluster2
From any machine in cluster1, use the hadoop distcp commands to copy the files from cluster1 to cluster2.
For example where styes4 corresponds to cluster1 and blab4 corresponds to cluster2 hadoop distcp hdfs://styes4.fyre.ibm.com:8020/user/bigsql/hadoopds1g/call_center/call_center.dat hdfs://blab4.fyre.ibm.com:8020/user/bigsql/hadoopds1g/call_center
7. Restart Big SQL and make Client Application changes
Restart the Big SQL service after copying all the data to the new cluster. After the databases have been restored, then client applications changes, may also be needed. Automatic Client Re-route can be used to run on the new database. This could also be done by switching at the DNS level, or using Automatic Client Reroute.
Replication of a Big SQL database from one cluster to another was outlined in this blog. The backup procedure outlined is an offline backup which means that there can be no active Big SQL users when the backup is taken. The cluster that the database is replicated to should be identical in terms of Big SQL and OS configuration. The Hive meta-store also must be replicated from the first cluster to the second cluster. After the databases are restored, the HDFS files associated with tables in the database need to be copied over and Big SQL should be restarted. After the database has been restored, then client applications changes, maybe needed as well to run on the new database.