{"id":2001,"date":"2021-09-03T06:00:00","date_gmt":"2021-09-03T11:00:00","guid":{"rendered":"https:\/\/www.bigdatainrealworld.com\/?p=2001"},"modified":"2023-02-19T07:31:17","modified_gmt":"2023-02-19T13:31:17","slug":"how-does-lateral-view-work-in-hive","status":"publish","type":"post","link":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/","title":{"rendered":"How does LATERAL VIEW work in Hive?"},"content":{"rendered":"

LATERAL VIEW is a very powerful concept in Hive. It is used when we have to work with data with complex types. Let\u2019s see this with an example.<\/p>\n

Problem<\/span><\/h2>\n

Let\u2019s say we have an employee table with employee name and an Array of department ids the employee belongs to.<\/span><\/p>\n

\u00a0select * from employee;<\/span><\/p>\n

+-----------------+---------------------+--+\n| employee.ename\u00a0 | employee.dept_list\u00a0 |\n+-----------------+---------------------+--+\n| Tom \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | [20]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| Jerry \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | [10,20] \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n| Riley \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | [20,30,40]\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n+-----------------+---------------------+--+<\/pre>\n
With the data above we would like to count the number of departments the employee belongs to. The output should look like below.<\/span><\/p>\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+--------+------+--+<\/pre>\n
This is an easy problem to solve if the data we have is not nested. But with our data the dept_list is an Array of integers providing the list of departments the employee belongs to.<\/span><\/p>\n
Solution<\/span><\/h2>\n
Lateral view is used in conjunction with user-defined table generating functions such as <\/span>explode()<\/span>. A UDTF generates zero or more output rows for each input row.\u00a0<\/span><\/p>\n
Click here if you like to know the difference between UDF, UDAF and UDTF<\/span><\/a><\/p>\n
A lateral view first applies the UDTF to each row of the base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.<\/span><\/p>\n
LATERAL VIEW can\u2019t function alone. It needs to be used along with a UDTF. Here we are using explode() to first explode the array to individual rows. For the exploded data we are naming the table as depts with a column dept_id.<\/span><\/p>\n
LATERAL VIEW joins resulting output exploded rows to the input rows from employee providing the below output.<\/span><\/p>\n
SELECT 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>\n
We 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>\n
SELECT 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 is a very powerful concept in Hive. It is used when we have to work with data with complex types. Let\u2019s see this with [\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-2001","post","type-post","status-publish","format-standard","hentry","category-apache-hive"],"yoast_head":"\nHow does LATERAL VIEW work in Hive? - Big Data In Real World<\/title>\n<meta name=\"description\" content=\"Detailed post explaining how LATERAL view work in Hive 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:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How does LATERAL VIEW work in Hive? - Big Data In Real World\" \/>\n<meta property=\"og:description\" content=\"Detailed post explaining how LATERAL view work in Hive with example.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-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-09-03T11:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-19T13:31:17+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\/how-does-lateral-view-work-in-hive\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/\"},\"author\":{\"name\":\"Big Data In Real World\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67\"},\"headline\":\"How does LATERAL VIEW work in Hive?\",\"datePublished\":\"2021-09-03T11:00:00+00:00\",\"dateModified\":\"2023-02-19T13:31:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/\"},\"wordCount\":282,\"publisher\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#organization\"},\"articleSection\":[\"Apache Hive\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/\",\"url\":\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/\",\"name\":\"How does LATERAL VIEW work in Hive? - Big Data In Real World\",\"isPartOf\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#website\"},\"datePublished\":\"2021-09-03T11:00:00+00:00\",\"dateModified\":\"2023-02-19T13:31:17+00:00\",\"description\":\"Detailed post explaining how LATERAL view work in Hive with example.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.bigdatainrealworld.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How does LATERAL VIEW work 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":"How does LATERAL VIEW work in Hive? - Big Data In Real World","description":"Detailed post explaining how LATERAL view work in Hive 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:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/","og_locale":"en_US","og_type":"article","og_title":"How does LATERAL VIEW work in Hive? - Big Data In Real World","og_description":"Detailed post explaining how LATERAL view work in Hive with example.","og_url":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/","og_site_name":"Big Data In Real World","article_publisher":"https:\/\/www.facebook.com\/bigdatainrealworld","article_published_time":"2021-09-03T11:00:00+00:00","article_modified_time":"2023-02-19T13:31:17+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\/how-does-lateral-view-work-in-hive\/#article","isPartOf":{"@id":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/"},"author":{"name":"Big Data In Real World","@id":"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67"},"headline":"How does LATERAL VIEW work in Hive?","datePublished":"2021-09-03T11:00:00+00:00","dateModified":"2023-02-19T13:31:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/"},"wordCount":282,"publisher":{"@id":"https:\/\/www.bigdatainrealworld.com\/#organization"},"articleSection":["Apache Hive"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/","url":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/","name":"How does LATERAL VIEW work in Hive? - Big Data In Real World","isPartOf":{"@id":"https:\/\/www.bigdatainrealworld.com\/#website"},"datePublished":"2021-09-03T11:00:00+00:00","dateModified":"2023-02-19T13:31:17+00:00","description":"Detailed post explaining how LATERAL view work in Hive with example.","breadcrumb":{"@id":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.bigdatainrealworld.com\/how-does-lateral-view-work-in-hive\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.bigdatainrealworld.com\/"},{"@type":"ListItem","position":2,"name":"How does LATERAL VIEW work 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\/2001"}],"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=2001"}],"version-history":[{"count":2,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts\/2001\/revisions"}],"predecessor-version":[{"id":2084,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts\/2001\/revisions\/2084"}],"wp:attachment":[{"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/media?parent=2001"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/categories?post=2001"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/tags?post=2001"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}