{"id":3935,"date":"2017-09-05T11:20:35","date_gmt":"2017-09-05T11:20:35","guid":{"rendered":"http:\/\/data-flair.training\/blogs\/?p=3935"},"modified":"2017-09-05T11:20:35","modified_gmt":"2017-09-05T11:20:35","slug":"hive-internal-tables-vs-external-tables","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/","title":{"rendered":"Comparison between Hive Internal Tables vs External Tables"},"content":{"rendered":"

Curious to know different types of Hive\u00a0tables and how they are different from each other?<\/strong><\/p>\n

As discussed the basics of Hive tables in\u00a0Hive Data Models,<\/strong>\u00a0let us now explore\u00a0the major difference between hive internal and external tables.<\/p>\n

In this article, we are going to discuss the two different types of Hive Table that are Internal table (Managed table) and External table. The article then enlists the differences between Hive Internal tables and External Tables. We will also see different cases where we can use these Hive tables.<\/p>\n

So, let’s start with Hive internal tables and external tables.<\/p>\n

Introduction to Hive Internal and External tables<\/h2>\n

 <\/p>\n

Fundamentally, Hive knows two different types of tables: Internal\u00a0table<\/strong> and the External table<\/strong>. The Internal table is also known as the managed table.<\/p>\n

We can identify the internal or External tables using the DESCRIBE FORMATTED table_name<\/code><\/strong> statement in the Hive, which will display either MANAGED_TABLE<\/code><\/strong> or EXTERNAL_TABLE<\/code><\/strong> depending on the table type.<\/p>\n

1. Hive Internal Table<\/h3>\n

Hive owns the data for the internal tables.<\/p>\n

It is the default table in Hive. When the user creates a table in Hive without specifying it as external, then by default, an internal table gets created in a specific location in HDFS.<\/p>\n

By default, an internal table will be created in a folder path similar to \/user\/hive\/warehouse<\/strong> directory of HDFS. We can override the default location by the location property during table creation.<\/p>\n

If we drop the managed table or partition, the table data and the metadata associated with that table will be deleted from the HDFS.<\/p>\n

2. Hive External Table<\/h3>\n

Hive does not manage the data of the External table.<\/p>\n

We create an external table for external use as when we want to use the data outside the Hive.<\/p>\n

External tables are stored outside the warehouse directory. They can access data stored in sources such as remote HDFS locations or Azure Storage Volumes.<\/p>\n

Whenever we drop the external table, then only the metadata associated with the table will get deleted, the table data remains untouched by Hive.<\/p>\n

We can create the external table by specifying the EXTERNAL<\/strong> keyword in the Hive create table statement.<\/p>\n

Difference between Hive Internal and External Table<\/h2>\n

\"hive<\/a><\/p>\n

Let us now see the difference between both Hive tables. The major differences in the internal and external tables in Hive are:<\/p>\n

1. LOAD semantics<\/h3>\n

The Load semantics varies in both the tables. Let us see the difference in load semantics between the internal table and the external table.<\/p>\n

a. Internal Table<\/h4>\n

When we load data into an internal table, then Hive moves data into the warehouse directory.<\/p>\n

For example:<\/strong><\/p>\n

Here in this example, we are creating a table \u2018internaldemo\u2019. When we load data into the \u2018internaldemo\u2019 table, then Hive moves the data to the warehouse directory.<\/p>\n

\"create<\/a><\/p>\n

Now, loading data into the internal table created above. \"load<\/a><\/p>\n

On describing the table, we see that the table data is moved to the Hive warehouse directory.\"describe<\/a><\/p>\n

b. External Table<\/h4>\n

With the EXTERNAL keyword, Hive knows that it is not managing the table data, so it does not move data to its warehouse directory. Hive does not even check whether the external location at the time it is defined exists or not.<\/p>\n

For example:<\/strong><\/p>\n

In this example, we are creating an external table, \u2018external_demo\u2019 on the specified location that is \u2018\/home\/dataflair\/\u2019. On loading the data into the external table, the Hive does not move table data to its warehouse directory.<\/p>\n

\"create<\/a><\/p>\n

Now, loading data to the external table created above. \"load<\/a><\/p>\n

On browsing the table, we can see that the Hive table data is not moved to the Hive warehouse directory. It is stored in the location specified while creating a table.\"describe<\/a><\/p>\n

\n

2. DROP semantics<\/h3>\n

Like load semantics, drop semantics also varies in both tables. Let us see the difference in drop semantics between the internal table and the external table.<\/p>\n

a. Internal table<\/h4>\n

Dropping the internal table will delete the table data, as well as the metadata associated with the table.<\/p>\n

For example:<\/strong><\/p>\n

In this example, we are dropping the managed table \u2018internaldemo\u2019. You can see that it will delete both the table metadata as well as the table data.<\/p>\n

\"drop<\/a><\/p>\n

\"locating<\/a><\/p>\n

Table data also gets deleted from the HDFS.<\/p>\n

b. External table<\/h4>\n

Dropping the external table will delete only table metadata. The table content remains untouched.<\/p>\n

For example:<\/strong><\/p>\n

In this example, we can see that on deleting the external table \u2018external demo\u2019, the content of the table is still present in the HDFS location.<\/p>\n

\"droping<\/a><\/p>\n

\"locating<\/a><\/p>\n

3. TRUNCATE support<\/h3>\n

The TRUNCATE command<\/strong> only works for the internal table.<\/p>\n

For example:<\/strong><\/p>\n

In this example, we are trying to use the truncate command with \u2018externaldemo\u2019 table. We will get an error message that says \u2018Cannot truncate non-managed table externaldemo\u2019.<\/p>\n

\"truncate<\/a><\/p>\n

4. ACID Support<\/h3>\n

ACID\/transactional works only for the internal table. They do not work for External Table.<\/p>\n

5. Query Result Caching<\/h3>\n

Query Result Caching that saves the results of an executed Hive query for reuse on subsequent queries only works for the internal table.<\/p>\n

Thus there are some differences between the Hive internal table and the external table.<\/p>\n

Let us now see when to use which Hive table.<\/p>\n

When to use the Internal and External table?<\/h2>\n

1. Hive Internal Table<\/h3>\n

We can use the internal table in cases:<\/p>\n

    \n
  • When generating temporary tables.<\/li>\n
  • When required that Hive should manage the lifecycle of the table.<\/li>\n
  • And when we don\u2019t want table data after deletion.<\/li>\n<\/ul>\n

    2. Hive External Table<\/h3>\n

    We can use the external table in cases:<\/p>\n

      \n
    • When we are not creating the table based on the existing table.<\/li>\n
    • When required to use data outside of Hive. For example, the data files are read and processed by an existing program that does not lock the files.<\/li>\n
    • When we don\u2019t want to delete the table data completely even after DROP.<\/li>\n
    • When the data should not be own by Hive.<\/li>\n<\/ul>\n

      So, this was all in Hive internal and external tables. I hope you like our explanation.<\/p>\n

      Conclusion<\/h2>\n

      After reading the article, we can conclude that the Hive tables are of two types- internal table, also known as the managed table, and the External table.<\/p>\n

      The internal table data gets managed by the Hive. Hive is not responsible for managing data of the External table.<\/p>\n

      On deleting the Hive internal table, the table data and the metadata both get deleted and on deleting the Hive External table, only the table metadata will delete.<\/p>\n","protected":false},"excerpt":{"rendered":"

      Curious to know different types of Hive\u00a0tables and how they are different from each other? As discussed the basics of Hive tables in\u00a0Hive Data Models,\u00a0let us now explore\u00a0the major difference between hive internal and...<\/p>\n","protected":false},"author":7,"featured_media":76921,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[22062,22058,22060,22061,22063,5708,22059,5731,5794],"class_list":["post-3935","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-create-external-table-in-hive","tag-difference-between-internal-and-external-table-in-hive","tag-difference-between-internal-table-and-external-table-in-hive","tag-hive-external-and-internal-tables","tag-hive-external-table-example","tag-hive-external-tables","tag-hive-internal-and-external-tables","tag-hive-internal-tables","tag-hive-tables"],"yoast_head":"\nComparison between Hive Internal Tables vs External Tables - DataFlair<\/title>\n<meta name=\"description\" content=\"Apache Hive internal & external tables cover feature wise differences in Internal tables & external tables,usage of Internal & external Tables with example.\" \/>\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-internal-tables-vs-external-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Comparison between Hive Internal Tables vs External Tables - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Apache Hive internal & external tables cover feature wise differences in Internal tables & external tables,usage of Internal & external Tables with example.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/\" \/>\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=\"2017-09-05T11:20:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/Hive-internal-table-vs-external-table.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=\"8 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Comparison between Hive Internal Tables vs External Tables - DataFlair","description":"Apache Hive internal & external tables cover feature wise differences in Internal tables & external tables,usage of Internal & external Tables with example.","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-internal-tables-vs-external-tables\/","og_locale":"en_US","og_type":"article","og_title":"Comparison between Hive Internal Tables vs External Tables - DataFlair","og_description":"Apache Hive internal & external tables cover feature wise differences in Internal tables & external tables,usage of Internal & external Tables with example.","og_url":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2017-09-05T11:20:35+00:00","og_image":[{"width":802,"height":420,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/Hive-internal-table-vs-external-table.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":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"Comparison between Hive Internal Tables vs External Tables","datePublished":"2017-09-05T11:20:35+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/"},"wordCount":1065,"commentCount":10,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/Hive-internal-table-vs-external-table.jpg","keywords":["create external table in Hive","difference between internal and external table in Hive","difference between internal table and external table in Hive","Hive external and internal tables","Hive external table example","Hive External tables","Hive internal and external tables","Hive Internal Tables","Hive Tables"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/","url":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/","name":"Comparison between Hive Internal Tables vs External Tables - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/Hive-internal-table-vs-external-table.jpg","datePublished":"2017-09-05T11:20:35+00:00","description":"Apache Hive internal & external tables cover feature wise differences in Internal tables & external tables,usage of Internal & external Tables with example.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/Hive-internal-table-vs-external-table.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/09\/Hive-internal-table-vs-external-table.jpg","width":802,"height":420,"caption":"hive internal and external tableshive internal and external tables"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hive-internal-tables-vs-external-tables\/#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":"Comparison between Hive Internal Tables vs External Tables"}]},{"@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\/3935","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=3935"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/3935\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/76921"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=3935"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=3935"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=3935"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}