getDynamicFrame. key not found: location error when glue job connecting to postgres sql database with query

Question

I am running a glue spark script , which connects to a postgres sql 13 database within VPC. 
the glue connection to postgres sql has been defined in the glue connection with connection type of JDBC. 
The glue job connection name=postgres13 jdbc connection is used in the connection properties inside the glue job. 
If the job uses the table name inside the connection options with "dbtable" parameter , the job runs successfully and able to get the data from the table.

But if i use the connection options with the query to extract the data from the same table. i am getting the below error.

Not sure why we are getting this error? could some one please check and share your inputs.

Error Category: RESOURCE_NOT_FOUND_ERROR; Failed Line Number: 52; An error occurred while calling o147.getDynamicFrame. key not found: location

#######  error log ###################

File "/home/hadoop/.local/lib/python3.11/site-packages/awsglue/data_source.py", line 37, in getFrame
    jframe = self._jsource.getDynamicFrame()
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/spark/python/lib/py4j-0.10.9.7-src.zip/py4j/java_gateway.py", line 1322, in __call__
    return_value = get_return_value(
                   ^^^^^^^^^^^^^^^^^
  File "/usr/lib/spark/python/lib/pyspark.zip/pyspark/errors/exceptions/captured.py", line 179, in deco
    return f(*a, **kw)
           ^^^^^^^^^^^
  File "/usr/lib/spark/python/lib/py4j-0.10.9.7-src.zip/py4j/protocol.py", line 326, in get_return_value
    raise Py4JJavaError(
py4j.protocol.Py4JJavaError: An error occurred while calling o147.getDynamicFrame.
: java.util.NoSuchElementException: key not found: location
	at scala.collection.MapLike.default(MapLike.scala:236)
	at scala.collection.MapLike.default$(MapLike.scala:235)
	at scala.collection.AbstractMap.default(Map.scala:65)
	at scala.collection.MapLike.apply(MapLike.scala:144)
	a
###############################################

# #####My Glue job:

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
from awsglue.dynamicframe import DynamicFrame
from pyspark.sql.functions import explode, col, lit, to_json, struct, concat, concat_ws
import json
from datetime import datetime
from itertools import zip_longest

if __name__ == "__main__":
    sc = SparkContext()
    glueContext = GlueContext(sc)
    spark = glueContext.spark_session
    job = Job(glueContext)

query = "select object_key from s3_event_logs limit 1"

# below connection option is not working with query mode

connection_psql_options_with_query = {
        "connectionName" : "postgres13 jdbc connection",
        "useConnectionProperties" : "true",
        "query" : query
        }

# below connection option is working

connection_psql_options_dbtable = {
        "connectionName" : "postgres13 jdbc connection",
        "useConnectionProperties" : "true",
        "dbtable": "s3_event_logs"
        }

print("testing postgres connection")
    df_po = glueContext.create_dynamic_frame.from_options(connection_type="postgresql",
                                                            connection_options=connection_psql_options_with_query)
    print(df_po)
    s3_paths_df = df_po.toDF()
    print('dataframe printoutput')
    print(s3_paths_df)

re:Post Agent · Answer

The error you're encountering is likely due to how AWS Glue handles connection options when using a query instead of a table name. When using a query, Glue expects different parameters in the connection options.

To resolve this issue, try modifying your connection options as follows:

1. Remove the "useConnectionProperties" parameter.
2. Add a "dbtable" parameter with a placeholder value (e.g., "(SELECT 1) AS dummy").
3. Keep your "query" parameter as is.

Here's how your connection options should look:

```python
connection_psql_options_with_query = {
    "connectionName": "postgres13 jdbc connection",
    "dbtable": "(SELECT 1) AS dummy",
    "query": query
}
```

This approach tells Glue to use the query you've provided while still satisfying the requirement for a "dbtable" parameter. The dummy table is not actually used, but its presence helps avoid the "key not found: location" error.

Also, ensure that your Glue job has the necessary permissions to access the PostgreSQL database, and that the network configuration (VPC, security groups, etc.) allows the connection from Glue to your database.

If you continue to experience issues, you may want to check the Glue job's CloudWatch logs for more detailed error messages, and verify that your connection details (host, port, database name, credentials) are correct in the Glue connection you're referencing.  
**Sources**  
[DynamicFrameReader class - AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-extensions-dynamic-frame-reader.html)  
[AWS Glue and RDS Postgres Issue | AWS re:Post](https://repost.aws/questions/QUtXNdTcOGRW-_4cp9vE1Buw/aws-glue-and-rds-postgres-issue)  
[Troublehsoot AWS Glue connection to a JDBC data source | AWS re:Post](https://repost.aws/knowledge-center/glue-troubleshoot-connection-jdbc-source)

getDynamicFrame. key not found: location error when glue job connecting to postgres sql database with query

#####My Glue job:

Add your answer

Relevant content