Glue: UPDATE_IN_DATABASE is not working

Question

Hello All,
We have set up few AWS glue jobs that read data from the database and writes them to S3 files in an parquet format. Along the way, we also create Glue Data Catalog.

We had created glue data catalog table with 5 columns (say A, B, C, D and E). Now after 2 months, the team decided to drop columns D and E and rename C to c.

I tried to use updateBehavior="UPDATE_IN_DATABASE", but that didn't work. The parquet files that we were writing in S3 had the updated data i.e. A, B and c; but the glue data catalog had the new columns but also the old columns were present but without any data.

code is like below :

silver_target = self.glue_context.getSink(
            path=silverLakeLocation + table_name,
            connection_type="s3",
            updateBehavior="UPDATE_IN_DATEBASE",
            partitionKeys=["pyear"],
            enableUpdateCatalog=True,
            transformation_ctx="silver_target")

if I glue catalog is delete first then i see updated table with removed columns,
What should I do to update table to remove ols columns too ? Please advise

Accepted Answer

I understand that, your use case is to update the schema of the data catalog. So, for that you tried using the below code:

```
silver_target = self.glue_context.getSink( path=silverLakeLocation + table_name, connection_type="s3", updateBehavior="UPDATE_IN_DATEBASE", partitionKeys=["pyear"], enableUpdateCatalog=True, transformation_ctx="silver_target")
```
Please note that, the method that you have used above is used for creating new tables and I do not think that it would be updating the existing schema. I request you to refer this [documentation](https://docs.aws.amazon.com/glue/latest/dg/update-from-job.html) in which it is stated that you need to make use of the following code for updating your schema:

```
additionalOptions = {
    "enableUpdateCatalog": True, 
    "updateBehavior": "UPDATE_IN_DATABASE"}
additionalOptions["partitionKeys"] = ["partition_key0", "partition_key1"]

sink = glueContext.write_dynamic_frame_from_catalog(frame=last_transform, database=,
    table_name=, transformation_ctx="write_sink",
    additional_options=additionalOptions)
job.commit()
```
Alternatively, you can directly go ahead and update the schema of your Glue catalog table from the AWS Glue console itself. 
* -> Login to your AWS Glue console and click on tables.
* -> Open your required table and then Actions > Edit schema
* -> Select the required column to be deleted and then click on Delete button.

I hope this answers your question.

Glue: UPDATE_IN_DATABASE is not working

Contenus pertinents