Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

carriage return in middle of row for fixed width file

carriage return in middle of row for fixed width file

New Contributor

Hi , we are getting carriage return (hex value 0D) in middle of row for fixed width file. so i am not able to use RegexSerDe. I created external table with one field(later substring for different fileds) on that file. but the row that contain carriage return in miiddle of field value is splitting into two rows.how to escape carriage returns in hive. I tried below but no luck.

CREATE EXTERNAL TABLE my_table(a string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = "\t", "quoteChar" = "'", "escapeChar" = "\0D" ) STORED AS TEXTFILE LOCATION '/QA01/raw/L0_adp_owner_ip_1'

;CREATE EXTERNAL TABLE my_table(a string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = "\t", "quoteChar" = "'", "escapeChar" = "\\" ) STORED AS TEXTFILE LOCATION '/QA01/raw/L0_adp_owner_ip_1'

below are sample rows.first row causing issue.

BDY221808940PANGELINA SPRAGGS
BDY221808942C can NB 40th floor 20190103
BDY221808941PANGELINA SPRAGGS 06 20190103
Don't have an account?
Coming from Hortonworks? Activate your account here