Athena error for column rootcause

[man-jiteshm-myob]

I get the following issue in Athena for the cost_anomaly_data table when running CID data collection

HIVE_PARTITION_SCHEMA_MISMATCH: There is a mismatch between the table and partition schemas. The types are incompatible and cannot be coerced. The column 'rootcauses' in table 'optimization_data.cost_anomaly_data' is declared as type 'array<struct<service:string,region:string,linkedaccount:string,linkedaccountname:string,usagetype:string,impact:struct<contribution:double>>>', but partition 'payer_id=562791833499/year=2025/month=04/day=18' declared column 'rootcauses' as type 'array<struct<service:string,impact:struct<contribution:double>>>'.
This query ran against the "optimization_data" database, unless qualified by the query. Please post the error 
message on our forum  or contact customer support  with Query Id: adf72b1e-c759-4b8e-adc4-c831c24bb622

I believe it is to do with the wrong array ordering:

array<struct<service:string,region:string,linkedaccount:string,usagetype:string,linkedaccountname:string,impact:struct<contribution:double>>>

vs

'array<struct<service:string,impact:struct<contribution:double>>>'

possibly related to #341 but the workaround provided here is temporary.

[iakov-aws]

We pull store json with rootcause as an array of dicts:

• cloud-intelligence-dashboards-data-collection/data-collection/deploy/module-cost-anomaly.yaml

  Line 232 in 5377959

  'RootCauses': get_value_by_path(record, 'RootCauses', []), # Store full array of root causes

Crawler adds the column into definition once there is and not to all partitions that were created previously. I would suggest you try to delete the table and re-run crawler to create it again.

We probably need to include the full definition to the anomaly module to prevent this from happening. Or use string json for more complex data types.

[man-jiteshm-myob]

Hi @iakov-aws,
not sure how i missed this, but i already tried that approach and it did not work.
still getting the same error.

the Glue schema looks alright as you can see below, however i think when Athena is probing the s3 it's failing to satisfy the schema

{
  "rootcauses": [
    {
      "Service": "string",
      "Region": "string",
      "LinkedAccount": "string",
      "UsageType": "string",
      "LinkedAccountName": "string",
      "Impact": {
        "Contribution": "double"
      }
    }
  ]
}

[man-jiteshm-myob]

just to give more data, this is an error
HIVE_PARTITION_SCHEMA_MISMATCH: There is a mismatch between the table and partition schemas. The types are incompatible and cannot be coerced. The column 'rootcauses' in table 'optimization_data.cost_anomaly_data' is declared as type 'array<struct<service:string,region:string,linkedaccount:string,usagetype:string,linkedaccountname:string,impact:struct<contribution:double>>>', but partition 'payer_id=<AWS::accountid>/year=2025/month=05/day=12' declared column 'rootcauses' as type 'array<struct<service:string,impact:struct<contribution:double>>>'.

and this is the contents of the s3 file
{ "AnomalyId": "b746d0e3-95bf-4427-853b-xxxxxx", "AnomalyStartDate": "2025-05-07T00:00:00Z", "AnomalyEndDate": "2025-05-10T00:00:00Z", "DimensionValue": "Amazon MQ", "MaxImpact": 0.71, "TotalActualSpend": 8.46, "TotalExpectedSpend": 5.76, "TotalImpact": 2.7, "TotalImpactpercentage": 46.88, "MonitorArn": "arn:aws:ce::<AWS::accountid>:anomalymonitor/102c2571-129a-459e-83f4-xxxxxxxxx", "LinkedAccount": null, "LinkedAccountName": null, "Region": null, "Service": "Amazon MQ", "UsageType": null, "RootCauses": [ { "Service": "Amazon MQ", "Impact": { "Contribution": 2.7 } } ] }

[iakov-aws]

We are working on a fix for that.

[man-jiteshm-myob]

@iakov-aws is there any update on this?

[man-jiteshm-myob]

@iakov-aws I noticed one of the datasets ca_summary_view has no Schedule to refresh data, however all other datasets have one. I manually refreshed the dataset and the dashboards were updated.

Can you please advise if the dataset should have a schedule or not. How did it get missed. can I debug it somehow? Can I manually add the schedule or can the cloudformation still do it?

[iakov-aws]

the absence of schedule was a regression in one of the releases. it was fixed few days after but if you installed this period you need to add it manually or update dashboard with cid-cmd update --force --recursive with the latest cid-cmd tool (pip3 install -U cid-cmd)

[man-jiteshm-myob]

i ran the above command as recommended, the dataset was updated but there was no schedule added. i have also tried deleting and recreating the cost anomalies cloudformation stack. if i have to manually add the schedule, what time should i set it to? Is there an order or dependency ?