LATERAL VIEW joins resulting output exploded rows to the input rows from employee providing the below output.<\/span><\/p>\nSELECT ename, dept_id FROM employee\nLATERAL VIEW explode(dept_list) depts AS dept_id;\n+--------+----------+--+\n| ename\u00a0 | dept_id\u00a0 |\n+--------+----------+--+\n| Tom\u00a0 \u00a0 | 20 \u00a0 \u00a0 \u00a0 |\n| Jerry\u00a0 | 10 \u00a0 \u00a0 \u00a0 |\n| Jerry\u00a0 | 20 \u00a0 \u00a0 \u00a0 |\n| Riley\u00a0 | 20 \u00a0 \u00a0 \u00a0 |\n| Riley\u00a0 | 30 \u00a0 \u00a0 \u00a0 |\n| Riley\u00a0 | 40 \u00a0 \u00a0 \u00a0 |\n+--------+----------+--+\n6 rows selected (0.16 seconds)<\/pre>\nWe can enhance the above query to group by ename and count on the dept_id to get the number of departments an employee belongs to.<\/span><\/p>\nSELECT ename, count(dept_id) FROM employee\nLATERAL VIEW explode(dept_list) depts AS dept_id\nGROUP BY ename;\n+--------+------+--+\n| ename\u00a0 | _c1\u00a0 |\n+--------+------+--+\n| Jerry\u00a0 | 2\u00a0 \u00a0 |\n| Riley\u00a0 | 3\u00a0 \u00a0 |\n| Tom\u00a0 \u00a0 | 1\u00a0 \u00a0 |\n+--------+------+--+\n3 rows selected (30.312 seconds)<\/pre>\n","protected":false},"excerpt":{"rendered":"LATERAL VIEW and EXPLODE are 2 different things in Hive. Lateral view is used in conjunction with user-defined table generating functions such as explode().\u00a0 Problem Let\u2019s [\u2026]<\/span><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-2003","post","type-post","status-publish","format-standard","hentry","category-apache-hive"],"yoast_head":"\nWhat is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive? - Big Data In Real World<\/title>\n<meta name=\"description\" content=\"Learn the difference with examples between explode and lateral view and when to use them in Hive in this post.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive? - Big Data In Real World\" \/>\n<meta property=\"og:description\" content=\"Learn the difference with examples between explode and lateral view and when to use them in Hive in this post.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/\" \/>\n<meta property=\"og:site_name\" content=\"Big Data In Real World\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/bigdatainrealworld\" \/>\n<meta property=\"article:published_time\" content=\"2021-10-15T11:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-19T13:31:05+00:00\" \/>\n<meta name=\"author\" content=\"Big Data In Real World\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Big Data In Real World\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/\"},\"author\":{\"name\":\"Big Data In Real World\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67\"},\"headline\":\"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive?\",\"datePublished\":\"2021-10-15T11:00:00+00:00\",\"dateModified\":\"2023-02-19T13:31:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/\"},\"wordCount\":281,\"publisher\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#organization\"},\"articleSection\":[\"Apache Hive\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/\",\"url\":\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/\",\"name\":\"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive? - Big Data In Real World\",\"isPartOf\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#website\"},\"datePublished\":\"2021-10-15T11:00:00+00:00\",\"dateModified\":\"2023-02-19T13:31:05+00:00\",\"description\":\"Learn the difference with examples between explode and lateral view and when to use them in Hive in this post.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.bigdatainrealworld.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#website\",\"url\":\"https:\/\/www.bigdatainrealworld.com\/\",\"name\":\"Big Data In Real World\",\"description\":\"Learn Big Data from experts!\",\"publisher\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.bigdatainrealworld.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#organization\",\"name\":\"Big Data In Real World\",\"url\":\"https:\/\/www.bigdatainrealworld.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.bigdatainrealworld.com\/wp-content\/uploads\/2023\/02\/black.png\",\"contentUrl\":\"https:\/\/www.bigdatainrealworld.com\/wp-content\/uploads\/2023\/02\/black.png\",\"width\":500,\"height\":500,\"caption\":\"Big Data In Real World\"},\"image\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/bigdatainrealworld\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67\",\"name\":\"Big Data In Real World\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d332bc24fe9b3182f0a22135f163ac4e?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d332bc24fe9b3182f0a22135f163ac4e?s=96&d=retro&r=g\",\"caption\":\"Big Data In Real World\"},\"description\":\"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.\",\"sameAs\":[\"https:\/\/www.bigdatainrealworld.com\/\"],\"url\":\"https:\/\/www.bigdatainrealworld.com\/author\/bigdatainrealworld\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive? - Big Data In Real World","description":"Learn the difference with examples between explode and lateral view and when to use them in Hive in this post.","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:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/","og_locale":"en_US","og_type":"article","og_title":"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive? - Big Data In Real World","og_description":"Learn the difference with examples between explode and lateral view and when to use them in Hive in this post.","og_url":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/","og_site_name":"Big Data In Real World","article_publisher":"https:\/\/www.facebook.com\/bigdatainrealworld","article_published_time":"2021-10-15T11:00:00+00:00","article_modified_time":"2023-02-19T13:31:05+00:00","author":"Big Data In Real World","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Big Data In Real World","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/#article","isPartOf":{"@id":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/"},"author":{"name":"Big Data In Real World","@id":"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67"},"headline":"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive?","datePublished":"2021-10-15T11:00:00+00:00","dateModified":"2023-02-19T13:31:05+00:00","mainEntityOfPage":{"@id":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/"},"wordCount":281,"publisher":{"@id":"https:\/\/www.bigdatainrealworld.com\/#organization"},"articleSection":["Apache Hive"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/","url":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/","name":"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive? - Big Data In Real World","isPartOf":{"@id":"https:\/\/www.bigdatainrealworld.com\/#website"},"datePublished":"2021-10-15T11:00:00+00:00","dateModified":"2023-02-19T13:31:05+00:00","description":"Learn the difference with examples between explode and lateral view and when to use them in Hive in this post.","breadcrumb":{"@id":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.bigdatainrealworld.com\/what-is-the-difference-between-explode-and-lateral-view-explode-in-hive\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.bigdatainrealworld.com\/"},{"@type":"ListItem","position":2,"name":"What is the difference between EXPLODE and LATERAL VIEW EXPLODE in Hive?"}]},{"@type":"WebSite","@id":"https:\/\/www.bigdatainrealworld.com\/#website","url":"https:\/\/www.bigdatainrealworld.com\/","name":"Big Data In Real World","description":"Learn Big Data from experts!","publisher":{"@id":"https:\/\/www.bigdatainrealworld.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.bigdatainrealworld.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.bigdatainrealworld.com\/#organization","name":"Big Data In Real World","url":"https:\/\/www.bigdatainrealworld.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.bigdatainrealworld.com\/#\/schema\/logo\/image\/","url":"https:\/\/www.bigdatainrealworld.com\/wp-content\/uploads\/2023\/02\/black.png","contentUrl":"https:\/\/www.bigdatainrealworld.com\/wp-content\/uploads\/2023\/02\/black.png","width":500,"height":500,"caption":"Big Data In Real World"},"image":{"@id":"https:\/\/www.bigdatainrealworld.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/bigdatainrealworld"]},{"@type":"Person","@id":"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67","name":"Big Data In Real World","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d332bc24fe9b3182f0a22135f163ac4e?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d332bc24fe9b3182f0a22135f163ac4e?s=96&d=retro&r=g","caption":"Big Data In Real World"},"description":"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.","sameAs":["https:\/\/www.bigdatainrealworld.com\/"],"url":"https:\/\/www.bigdatainrealworld.com\/author\/bigdatainrealworld\/"}]}},"_links":{"self":[{"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts\/2003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/comments?post=2003"}],"version-history":[{"count":2,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts\/2003\/revisions"}],"predecessor-version":[{"id":2102,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts\/2003\/revisions\/2102"}],"wp:attachment":[{"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/media?parent=2003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/categories?post=2003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/tags?post=2003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}