Athena CTAS replacing null values in tables with \N

Question

When I use Athena CTAS to generate CSV files, I found that null values in the Athena table are replaced by "\N". How do I get it to just leave these values as empty columns?

The CTAS query I'm using is something like this:

CREATE TABLE table_name WITH (format = 'TEXTFILE', field_delimiter=',', external_location='s3://bucket_name/location') AS SELECT * FROM "db_name"."src_table_name";

Am I doing something wrong?

user_1109 · Answer

This is the default token for NULL for LazySimpleSerDe, and CTAS does not expose any mechanism for changing it unfortunately.

If you'd rather have empty fields for your NULL values you have to ensure they are all empty strings, e.g. … AS SELECT COALESCE(col1, ''), COALESCE(col2, ''), ….

Athena CTAS replacing null values in tables with \N

1 Answers