使用AWS Glue覆盖MySQL表

本文介绍了使用AWS Glue覆盖MySQL表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个lambda进程，偶尔会轮询API以获取最新数据.该数据具有唯一键，我想使用Glue更新MySQL中的表.是否可以使用此键覆盖数据? (类似于Spark的mode = overwrite).如果不是，我是否可以在插入所有新数据之前截断Glue中的表?

I have a lambda process which occasionally polls an API for recent data. This data has unique keys, and I'd like to use Glue to update the table in MySQL. Is there an option to overwrite data using this key? (Similar to Spark's mode=overwrite). If not - might I be able to truncate the table in Glue before inserting all new data?

谢谢

推荐答案

我提出的解决方法如下:

The workaround I've come up with, which is a little simpler than the alternative posted, is the following:

• 在mysql中创建一个临时表，并将新数据加载到该表中.
• 运行命令:REPLACE INTO myTable SELECT * FROM myStagingTable;
• 截断登台表

这可以通过以下方式完成:

This can be done with:

import sys from awsglue.transforms import * from awsglue.utils import getResolvedOptions from pyspark.context import SparkContext from awsglue.context import GlueContext from awsglue.job import Job ## @params: [JOB_NAME] args = getResolvedOptions(sys.argv, ['JOB_NAME']) sc = SparkContext() glueContext = GlueContext(sc) spark = glueContext.spark_session job = Job(glueContext) job.init(args['JOB_NAME'], args) import pymysql pymysql.install_as_MySQLdb() import MySQLdb db = MySQLdb.connect("URL", "USERNAME", "PASSWORD", "DATABASE") cursor = db.cursor() cursor.execute("REPLACE INTO myTable SELECT * FROM myStagingTable") cursor.fetchall() db.close() jobmit()