如何获取/生成现有 hive 表的 create 语句？

小开

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

hive> describe Formatted dbname.tablename;

小开

最佳答案

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;

小开

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

小开

# !/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