[HIVE-3682] when output hive table to file,users should could have a separator of their own choice

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.8.1
• Fix Version/s: 0.11.0
• Component/s: CLI

Environment

Linux 3.0.0-14-generic #23-Ubuntu SMP Mon Nov 21 20:34:47 UTC 2011 i686 i686 i386 GNU/Linux
java version "1.6.0_25"
hadoop-0.20.2-cdh3u0
hive-0.8.1

• Assignee: Sushanth Sowmyan
• Reporter: caofangkun
• Created: 07/Nov/12 07:50
• Updated: 30/Dec/15 09:51
• Resolved: 29/Apr/13 22:10

Description

By default,when output hive table to file ,columns of the Hive table are separated by ^A character (that is \001).
But indeed users should have the right to set a seperator of their own choice.

Usage Example:
create table for_test (key string, value string);
load data local inpath './in1.txt' into table for_test
select * from for_test;
UT-01：default separator is \001 line separator is \n
insert overwrite local directory './test-01'
select * from src ;

create table array_table (a array<string>, b array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

load data local inpath "../hive/examples/files/arraytest.txt" overwrite into table table2;

CREATE TABLE map_table (foo STRING , bar MAP<STRING, STRING>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

UT-02：defined field separator as ':'
insert overwrite local directory './test-02'
row format delimited
FIELDS TERMINATED BY ':'
select * from src ;

UT-03: line separator DO NOT ALLOWED to define as other separator
insert overwrite local directory './test-03'
row format delimited
FIELDS TERMINATED BY ':'
select * from src ;

UT-04: define map separators
insert overwrite local directory './test-04'
row format delimited
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
select * from src;

Issue Links

• is duplicated by: HIVE-634 ctrl-A is the only output delimiter used, regardless of the Hive table structure
• is duplicated by: HIVE-268 "Insert Overwrite Directory" to accept configurable table row format
• is related to: HIVE-5672 Insert with custom separator not supported for non-local directory
• is related to: HIVE-6410 Allow output serializations separators to be set for HDFS path as well.
• relates to: HIVE-4346 when writing data into filesystem from queries ,the output files could contain a line of column names