

{"id":10450,"date":"2018-03-10T09:37:28","date_gmt":"2018-03-10T09:37:28","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=10450"},"modified":"2018-03-10T09:37:28","modified_gmt":"2018-03-10T09:37:28","slug":"hive-ddl-commands","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/","title":{"rendered":"Hive DDL Commands &#8211; Types of DDL Hive Commands"},"content":{"rendered":"<p>In this article, we are going to learn Hive DDL commands. The article describes the Hive Data Definition Language(DDL) commands for performing various operations like creating a table\/database in Hive, dropping a table\/database in Hive, altering a table\/database in Hive, etc.<\/p>\n<p>There are many DDL commands. This article will cover each DDL command individually, along with their syntax and examples.<\/p>\n<p>For running Hive DDL commands, you must have Hive installed on your system.<\/p>\n<p>Follow the <a href=\"https:\/\/data-flair.training\/blogs\/apache-hive-installation\/\"><strong>Hive-3.1.2 installation guide<\/strong><\/a> to install Hive on your system.<\/p>\n<h2>Introduction to Hive DDL commands<\/h2>\n<p>Hive DDL commands are the statements used for defining and changing the structure of a table or database in Hive. It is used to build or modify the tables and other objects in the database.<\/p>\n<p>The several types of Hive DDL commands are:<\/p>\n<ol>\n<li>CREATE<\/li>\n<li>SHOW<\/li>\n<li>DESCRIBE<\/li>\n<li>USE<\/li>\n<li>DROP<\/li>\n<li>ALTER<\/li>\n<li>TRUNCATE<\/li>\n<\/ol>\n<p><strong>Table-1 Hive DDL commands<\/strong><\/p>\n<table class=\"df-table-center\">\n<tbody>\n<tr>\n<td><b>DDL Command<\/b><\/td>\n<td><b>Use With<\/b><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">CREATE<\/span><\/td>\n<td><span style=\"font-weight: 400\">Database, Table<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">SHOW<\/span><\/td>\n<td><span style=\"font-weight: 400\">Databases, Tables, Table Properties, Partitions, Functions, Index<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">DESCRIBE<\/span><\/td>\n<td><span style=\"font-weight: 400\">Database, Table, view<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">USE<\/span><\/td>\n<td><span style=\"font-weight: 400\">Database<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">DROP<\/span><\/td>\n<td><span style=\"font-weight: 400\">Database, Table<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">ALTER<\/span><\/td>\n<td><span style=\"font-weight: 400\">Database, Table<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">TRUNCATE<\/span><\/td>\n<td><span style=\"font-weight: 400\">Table<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Before moving forward, note that the Hive commands are\u00a0<strong>case-insensitive<\/strong>.<\/p>\n<p>CREATE DATABASE is the same as create database.<\/p>\n<p>So now, let us go through each of the commands deeply. Let&#8217;s start with the DDL commands on Databases in Hive.<\/p>\n<h3>DDL Commands On Databases in Hive<\/h3>\n<h4>1. CREATE DATABASE in Hive<\/h4>\n<p>The <strong>CREATE DATABASE<\/strong> statement is used to create a database in the Hive. The DATABASE and SCHEMA are interchangeable. We can use either DATABASE or SCHEMA.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name\n[COMMENT database_comment]\n[LOCATION hdfs_path]\n[WITH DBPROPERTIES (property_name=property_value, ...)];<\/pre>\n<p><strong>DDL CREATE DATABASE Example:<\/strong><\/p>\n<p>Here in this example, we are creating a database \u2018dataflair\u2019.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76644\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1.png\" alt=\"CREATE DATABASE - Hive DDL commands\" width=\"1299\" height=\"177\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1-150x20.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1-300x41.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1-768x105.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1-1024x140.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATEDATABASE-HiveDDLcommands1-520x71.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76645\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2.png\" alt=\"Creating DataBase - Hive DDL commands\" width=\"1299\" height=\"687\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2-150x79.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2-300x159.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2-768x406.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2-1024x542.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CreatingDataBase-HiveDDLcommands2-520x275.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>2. SHOW DATABASE in Hive<\/h4>\n<p>The<strong> SHOW DATABASES<\/strong> statement lists all the databases present in the Hive.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">SHOW (DATABASES|SCHEMAS);<\/pre>\n<p><strong>DDL SHOW DATABASES Example:<\/strong><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76646\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3.png\" alt=\"SHOW DATABASES - Hive DDL commands\" width=\"1299\" height=\"161\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3-150x19.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3-300x37.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3-768x95.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3-1024x127.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWDATABASES-HiveDDLcommands3-520x64.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76647\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4.png\" alt=\"listing DataBase - Hive DDL commands\" width=\"1299\" height=\"741\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4-150x86.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4-300x171.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4-768x438.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4-1024x584.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands4-520x297.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>3. DESCRIBE DATABASE in Hive<\/h4>\n<p>The <strong>DESCRIBE DATABASE<\/strong> statement in Hive shows the name of Database in Hive, its comment (if set), and its location on the file system.<\/p>\n<p>The <strong>EXTENDED<\/strong> can be used to get the database properties.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">DESCRIBE DATABASE\/SCHEMA [EXTENDED] db_name;<\/pre>\n<p><strong>DDL DESCRIBE DATABASE Example:<\/strong><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76648\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5.png\" alt=\"DESCRIBE DATABASE - Hive DDL commands\" width=\"1299\" height=\"159\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5-150x18.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5-300x37.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5-768x94.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5-1024x125.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBEDATABASE-HiveDDLcommands5-520x64.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76649\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6.png\" alt=\"Describing DataBase - Hive DDL commands\" width=\"1299\" height=\"513\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6-150x59.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6-300x118.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6-768x303.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6-1024x404.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DescribingDataBase-HiveDDLcommands6-520x205.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>4. USE DATABASE in Hive<\/h4>\n<p>The <strong>USE<\/strong> statement in Hive is used to select the specific database for a session on which all subsequent HiveQL statements would be executed.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">USE database_name;<\/pre>\n<p><strong>DDL USE DATABASE Example:<\/strong><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76650\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7.png\" alt=\"USE DATA BASE - Hive DDL commands\" width=\"1299\" height=\"591\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7-150x68.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7-300x136.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7-768x349.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7-1024x466.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/USEDATABASE-HiveDDLcommands7-520x237.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>5. DROP DATABASE in Hive<\/h4>\n<p>The <strong>DROP DATABASE<\/strong> statement in Hive is used to Drop (delete) the database.<\/p>\n<p>The default behavior is RESTRICT which means that the database is dropped only when it is empty. To drop the database with tables, we can use CASCADE.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];<\/pre>\n<p><strong>DDL DROP DATABASE Example:<\/strong><\/p>\n<p>Here in this example, we are dropping a database \u2018dataflair\u2019 using the DROP statement.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76651\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8.png\" alt=\"DROP DATABASE - Hive DDL commands\" width=\"1299\" height=\"160\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8-150x18.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8-300x37.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8-768x95.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8-1024x126.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPDATABASE-HiveDDLcommands8-520x64.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76652\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9.png\" alt=\"listing DataBase - Hive DDL commands\" width=\"1299\" height=\"741\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9-150x86.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9-300x171.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9-768x438.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9-1024x584.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingDataBase-HiveDDLcommands9-520x297.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>6. ALTER DATABASE in Hive<\/h4>\n<p>The <strong>ALTER DATABASE<\/strong> statement in Hive is used to change the metadata associated with the database in Hive.<\/p>\n<p><strong>Syntax for changing Database Properties:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);<\/pre>\n<p><strong>DDL ALTER DATABASE properties Example:<\/strong><\/p>\n<p>In this example, we are setting the database properties of the \u2018dataflair\u2019 database after its creation by using the ALTER command.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76653\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10.png\" alt=\"SETDB PROPERTIES- Hive DDL commands\" width=\"1299\" height=\"147\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10-150x17.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10-300x34.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10-768x87.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10-1024x116.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETDBPROPERTIES-HiveDDLcommands10-520x59.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76654\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11.png\" alt=\"checking DB PROPERTIES - Hive DDL commands\" width=\"1299\" height=\"741\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11-150x86.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11-300x171.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11-768x438.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11-1024x584.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/checkingDBPROPERTIES-HiveDDLcommands11-520x297.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><strong>Syntax for changing Database owner:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;<\/pre>\n<p><strong>DDL ALTER DATABASE owner Example:<\/strong><\/p>\n<p>In this example, we are changing the owner role of the \u2018dataflair\u2019 database using the ALTER statement.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76655\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12.png\" alt=\"SETOWNERROLE - Hive DDL commands\" width=\"1299\" height=\"149\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12-150x17.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12-300x34.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12-768x88.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12-1024x117.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SETOWNERROLE-HiveDDLcommands12-520x60.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76656\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13.png\" alt=\"Showing changed role - Hive DDL commands\" width=\"1299\" height=\"741\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13-150x86.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13-300x171.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13-768x438.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13-1024x584.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/Showingchangedrole-HiveDDLcommands13-520x297.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><strong>Syntax for changing Database Location:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;<\/pre>\n<p><strong>Note:<\/strong> The ALTER DATABASE &#8230; SET LOCATION statement does not move the database current directory contents to the newly specified location.<\/p>\n<p>This statement does not change the locations associated with any tables or partitions under the specified database. Instead, it changes the default parent-directory, where new tables will be added for this database.<\/p>\n<p>No other metadata associated with the database can be changed.<\/p>\n<h3>DDL Commands on Tables in Hive<\/h3>\n<h4>1. CREATE TABLE<\/h4>\n<p>The <strong>CREATE TABLE<\/strong> statement in Hive is used to create a table with the given name. If a table or view already exists with the same name, then the error is thrown. We can use <strong>IF NOT EXISTS<\/strong> to skip the error.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">CREATE TABLE [IF NOT EXISTS] [db_name.] table_name [(col_name data_type [COMMENT col_comment], ... [COMMENT col_comment])] [COMMENT table_comment] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path];<\/pre>\n<p><strong>\u00a0DDL CREATE TABLE Example:<\/strong><\/p>\n<p>In this table, we are creating a table \u2018Employee\u2019 in the \u2018dataflair\u2019 database.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76657\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14.png\" alt=\"CREATE TABLE - Hive DDL commands\" width=\"1299\" height=\"307\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14-150x35.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14-300x71.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14-768x182.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14-1024x242.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/CREATETABLE-HiveDDLcommands14-520x123.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><strong>ROW FORMAT DELIMITED<\/strong> means we are telling the Hive that when it finds a new line character, that means a new record.<\/p>\n<p><strong>FIELDS TERMINATED BY \u2018,\u2019<\/strong> tells Hive what delimiter we are using in our files to separate each column.<\/p>\n<p><strong>STORED AS TEXTFILE<\/strong> is to tell Hive what type of file to expect.<\/p>\n<h4>2. SHOW TABLES in Hive<\/h4>\n<p>The <strong>SHOW TABLES<\/strong> statement in Hive lists all the base tables and <strong>views<\/strong> in the current database.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">SHOW TABLES [IN database_name];<\/pre>\n<p><strong>DDL SHOW TABLES Example:<\/strong><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76658\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15.png\" alt=\"SHOW TABLES - Hive DDL commands\" width=\"1299\" height=\"741\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15-150x86.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15-300x171.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15-768x438.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15-1024x584.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/SHOWTABLES-HiveDDLcommands15-520x297.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>3. DESCRIBE TABLE in Hive<\/h4>\n<p>The <strong>DESCRIBE<\/strong> statement in Hive shows the lists of columns for the specified table.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">DESCRIBE [EXTENDED|FORMATTED] [db_name.] table_name[.col_name ( [.field_name])];<\/pre>\n<p><strong>DDL DESCRIBE TABLE Example:<\/strong><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76659\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16.png\" alt=\"DESCRIBE TABLE - Hive DDL commands\" width=\"1299\" height=\"684\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16-150x79.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16-300x158.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16-768x404.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16-1024x539.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DESCRIBETABLE-HiveDDLcommands16-520x274.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>4. DROP TABLE in Hive<\/h4>\n<p>The <strong>DROP TABLE<\/strong> statement in Hive deletes the data for a particular table and remove all metadata associated with it from Hive metastore.<\/p>\n<p>If <strong>PURGE<\/strong> is not specified then the data is actually moved to the .Trash\/current directory. If <strong>PURGE<\/strong> is specified, then data is lost completely.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">DROP TABLE [IF EXISTS] table_name [PURGE];<\/pre>\n<p><strong>DDL DROP TABLE Example:<\/strong><\/p>\n<p>In the below example, we are deleting the \u2018employee\u2019 table.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76661\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17.png\" alt=\"DROP TABLE - Hive DDL commands\" width=\"1299\" height=\"582\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17-150x67.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17-300x134.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17-768x344.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17-1024x459.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/DROPTABLE-HiveDDLcommands17-520x233.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>5. ALTER TABLE in Hive<\/h4>\n<p>The <strong>ALTER TABLE<\/strong> statement in Hive enables you to change the structure of an existing table. Using the ALTER TABLE statement we can rename the table, add columns to the table, change the table properties, etc.<\/p>\n<p><strong>Syntax to Rename a table:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">ALTER TABLE table_name RENAME TO new_table_name;<\/pre>\n<p><strong>DDL ALTER TABLE name Example:<\/strong><\/p>\n<p>In this example, we are trying to rename the \u2018Employee\u2019 table to \u2018Com_Emp\u2019 using the ALTER statement.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76662\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18.png\" alt=\"ALTERTABLERENAME - Hive DDL commands\" width=\"1299\" height=\"585\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18-150x68.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18-300x135.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18-768x346.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18-1024x461.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLERENAME-HiveDDLcommands18-520x234.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76663\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19.png\" alt=\"listing table afterrename - Hive DDL commands\" width=\"1299\" height=\"741\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19-150x86.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19-300x171.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19-768x438.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19-1024x584.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/listingtableafterrename-HiveDDLcommands19-520x297.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><strong>Syntax to Add columns to a table:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">ALTER TABLE table_name ADD COLUMNS (column1, column2) ;<\/pre>\n<p><strong>DDL ALTER TABLE columns Example:<\/strong><\/p>\n<p>In this example, we are adding two columns \u2018Emp_DOB\u2019 and \u2018Emp_Contact\u2019 in the \u2018Comp_Emp\u2019 table using the ALTER command.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76664\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20.png\" alt=\"ALTER TABLE ADD COLUMN - Hive DDL commands\" width=\"1299\" height=\"585\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20-150x68.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20-300x135.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20-768x346.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20-1024x461.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEADDCOLUMN-HiveDDLcommands20-520x234.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76665\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21.png\" alt=\"describingtableafterrename-HiveDDLcommands\" width=\"1299\" height=\"741\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21-150x86.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21-300x171.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21-768x438.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21-1024x584.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/describingtableafterrename-HiveDDLcommands21-520x297.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<p><strong>Syntax to set table properties:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">ALTER TABLE table_name SET TBLPROPERTIES (\u2018property_key\u2019=\u2019property_new_value\u2019);<\/pre>\n<p><strong>DDL ALTER TABLE properties Example:<\/strong><\/p>\n<p>In this example, we are setting the table properties after table creation by using ALTER command.<\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76666\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22.png\" alt=\"ALTER TABLE PROPERTIES - Hive DDL commands\" width=\"1299\" height=\"570\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22-150x66.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22-300x132.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22-768x337.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22-1024x449.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/ALTERTABLEPROPERTIES-HiveDDLcommands22-520x228.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h4>6. TRUNCATE TABLE<\/h4>\n<p><strong>TRUNCATE TABLE<\/strong> statement in Hive removes all the rows from the table or partition.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">TRUNCATE TABLE table_name;<\/pre>\n<p><strong>DDL TRUNCATE TABLE Example:<\/strong><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-76667\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23.png\" alt=\"TRUNCATE TABLE - Hive DDL commands\" width=\"1299\" height=\"150\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23.png 1299w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23-150x17.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23-300x35.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23-768x89.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23-1024x118.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2020\/03\/TRUNCATETABLE-HiveDDLcommands23-520x60.png 520w\" sizes=\"auto, (max-width: 1299px) 100vw, 1299px\" \/><\/a><\/p>\n<h2>Summary<\/h2>\n<p>Thus in this article, we have studied how to create a database, drop a database, alter a database, etc. We have also studied statements for creating, dropping, altering a table in Hive.<\/p>\n<p>These are the basic HiveQL DDL statements that one should know before working with Hive.<\/p>\n<p><span style=\"font-weight: 400\">Still, if you have doubts, feel free to ask in the comment section.<\/span><\/p>\n<p>Keep Learning!!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this article, we are going to learn Hive DDL commands. The article describes the Hive Data Definition Language(DDL) commands for performing various operations like creating a table\/database in Hive, dropping a table\/database in&#46;&#46;&#46;<\/p>\n","protected":false},"author":7,"featured_media":76668,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[442,3061,3140,4067,5693,22050,5704,5785,15056],"class_list":["post-10450","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-altering-and-dropping-tables","tag-create-database-statement","tag-creating-hive-tables","tag-drop-database","tag-hive-commands","tag-hive-commands-with-examples","tag-hive-ddl-commands","tag-hive-show-database","tag-types-of-hive-ddl-commands"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Hive DDL Commands - Types of DDL Hive Commands - DataFlair<\/title>\n<meta name=\"description\" content=\"Hive DDL Commands- Explore how to Create Database, Show Database, Drop database, Create Hive Tables, Browse table, Select, Alter &amp; Drop Table.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Hive DDL Commands - Types of DDL Hive Commands - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Hive DDL Commands- Explore how to Create Database, Show Database, Drop database, Create Hive Tables, Browse table, Select, Alter &amp; Drop Table.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-10T09:37:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/hive-ddl-commands.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"802\" \/>\n\t<meta property=\"og:image:height\" content=\"420\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Hive DDL Commands - Types of DDL Hive Commands - DataFlair","description":"Hive DDL Commands- Explore how to Create Database, Show Database, Drop database, Create Hive Tables, Browse table, Select, Alter & Drop Table.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/","og_locale":"en_US","og_type":"article","og_title":"Hive DDL Commands - Types of DDL Hive Commands - DataFlair","og_description":"Hive DDL Commands- Explore how to Create Database, Show Database, Drop database, Create Hive Tables, Browse table, Select, Alter & Drop Table.","og_url":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-10T09:37:28+00:00","og_image":[{"width":802,"height":420,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/hive-ddl-commands.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"Hive DDL Commands &#8211; Types of DDL Hive Commands","datePublished":"2018-03-10T09:37:28+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/"},"wordCount":1031,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/hive-ddl-commands.jpg","keywords":["Altering and Dropping Tables","Create Database Statement","Creating Hive Tables","Drop database","Hive Commands","hive commands with examples","Hive DDL commands","Hive Show Database","types of Hive DDL commands"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/","url":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/","name":"Hive DDL Commands - Types of DDL Hive Commands - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/hive-ddl-commands.jpg","datePublished":"2018-03-10T09:37:28+00:00","description":"Hive DDL Commands- Explore how to Create Database, Show Database, Drop database, Create Hive Tables, Browse table, Select, Alter & Drop Table.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/hive-ddl-commands.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/hive-ddl-commands.jpg","width":802,"height":420,"caption":"hive ddl commands"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hive-ddl-commands\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Hive Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/hive\/"},{"@type":"ListItem","position":3,"name":"Hive DDL Commands &#8211; Types of DDL Hive Commands"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team specializes in creating clear, actionable content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Backed by industry expertise, we make learning easy and career-oriented for beginners and pros alike.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam3\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10450","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=10450"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10450\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/76668"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=10450"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=10450"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=10450"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}