question Re: Escaping field delimiters in Archives of Support Questions (Read Only)

Escaping field delimiters

rpagnin — Fri, 16 Sep 2022 08:47:00 GMT

Hi,

I'm trying to implement a simple data analytic use case.

I put in the cluster files with random number of rows similar to the following (fields could be populated randomly):

--;1374487936469;'';;;;;;;;;;;0.24516626;-0.85808194;7.4775715;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Then I create a Hive external table w/ the following command:

CREATE EXTERNAL TABLE myTable (f1 STRING, f2 STRING, f3 STRING, f4 STRING, <otherFields> )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\U003B'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/test/2013-07_08';

In '/user/test/2013-07_08' there is at least a file w/ rows similar to the one described above.

Trying to select a bunch of rows w/: select * from myTable limit 10;

I obtain a resultset of 10 rows where each row contains a complete line of the file addressed in column f1.

" FIELDS TERMINATED BY '\U003B' " doesn't work, but even w/ '\u037E, ';' (this raises a stringLiteral error), '\059'.

Seems a simple problem but I can't realize which, and... I can't change the separator.

Have you any advice?

Thanks

Rob

Re: Escaping field delimiters

dvohra — Tue, 20 Aug 2013 14:51:16 GMT

Use Hive SerDe for CSV

https://github.com/ogrodnek/csv-serde

Also, refer

https://issues.apache.org/jira/browse/HIVE-136

Re: Escaping field delimiters

mgrover — Tue, 20 Aug 2013 17:00:19 GMT

The good thing is that you created an external table, so you can just delete the table and recreate it. The underlying data in HDFS (/user/test/...) wouldn't be deleted.

Re: Escaping field delimiters

rpagnin — Thu, 22 Aug 2013 07:28:27 GMT

Thanks for the answer,

I've tried it but faced another problem probably related to the positioning of the jar.

I've put it in the folder /usr/lib/hive/lib on all the datanode and even on the namenode and secnamenode (just to cover all the basis).

Even tried to load it by Hue (query editor) but nothing, maybe is a problem of my cluster (prototyping one).

Actually I've solved using

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\073'

I did forget that octal representation of escapes.

Anyway thank you for your time

Rob