Recently we added a new column to a table that was defined by Glue in CloudFormation. The original was set up as
ShipmentEventTable:
Type: AWS::Glue::Table
Properties:
CatalogId: !Ref AWS::AccountId
DatabaseName: !Ref DataWarehouseEnhancedGlueDb
TableInput:
Description: Shipment event table, in parquet format, partitioned by created_date_year and created_date_month
PartitionKeys:
- Name: created_date_year
Type: string
- Name: created_date_month
Type: string
Name: enhanced_shipment_event
TableType: EXTERNAL_TABLE
Parameters:
classification: parquet
StorageDescriptor:
Columns:
- Name: event_id
Type: string
- Name: event_name
Type: string
- Name: event_type
Type: string
- Name: event_message
Type: string
- Name: event_child
Type: string
- Name: event_client
Type: string
- Name: event_created_date
Type: timestamp
- Name: event_occurred_date
Type: timestamp
- Name: event_migrated_date
Type: timestamp
- Name: event_application
Type: string
- Name: event_inactive
Type: boolean
- Name: shipment_id
Type: string
- Name: shipment_tracking_number
Type: string
- Name: shipment_from_template_id
Type: string
- Name: shipment_from_template_name
Type: string
- Name: shipment_client
Type: string
- Name: shipment_transport_supplier
Type: string
- Name: shipment_planned_pickup_date
Type: timestamp
- Name: shipment_planned_delivery_date
Type: timestamp
- Name: shipment_actual_pickup_date
Type: timestamp
- Name: shipment_actual_delivery_date
Type: timestamp
- Name: shipment_origin_location
Type: string
- Name: shipment_destination_location
Type: string
- Name: shipment_requested_delivery_date
Type: timestamp
- Name: shipment_state
Type: string
- Name: shipment_content_description
Type: string
- Name: shipment_reason_for_exportation
Type: string
- Name: shipment_remarks
Type: string
- Name: shipment_created_date
Type: timestamp
- Name: shipment_type
Type: string
- Name: shipment_incoterms
Type: string
- Name: shipment_expedition_area
Type: string
- Name: shipment_content_type
Type: string
- Name: shipment_pick_mode
Type: string
- Name: shipment_modified_date
Type: timestamp
- Name: shipment_content
Type: string
Compressed: false
InputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat
Location: !Sub s3://${DataWarehouseS3Bucket}/shipment-event/partitioned-by-created-date/
OutputFormat: org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat
SerdeInfo:
SerializationLibrary: org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
we added
- Name: shipment_reference
Type: string
to the bottom of the column, and then started adding data using Athena assuming that the shipment_reference would be the last field.
This was not correct. It appears that Glue adds the Partition Keys as the last columns.
We now have a lot of data where the columns are misaligned
I was hoping there was a way to either Add a new column as the last column or Define the order of the columns.
I have done some searching, but have not been able to find a method to do this using Cloudformation
I would like to avoid having the correct the data, and instead just change the schema to reflect the new data.