Glue ETL Job Error (An error occurred while calling WriteDynamicFrame. MONTH)

Q&A
, ,
1

In AWS Glue i tried to ETL a table from phpmyadmin DB to Athena to use as a dataset in Quicksight. One of the column in the table is a date type column with No NULLs and the default value is ‘0000-00-00’.

When I select all from the table and run the job, an error pops up (An error occurred while calling WriteDynamicFrame. MONTH). So, I tried to change all the ‘0000-00-00’ to NULL, same error occurred.

but when i add a condition ‘SELECT * FROM table_name WHERE date_column <> ‘0000-00-00’ OR date_column <> NULL’, the error resolves. The only problem, i need to ETL the whole table.

Any ideas on how do i solve this issue? What can I replace with NULL/ ‘0000-00-00’?

2

Hi @danial

The error arises because Athena (and by extension, Quick Sight and AWS Glue) does not handle the 0000-00-00 value well, as it is an invalid date format.

Replace 0000-00-00 with a valid dummy placeholder date. Ex: 1900-01-01 or NULL

If you’re using Glue with a JDBC connection, modify the SQL query in your Glue job.

Example:

SELECT *, 
       CASE WHEN date_column = '0000-00-00' THEN '1900-01-01' ELSE date_column END AS date_column
FROM table_name;

If you’ve already imported the raw data into Athena, create a view to handle the transformation.

Example:

CREATE OR REPLACE VIEW transformed_table AS
SELECT *, 
       CASE WHEN date_column = '0000-00-00' THEN '1900-01-01' ELSE date_column END AS date_column
FROM raw_table_name;

Use this view instead of the raw table in Quick Sight. This ensures that Quick Sight pulls clean, transformed data.