CODEWITHSUNDEEP
hivehiveql
Somik
Somik

Reputation: 13

How to avoid delimeter inside string

First of all please excuse if the question is very regular, I am very new to HIVE and trying my hands on. I have requirement where i need to insert data into a table columns with the help of comma (',') delimeter, below is a sample entry :

Column headers : Name,status,location

Sample data : Arverne,closed,"312 Beach 54 Street Arverne,NY 11692 (40.59428994144626, -73.78442865540268)"

Problem is when I am trying FIELDS TERMINATED BY ',' for location, only able to fetch "312 Beach 54 Street Arverne but requirement is to fetch 312 Beach 54 Street Arverne,NY 11692 (40.59428994144626, -73.78442865540268)

 create table library(name string,Location string) row format delimited
 fields terminated by ','  stored as TextFile;

Upvotes: 1

Views: 166

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

1.

tblproperties ('serialization.last.column.takes.rest'='true')

create external table library (Name string,status string,location string)
row format delimited
fields terminated by ','
tblproperties ('serialization.last.column.takes.rest'='true')    
;

select * from library
;

+---------+--------+---------------------------------------------------------------------------------+
|  name   | status |                                    location                                     |
+---------+--------+---------------------------------------------------------------------------------+
| Arverne | closed | "312 Beach 54 Street Arverne,NY  11692 (40.59428994144626, -73.78442865540268)" |
+---------+--------+---------------------------------------------------------------------------------+

2.

OpenCSVSerde

https://cwiki.apache.org/confluence/display/Hive/CSV+Serde
https://github.com/ogrodnek/csv-serde

Default SerDe de properties

create external table library (Name string,status string,location string)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
stored as textfile
;

select * from library
;

+---------+--------+-------------------------------------------------------------------------------+
|  name   | status |                                   location                                    |
+---------+--------+-------------------------------------------------------------------------------+
| Arverne | closed | 312 Beach 54 Street Arverne,NY  11692 (40.59428994144626, -73.78442865540268) |
+---------+--------+-------------------------------------------------------------------------------+

Explicit SerDe properties

create external table library (Name string,status string,location string)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties 
(
    'separatorChar' = ','
   ,'quoteChar'     = '"'
   ,'escapeChar'    = '\\'
)  
stored as textfile
;

select * from library
;

+---------+--------+-------------------------------------------------------------------------------+
|  name   | status |                                   location                                    |
+---------+--------+-------------------------------------------------------------------------------+
| Arverne | closed | 312 Beach 54 Street Arverne,NY  11692 (40.59428994144626, -73.78442865540268) |
+---------+--------+-------------------------------------------------------------------------------+

Upvotes: 1

Related Questions