AWS Redshift CTAS query complete in cluster query tab but still running from the client sql workbench/j.The table is also not getting created

0
votes

I have a cross join in my query. Here is the modified query :

create table abc.abcd as with temp1 as 
(select
1 as bid, *
from
abc.data_1 ), temp2 as (select
1 as aid, *
from
abc.data_2 b), temp3 as ( select
a.*,
b.*
from temp2 a
join
temp1 b on a.aid=b.bid) 
select * from temp3

The query is successfully completed in the redshift query history but the status of the query is still running in sqlworkbench/J.

status_complete

sqlworkbench/J status is running

Upon querying the newly created table, I am getting an error that object does not exist. I am using a 4 node dc2.large cluster. What could be going wrong here ?

UPDATE 1 : If I am running the same query but using a limit clause, the query returns the output just fine and the table is getting created.

Here is the query using limit clause:

create table abc.abcd as with temp1 as 
(select
1 as bid, *
from
abc.data_1 ), temp2 as (select
1 as aid, *
from
abc.data_2 b), temp3 as ( select
a.*,
b.*
from temp2 a
join
temp1 b on a.aid=b.bid) 
select * from temp3 limit 200
1
amazon-web-servicesamazon-redshiftsql-workbench-j
you probably lost connection while waiting? do you have autocommit turned on in sqlworkbench/j if not then thats probably why the table is now gone.? see point 10 of docs.aws.amazon.com/redshift/latest/mgmt/… – Jon Scott
Here are the sqlworkbench/J options in my client - imgur.com/XHdJUFj – Rishabh Dixit
try using a ec2 instance to run the same (using tmux/screen + psql) - you should find it works fine. almost certainly its been caused by drop in connection. – Jon Scott
But after resetting the connection in my sqlworkbench/J client, the table should be available as the query is completed in Redshift, right ? It is not even available in the client which is more bizarre. – Rishabh Dixit
@JonScott I tried the same thing again with an EC2 instance and it worked ! Thank you for your suggestion. – Rishabh Dixit

1 Answers

1
votes

I created an EC2 instance and installed sqlworkbench/J on it and ran the same query and it worked like it is supposed to be.

I am suspecting the reason for this to be network issues. The queries were submitted to the cluster but the "Autocommit" was somehow not being sent to the cluster from the sqlworkbench/J client due to the network issues. Even my RDP connection broke multiple times when I was running the queries in the EC2 instance. So it seems like it were network issues causing the abnormal behavior.