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?
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.
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.
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
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.
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
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.
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
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
In case you are doing it from Windows you can use Python script hivehoney to extract table data to local CSV file.
It will:
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
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
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
