How to get/generate the create statement for an existing hive table?
SqlHiveHiveqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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;
Solution 2 - Sql
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
Solution 3 - Sql
Describe Formatted/Extended will show the data definition of the table in hive
hive> describe Formatted dbname.tablename;