How to ingest '\n' in data with hive

[#1]

Usually hive treats \n as newline character and everytime we ingest the data it will split single line into multiple lines depending on no of \n in the text.

For eg.

Step1: Create a table and try ingesting the data using insert into table command.

39565-screen-shot-2017-09-26-at-31131-pm.png

Step2: List the contents of table. We will observe that single line with '\n' in between is split in two lines.

39566-screen-shot-2017-09-26-at-31311-pm.png

Workaround

There are two ways to ingest data containing newline character

1. Using escape character

2. Using load data inpath command

Solution1: When we use escape character "\" with "\n", hive will ignore \n as newline but will treat it as string. (abcd\ndefg)

39567-screen-shot-2017-09-26-at-31652-pm.png

querying the table

39569-screen-shot-2017-09-26-at-31828-pm.png

Solution 2: Using "load data inpath" command.

Step1: Upload the file on HDFS

39570-screen-shot-2017-09-26-at-32019-pm.png

Step2: Load data from file to table

39571-screen-shot-2017-09-26-at-32132-pm.png

[#2]

Thanks for this @rchaman

Do you have a best practice suggestion for for \n ingestion via SAS - via libname?