Migrate from Big Data Cloud Compute Edition

$ sudo su hdfs -c 'hdfs dfsadmin -safemode get'
Safe mode is OFF
 
$ sudo su hdfs -c 'hdfs dfsadmin -safemode enter'
Safe mode is ON

export LIBJARS=/data/hdfsjars/oci-hdfs-full-2.7.7.0.jar

export DISTCP_PROXY_OPTS="-Dhttps.proxyHost=<proxy-host> -Dhttps.proxyPort=<proxy-port> -Dsun.net.inetaddr.ttl=60"

hadoop fs -Dfs.oci.client.auth.fingerprint=<fingerprint> \
-Dfs.oci.client.auth.pemfilepath=<oci_pem_key> \
-Dfs.oci.client.auth.passphrase=<passphrase> \
-Dfs.oci.client.auth.tenantId=<OCID forTenancy> \
-Dfs.oci.client.auth.userId=<OCID forUser> \
-Dfs.oci.client.hostname=<HostName. Example: https://objectstorage.us-phoenix-1.oraclecloud.com/> \
-Dfs.oci.client.proxy.uri=http://<proxyhost>:<proxyport> \
-ls oci://<bucket>@<namespace>/

hadoop fs -Dfs.oci.client.auth.fingerprint=<fingerprint> \
      -Dfs.oci.client.auth.pemfilepath=<oci_pem_key> \
      -Dfs.oci.client.auth.passphrase=<passphrase> \
      -Dfs.oci.client.auth.tenantId=<OCID for Tenancy> \
      -Dfs.oci.client.auth.userId=<OCID for User> \
      -Dfs.oci.client.hostname=<HostName. Example: https://objectstorage.us-phoenix-1.oraclecloud.com/> \
      -Dfs.oci.client.proxy.uri=http://<proxyhost>:<proxyport>  \
      -mkdir oci://<bucket>@<namespace>/<Dir>

hadoop fs -Dfs.oci.client.auth.fingerprint=<fingerprint> \
      -Dfs.oci.client.auth.pemfilepath=<oci_pem_key> \
      -Dfs.oci.client.auth.passphrase=<passphrase> \
      -Dfs.oci.client.auth.tenantId=<OCID forTenancy> \
      -Dfs.oci.client.auth.userId=<OCID forUser> \
      -Dfs.oci.client.hostname=<HostName>\
      -ls oci://<bucket>@<namespace>/

#!/bin/bash
#
# Copyright (c) 2022, Oracle and/or its affiliates. All rights reserved.
# Properties file to overide
#
# This file will be sourced by the generate_target_ddl_from_source.sh
# to customize/overide properties
# SRC_HDFS_PATH and SRC_OBJECTSTORE_PATH will be derived automatically from the cluster.
# You will be customizing
#     RUNDIR , TARGET_HDFS_PATH, TARGET_OBJECTSTORE_PATH
#
# ------------------------------------------------------------
# Location to hold intermediate and final scripts to be generated.
# You must have write privileges on this directory
export RUNDIR=/tmp/hivemigrate
 
# ------------------------------------------------------------
# Specify current hive.metastore.warehouse.dir from Hive Config.
#
export SRC_WAREHOUSE_DIR=/apps/hive/warehouse
 
 
# ------------------------------------------------------------
# Modify expected target BDS hdfs path in your hive DDL script
#
export TARGET_HDFS_PATH=hdfs://testbdmn0.bmbdcsad1.bmbdcs.oraclevcn.com:8020/warehouse/tablespace/external/hive
 
 
# ------------------------------------------------------------
# Modify expected target BDS Object Store path in your hive DDL script
#
export TARGET_OBJECTSTORE_PATH=oci://bdc2odh@bdsdevcluster/warehouse

#!/bin/bash
#
# Copyright (c) 2022, Oracle and/or its affiliates. All rights reserved.
#
# Script to generate queries that can be run on a target cluster for hive metadata migration.
#
# Usage :
#   sudo generate_target_ddl_from_source.sh
# The generated scripts are available under /tmp/hivemigrate/generatedSQL_target_Hive
#  - 1_CREATE_DB.hql
#  - 2_CREATE_TABLE.hql
#  - 3_ADD_PARTITION.hql
#
# These scripts can be run on a target cluster AS IS to create hive metadata that matches the source..
#
 
 
#------------------------------------------------------------
# Set Environment
detect_basedir()
{
  DIRNAME=`dirname $0`
  BASENAME=`basename $0`
  BASEDIR=$(cd "$DIRNAME" ; pwd)
}
detect_basedir
 
 
#------------------------------------------------------------
# Initilize any default config properties that has been overriden in
#     ${BASEDIR}/hive_migrate.properties
# DO NOT MODIFY. PLEASE OVERRIDE ANY PARAMETER IN ${BASEDIR}/hive_migrate.properties
export RUNDIR=/tmp/hivemigrate
export TARGET_HDFS_PATH=hdfs://Some_cluster_name/tmp
export TARGET_OBJECTSTORE_PATH=oci://mybucket@mytenancy/tmp/
#
#------------------------------------------------------------
if [ -f ${BASEDIR}/hive_migrate.properties ]; then
    echo -e "*** Sourcing Overide proprties file ${BASEDIR}/hive_migrate.properties"
    source ${BASEDIR}/hive_migrate.properties
else
    echo -e "*** Warning : Missing Overide proprties file ${BASEDIR}/hive_migrate.properties"
    echo -e "*** Continue with default settings\n"
fi
#
usage()
{
  echo -e "Usage : ${BASENAME} \n"
  echo -e "**  This script is to be run on your source cluster and can be run as many times as you wish."
  echo -e "**  It cleans up any older generated scripts located in ${RUNDIR} before starting \n"
  echo -e "**  The generated output hive query scripts will be available in RUNDIR: ${RUNDIR} ...\n"
  echo -e "*** Modify RUNDIR if you would like to override\n"
  echo -e "**  Currently TARGET_HDFS_PATH is set to hdfs://Some_cluster_name/"
  echo -e "*** Modify TARGET_HDFS_PATH to match your hdfs path in Hive DDL \n"
}
 
 
# Make sure only root can run our script
if [ "$(id -u)" != "0" ]; then
   echo -e "ERROR: *** This script ${BASENAME} must be run as root" 1>&2
   usage
   exit 1
fi
 
if [ "$1" != "" ]; then
   usage
   exit 1
fi
 
 
#------------------------------------------------------------
#
# Figure out the HDFS Path in source cluster.
# Depending on HA or non-HA cluster the hdfs path syntax is different
if ! grep -q "dfs.ha.namenodes.mycluster" /etc/bdcsce/conf/blueprint/*.json
then
    echo -e "INFO:**  Source: Non-HA cluster..."
    nn_fqdn=$(hostname -f)
    SRC_HDFS_PATH=hdfs://${nn_fqdn}:8020${SRC_WAREHOUSE_DIR}
else
    echo -e 'INFO:**  Source: HA cluster...'
    SRC_HDFS_PATH=hdfs://mycluster${SRC_WAREHOUSE_DIR}
fi
echo -e "INFO:**  Assumed source hdfspath : $SRC_HDFS_PATH"
 
 
#------------------------------------------------------------
#
# Figureout Source Object source Patch
source /etc/bdcsce/conf/datasources.properties
export SRC_OBJECTSTORE_PATH=${bdcsce_default_objectstore_url}
 
 
 
 
#------------------------------------------------------------
# Step-1 : Setup/Clean source and target directories to hold intermediate and final scripts
 
 
#Path to store intermediate hive queries
outputSourcePath=${RUNDIR}/generatedSQL_source_Hive
rm -rf $outputSourcePath
mkdir -m 777 -p $outputSourcePath
 
 
#Path to store final target hive queries to be used at target system
outputTargetPath=${RUNDIR}/generatedSQL_target_Hive
rm -rf $outputTargetPath
mkdir -m 777 -p $outputTargetPath
 
 
#------------------------------------------------------------
# Step-2 : Extract Create DB statements from MetaStore to Run in Target Hive Installation.
#
mysql -Dhive --skip-column-names -e"
 
 
-- Query for creating databases
SELECT
    CONCAT('CREATE DATABASE IF NOT EXISTS ',
            D.NAME,
            '\;') AS CREATE_DB_STATEMENTS
FROM
    DBS D;" > $outputTargetPath/1_CREATE_DB.hql
 
 
ret=$?
if [ $ret -ne 0 ];
then
    echo -e "Error:** - MySQL Error code is $ret while trying to extract DB creation scripts"
    exit $ret
else
    echo -e "**  DB Creation extraction script $outputTargetPath/1_CREATE_DB.hql completed successfully"
fi
 
 
#------------------------------------------------------------
# Extract Show Create Table statements from MetaStore to Run in Source Hive Installation.
 
 
mysql -Dhive --skip-column-names -e"
 
 
-- Query for Creating Show CT Statements to be run in the Source Hive Installation
SELECT
    CONCAT('USE ', D.name, '\; ','SHOW CREATE TABLE ',
            T.tbl_name,
            '\;') AS SHOW_CT_STATEMENTS
FROM
    TBLS T
        INNER JOIN
    DBS D ON D.DB_ID = T.DB_ID
WHERE
    T.TBL_TYPE != 'INDEX_TABLE';" > $outputSourcePath/1_SHOW_CREATE_TABLE.hql
 
 
 
 
ret=$?
if [ $ret -ne 0 ];
then
    echo -e "Error:** - MySQL Error code is $ret while trying to extract SHOW CT scripts"
    exit $ret
else
    echo -e "\n\n"
    echo -e "**  SHOW CREATE TABLE extraction script $outputSourcePath/1_SHOW_CREATE_TABLE.hql completed successfully"
fi
 
 
 
 
#------------------------------------------------------------
# Run Show Create Table Command on Source Hive Installation to create target Hive CT scripts
echo -e "**  Running show create table in source cluster"
echo -e "**  This may take a while...Depends on hive metadata size\n\n"
 
 
 
 
sudo -u hive hive -f $outputSourcePath/1_SHOW_CREATE_TABLE.hql > $outputTargetPath/2_CREATE_TABLE_temp.hql
ret=$?
if [ $ret -ne 0 ];
then
    echo -e "Error - Hive Error code is $ret while trying to execute SHOW CT scripts"
    exit $ret
else
    echo -e "\n\n"
    echo -e "**  SHOW CT execution script $outputTargetPath/2_CREATE_TABLE_temp.hql completed successfully"
fi
 
 
# Add semicolon suffix ; after CT statements
echo -e "USE default;" > $outputTargetPath/2_CREATE_TABLE.hql
cat $outputTargetPath/2_CREATE_TABLE_temp.hql | tr '\n' '\f' | sed -e 's/)\fCREATE/);\fCREATE/g'  | tr '\f' '\n' >> $outputTargetPath/2_CREATE_TABLE.hql
echo -e ";">>$outputTargetPath/2_CREATE_TABLE.hql
rm -rf $outputTargetPath/2_CREATE_TABLE_temp.hql
echo -e "**  Create Table execution script $outputTargetPath/2_CREATE_TABLE.hql created successfully"
 
#------------------------------------------------------------
# Convert Managed Table to External table for HIVE 3
#
echo -e "INFO:** Convering Managed Table to External table for HIVE 3 in CT $outputTargetPath/2_CREATE_TABLE.hql scripts"
sed -i "s/^CREATE TABLE/CREATE EXTERNAL TABLE/g" $outputTargetPath/2_CREATE_TABLE.hql
 
#------------------------------------------------------------
# Replace HDFS Path
#
echo -e "INFO:** Replacing $SRC_HDFS_PATH with $TARGET_HDFS_PATH in CT $outputTargetPath/2_CREATE_TABLE.hql scripts"
sed -i "s,$SRC_HDFS_PATH,$TARGET_HDFS_PATH,g" $outputTargetPath/2_CREATE_TABLE.hql
 
#------------------------------------------------------------
# Replace Target Object Store Path
#
echo -e "INFO:** Replacing $SRC_OBJECTSTORE_PATH with $TARGET_OBJECTSTORE_PATH in CT $outputTargetPath/2_CREATE_TABLE.hql scripts"
sed -i "s,$SRC_OBJECTSTORE_PATH,$TARGET_OBJECTSTORE_PATH,g" $outputTargetPath/2_CREATE_TABLE.hql
 
 
echo -e "INFO:** Replaced $SRC_OBJECTSTORE_PATH with $TARGET_OBJECTSTORE_PATH in CT $outputTargetPath/2_CREATE_TABLE.hql scripts"
 
 
 
#------------------------------------------------------------
# Extract Add Partition statements if any from MetaStore to Run in Target Hive Installation.
 
 
mysql -Dhive --skip-column-names -e"
-- Query to create add partition scripts to run in target Hive Installation
 
 
 
 
SET @oldNameNode = '$SRC_HDFS_PATH'; -- Old NameNode address
SET @newNameNode = '$TARGET_HDFS_PATH'; -- New NameNode address
 
 
 
 
SELECT
    REPLACE(add_partition_query,
        @oldNameNode,
        @newNameNode) AS add_partition_query
 
 
 
 
FROM
        (SELECT
                CONCAT('USE ', D.name, '\; ', ' ALTER TABLE ', T.TBL_NAME, ' ADD PARTITION (', GROUP_CONCAT(PK.PKEY_NAME, '=', '\'', PKV.PART_KEY_VAL, '\''
                    ORDER BY PK.INTEGER_IDX), ') LOCATION \'', S.location, '\'\; ') AS add_partition_query
    FROM
        TBLS T
    INNER JOIN DBS D ON T.DB_ID = D.DB_ID
    INNER JOIN PARTITION_KEYS PK ON T.TBL_ID = PK.TBL_ID
    INNER JOIN PARTITIONS P ON P.TBL_ID = T.TBL_ID
    INNER JOIN PARTITION_KEY_VALS PKV ON P.PART_ID = PKV.PART_ID
        AND PK.INTEGER_IDX = PKV.INTEGER_IDX
    INNER JOIN SDS S ON P.SD_ID = S.SD_ID
    GROUP BY P.PART_ID
    ORDER BY D.name) alias1;" > $outputTargetPath/3_ADD_PARTITION.hql
ret=$?
if [ $ret -ne 0 ];
then
    echo -e "Error - MySQL Error code is $ret while trying to extract ADD PARTITION scripts"
    exit $ret
else
    echo -e "\n\n"
    echo -e "**  ADD PARTITION extraction script $outputTargetPath/3_ADD_PARTITION.hql completed successfully"
fi
 
 
 
 
echo -e "\n\n"
echo -e "INFO: ** Here are your Target installation scripts"
ls -al $outputTargetPath/*
echo -e "\n\n"
echo -e "INFO: ** Run these hql scripts on your Target hive shell..."
 
 
exit 0

sudo ./generate_target_ddl_from_source.sh