How to Extract All Hive Tables DDL

[#1]

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

[#2]

Well you can write a small Unix script to do that. Here are the steps breaukp.

Step 1: Get the list of all the databases, commands for that is and redirect the output to any temporary file (e.g. /tmp/databases)

hive -e "show databases;" >> /tmp/databases

Step 2: Loop through each database to get the list of tables by using "show tables" and redirect the output to temporary file (e.g. /tmp/tables)

for f in `cat /tmp/databases`

do

echo "Database name is $f , Table Names are as below:"

hive -e "show tables;" >> /tmp/tables

done

[#3]

@Vj1989 

Great way to simplify it. Believe you missed a 'use $f;' from Step 2 though, otherwise you're just getting the same tables over and over.

E.g.

for f in `cat /tmp/databases`

do
echo "Database name is $f , Table Names are as below:"
hive -e "use $f; show tables;" >> /tmp/tables
done