Overwrite or truncate existing data on jdbc connection Mysql with glue job

Question

I want to overwrite or truncate a table in Mysql using aws glue job python, I tried using preactions like redshift but It doesn't work.
here is my code :

```
datasink4 = glueContext.write_dynamic_frame.from_jdbc_conf(
    frame = dropnullfields3, 
    catalog_connection = "mysql_connection", 
    connection_options = {
        "preactions":"TRUNCATE TABLE .", 
        "dbtable": "", 
        "database": ""
    }, 
    transformation_ctx = "datasink4"
)
```

Answer

Hi, I faced a similar issue to satisfy a requirement for loading data from Redshift to a MySQL database. It seems that there is no overwrite function for data frames when dealing with MySQL. What was implemented was before the data load I truncate the required table by connecting to the RDS.

Example Code

secret_value = client_secret.get_secret_value(SecretId=secret_name)
secret_string = secret_value['SecretString']
secret_json = json.loads(secret_string)

region = session.region_name
user_name = secret_json["username"]
password = secret_json["password"]
host = secret_json["host"]
port = secret_json["port"]

dbname = "<database>"
table_name = "<table>"

conn =  pymysql.connect(host=host, user=user_name, passwd=password, port=port, database=dbname)
cur = conn.cursor()
query = "TRUNCATE TABLE {0}.{1}".format(dbname,table_name)
cur.execute(query)
conn.commit()

I hope this helps

Overwrite or truncate existing data on jdbc connection Mysql with glue job

Relevant content