Unable to trim empty space in pyspark dataframe

Question

While loading data from Oracle and writing to PostgreSQL facing weird issue. Unable to write string with space to postgres. Facing below issue

Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO xyz("col1","col2") VALUES ('7643'::numeric,'xyz/xyz xyzxy xyz/xyz xyzxy ') was aborted: ERROR: invalid byte sequence for encoding "UTF8": 0x00  Call getNextException to see other errors in the batch

So trying to trim col in dataframe but that is not working. Before and after trimming data is same.

data= data.withColumn("trimmed", trim(col("col2")))

Am very new to pyspark and data cleaning, any help is highly appreciated.

danielsepulvedab · Answer 1 · 2021-03-06T19:36:41.963

1

Probably the trim is working just fine. The trim function just removes spaces from both ends of the stream. And actually your problem is not that. The issue is that Postgres doesn't accept the NULL character (i.e. 0x00, check this), and it looks like you have some in your col2. Try cleaning those first with something like:

from pyspark.sql.functions import regexp_replace, col
data = data.withColumn("col2", regexp_replace(col("col2"), "\u0000", ""))

edited Mar 06 '21 at 19:36

answered Mar 06 '21 at 19:31

danielsepulvedab

676
1
11
22

It worked. I have used dataframe.replace..not sure why it didn't work. Need to check what it does. – arya.s Mar 07 '21 at 06:30
Maybe you can accept this answer then? :) – danielsepulvedab Mar 07 '21 at 17:33
Already accepted. But what I couldn't get is the column value for which this error is coming is not null. It just has some empty space at the end. Any idea on that ? – arya.s Mar 08 '21 at 04:23

Unable to trim empty space in pyspark dataframe

1 Answers1