question Re: how to load double quotes data of fields in hive table without excluding double quotes? in Support Questions

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

yashwanth — Tue, 12 Mar 2024 17:57:17 GMT

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"

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

DianaTorres — Tue, 12 Mar 2024 23:50:10 GMT

@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.

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

smruti — Wed, 13 Mar 2024 06:56:11 GMT

@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 ',' ...

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

yashwanth — Wed, 13 Mar 2024 15:53:22 GMT

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.

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

smruti — Thu, 14 Mar 2024 13:29:37 GMT

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.

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

DianaTorres — Mon, 18 Mar 2024 21:56:20 GMT

@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.