Hi I am creating external table which is loading data from S3 Bucket file. But for some column I am getting CRLF due to which data is going to another row and not loading perfectly. Could you please help me how can I resolve this?
Example:
Draft recommendation issued to sponsor
"August 23, 2021
To
August 25, 2021"
This is loading same into table. How to load the above data into same row.
Draft recommendation issued to sponsor
"August 23, 2021 To August 25, 2021"
I am using below DDL to insert data into external table-
Drop table hta_spectrum.stg_cadth_onco_pharma;
CREATE EXTERNAL TABLE hta_spectrum.stg_cadth_onco_pharma
(
Draft recommendation issued to sponsor VARCHAR(1000)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = '|',
'quoteChar' = '\"',
'escapeChar' = '\n'
)
LOCATION 's3://********/Intermediate_layer/onco_pharma/'
TABLE PROPERTIES ('skip.header.line.count'='1');