Working with a job in AWS Glue to perform an upsert from S3 to Redshift I ran into this error:
exception: java.sql.SQLException: [Amazon](500310) Invalid operation: relation "public.#table_stg" does not exist
Im using pre and post actions in my connection options so I can create a temp table as a staging phase. My code looks like this:
table_wo_schema = "my_table"
stg_table_name = f"#{table_wo_schema}_stg"
table_name = f"myschema.{table_wo_schema}"
pre_query = f"drop table if exists {stg_table_name};create table {stg_table_name} as select * from {table_name} where 1=2;"
post_query= f"delete from {table_name} using {stg_table_name} where {stg_table_name}.id = {table_name}.id ; insert into {table_name} select * from {stg_table_name}; drop table {stg_table_name};"
#Job fails here
glueContext.write_dynamic_frame.from_jdbc_conf(
frame = dynamic_frame_to_write,
catalog_connection = "my-redshift-connection",
connection_options = {
"preactions":pre_query,
"dbtable": stg_table_name,
"database": "my-redshift-database",
"postactions":post_query
},
redshift_tmp_dir = "s3://some/dir"
)
I took the idea from here:
https://aws.amazon.com/es/premiumsupport/knowledge-center/sql-commands-redshift-glue-job/
what I've found is that, behind the scenes, the glueContext object adds the 'public' schema when none is provided in:
conection_options = { "dbtable" : "#my_table_stg" }
which will into a similar SQL statement chain:
--pre action statements
statement1;
statement2;
...
--write dynamic frame to Redshift
COPY public.#my_table_stg (column1, column2, ...) FROM 's3://some/path/manifest.json' CREDENTIALS '' FORMAT AS CSV NULL AS '@NULL@' manifest;
--post action statements
...
statement N;
As far as I know, temp tables may reside in a different schema dependending on the first element of the search_path. Despite this, it is not possible to reference a temp table preceded by any schema
--Invalid Redshift statement
select * from someschema.#temp_table
--Valid statement
select * from #temp_table
I don't know what configuration I'm missing to achieve the upsert job using temp tables.
To make it clear, I'm aware i could work around it by using normal tables instead of temporary ones, but I don't like the idea of having staging tables accesible from other sessions. I appreciate all kinds of help in advance