BookmarkSubscribeRSS Feed
LaurieF
Barite | Level 11

Since replacing Cloudera with Snowflake a few years ago, we constantly have to battle with the Snowflake ODBC connection. Getting data to and from Snowflake is fine - but when we have to access the metadata the process slows to a crawl. It appears to be doing a scan of all metadata, possibly because of the underscore in the schema names of *****_dev, *****_test and so on. Yeah, bit silly, but there you go.

 

I've written my bespoke ingestion code within DI to compensate for that: using the exist function takes thirty seconds or so, whereas using the open function and checking for success/fail is virtually instantenous. While we investigate the processes required to change the schema names, there's another issue with pass-through SQL joins, related to automatic deletion of the target table.

 

If I set up a user-written transformation, I can tell it not to delete the target table (Inputs/Outputs * Automatically generate delete code for outputs). But if I do an SQL join, it generates proc datasets/delete and there doesn't seem to be any way to get around it. For a SAS output dataset, it doesn't matter, but if it's a table accessed via a Snowflake libname, again we're hit with the 30-second delay while the whole schema metadata is scanned to find the table. If there are half a dozen joins in a job, that's quite a hit.

 

Is there any way to turn this behaviour off? I've looked everywhere in all the options and I can't find it.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 0 replies
  • 834 views
  • 0 likes
  • 1 in conversation