By using AWS re:Post, you agree to the Terms of Use

Exception in User Class: org.postgresql.util.PSQLException : ERROR: column "id" does not exist

0

I'm doing ETL from Postgres to Redshift with AWS Glue. I have imported a table with a Crawler and created a Job to just transfer the data and create a new table in Redshift. I get: Exception in User Class: org.postgresql.util.PSQLException : ERROR: column "id" does not exist In the original table, the column is "Id", case sensitive. Is there a way to make Glue case sensitive? (I don't have permission to change the Postgres schema). Also, I tried to create a job with Spark 3.1, Scala 2 (Glue version 3.0) and set caseSensitive = false, still not working and getting the same error

asked 9 months ago510 views
1 Answer
0

Hi ,

I think that the issue is when you are reading the data from Postgres, because the query built by Glue uses the lower case column name. PostgreSQL is a case-sensitive database by default.

if you are using from_catalog to read the data, you could use the following code to avoid glue automatic query building, and force the query you need:

dyf_query_orddt = glueContext.create_dynamic_frame.from_catalog(
    database="mysqltest",
    table_name="classicmodels_orders",
    additional_options={"query":"SELECT * from classicmodels.orders WHERE orderDate > '2005-01-01';"},
    transformation_ctx = "dyf_query_orddt",

)

hope this helps

EXPERT
answered 9 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions