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

0

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 <target_database>.<target_table>", 
        "dbtable": "<target_table>", 
        "database": "<target_database>"
    }, 
    transformation_ctx = "datasink4"
)
Alberto
質問済み 1年前1943ビュー
1回答
1

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

profile picture
Bisina
回答済み 1年前
  • Yes preactions are only for Redsfhit. To add to that solution, if the connection details are in the connection instead of the secret, there is the an API in GlueContext to read it from them so you can pass it to the connect()

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ