Athena UNLOAD and empty values

Question

Im saving data to JSON using `UNLOAD`  Athena command. Some of non-string fields are empty. `UNLOAD` command puts `null`'s everywhere instead. Is there any way to tell Athena to skip serialization of a field if it is empty?

Answer

When using the Amazon Athena UNLOAD command, it uses the default serializer [LazySimpleSerDe](https://docs.aws.amazon.com/athena/latest/ug/lazy-simple-serde.html). This serializer outputs empty values as Null, like you are experiencing, and cannot be changed.

One option is to specify each column in your select that you do not want to output to null and use a function to transform nulls. This would be instead of doing a `SELECT *`.

For example:

```
UNLOAD (SELECT col1, COALESCE(col2,'') FROM table)
TO 's3://my_athena_data_location/my_folder/'
WITH (format = 'JSON')
```

References:

* [LazySimpleSerDe - Amazon Athena User Guide](https://docs.aws.amazon.com/athena/latest/ug/lazy-simple-serde.html)
* [UNLOAD - Amazon Athena User Guide](https://docs.aws.amazon.com/athena/latest/ug/unload.html)

Athena UNLOAD and empty values

Contenuto pertinente