使用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()
job.commit()

这篇关于使用AWS Glue覆盖MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！