{"id":11896,"date":"2018-03-29T05:16:38","date_gmt":"2018-03-29T05:16:38","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=11896"},"modified":"2021-05-09T13:11:15","modified_gmt":"2021-05-09T07:41:15","slug":"impala-drop-database-statement","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/","title":{"rendered":"Impala DROP DATABASE Statement – Process & Example"},"content":{"rendered":"

In\u00a0our last Impala tutorial<\/strong>, we studied Impala Create Database<\/strong>. Today we see How Impala Drop a Database when we need to remove a database from Impala, we use Impala DROP DATABASE Statement. <\/span><\/p>\n

However, there is much more to know about \u00a0Impala DROP DATABASE Statement. Also, we will cover its syntax, usage as well as the example to understand well.<\/span><\/p>\n

So, let’s discuss How Impala DROP Database Statements.<\/p>\n

Introduction – Impala DROP DATABASE\u00a0<\/span><\/h2>\n

In order to remove a database from Impala, we use Impala DROP DATABASE Statement. Moreover, it removes the metadata for the database from the Metastore. Also, deletes the corresponding *.db directory from HDFS<\/strong>.<\/span><\/p>\n

    \n
  • Basically, it removes a database from the system.<\/span><\/li>\n
  • However, it is recommended to remove all the tables from it, before deleting the database.<\/span><\/li>\n<\/ul>\n

    a. Syntax to Impala DROP DATABASE\u00a0<\/span><\/h3>\n

    Here, is the syntax of the DROP DATABASE Statement, below;<\/span><\/p>\n

    DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT | CASCADE];<\/pre>\n
    b. Statement type<\/span><\/h3>\n
    This statement is of DDL Type.<\/span><\/p>\n
    c. Usage of Impala DROP DATABASE Statement<\/span><\/h3>\n
    Basically, to avoid losing any data, keep the database empty before it can be dropped.<\/span>
    \n<\/span>
    \n<\/span>Though, we can include the CASCADE clause to make Impala drop all tables and other objects in the database before dropping the database itself, in Impala 2.3 and higher. <\/span><\/p>\n
    So, there is one demand of the RESTRICT clause that the database is empty before being dropped. However,\u00a0this clause is optional\u00a0since the RESTRICT behavior is still the default.<\/span>
    \n<\/span>
    \n<\/span>Make sure to drop or move all the objects inside the database manually before dropping the database itself, while we do not use the CASCADE clause:<\/span><\/p>\n
      \n
    • In order to locate all tables and views in the database, use the SHOW TABLES statement. Hence, to remove them all, \u00a0issue DROP TABLE and DROP VIEW statements.<\/span><\/li>\n
    • Further, to locate all user-defined functions in the database, use the SHOW FUNCTIONS and SHOW AGGREGATE FUNCTIONS statements. Again to remove them all, issue DROP FUNCTION and DROP AGGREGATE FUNCTION statements.<\/span><\/li>\n
    • Also, while removing the database itself, use ALTER TABLE and ALTER VIEW, to keep tables or views contained by a database or to move the relevant objects to a different database before dropping the original database.<\/span><\/li>\n<\/ul>\n
      Although, make sure we cannot drop the current database. Current database means the database through which our session is connected to either by the USE statement or the -d option of impala-shell. Also, remember to issue a USE statement to switch to a different database first. <\/span><\/p>\n
      So, the reason behind it is the default database is always available, issuing USE default is a convenient way to leave the current database before dropping it.<\/span><\/p>\n
      d. Hive Considerations<\/span><\/h3>\n
      When you drop a database in Impala, the database can no longer be used by Hive<\/strong>.<\/span><\/p>\n
      Amazon S3 Considerations<\/span><\/h2>\n
      As we know, we can create or remove folders as needed in the Amazon S3 system Through following commands of Impala 2.6 and higher. Such as CREATE DATABASE, CREATE TABLE<\/strong>, DROP DATABASE CASCADE, DROP TABLE<\/strong>, and ALTER TABLE<\/strong> [ADD|DROP] PARTITION. <\/span><\/p>\n
      However, previous to Impala 2.6, we needed to create folders\u00a0manually,\u00a0 and point Impala database, tables, or partitions at them. Also, we require removing folders when no longer needed on our own.<\/span><\/p>\n
      Cancellation<\/span><\/h2>\n
      It is not possible to cancel the Impala DROP database Statement. That implies it Cannot be canceled.<\/span><\/p>\n
      HDFS Permissions<\/span><\/h2>\n
      However, it is essential that for the user ID that the ImpalaD daemon runs under, have to write permission for the parent HDFS directory under which the database is located.<\/span><\/p>\n
      Examples of Drop Database in Impala<\/span><\/h2>\n
      For Example,
      \n<\/span><\/p>\n
      create database first_db;\nuse first_db;\ncreate table t1 (x int);\ncreate database second_db;\nuse second_db;\n<\/pre>\n
      — Each database has its own namespace for tables.<\/span>
      \n<\/span>— You can reuse the same table names in each database.<\/span>
      \n<\/span><\/p>\n
      create table t1 (s string);\ncreate database temp;\n<\/pre>\n
      — You can either USE a database after creating it,<\/span>
      \n<\/span>— or qualify all references to the table name with the name of the database.<\/span>
      \n<\/span>
      \n— Here, tables T2 and T3 are both created in the TEMP database.<\/span><\/p>\n
      create table temp.t2 (x int, y int);\nuse database temp;\ncreate table t3 (s string);\n<\/pre>\n
      — You cannot drop a database while it is selected by the USE statement.<\/span>
      \n<\/span>drop database temp;<\/span>
      \n<\/span>
      \nERROR:<\/strong> AnalysisException: Cannot drop current default database: temp<\/span>
      \n<\/span>
      \n<\/span>— The always-available database ‘default’ is a convenient one to USE<\/span>
      \n<\/span>
      \n— before dropping a database you created.<\/span>
      \n<\/span>use default;<\/span>
      \n<\/span>
      \n<\/span>— Before dropping a database, first, drop all the tables inside it,<\/span>
      \n<\/span>— or in Impala 2.3 and higher use the CASCADE clause.<\/span>
      \n<\/span>drop database temp;<\/span>
      \n<\/span><\/p>\n
      ERROR:<\/strong> ImpalaRuntimeException: Error making ‘dropDatabase’ RPC to Hive Metastore:<\/span>
      \n<\/span>CAUSED BY: InvalidOperationException: Database temp is not empty<\/span>
      \n<\/span>show tables in temp;<\/span>
      \n<\/span>+——+<\/span>
      \n<\/span>| name |<\/span>
      \n<\/span>+——+<\/span>
      \n<\/span>| t3 \u00a0\u00a0|<\/span>
      \n<\/span>+——+<\/span>
      \n<\/span>
      \n<\/span>— Impala 2.3 and higher:<\/span>
      \n<\/span>drop database temp cascade;<\/span>
      \n<\/span>
      \n<\/span>— Earlier releases:<\/span>
      \n<\/span>drop table temp.t3;<\/span>
      \n<\/span>drop database temp;<\/span><\/p>\n
      So, this was all about Impala Drop Database Statement. Hope you like our explanation.<\/p>\n
      Conclusion – Impala Drop Database<\/span><\/h2>\n
      So, we have seen the whole concept of Impala DROP DATABASE Statement. Still, if any doubt occurs, feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
      In\u00a0our last Impala tutorial, we studied Impala Create Database. Today we see How Impala Drop a Database when we need to remove a database from Impala, we use Impala DROP DATABASE Statement. However, there...<\/p>\n","protected":false},"author":6,"featured_media":19078,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[4070,5588,5689,6512,6513,6514,6515],"class_list":["post-11896","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-impala","tag-drop-database-statement-in-impala","tag-hdfs-permission","tag-hive-cancellation","tag-impala-drop-data","tag-impala-drop-data-base-example","tag-impala-drop-database","tag-impala-drop-database-statement"],"yoast_head":"\nImpala DROP DATABASE Statement - Process & Example - DataFlair<\/title>\n<meta name=\"description\" content=\"Impala DROP DATABASE Statement- usage, Syntax & example of Drop database in Impala, HDFS Permissions, Amazon S3 Considerations\" \/>\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\/impala-drop-database-statement\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Impala DROP DATABASE Statement - Process & Example - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Impala DROP DATABASE Statement- usage, Syntax & example of Drop database in Impala, HDFS Permissions, Amazon S3 Considerations\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/\" \/>\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-29T05:16:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-05-09T07:41:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-DROP-DATABASE-Statement-01-2.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\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=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Impala DROP DATABASE Statement - Process & Example - DataFlair","description":"Impala DROP DATABASE Statement- usage, Syntax & example of Drop database in Impala, HDFS Permissions, Amazon S3 Considerations","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\/impala-drop-database-statement\/","og_locale":"en_US","og_type":"article","og_title":"Impala DROP DATABASE Statement - Process & Example - DataFlair","og_description":"Impala DROP DATABASE Statement- usage, Syntax & example of Drop database in Impala, HDFS Permissions, Amazon S3 Considerations","og_url":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-29T05:16:38+00:00","article_modified_time":"2021-05-09T07:41:15+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-DROP-DATABASE-Statement-01-2.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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Impala DROP DATABASE Statement – Process & Example","datePublished":"2018-03-29T05:16:38+00:00","dateModified":"2021-05-09T07:41:15+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/"},"wordCount":804,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-DROP-DATABASE-Statement-01-2.jpg","keywords":["DROP database Statement in Impala","HDFS Permission","Hive cancellation","Impala Drop Data","Impala drop data base example","Impala DROP DATABASE","Impala DROP DATABASE Statement"],"articleSection":["Impala Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/","url":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/","name":"Impala DROP DATABASE Statement - Process & Example - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-DROP-DATABASE-Statement-01-2.jpg","datePublished":"2018-03-29T05:16:38+00:00","dateModified":"2021-05-09T07:41:15+00:00","description":"Impala DROP DATABASE Statement- usage, Syntax & example of Drop database in Impala, HDFS Permissions, Amazon S3 Considerations","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-DROP-DATABASE-Statement-01-2.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-DROP-DATABASE-Statement-01-2.jpg","width":1200,"height":628,"caption":"Impala DROP DATABASE Statement"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/impala-drop-database-statement\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Impala Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/impala\/"},{"@type":"ListItem","position":3,"name":"Impala DROP DATABASE Statement – Process & Example"}]},{"@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\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11896","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\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=11896"}],"version-history":[{"count":1,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11896\/revisions"}],"predecessor-version":[{"id":94053,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11896\/revisions\/94053"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/19078"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=11896"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=11896"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=11896"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}