Queyring Snowflake data is super slow

[sonnysangBB]

Hi everyone,

I am using SAS EG 8.3 to query Snowflake database. I wrote a query like below:

proc sql;
select count(*) as cnt from (
select
   col1, col2, col3... (there are 12 columns)
from tbl1
inner join tbl2
on ...
inner join tbl3
on ...
where ...
)
quit;

the expected cnt should be 337,947 because I ran similar code in snowflake successfully (less than 1 minute). But I got errors saying 

• ERROR: Insufficient space in file WORK.'SASTMP-000000054'.UTILITY.
• ERROR: File WORK.'SASTMP-000000054'.UTILITY is damaged. I/O processing did not complete.
• ERROR: There is not enough space to store some intermediate results during join processing.

My question is how to modify the code so it can run in SAS? Because I need the 337,947 records for further analysis. 

To me, I feel the code is trying to pull all data into local machine before aggregation. Is the code supposed to be run in Snowflake?

Thanks! 

[Tom · Accepted solution]

Move it into the Snowflake server.

Say you have a libref named SNOW pointing to Snowflake then use code like:

proc sql;
connect using snow;
select cnt from connection to snow (
select count(*) as cnt from (
select
   col1, col2, col3... (there are 12 columns)
from tbl1
inner join tbl2
on ...
inner join tbl3
on ...
where ...
))
;
quit;

[sonnysangBB]

it works! Thank you @Tom