How do you read from a postgres table (or other system via JDBC) using Glue and Spark?

I ended up going with the following because converting a dynamic_frame to a Spark dataframe is eager, which caused performance issues in some of my jobs that use this util function I created.

connection_properties = glue_context.extract_jdbc_conf(connection_name=connection_name)

spark_reader_options = {
    "url": connection_properties["fullUrl"],
    "user": connection_properties["user"],
    "password": connection_properties["password"],
    "queryTimeout": 60,
    "dbtable": <db.table>,
    # "query": <sql query string>,
    }

spark_reader = spark_session.read.format("jdbc")

for key, value in spark_reader_options.items():
    spark_reader = spark_reader.option(key, value)

df = spark_reader.load()
df = F.broadcast(df)

Allowing the VPC access to the Glue endpoint enabled some Glue jobs to now work properly with the connection. However, jobs requiring a public Python package (e.g., apache-sedona) no longer have access to install the public package when running within the connector VPC.

Do you have any specific recommendation as to how to account for this in addition to accounting for com.amazonaws.us-east-1.glue?

That means your job runs in a VPC that neither has internet connectivity nor the Glue regional endpoint added to the VPC (com.amazonaws.us-east-1.glue)

Thank you for that info. Let me be more specific. I get that error when I specify the connector in the Glue job configuration. If I remove it, I get further along. Without it, I am able to use two other methods to retrieve connection details

    import boto3
    secrets_manager_client = boto3.client(
        service_name="secretsmanager",
        region_name="us-east-1",
    )
    secret = secrets_manager_client.get_secret_value(SecretId=secret_name)
    connection_info = json.loads(secret["SecretString"])
    return connection_info

    import boto3
    glue_client = boto3.client(service_name="glue", region_name="us-east-1")
    connection_info = glue_client.get_connection(Name=connection_name)
    return connection_info

But I am unable to connect in the following methods based on different input credentials format:

    df = spark.read.jdbc(
        url="<url>",
        table=db_table,
        properties={
            "driver": "org.postgresql.Driver",
            # "user": connection_details["username"],
            # "password": connection_details["password"],
            "user": connection_details["Connection"]["username"],
            "password": connection_details["Connection"]["password"]
        },
    )

    dynamic_frame = (
        glue_context
        .read.format("jdbc")
        .option("driver", "org.postgresql.Driver")
        .option("url", "<url>")
        # .option("url", connection_details["Connection"]["ConnectionProperties"]["JDBC_CONNECTION_URL"])
        .option("dbtable", db_table)
        .option("user", connection_details["username"])
        .option("password", connection_details["password"])
        # .option("user", connection_details["Connection"]["ConnectionProperties"]["USERNAME"])
        # .option("password", connection_details["Connection"]["ConnectionProperties"]["PASSWORD"])
        .load()
    )

    connection_details = {
        "dbTable": db_table,
        "connectionName" : connector_name,
        "useConnectionProperties": True,
        "url": "<url>"
    }
    dynamic_frame = glue_context.create_dynamic_frame_from_options(
        connection_type="postgresql",
        connection_options=connection_details,
    )

Depending on the API might check the catalog or not, the point is that the job should be able to do that, you should have the Glue service endpoint accessible or you can run into issues any time

Yes as long as your job doesn't try to access the catalog for any reason, directly or indirectly (for instance using a SparkSession might list the databases in the catalog). It's better if you allow the job to connect to the catalog with the endpoint, otherwise any minute it can break