Stack Overflow for Teams is now called
Stack Internal
. Bring the best of human thought and AI automation together at your work.
Try for free
Learn more
Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Stack Internal
Knowledge at work
Bring the best of human thought and AI automation together at your work.
Explore Stack Internal
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?
another question, when i save a big hive table to several blocks on hdfs , i found there schema becomes different sometimes ,i.e. the type of columns maybe changed, how to prevent this problem?
2023-07-21 03:07:39 +00:00
CommentedJul 21, 2023 at 3:07
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.
I tried exporting this for exporting a hive query to local and hdfs files but the same file can't be read from spark session - header not identified properly!!
While this approach works most of the time, but if the's a '\t' in the query result value. It will break. How can we solve this issue?
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.
Thank you, this created folder with multiple csv files. Is there anyway to put everything into one file? Also is there anyway to include header (column name) in the csv file?
For me this command has produced a bunch of files ending with the extension .snappy which looks like a compressed format. I am not sure how to convert un-compress them. I know how to merge files locally using the command cat file1 file2 > file on my local machine.
Check Ranger's policies for permission errors with hive
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.
(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
#!/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.
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 :-
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;
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
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:
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
echo "you,can,echo,your,header,here,if,you,like" > $INDIRECTORY/final_output.csv
cat $INDIRECTORY/*-temp >> $INDIRECTORY/final_output.csv
rm $INDIRECTORY/*-temp
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).
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
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[*]}
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/
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
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
