How to export a Hive table into a CSV file?
CsvHiveCsv Problem Overview
I used this Hive query to export a table into a CSV file.
INSERT OVERWRITE DIRECTORY '/user/data/output/test' select column1, column2 from table1;
The file generated '000000_0' does not have comma separator
Is this the right way to generate CSV file? If no, please let me know how can I generate the CSV file?
Csv Solutions
Solution 1 - Csv
or use this
hive -e 'select * from your_Table' | sed 's/[\t]/,/g' > /home/yourfile.csv
You can also specify property set hive.cli.print.header=true
before the SELECT
to ensure that header along with data is created and copied to file.
For example:
hive -e 'set hive.cli.print.header=true; select * from your_Table' | sed 's/[\t]/,/g' > /home/yourfile.csv
If you don't want to write to local file system, pipe the output of sed
command back into HDFS
using the hadoop fs -put
command.
It may also be convenient to SFTP to your files using something like Cyberduck, or you can use scp
to connect via terminal / command prompt.
Solution 2 - Csv
If you're using Hive 11 or better you can use the INSERT
statement with the LOCAL
keyword.
Example:
insert overwrite local directory '/home/carter/staging' row format delimited fields terminated by ',' select * from hugetable;
Note that this may create multiple files and you may want to concatenate them on the client side after it's done exporting.
Using this approach means you don't need to worry about the format of the source tables, can export based on arbitrary SQL query, and can select your own delimiters and output formats.
Solution 3 - Csv
That should work for you
-
tab separated
hive -e 'select * from some_table' > /home/yourfile.tsv
-
comma separated
hive -e 'select * from some_table' | sed 's/[\t]/,/g' > /home/yourfile.csv
Solution 4 - Csv
You can not have a delimiter for query output,after generating the report (as you did).
you can change the delimiter to comma.
It comes with default delimiter \001
(inivisible character).
hadoop fs -cat /user/data/output/test/* |tr "\01" "," >>outputwithcomma.csv
Solution 5 - Csv
INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * from table;
is the correct answer.
If the number of records is really big, based on the number of files generated
the following command would give only partial result.
hive -e 'select * from some_table' > /home/yourfile.csv
Solution 6 - Csv
Recent versions of hive comes with this feature.
INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
select * from table;
this way you can choose your own delimiter and file name. Just be careful with the "OVERWRITE" it will try to delete everything from the mentioned folder.
Solution 7 - Csv
I have used simple linux shell piping + perl to convert hive generated output from tsv to csv.
hive -e "SELECT col1, col2, … FROM table_name" | perl -lpe 's/"/\\"/g; s/^|$/"/g; s/\t/","/g' > output_file.csv
(I got the updated perl regex from someone in stackoverflow some time ago)
The result will be like regular csv:
"col1","col2","col3"
... and so on
Solution 8 - Csv
The following script should work for you:
#!/bin/bash
hive -e "insert overwrite local directory '/LocalPath/'
row format delimited fields terminated by ','
select * from Mydatabase,Mytable limit 100"
cat /LocalPath/* > /LocalPath/table.csv
I used limit 100
to limit the size of data since I had a huge table, but you can delete it to export the entire table.
Solution 9 - Csv
Here using Hive warehouse dir you can export data instead of Hive table. first give hive warehouse path and after local path where you want to store the .csv file For this command is bellow :-
hadoop fs -cat /user/hdusr/warehouse/HiveDb/tableName/* > /users/hadoop/test/nilesh/sample.csv
Solution 10 - Csv
I had a similar issue and this is how I was able to address it.
Step 1 - Loaded the data from hive table into another table as follows > DROP TABLE IF EXISTS TestHiveTableCSV; > CREATE TABLE TestHiveTableCSV ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' AS > SELECT Column List FROM TestHiveTable;
Step 2 - Copied the blob from hive warehouse to the new location with appropriate extension
> Start-AzureStorageBlobCopy > -DestContext $destContext
> -SrcContainer "Source Container" > -SrcBlob "hive/warehouse/TestHiveTableCSV/000000_0"
> -DestContainer "Destination Container" `
> -DestBlob "CSV/TestHiveTable.csv"
Hope this helps!
Best Regards, Dattatrey Sindol (Datta) http://dattatreysindol.com
Solution 11 - Csv
There are ways to change the default delimiter, as shown by other answers.
There are also ways to convert the raw output to csv with some bash scripting. There are 3 delimiters to consider though, not just \001. Things get a bit more complicated when your hive table has maps.
I wrote a bash script that can handle all 3 default delimiters (\001 \002 and \003) from hive and output a csv. The script and some more info are here:
> ###Hive Default Delimiters to CSV
>
> Hive's default delimiters are
>
> Row Delimiter => Control-A ('\001')
> Collection Item Delimiter => Control-B ('\002')
> Map Key Delimiter => Control-C ('\003')
>
>
> There are ways to change these delimiters when exporting tables but
> sometimes you might still get stuck needing to convert this to csv.
>
> Here's a quick bash script that can handle a DB export that's
> segmented in multiple files and has the default delimiters. It will
> output a single CSV file.
>
> It is assumed that the segments all have the naming convention 000_0
>
>
> INDIRECTORY="path/to/input/directory"
> for f in $INDIRECTORY/000_0; do
> echo "Processing $f file..";
> cat -v $f |
> LC_ALL=C sed -e "s/^/"/g" |
> LC_ALL=C sed -e "s/^A/","/g" |
> LC_ALL=C sed -e "s/^C^B/"":"""",""/g" |
> LC_ALL=C sed -e "s/^B/"",""/g" |
> LC_ALL=C sed -e "s/^C/"":""/g" |
> LC_ALL=C sed -e "s/$/"/g" > $f-temp
> done
> echo "you,can,echo,your,header,here,if,you,like" > $INDIRECTORY/final_output.csv
> cat $INDIRECTORY/-temp >> $INDIRECTORY/final_output.csv
> rm $INDIRECTORY/-temp
More explanation on the gist
Solution 12 - Csv
In case you are doing it from Windows you can use Python script hivehoney to extract table data to local CSV file.
It will:
- Login to bastion host.
- pbrun.
- kinit.
- beeline (with your query).
- Save echo from beeline to a file on Windows.
Execute it like this:
set PROXY_HOST=your_bastion_host
set SERVICE_USER=you_func_user
set LINUX_USER=your_SOID
set LINUX_PWD=your_pwd
python hh.py --query_file=query.sql
Solution 13 - Csv
The problem solutions are fine but I found some problems in both:
-
As Carter Shanklin said, with this command we will obtain a csv file with the results of the query in the path specified:
insert overwrite local directory '/home/carter/staging' row format delimited fields terminated by ',' select * from hugetable;
The problem with this solution is that the csv obtained won´t have headers and will create a file that is not a CSV (so we have to rename it).
-
As user1922900 said, with the following command we will obtain a CSV files with the results of the query in the specified file and with headers:
hive -e 'select * from some_table' | sed 's/[\t]/,/g' > /home/yourfile.csv
With this solution we will get a CSV file with the result rows of our query, but with log messages between these rows too. As a solution of this problem I tried this, but without results.
So, to solve all these issues I created a script that execute a list of queries, create a folder (with a timestamp) where it stores the results, rename the files obtained, remove the unnecesay files and it also add the respective headers.
#!/bin/sh
QUERIES=("select * from table1" "select * from table2")
IFS=""
directoryname=$(echo "ScriptResults$timestamp")
mkdir $directoryname
counter=1
for query in ${QUERIES[*]}
do
tablename="query"$counter
hive -S -e "INSERT OVERWRITE LOCAL DIRECTORY '/data/2/DOMAIN_USERS/SANUK/users/$USER/$tablename' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' $query ;"
hive -S -e "set hive.cli.print.header=true; $query limit 1" | head -1 | sed 's/[\t]/,/g' >> /data/2/DOMAIN_USERS/SANUK/users/$USER/$tablename/header.csv
mv $tablename/000000_0 $tablename/$tablename.csv
cat $tablename/$tablename.csv >> $tablename/header.csv.
rm $tablename/$tablename.csv
mv $tablename/header.csv $tablename/$tablename.csv
mv $tablename/$tablename.csv $directoryname
counter=$((counter+1))
rm -rf $tablename/
done
Solution 14 - Csv
try
hive --outputformat==csv2 -e "select * from YOUR_TABLE";
This worked for me
my hive version is "Hive 3.1.0.3.1.0.0-78"
Solution 15 - Csv
This is a much easier way to do it within Hive's SQL:
set hive.execution.engine=tez;
set hive.merge.tezfiles=true;
set hive.exec.compress.output=false;
INSERT OVERWRITE DIRECTORY '/tmp/job/'
ROW FORMAT DELIMITED
FIELDS TERMINATED by ','
NULL DEFINED AS ''
STORED AS TEXTFILE
SELECT * from table;
Solution 16 - Csv
Below is the end-to-end solution that I use to export Hive table data to HDFS as a single named CSV file with a header.
(it is unfortunate that it's not possible to do with one HQL statement)
It consists of several commands, but it's quite intuitive, I think, and it does not rely on the internal representation of Hive tables, which may change from time to time.
Replace "DIRECTORY" with "LOCAL DIRECTORY" if you want to export the data to a local filesystem versus HDFS.
# cleanup the existing target HDFS directory, if it exists
sudo -u hdfs hdfs dfs -rm -f -r /tmp/data/my_exported_table_name/*
# export the data using Beeline CLI (it will create a data file with a surrogate name in the target HDFS directory)
beeline -u jdbc:hive2://my_hostname:10000 -n hive -e "INSERT OVERWRITE DIRECTORY '/tmp/data/my_exported_table_name' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT * FROM my_exported_table_name"
# set the owner of the target HDFS directory to whatever UID you'll be using to run the subsequent commands (root in this case)
sudo -u hdfs hdfs dfs -chown -R root:hdfs /tmp/data/my_exported_table_name
# write the CSV header record to a separate file (make sure that its name is higher in the sort order than for the data file in the target HDFS directory)
# also, obviously, make sure that the number and the order of fields is the same as in the data file
echo 'field_name_1,field_name_2,field_name_3,field_name_4,field_name_5' | hadoop fs -put - /tmp/data/my_exported_table_name/.header.csv
# concatenate all (2) files in the target HDFS directory into the final CSV data file with a header
# (this is where the sort order of the file names is important)
hadoop fs -cat /tmp/data/my_exported_table_name/* | hadoop fs -put - /tmp/data/my_exported_table_name/my_exported_table_name.csv
# give the permissions for the exported data to other users as necessary
sudo -u hdfs hdfs dfs -chmod -R 777 /tmp/data/hive_extr/drivers
Solution 17 - Csv
None of the above options work perfect for me. Few issues I want to solve
- If there's tab in the value, it shouldn't break CSV output
- I need the head to be automatically added without any manual work
- Struct, array or map field should be JSON encoded
So I create the UDF to do that. (A bit surprised Hive didn't have this build in support)
Usage:
ADD JAR ivy://org.jsonex:HiveUDF:0.1.24?transitive=true;
CREATE TEMPORARY FUNCTION to_csv AS 'org.jsonex.hiveudf.ToCSVUDF';
SELECT to_csv(*) FROM someTable; -- Default separator and headers
SELECT to_csv('{noHead:true}', *) FROM someTable; -- No headers
SELECT to_csv('{headers:[,,,col3,]}', *) FROM someTable; -- Custom Headers
SELECT to_csv('{fieldSep:|,quoteChar:\"\\'\"}', *) FROM someTable" -- Custom fieldSep and quoteChar