MALFORMED_DATE on SPICE refresh

Question & Answer
1

Hi everyone!

I am getting a MALFORMED_DATE error when trying to refresh a data set whose data source is an Amazon TImestream table.

These are two example lines from the error rows csv file:

ERROR_TYPE,COLUMN_NAME,customer_id,site_name,device_id,measure_name,time,ph_avg_1w,ph_count_1w,ph_min_1w,ph_max_1w,ph_p10_1w,ph_p90_1w,ph_temp_avg_1w,ph_temp_count_1w,ph_temp_min_1w,ph_temp_max_1w,ph_temp_p10_1w,ph_temp_p90_1w,turbidity_avg_1w,turbidity_count_1w,turbidity_min_1w,turbidity_max_1w,turbidity_p10_1w,turbidity_p90_1w,turbidity_temp_avg_1w,turbidity_temp_count_1w,turbidity_temp_min_1w,turbidity_temp_max_1w,turbidity_temp_p10_1w,turbidity_temp_p90_1w,electrical_conductivity_avg_1w,electrical_conductivity_count_1w,electrical_conductivity_min_1w,electrical_conductivity_max_1w,electrical_conductivity_p10_1w,electrical_conductivity_p90_1w,electrical_conductivity_temp_avg_1w,electrical_conductivity_temp_count_1w,electrical_conductivity_temp_min_1w,electrical_conductivity_temp_max_1w,electrical_conductivity_temp_p10_1w,electrical_conductivity_temp_p90_1w,dissolved_oxygen_avg_1w,dissolved_oxygen_count_1w,dissolved_oxygen_min_1w,dissolved_oxygen_max_1w,dissolved_oxygen_p10_1w,dissolved_oxygen_p90_1w,dissolved_oxygen_temp_avg_1w,dissolved_oxygen_temp_count_1w,dissolved_oxygen_temp_min_1w,dissolved_oxygen_temp_max_1w,dissolved_oxygen_temp_p10_1w,dissolved_oxygen_temp_p90_1w,flow_avg_1w,flow_count_1w,flow_min_1w,flow_max_1w,flow_p10_1w,flow_p90_1w,volume_count_1w,volume_min_1w,volume_max_1w

MALFORMED_DATE,time,customer10,site10,device10,manual-load-weekly-target,1577664000000,7.86737236932472,864,7.0274658203125,8.471752166748047,7.54818058013916,8.191645622253418,16.90101245156041,864,16.074100494384766,17.638259887695312,16.510913848876953,17.273357391357422,124.19242492428532,864,86.68220520019531,167.3697967529297,106.25920867919922,142.29360961914062,16.890356370696313,864,15.96194076538086,17.852338790893555,16.518884658813477,17.258811950683594,124.87413333080433,864,80.47364044189453,182.3530731201172,102.1494140625,148.23678588867188,16.866927639201837,864,16.112037658691406,17.76652717590332,16.461544036865234,17.253541946411133,3.45409236130891,864,2.576868772506714,4.250640869140625,3.0801808834075928,3.854489326477051,16.8727101860223,864,16.009130477905273,17.542911529541016,16.512298583984375,17.21934700012207,0.0803320579128874,864,0.0,0.5258098336116269,0.0,0.5258098336116269,864,69461672854552576,69461672854568576

MALFORMED_DATE,time,customer10,site10,device10,manual-load-weekly-target,1578268800000,7.885276174734509,2016,7.187221527099609,9.191184997558594,7.516930103302002,8.243422508239746,16.922391070732996,2016,15.954058647155762,17.99000358581543,16.52247428894043,17.335134506225586,126.34166321300324,2016,77.99385070800781,171.3419952392578,104.34043884277344,146.34390258789062,16.884068400613845,2016,15.985934257507324,17.781112670898438,16.49813461303711,17.272974014282227,127.53403772248163,2016,76.11845397949219,177.5960693359375,105.06119537353516,149.0402069091797,16.900087246819147,2016,15.97212028503418,17.787946701049805,16.514854431152344,17.297170639038086,3.382909566873596,2016,2.5400326251983643,8.078622817993164,2.9796977043151855,3.7699778079986572,16.900643721459403,2016,16.05769157409668,17.858877182006836,16.51791763305664,17.28469467163086,0.186155045453465,2016,0.0,0.5856465720907388,0.0,0.5771544103321405,2016,69461672854552576,69461672854568768

time is a millis timestamp, which is what Timestream stores. The weird thing is that some rows of the source table are being loaded.

Querying Timestream directly there is no apparent difference between a row that fails to be loaded in SPICE and one that succeeds in being loaded.

Is there anyway to troubleshoot this?

2

Can you provide examples of a row that works?

Also are you casting this field anywhere?

3

I deleted my source tables, recreated from scratch and reloaded similar data and it worked. I don’t yet know why the errors were happening, but if it does happen again I will provide more details.

1 Like
4

I’m getting the same error with SPICE. It works well when I’m using direct query but gets MALFORMED_DATE error when using SPICE (it was working before and the same date selection works on other datasets)

5

Hi @a2go!
You can try to edit the date format in QuickSight following instructions here: Using unsupported or custom dates - Amazon QuickSight

Best Regards,
Kellie

6

@Kellie_Burton Please see the post I made last night regarding a similar error: New custom SQL SPICE import failures--recent code update?

Essentially, some combination of renamed fields and custom fields is causing column parsing to shift around. The datetime values themselves are unix timestamps directly from the query results, so they should work (and have worked for two years). This appears to be a pretty serious regression on Amazon’s side regarding SPICE import, but I’m not sure where else to escalate this.