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

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 2

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;