how to load double quotes data of fields in hive table without excluding double quotes?

[#1]

Can I know the working table property for splitting the records as shown below.

Input field - 123,"456","INDIA","INDIA",789,"DELHI INDIA, PIN. North INDIA","101","NEW Delhi ","LOCATION"

Expected hive output("|" indicates split) - 123 |  "456" |  "INDIA" |  "INDIA" |  789 |  "DELHI INDIA, PIN. North INDIA" |  "101" |  "NEW Delhi " |  "LOCATION"

[#2]

@yashwanth Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive experts @abathla @Shmoo  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

[#3]

@yashwanth It seems like you want to separate columns based on the position of comma.

In that case, you may create the table as follows:

CREATE TABLE my_table (
  col1 STRING,
  col2 INT,
  ...
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' ...

[#4]

Hi Smruti,

Thanks for your reply, I want the property in a way that ',' within double quotes should not split and the fields which are not having ',' inside double quotes should be splitted. 

[#5 · Accepted solution]

Try this:

CREATE external TABLE mytable (
    col1 INT,
    col2 STRING,
    col3 STRING,
    col4 STRING,
    col5 INT,
    col6 STRING,
    col7 STRING
    ...
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    "separatorChar" = ",",
    "quoteChar"     = "\""
)
STORED AS TEXTFILE;

It should work.

[#6]

@yashwanth Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.  Thanks.