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?

CodePudding user response：

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, ''), ….