AWS Glue Job How to Load MySQL unsigned integer into Redshift

Question

I'm using AWS Glue to load data from MySQL to Redshift and encountering some issues. When the MySQL column type is numeric, it loads as NULL in the corresponding Redshift table.

For columns in MySQL with the type Decimal(N,0), I was able to resolve the issue by setting the corresponding Redshift column type to bigint. However, I have not found a solution yet for columns in MySQL with the type unsigned int.

Requesting:
How to set Redshift column types for these columns?
How to set the Glue Job script?

Answer

https://stackoverflow.com/questions/54468084/aws-glue-not-copying-idint-column-to-redshift-its-blank

When AWS Glue crawls a column, it might not decide on a definitive type for that column. Instead, it maintains a 'type choice' where the exact type of a crawled column can remain as a number of possibilities throughout the ETL Job. The crawler only crawls a subset of a column's data to determine the probable type. This is why when you run the job or look at a preview, the type of the column might get resolved to a type that is incompatible with the dataset, which results in empty data for the column in question​.

The solution is to add into your script an explicit resolution of the choices, by casting the column that's failing to the desired target type. 
Here is how you can do it:

```
df.resolveChoice(specs = [('id', 'cast:int')])

```

AWS Glue Job How to Load MySQL unsigned integer into Redshift

관련 콘텐츠