Hello folks,
I have the table like below:
laod_order, load_group, src_location, region, extra-col
62, 1, Biz_flow, GoM, abc
21, 1, Primavera, Angola, xyz
23, 1, Primavera, GoM, Finder, abc
Where there are commas in between the text/column. How can i create hive table to handle this ?
Thanks in advance.
You can try this:
CREATE TABLE mytable
(laod_order int, load_group varchar(100), src_location varchar(100) , region varchar(100), extra-col varchar(100))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE
But in my text there is one extra comma inside a row. If I use the command you suggested then my data will be corrupted. I mean the data of a column where comma is there it will be shifted to next column data.
The comma that is existing inside the cell value, is it surrounded by double quotes?
If that is the case, you could use this serde: https://github.com/ogrodnek/csv-serde
No, I don’t have any quote. that’s what my problem is.
Is there any way to differentiate between the comma in the content of a cell and the comma separating the cells?
In other words, how would a human figure out that this comma is for separating two cell values and this comma is inside the cell value?
If you could post an example row, it will be better.