question Re: How to export DDL's of all table's for a schema in Hive in Support Questions

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

prakodi — Tue, 10 Jan 2023 15:53:49 GMT

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

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

varunshinde88 — Tue, 10 Jan 2023 17:25:33 GMT

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

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

DianaTorres — Fri, 13 Jan 2023 17:31:26 GMT

@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