How to Export Hive Table to CSV File

hive-logo

If your Hadoop cluster allows you to connect to Hive through the command line interface (CLI), you can very easily export a Hive table of data in Hadoop to a CSV.

It only takes a few small lines of code, which I’ve written into a few bash/shell scripts:

Approach One (Hive Insert Overwrite a Directory):


#!/bin/bash
hive -e "insert overwrite local directory '/path/in/local/'
row format delimited fields terminated by ','
select * from my_database.my_table"
cat /path/in/local/* > /another/path/in/local/my_table.csv

This approach writes the contents of a Hive table to a local path (linux) in as many files as it needs. It then uses a Linux “cat” command to merge all files to one csv.

Here’s what happened:

  1. shebang line (optional)
  2. Command issued to Hive that selects all records from a table in Hive, separates the fields/columns by a comma, and writes the file to a local directory (wiping anything previously in that path).
  3. Cat command issued to get/merge all part files (remember, the output was from a Map/Reduce job) in directory into a single .csv file.

Approach Two (Hive CSV Dump Internal Table):


#!/bin/bash
hive -e "drop table if exists csv_dump;
create table csv_dump ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
LOCATION '/temp/storage/path' as
select * from my_data_table;"
hadoop fs -getmerge /temp/storage/path/ /local/path/my.csv


This approach writes a table’s contents to an internal Hive table called csv_dump, delimited by commas — stored in HDFS as usual. It then uses a hadoop filesystem command called “getmerge” that does the equivalent of Linux “cat” — it merges all files in a given directory, and produces a single file in another given directory (it can even be the same directory).

In either approach, that .csv now lives on your local edge node, and can be placed into HDFS, used in other scripts, or SCP’d to your local desktop. It’s a very efficient and easy way to get the contents of a Hive table into a easily human and application-readable format.

For more Hadoop tutorials, subscribe to this blog (button in sidebar).

Advertisements

3 thoughts on “How to Export Hive Table to CSV File

  1. Josh June 2, 2016 / 7:06 pm

    In approach one, I believe the cat command should be “cat /path/in/local/* > /another/path/in/local/my_table.csv”.

    Like

    • Landon Robinson June 2, 2016 / 8:08 pm

      Nice catch! I guess wordpress’s HTML chewed it up. It’s not the best for code snippets. Thanks!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s