Can Regex Serde in AWS Athena handle new line characters?

user_16198689 0
Viewed 856

I have a table which has a few columns that contain line breaks within the data. I have tried using Glue Crawler to create the tables in Athena but the values are overflowing into the wrong columns due to line breaks. The source data is a CSV in S3 bucket

Now I am trying to achieve the same but by creating a table using the Regex SerDe. My problem is that I am unable to handle the new line characters within the data. I read this post: How to handle new line characters in hive? and wondering if the below is still the case:

You cannot put records into Hive that are separated by newlines which contain newlines within the data itself. At least, not as plain text. You need to use sqoop otherwise for the correct columns to be parsed and loaded – OneCricketeer Mar 4 '18 at 7:24

Simple example of source data with no line breaks, that works: "This is a sentence, with a comma, it is great","Kind regards, John"

"This is another sentence with a comma, its not great",Hello

Two columns, two rows, below is the regex that works:

CREATE EXTERNAL TABLE `commas_regex`(
  `col1` string COMMENT '', 
  `col2` string COMMENT '')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES ( 
  'input.regex'='(\"*[\\w|,|\\s|\\n|\\r]+\"*),(\"*[\\w|,|\\s\\n|\\r]+\"*)$[\\r\\n]*') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://....' /* removed for security */ 
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'transient_lastDdlTime'='1623409617')

Now change source data to include line breaks and it stops working:

"This is a sentence, with a comma, it is great","Kind regards,

John"

"This is another sentence with a comma, its not great",Hello See picture of the results in Athena here

amazon-athena hive amazon-web-services ddl regex
2 Answers
user_153228870

Only binary formats like ORC, Parquet can store newlines and in the query you can check for newlines using regexp only when working with binary formats, Also, if you do not remove in the query some tools may display rows with newlines as few different rows, though this may be not an issue with your tools.

Text formats like TEXTFILE, JSON should not contain newlines because rows are processed row by row considering newlines as row delimiter.

With JSON things are more complicated. JSON can contain \n \r \t combinations and according to JSON specification these combinations are being interpreted by JSONSerDe as newline, carriage return and tab characters, this is why you need to take care about these combinations when working with JSONSerDe, combinamtions like \\n \\t \\r are also should be taken care because they will be interpreted during output. Better to remove all such combinations with any number of slashes along with newlines, tabs, CRs.

user_27003440
Related