{"id":1707,"date":"2021-08-06T06:00:00","date_gmt":"2021-08-06T11:00:00","guid":{"rendered":"https:\/\/www.bigdatainrealworld.com\/?p=1707"},"modified":"2023-02-19T07:31:17","modified_gmt":"2023-02-19T13:31:17","slug":"how-to-export-a-hive-table-into-a-csv-file","status":"publish","type":"post","link":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/","title":{"rendered":"How to export a Hive table into a CSV file?"},"content":{"rendered":"<p>It is a pretty common use case to export the contents of a Hive table into a CSV file. It\u2019s pretty simple if you are using a recent version of Hive. In this post, we will see who to achieve this with both newer and older versions of Hive.<\/p>\n<h2><span style=\"font-weight: 400;\">Hive version 11 or higher<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">If you are using Hive version 11 or higher below command will do the job. <\/span><span style=\"font-weight: 400;\">ROW FORMAT DELIMITED FIELDS TERMINATED BY &#8216;,&#8217; dictates that the columns should be delimited by a comma.<\/span><\/p>\n<pre class=\"lang:default decode:true \">INSERT OVERWRITE LOCAL DIRECTORY '\/home\/hirw\/sales&nbsp;\nROW FORMAT DELIMITED FIELDS TERMINATED BY ','&nbsp;\nselect * from sales_table;<\/pre>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-weight: 400;\">Hive versions older than 11<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">By default selecting the hive table and writing to a file like below will result in a tab separated file and of course this is not what you want as you want a comma separated file.<\/span><\/p>\n<pre class=\"lang:default decode:true \">hive -e 'select * from sales_table' &gt; \/home\/hirw\/sales.tsv<\/pre>\n<p><span style=\"font-weight: 400;\">With below, you are selecting the table and piping the results to sed command and passing a regex expression.<\/span><\/p>\n<pre class=\"lang:default decode:true \">hive -e 'select * from sales_table' | sed 's\/[\\t]\/,\/g' &gt; \/home\/hirw\/sales.csv<\/pre>\n<p><span style=\"font-weight: 400;\">The regex expression does a global match of all tab character [t] and replace with a ,<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This approach is not preferred as you might get inconsistent results when dealing with huge datasets. Also you are combing Hive and sed (Linux) tools to get the job done which in our opinion is not a clean approach.<\/span><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It is a pretty common use case to export the contents of a Hive table into a CSV file. It\u2019s pretty simple if you are using<span class=\"excerpt-hellip\"> [\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-1707","post","type-post","status-publish","format-standard","hentry","category-apache-hive"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.7 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to export a Hive table into a CSV file? - Big Data In Real World<\/title>\n<meta name=\"description\" content=\"Learn how to export a Hive table to a CSV file with both newer and older versions of Hive.\" \/>\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-to-export-a-hive-table-into-a-csv-file\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to export a Hive table into a CSV file? - Big Data In Real World\" \/>\n<meta property=\"og:description\" content=\"Learn how to export a Hive table to a CSV file with both newer and older versions of Hive.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/\" \/>\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-08-06T11: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-to-export-a-hive-table-into-a-csv-file\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/\"},\"author\":{\"name\":\"Big Data In Real World\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67\"},\"headline\":\"How to export a Hive table into a CSV file?\",\"datePublished\":\"2021-08-06T11:00:00+00:00\",\"dateModified\":\"2023-02-19T13:31:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/\"},\"wordCount\":212,\"publisher\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#organization\"},\"articleSection\":[\"Apache Hive\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/\",\"url\":\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/\",\"name\":\"How to export a Hive table into a CSV file? - Big Data In Real World\",\"isPartOf\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/#website\"},\"datePublished\":\"2021-08-06T11:00:00+00:00\",\"dateModified\":\"2023-02-19T13:31:17+00:00\",\"description\":\"Learn how to export a Hive table to a CSV file with both newer and older versions of Hive.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.bigdatainrealworld.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to export a Hive table into a CSV file?\"}]},{\"@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 to export a Hive table into a CSV file? - Big Data In Real World","description":"Learn how to export a Hive table to a CSV file with both newer and older versions of Hive.","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-to-export-a-hive-table-into-a-csv-file\/","og_locale":"en_US","og_type":"article","og_title":"How to export a Hive table into a CSV file? - Big Data In Real World","og_description":"Learn how to export a Hive table to a CSV file with both newer and older versions of Hive.","og_url":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/","og_site_name":"Big Data In Real World","article_publisher":"https:\/\/www.facebook.com\/bigdatainrealworld","article_published_time":"2021-08-06T11: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-to-export-a-hive-table-into-a-csv-file\/#article","isPartOf":{"@id":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/"},"author":{"name":"Big Data In Real World","@id":"https:\/\/www.bigdatainrealworld.com\/#\/schema\/person\/24cab2292ef49c73053440c86515ef67"},"headline":"How to export a Hive table into a CSV file?","datePublished":"2021-08-06T11:00:00+00:00","dateModified":"2023-02-19T13:31:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/"},"wordCount":212,"publisher":{"@id":"https:\/\/www.bigdatainrealworld.com\/#organization"},"articleSection":["Apache Hive"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/","url":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/","name":"How to export a Hive table into a CSV file? - Big Data In Real World","isPartOf":{"@id":"https:\/\/www.bigdatainrealworld.com\/#website"},"datePublished":"2021-08-06T11:00:00+00:00","dateModified":"2023-02-19T13:31:17+00:00","description":"Learn how to export a Hive table to a CSV file with both newer and older versions of Hive.","breadcrumb":{"@id":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.bigdatainrealworld.com\/how-to-export-a-hive-table-into-a-csv-file\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.bigdatainrealworld.com\/"},{"@type":"ListItem","position":2,"name":"How to export a Hive table into a CSV file?"}]},{"@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\/1707"}],"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=1707"}],"version-history":[{"count":2,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts\/1707\/revisions"}],"predecessor-version":[{"id":2072,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/posts\/1707\/revisions\/2072"}],"wp:attachment":[{"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/media?parent=1707"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/categories?post=1707"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bigdatainrealworld.com\/wp-json\/wp\/v2\/tags?post=1707"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}