question Hive support for Line Terminator other than '\n' when creating table in Archives of Support Questions (Read Only)

Hive support for Line Terminator other than '\n' when creating table

vagarwal — Wed, 16 Dec 2015 22:50:39 GMT

I have XML data in one of DB fields which has '\n' in it. So, I need to use an alternate Line Terminator.

I get following error when I try using a different one.

FAILED: SemanticException 13:20 LINES TERMINATED BY only supports newline '\n' right now. Error encountered near token '';''

Re: Hive support for Line Terminator other than '\n' when creating table

gbraccialli3 — Wed, 16 Dec 2015 23:29:17 GMT

@Virendra Agarwal have you considered using XML Serde? It supports line breaks natively and probably it will be easier to parse your XML data.

See this from @Neeraj Sabharwal:

https://community.hortonworks.com/articles/972/hive-and-xml-pasring.html

Re: Hive support for Line Terminator other than '\n' when creating table

pardeep_kumar — Thu, 17 Dec 2015 00:35:28 GMT

@Virendra Agarwal

A custom SerDe will work. While you can use custom serde like one explained in this article from @Neeraj Sabharwal:

https://community.hortonworks.com/articles/972/hive-and-xml-pasring.html

Look at below stack overflow discussion as well:

http://stackoverflow.com/questions/27583736/lines-...

But if at all possible, it would be a great idea if you could migrate away from character separated files to a modern format like ORC or avro. You will gain in performance, benefit from complex structures and have a much more future proof data format. Load raw data into external table using SerDe but try to get the final resting place a managed table with an advanced file format, you'll be much happier in the long run.

Re: Hive support for Line Terminator other than '\n' when creating table

ajay_a_narasimh — Wed, 02 Nov 2016 15:21:09 GMT

@Pardeep

There is a design pattern being developed at our organization wherein one of the scenarios is to load data from an Oracle database on to Hive. The Hive table is defined as ORC format with ZLIB compression.

However, there is an error occurring when the Oracle source contains data with newline '\n' characters within text columns. Also, when we attempt to change 'LINES TERMINTED BY' to anything other than '\n' it is not allowed.

Could you please elaborate on your statement - "migrate away from character separated files to a modern format like ORC or avro" ?

Any assistance or pseudocode will be much appreciated.

I could provide details on the configuration in use, if required.