Pyspark error DDL length exceeded when regexp_replace with added space

user_2019821 0
Viewed 91

This is really strange, this code works when i replace the \n and \r characters with no space. But when i use a space, either
" ", or "\s", or "\s", or "[\s]", or "-" (I've tried everything) it then causes the string to exceed length according to Redshift. So stl_load_errors says exceeded ddl length, but when i grab the text from the dataframe, or even from the stl_load_errors table, it shows its only 1024 characters. The field is set at varchar(1026).

Works:

rootTable.withColumn("firstfield",substring(regexp_replace("firstvalue","[\\r\\n]", ""), 1,1026)) \
                    .withColumn("secondfield",substring(regexp_replace("secondvalue","[\\r\\n]", ""), 1,1026))

Does not work:

rootTable.withColumn("firstfield",substring(regexp_replace("firstvalue","[\\r\\n]", " "), 1,1026)) \
                    .withColumn("secondfield",substring(regexp_replace("secondvalue","[\\r\\n]", " "), 1,1026))

Am i confusing the chars with bytes thing, like the RS table is 1026 bytes, not characters?

The data sample has some \n \r stuff in it: ...Non-applicable\nSubstances...

If I change the substring to around 1014, then it inserts ok, no ddl length exceeded.

Thanks

EDIT: It seems like this is due to special characters or those larger than 1 byte present in the text. Will share when i find a solution

amazon-redshift pyspark
1 Answers

From a lot of research I cannot find an elegant way to handle this. Removing the non-ascii characters is the only way. With non-ascii, there is an unpredictable length. And this can cause ddl length exceeded errors in Redshift in this case.

The pyspark substring method doesn't handle it properly for me. If i were dealing with binary data i guess it would, but to convert it back and forth and try to replace each value with a 1-byte value seems like way too much work for the need in this case.

def ascii_ignore(x):
    return x.encode('ascii', 'ignore').decode('ascii') if x else None
user_20198210
Related