How to Export Hive Table to CSV File (c)
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/' low 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&r" command to merge all files to one csv.
Here’s what happened:
- shebang line (optional)
- Command issued to Hive that selects all records from a table in Hive, separates the fields/columns by a comma, and writes tte file to a local directory (wiping anything previously in that path).
- 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.
(c) Internet