Hive INSERT OVERWRITE DIRECTORY command output is

Question

The file that I am loading is separated by ' ' (white space). Below is the file. The file resides in HDFS:-

001 000
001 000
002 001
003 002
004 003
005 004
006 005
007 006
008 007
099 007

1> I am creating an external table and loading the file by issuing the below command:-

CREATE EXTERNAL TABLE IF NOT EXISTS graph_edges (src_node_id STRING COMMENT 'Node ID of Source node', dest_node_id STRING COMMENT 'Node ID of Destination node') ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE LOCATION '/user/hadoop/input';

2> After this, I am simply inserting the table in another file by issuing the below command:-

INSERT OVERWRITE DIRECTORY '/user/hadoop/output' SELECT * FROM graph_edges;

3> Now, when I cat the file, the fields are not separated by any delimiter:-

hadoop dfs -cat /user/hadoop/output/000000_0

Output:-

001000
001000
002001
003002
004003
005004
006005
007006
008007
099007

Can somebody please help me out? Why is the delimiter being removed and how to delimit the output file?

In the CREATE TABLE command I tried DELIMITED BY '\t' but then I am getting unnecessary NULL column.

Any pointers help much appreciated. I am using Hive 0.9.0 version.

Answer 1

The default separator is "^A". In python language, it is "\x01".

When I want to change the delimiter, I use SQL like:

SELECT col1, delimiter, col2, delimiter, col3, ..., FROM table

Then, regard delimiter+"^A" as a new delimiter.

Answer 2

The problem is that HIVE does not allow you to specify the output delimiter - https://issues.apache.org/jira/browse/HIVE-634

The solution is to create external table for output (with delimiter specification) and insert overwrite table instead of directory.

--

Assuming that you have /user/hadoop/input/graph_edges.csv in HDFS,

hive> create external table graph_edges (src string, dest string) 
    > row format delimited 
    > fields terminated by ' ' 
    > lines terminated by '\n' 
    > stored as textfile location '/user/hadoop/input';

hive> select * from graph_edges;
OK
001 000
001 000
002 001
003 002
004 003
005 004
006 005
007 006
008 007
099 007

hive> create external table graph_out (src string, dest string) 
    > row format delimited 
    > fields terminated by ' ' 
    > lines terminated by '\n' 
    > stored as textfile location '/user/hadoop/output';

hive> insert into table graph_out select * from graph_edges;
hive> select * from graph_out;
OK
001 000
001 000
002 001
003 002
004 003
005 004
006 005
007 006
008 007
099 007

[user@box] hadoop fs -get /user/hadoop/output/000000_0 .

Comes back as above, with spaces.

Answer 3

I think using the concat_ws function you can achieve your output;

INSERT OVERWRITE DIRECTORY '/user/hadoop/output' SELECT concat_ws(',', col1, col2) FROM graph_edges;

here i have chosen comma as the column delimiter

Answer 4

I had this issue where the output of the hive query results should be pipe delimited.. Running this sed command you can replace: ^A to |

sed 's#\x01#|#g' test.log > piped_test.log

Answer 5

While the question is over 2 years old and the top answer was correct at the time, it is now possible to tell Hive to write delimited data to a directory.

Here is an example of outputting the data with the traditional ^A separator:

INSERT OVERWRITE DIRECTORY '/output/data_delimited'
SELECT *
FROM data_schema.data_table

And now with tab delimiters:

INSERT OVERWRITE DIRECTORY '/output/data_delimited'
row format delimited 
FIELDS TERMINATED BY '\t'
SELECT *
FROM data_schema.data_table

Answer 6

you can use this parameter "row format delimited fields terminated by '|'" for example in your case should be

INSERT OVERWRITE DIRECTORY '/user/hadoop/output' row format delimited fields terminated by '|' SELECT * FROM graph_edges;