Handle the newline characters within the data in Hive tables.

[#1]

The table definition "LINES TERMINATED BY" only supports newline '\n' right now. This is a known issue and Jira Hive 11996 has already been raised for the issue:

To handle the newline characters within the data, you can use the Omniture Data format which uses a EscapedLineReader which gets around Omniture's pesky escaped tabs and newlines.

Please note that the data files need to include '\' characters before the newline character within the data and run the below command in sequence and required jars are attached along with data file:

add jar /tmp/omnituredata-1.0.2-SNAPSHOT-jar-with-dependencies.jar;
add jar /tmp/omnituredata-1.0.2-SNAPSHOT-javadoc.jar;
add jar /tmp/omnituredata-1.0.2-SNAPSHOT-sources.jar;
add jar /tmp/omnituredata-1.0.2-SNAPSHOT.jar;

(Note: jars are available on the HDFS /tmp folder).

CREATE TABLE test8(id string,desc string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT 'org.rassee.omniture.hadoop.mapred.OmnitureDataFileInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION '/apps/hive/warehouse/test8';

Sample file under HDFS location '/apps/hive/warehouse/test8' is as:

[hive@sindhu root]$ hdfs dfs -cat /apps/hive/warehouse/test8/file.txt
id	desc
1	Hi\
I am a member and would like to open savings accts for both my kids aged 12 and 16.\
Is that possible and what documents do I need to bring?\
Also do I need to make an appt first?\
Thx!

Also, the inputformat as TEXT does not understand the escaped newline characters.

4 rows selected (0.165 seconds)
0: jdbc:hive2://sindhu:2181/> CREATE TABLE test9(id string,desc string) 
0: jdbc:hive2://sindhu:2181/> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
0: jdbc:hive2://sindhu:2181/> STORED AS textfile LOCATION '/apps/hive/warehouse/test9';
No rows affected (0.209 seconds)
0: jdbc:hive2://sindhu:2181/> select * from test9;
+---------------------------------------------------------------------------------------+-------------------+--+
| test9.id | test9.desc |
+---------------------------------------------------------------------------------------+-------------------+--+
| id | desc |
| 1 | Hi\ |
| I am a member and would like to open savings accts for both my kids aged 12 and 16.\ | NULL |
| Is that possible and what documents do I need to bring?\ | NULL |
| Also do I need to make an appt first?\ | NULL |
| Thx! | NULL |
| 2 | hi jihidp\ |
| uiunoo! | NULL |
| 3 | hi who are you\ |
| talking with | NULL |
+--------------------------+