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):

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
view raw hosted with ❤ by GitHub

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):

hive -e "drop table if exists csv_dump;
create table csv_dump ROW FORMAT DELIMITED
LOCATION '/temp/storage/path' as
select * from my_data_table;"
hadoop fs -getmerge /temp/storage/path/ /local/path/my.csv
view raw hosted with ❤ by GitHub

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).

7 thoughts

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


    1. There are a few ways. If you do option 1, what I’ve done is create a file on the edge node that has the file headers already in it, and then do an append instead of an overwrite on the last command: cat /path/in/local/* >> /another/path/in/local/my_table.csv

      Of course this is static. You can also insert the header with a bash command using awk/sed/cut/etc. This is “more” dynamic, but not enormously so.


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.