Hive: create table with arrays of struct from csv file where everything is comma delimited
<p>I have a csv file with array of structs where everything is delimited by ','. After the ID field, the data contains arrays of triplets of X, Y and Z coordinates.  </p>

<pre><code>ID, X1,Y1,Z1,X2,Y2,Z2,X3,Y3,Z3,...
1,1,2,3,4,5,6,7,8,9
2,4,5,6,7,8,9
3,10,11,12
4,15,16,17,18,19,20,25,26,27 
</code></pre>

<p>I tried to use the following code to create the Hive table which would have worked if my fields, collection items and map keys were delimted with different characters. However, since everything is delimited with a comma, it failed. Wondering if there is an alternate solution for this situation. </p>

<pre><code>CREATE TABLE IF NOT EXISTS Hivetable (
   ID              INT,
   XYZ             array&lt;STRUCT&lt;X:DOUBLE, Y:DOUBLE, Z:DOUBLE&gt;&gt;
)
row format delimited                                                                                       
fields terminated by ','   
collection items terminated by ',' 
map keys terminated by ',' 
stored as textfile 
;

LOAD DATA local INPATH 'Path/datafile.csv' OVERWRITE INTO TABLE Hivetable;
</code></pre>