CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftsql-workbench-j
Rishabh Dixit
Rishabh Dixit

Reputation: 115

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

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

Upvotes: 0

Views: 477

Answers (1)

Rishabh Dixit
Rishabh Dixit

Reputation: 115

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.

Upvotes: 1

Related Questions