How to get DDL or create script of an existing Hive table?

If you have worked with tools like Toad, DB Visualizer or SQL Server Management Studio you know it is quite easy to select an existing table and create a DDL script for the table to get the create script. How do we do the same or get the DDL or create script of an existing Hive table?

Create a table

Let’s create an employee table first and here is the script. We are logged in to beeline.

CREATE TABLE IF NOT EXISTS employee (eid int, name string, salary string) 
COMMENT 'table with employee details'  
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' STORED AS TEXTFILE;

No rows affected (0.81 seconds)

0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> show tables;
+-----------+--+
| tab_name  |
+-----------+--+
| employee  |
+-----------+--+

Get the DDL or create script

Execute SHOW CREATE TABLE <tablename>  to get the create table script. You can see that the create table script produced by Hive is much more descriptive and has more details than the create table script executed by us. This is because Hive automatically substitutes default values for properties for which we did not provide a value.

0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> SHOW CREATE TABLE employee;

+--------------------------------------------------------------------+--+
|                           createtab_stmt                           |
+--------------------------------------------------------------------+--+

| CREATE TABLE `employee`(                                           |
|   `eid` int,                                                       |
|   `name` string,                                                   |
|   `salary` string)                                                 |
| COMMENT 'table with employee details'                              |
| ROW FORMAT DELIMITED                                               |
|   FIELDS TERMINATED BY '\t'                                        |
|   LINES TERMINATED BY '\n'                                         |
| STORED AS INPUTFORMAT                                              |
|   'org.apache.hadoop.mapred.TextInputFormat'                       |
| OUTPUTFORMAT                                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'     |
| LOCATION                                                           |
|   'hdfs://ms1.hirw.com:8020/apps/hive/warehouse/hirw.db/employee'  |
| TBLPROPERTIES (                                                    |
|   'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',            |
|   'numFiles'='0',                                                  |
|   'numRows'='0',                                                   |
|   'rawDataSize'='0',                                               |
|   'totalSize'='0',                                                 |
|   'transient_lastDdlTime'='1605027545')                            |
+--------------------------------------------------------------------+--+