How to get DDL or create script of an existing Hive table? - Big Data In Real World

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

Hive architecture
October 22, 2021
Where does Hive store files for Hive tables?
October 29, 2021

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')                            |
+--------------------------------------------------------------------+--+

 

Big Data In Real World
Big Data In Real World
We are a group of Big Data engineers who are passionate about Big Data and related Big Data technologies. We have designed, developed, deployed and maintained Big Data applications ranging from batch to real time streaming big data platforms. We have seen a wide range of real world big data problems, implemented some innovative and complex (or simple, depending on how you look at it) solutions.

Comments are closed.

gdpr-image
This website uses cookies to improve your experience. By using this website you agree to our Data Protection Policy.

Hadoop In Real World is now Big Data In Real World!

X