question Re: Hive table with UTF-16 data in Archives of Support Questions (Read Only)

Hive table with UTF-16 data

csankaraiah — Fri, 04 Dec 2015 20:58:09 GMT

One of my client is trying to create an external Hive table in HDP from CSV files, (about 30 files, total of 2.5 TeraBytes)

But the files are formatted as: “Little-endian, UTF-16 Unicode text, with CRLF, CR line terminators”. Here are couple of issues

Is there an easy way to convert CSV/TXT files from Unicode (UTF-16 / UCS-2) to ASCII (UTF-8)?

Is there is a way for Hive to recognize this format?

He tried to use iconv to convert the utf-16 format to ascii format but it but it fails when source file is more than 15 GB file.

iconv -c -f utf-16 -t us-ascii

Any suggestions??

csankaraiah — Fri, 04 Dec 2015 23:40:07 GMT

Here are some solution options i received from Ryan Merriman, Benjamin Leonhardi & Peter Coates

Option1

You can use split –l to break the bigger file into small one while using iconv

Option2

I suppose it would be a good idea to write a little program using icu if iconv fails.

Option3

You can try to do it in Java. Here’s one example:

You can try using File(Input|Output)Stream and String classes. You can specify character encoding when reading (converting byte[] to String):

String s = String(byte[] bytes, Charset charset)

And when writing it back out (String to byte[]):

s.getBytes(Charset charset)

This approach should solve your size limit problem.

ahasson — Thu, 30 Jun 2016 02:01:19 GMT

I used NiFi's ConvertCharacterSet to change from UTF-16LE to UTF-8, it's a great and straightforward option if you're using it 🙂

lenovomi — Wed, 27 Jul 2016 23:11:08 GMT

Hi, where i can find the character set values that are accepted by ConvertCharacterSet processor?

Also what component can i use to load CSV file and to dump results into the converted CSV file?

lenovomi — Thu, 28 Jul 2016 14:20:07 GMT

So i found appropriate components but it doesnt convert the file properly, any idea? input file is a binary