Hive - Complex regexp_replace
<p>I'm not a specialist with regular expression and I'm facing issues using regexp_replace in Hive.</p>

<p>I would like to load a CSV file into Hive, which contains rows like that:</p>

<pre><code>AAA,1234,BBB,,,"""CC,CCC""","""DDD""","""EE"EEE""",,
"""AAA""",1234,BBB,,,CCCC,"""DD,DD""",,"""FFFF""",
</code></pre>

<p>As you can see, the format isn't perfect</p>

<ul>
<li>There are non-escaped commas into string fields</li>
<li>Some string fields are enclosed by """ (3 double-quotes)</li>
<li>There are non-escaped double-quotes into string fields</li>
<li>There are empty fields</li>
</ul>

<p>When I try to import it into a Hive table, the columns are not well parsed because of the non-escaped commas.</p>

<p>So I imported the raw data as rows into a Hive table like this:</p>

<pre><code>CREATE EXTERNAL TABLE MyRawTable
(
    RAW_DATA STRING
)
STORED AS TEXTFILE
LOCATION '/path/to/hdfs/file'
</code></pre>

<p>And i'm trying to use the regexp_replace function to transform the rows:</p>

<ul>
<li>Escape the commas, the double and simple quotes in the string fields</li>
<li>Not enclose string fields by double quotes</li>
</ul>

<p>So data will look like that:</p>

<pre><code>AAA,1234,BBB,,,CC\,CCC,DDD,EE\"EEE,,
AAA,1234,BBB,,,CCCC,DD\,DD,,FFFF,
</code></pre>

<p>I don't find the solution for this regex, any ideas? Thanks a lot!</p>