This is a method for processing only a portion of data from a data source in a Glue Job by adding a WHERE clause to the access with JDBC.
It seams impossible to do with DynamicFrame, so I use DataFrame.
When connecting with DataFrame, it seams necessary to explicitly specify the connection information with JDBC. I use glueContext.extract_jdbc_conf to retrieve the information from the Glue Connection.
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.dynamicframe import DynamicFrame
sparcContext = SparkContext()
glueContext = GlueContext(sparcContext)
logger = glueContext.get_logger()
sparkSession = glueContext.spark_session
CONNECTION_NAME = "test-connection" # Name of Glue Connection
DB_NAME = "testdb" # Name of the PostgreSQL database to connect to
# Retrieve the information from the Glue Connection
jdbc_conf = glueContext.extract_jdbc_conf(connection_name=CONNECTION_NAME)
logger.info(jdbc_conf)
# Outputs as follows:
# {'enforceSSL': 'false', 'skipCustomJDBCCertValidation': 'false', 'url': 'jdbc:postgresql://hogehoge.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com:5432', 'customJDBCCertString': '', 'user': 'postgres', 'customJDBCCert': '', 'password': 'xxxxxxxxxxxx', 'vendor': 'postgresql'}
# The string in jdbc_conf["url"] is in the format
# jdbc:postgresql://HOST:5432
# and missing the database name at the end.
# When passed to DynamicFrame.fromDF, adding the database name is necessary.
jdbc_url = jdbc_conf["url"] + "/" + DB_NAME
# The SQL is written as follows:
query = "(SELECT * FROM public.testtable WHERE id <= 100) t"
dyf0 = DynamicFrame.fromDF(sparkSession.read.format("jdbc").
option("url", jdbc_url).
option("driver", "org.postgresql.Driver").
option("user", jdbc_conf["user"]).
option("password", jdbc_conf["password"]).
option("dbtable", query).load(), glueContext, "dyf0")
# Do something
When this Glue Job is executed, the following query is issued to PostgreSQL.
SELECT "id","col1","col2","col3" FROM (SELECT * FROM public.testtable WHERE id <= 100) t
Top comments (0)