Skip to main content
yashwanth
yashwanth
Silver ⭐️
March 12, 2024
Solved

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

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"

 

smruti
smrutiAnswer
Gold ⭐️⭐️⭐️
March 14, 2024

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.

View in thread

5 replies

DianaTorres
Community Manager
DianaTorres
Community Manager
March 12, 2024

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

Translate
Regards, Diana Torres, Senior Community Moderator
smruti
Gold ⭐️⭐️⭐️
smruti
Gold ⭐️⭐️⭐️
March 13, 2024

@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 ',' ...
Translate
    yashwanth
    yashwanthAuthor
    Silver ⭐️
    March 13, 2024

    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. 

    Translate
      smruti
      Gold ⭐️⭐️⭐️
      smrutiAnswer
      Gold ⭐️⭐️⭐️
      March 14, 2024

      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.

      Translate
      DianaTorres
      Community Manager
      DianaTorres
      Community Manager
      March 18, 2024

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

      Translate
      Regards, Diana Torres, Senior Community Moderator
        Terms & ConditionsAccessibility statement