Problem with reading bit data type from MySQL and transformation into Redshift with AWS Glue

user_10201871 0
Viewed 1071

I have a table in a MySQL Database that contains a column called activity which is of data type BIT. When converted to INT it can take on the values 1,2 or 3.

When using the crawler in AWS Glue it recognizes activity as BOOLEAN. I have tried to edit the schema for the table and change data type for activity to INT but Glue still reads it as BOOLEAN when running the job.

I have also tried to use ApplyMapping to convert it to INT but with no success.

Any ideas on how to resolve this?

aws-glue pyspark mysql
2 Answers

I solved it by pushing down an query to the MySQL database where I CAST the BIT as INT when reading it into Glue using:

pushdown_query = "(SELECT col1, CAST(activity AS INT) AS activity FROM my_table) my_table"

df = glueContext.read.format("jdbc")\
.option("driver", "com.mysql.jdbc.Driver") \
.option("url", db_url) \
.option("dbtable", pushdown_query) \
.option("user", db_user) \
.option("password", db_pass).load()
user_102018710

You can use instead spark/pySpark code to read the table in your MySQL Database. For example, using pySpark will be as follow:

jdbcUrl = "jdbc:mysql://{0}:{1}/{2}".format(jdbcHostname, jdbcPort, jdbcDatabase)
connectionProperties = {
  "user" : jdbcUsername,
  "password" : jdbcPassword,
  "driver" : "com.mysql.jdbc.Driver"
}
employees_table = spark.read.jdbc(jdbcUrl, "employees", connectionProperties)

You can find more information on this link: Spark Data Sources Hopefully, spark will do a much better job than AWS Glue on inferring the schema.

user_102549100
Related