Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (1)
Rising Star

Goal:

This article provides script to extract the DDLs for all tables and partition in a given hive database. This scripts comes handy when migrating/creating Hive Tables from one cluster to another.

You can modify and loop this script by passing all the databases via command line.

#!/bin/bash
hiveDBName=testdbname;

showcreate="show create table "
showpartitions="show partitions "
terminate=";"

tables=`hive -e "use $hiveDBName;show tables;"`
tab_list=`echo "${tables}"`

rm -f ${hiveDBName}_all_table_partition_DDL.txt

for list in $tab_list
do
   showcreatetable=${showcreatetable}${showcreate}${list}${terminate}
   listpartitions=`hive -e "use $hiveDBName; ${showpartitions}${list}"`

   for tablepart in $listpartitions
   do
      partname=`echo ${tablepart/=/=\"}`
      echo $partname
      echo "ALTER TABLE $list ADD PARTITION ($partname\");" >> ${hiveDBName}_all_table_partition_DDL.txt
   done

done

echo " ====== Create Tables ======= : " $showcreatetable

## Remove the file
rm -f ${hiveDBName}_extract_all_tables.txt

hive -e "use $hiveDBName; ${showcreatetable}" >> ${hiveDBName}_extract_all_tables.txt
14,736 Views
Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
1 of 1
Last update:
‎06-14-2017 05:17 PM
Updated by:
 
Contributors
Top Kudoed Authors