How to export DDL's of all table's for a schema in Hive

[#1]

Hi All,

Do we have any script to export all the DDL's of the table for a schema in Hive ? we have a schema name - staging_DB and its contains around 2000 tables, now we need to export all the DDL's of this tables to a txt file so that we can run this DDL's in another setup. Please suggest if we have any script or any easy way to do it, thank you.

- PK

[#2 · Accepted solution]

Hello Prakodi, 

We do not have a specific script to export all the DDL's of the table for a schema in hive, however, what you are looking for could be solved with the command 

SHOW CREATE TABLE <HIVE_Table_name>;

and you will have to put this in the shell script to retrieve all the table names first and then run show create table for all the tables of the schema. 

I did a search online and found an external blog article doing the same. Please refer that 

https://dwgeek.com/export-hive-table-ddl-syntax-and-shell-script-example.html/

Let us know if you have any questions. 

- Varun 

[#3 · Accepted solution]

Hello Prakodi, 

We do not have a specific script to export all the DDL's of the table for a schema in hive, however, what you are looking for could be solved with the command 

SHOW CREATE TABLE <HIVE_Table_name>;

and you will have to put this in the shell script to retrieve all the table names first and then run show create table for all the tables of the schema. 

I did a search online and found an external blog article doing the same. Please refer that 

https://dwgeek.com/export-hive-table-ddl-syntax-and-shell-script-example.html/

Let us know if you have any questions. 

- Varun 

[#4 · Accepted solution]

@prakodi Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. Thanks