How to get/generate the create statement for an existing hive table?

Question

Assuming you have "table" already in Hive, is there a quick way like other databases to be able to get the "CREATE" statement for that table?

Answer 1

As of Hive 0.10 this patch-967 implements SHOW CREATE TABLE which "shows the CREATE TABLE statement that creates a given table, or the CREATE VIEW statement that creates a given view."

Usage:

SHOW CREATE TABLE myTable;

Answer 2

Steps to generate Create table DDLs for all the tables in the Hive database and export into text file to run later:

step 1)
create a .sh file with the below content, say hive_table_ddl.sh

#!/bin/bash
rm -f tableNames.txt
rm -f HiveTableDDL.txt
hive -e "use $1; show tables;" > tableNames.txt  
wait
cat tableNames.txt |while read LINE
   do
   hive -e "use $1;show create table $LINE;" >>HiveTableDDL.txt
   echo  -e "\n" >> HiveTableDDL.txt
   done
rm -f tableNames.txt
echo "Table DDL generated"

step 2)

Run the above shell script by passing 'db name' as paramanter

>bash hive_table_dd.sh <<databasename>>

output :

All the create table statements of your DB will be written into the HiveTableDDL.txt

Answer 3

Describe Formatted/Extended will show the data definition of the table in hive

hive> describe Formatted dbname.tablename;

Answer 4

# !/bin/bash 
for DB in `beeline --showHeader=false --outputformat=tsv2 -e "show databases;"` 
do
for Tab in `beeline --showHeader=false --outputformat=tsv2 -e "use $DB; show tables;"` 
do 
beeline --showHeader=false --outputformat=tsv2 -e "show create table $DB.$Tab;" >$DB.$Tab.hql
done
done