Defining the order of columns in a GLUE Table created by Cloudformation

Viewed 341

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.

0 Answers
Related