AWS Glue Job creates new column in Redshift if found different datatype for same column in parquet file from S3

Question

I am trying to load parquet file which in S3 into Redshift using Glue Job. When I am running Glue Job first time, it is creating table and loading the data but when running second time by changing the datatype of 1 column, job is not failing instead it is creating new column in Redshift and appending the data.

For example: Here, I am changing datatype of integer number

FileName **abc**
Code,Name,Amount
'A','XYZ',200.00

FileName **xyz**
Code,Name,Amount
'A','XYZ',200.00

In Redshift

Output after processing both the above file:
Code Name Amount Amount_String
A    XYZ  200.00
A    XYZ         200.00

Code

import os
import sys
from pyspark import SparkConf, SparkContext
from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql import SQLContext
from datetime import date

from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.dynamicframe import DynamicFrame

## @params: [TempDir, JOB_NAME]
args = getResolvedOptions(sys.argv, ['TempDir','JOB_NAME'])

spark = SparkSession.builder.getOrCreate()
glueContext = GlueContext(SparkContext.getOrCreate())

spark.conf.set('spark.sql.session.timeZone', 'Europe/London')

#sc = SparkContext()

data_source = "s3://bucket/folder/data/"
#read delta and source dataset
employee = spark.read.parquet(data_source)


sq_datasource0 = DynamicFrame.fromDF(employee, glueContext, "new_dynamic_frame")

datasink4 = glueContext.write_dynamic_frame.from_jdbc_conf(frame = sq_datasource0, catalog_connection = "redshiftDB", connection_options = {"dbtable": "employee", "database": "dbname"}, redshift_tmp_dir = args["TempDir"], transformation_ctx = "datasink4")

I want to fail the Glue Job if datatype mismatch issue is coming from the file. I would appreciate it if you could provide any guidance to resolve this issue.

user_11712883 · Answer

The above issue is due to Redshift writer used by Glue dynamicFrame. This creates new column for the table in Redshfit using alter table query, if there are empty records present in input data's specific column.

To avoid this behavior, convert Glue dynamicFrame to Spark dataframe and write out to redshift.

val amDf = am.toDF()
amDf.write.format("com.databricks.spark.redshift")
    .mode(SaveMode.Overwrite)
    .option("url", JDBC_URL)
    .option("dbtable", TABLE_NAME)
    .option("user", USER)
    .option("password", PASSWORD)
    .option("aws_iam_role", IAM_ROLE)
    .option("tempdir", args("TempDir"))
    .save()

user_2954256 · Answer

A long time has passed since the question was posted, but I had recently the same issue and here is how I solved it.

You must create a new user in Redshift and grant to it only SELECT, INSERT, UPDATE, DELETE permissions. Then when you use this user in Glue, you will receive an error if the columns do not match and Glue tries creating new columns.

The only thing to remember is that if you want to truncate a table, you need to use DELETE FROM ; instead, this is because TRUNCATE requires ALTER TABLE permissions and it would fail.

user_11739568 · Answer

Your crawler configuration setting might be set to the 1st or 2nd option as seen in the below picture:

If you do not want to modify your table when your S3 file structure change, you need edit your crawler and set the 'Configuration options' to select the third option "Ignore the change and don't update the table in the data catalog".

Let me know how it goes and kindly up-vote/accept if it solve your purpose.

AWS Glue Job creates new column in Redshift if found different datatype for same column in parquet file from S3

3 Answers