Loading Snowflake datasource into a view is slow

Hi Peter,

Thanks for reaching out to support with your issue.

Not entirely sure what the "tag not found" error is referring to, I'll see if our dev team can shine some light on this.

Would you be able to provide a compressed copy of the entire logs folder for additional analysis here? If it is to large to attach to this ticket you can upload to our ftp https://ftp.yellowfin.bi using the "upload files" option. Please indicate a file name and time of the experienced behavior in a reply here.

Also, I saw in our developer platform there have been some known issues with a particular function on Snowflake and JDBC being slow -

While snowflake reporting queries are very fast, the GetColumns query we run at several points, are very slow (~30sec). A few processes effected by this:

    First visiting the ER diagram
    Dragging a table into the ER diagram
    Entering the report builder for the first time

It almost feels like your experience could be related here, as I could see loading a view running a similar query... does this sound accurate? If so, there might be a workaround available.

Thanks,

Eric

Hi Eric,

Thank You for this...

I have just uploaded all the logs to the FTP.. including a performance-monitor-check. (zip'ed).

Take a look at it.

The issue you mention sounds like this one... Loading metadata from Snowflake....

I hope You can find a workaround for us - or a patch.. We have looked into issues regarding the JDBC driver for SF - but nothing comes up..

br Peter

Hi Again,

I found this on Snowflake community... Have you looked into this?

https://community.snowflake.com/s/question/0D53r00009sz79fCAA/improve-performance-of-jdbc-getmetadata-driver-apis

It looks like this could be the reason...

I have a touch base with the customer tomorrow - it would be nice some news from you :)

br Peter

Hi Peter,

Thanks for the update here.

In the article you linked, it looks like the root cause was tables that contained the underscore symbol... is this a variable present in the affected environment?

I've reached out to an implementation specialist in regards to this issue; according to the dev task I mentioned-

 I looked into this one a bit further and it turns out that part of the reason why this query is so slow, is because snowflake runs the query against all available databases and schemas by default. However, it is possible to run a session command to force these queries (get columns and tables) to only consider the context that has been presented to the connection,

I created a custom authentication adapter to run this command in applyPostConnectionAuthentication:

...

So I am going to see if he thinks this will help you in this case, and whether I can get a copy of this custom authentication adapter. are you using an API or custom JDBC driver in this situation? If so, would you be able to provide for reference?

Thanks,

Eric

Hi Eric,

The customer is t using a custom JDBC driver or custom API. We just updated the Snowflake driver some weeks ago.

But as you say - this looks like the main issue.. I have communicated with the customer - and they hope Yellowfin comes up with a workaround... I am not able to test it directly - but if Yellowfin comes up with a solution - we have a test environement where we can try it...

Br Peter.

Hi Peter,

Thanks for the additional info here. Just wanted to confirm whether you or the client think the updated driver has anything to do with this behavior? IE "it wasn't slow until the driver update."

I'll let you know if I can get this adapter for you, also would you like me your organization as an affected client to the existing dev task so you can receive updates in regards to a proper fix here?

Thanks,

Eric

Hi Peter,

Hope things are well with you. Just wanted to follow up here, I've got our implementation specialist digging for a custom Snowflake connector he's built that improves the performance of some of these Snowflake DB queries. He's of the opinion that this will be the fix you're looking for. Sorry for the delay, he's a busy guy, but we should be able to get things performing better immediately, as devs work on a proper fix in the meantime.

Thanks,

Eric

Hi Peter,

Thanks for your patience here, finally got the fix from our guy! Jar is attached. Let me know how it goes!

You will want to place this file into the Yellowfin lib folder:

 /appserver/webapps/ROOT/WEB-INF/lib
 
You will then need to add  the following line to the web.xml file (note that I have changed this since the last email), above the line that contains “&jsps;”

/appserver/webapps/ROOT/WEB-INF/web.xml

   

    SnowflakeAuthRegistrationServlet

    com.hof.pool.auth.adapters.thirdparty.SnowflakeAuthRegistrationServlet

    9

  

Thanks,

Eric

Hi Peter,

Hope things are good with you! Just wanted to check in to see if you were able to get the modified Snowflake connector up and running here?

Thanks,

Eric

HI Eric,

Sorry for the delay.. We had to find time for this..

We tried this on our clients test setup. Unfortunately we could not see any difference in the performance. The views are still slow..

Perhaps we have not installed it correcly.. Is there a way where we can check, that the jar-file is loaded correctly into yellowfin?

br Peter.

Hi Peter,

Thanks fr the update, after adding the jar file as instructed, I get this message on application startup -

================== SnowFlake Authentication Registration ============
| Version: 13 November 2014
|
| Adapter Registered
|
=====================================================================

Would you be able to confirm you can see this in the application logs on startup?

I don't see this listed in any of the Yellowfin information pages however. I'll double check to see if there an additional parameter we need for Yellowfin to fully integrate the new authenticator with the adapter. There might be a way to see if it's running during the view opening process by enabling DEBUG mode. Since I don't have a Snowflake DB to play with at the moment, I couldn't get very far in testing. But I can ask to see in HQ could provide one. I'll also followup with the connector constructor, to see if there are any additional caveats.

One thing I could imagine being an issue, is the age of the authenticator - perhaps Snowflake has changed some of its connection properties in the last 6 years! If thats the case, I could maybe see this working with an older JDBC driver, or with some "legacy" setting enabled on the Snowflake side... maybe you could test this while I'm waiting for some resources on my end?

Just the same, I come back to the "tag not found" error... I'll do another dig of the logs. Based on how my colleague described the issue, of Snowflake checking all schemas when loading the view... I'm wondering if the "include schema in SQL" data source setting might help here, have you tried this setting?

Thanks,

Eric

Hi Eric,

We have now checked everything...

The modified connector is loaded and "Include Schema in SQL" is also cheked..

But we do not see any difference in performance loading metadata from SF into Yellowfin.. it is still very slow.

Just to recap: We have issues when we open a view with a SF-source.. when we expand the tables in the view - we experience a delay in the load of the columnames.

Br Peter

Hi Peter,

Thanks for the update. And sorry to hear this wasn't the fix we'd hoped for. I have a Snowflake instance requested for replication purposes here. This might not be necessary, since we have this related unresolved task in our system already, where we seem to know what's happening in the snowflake metadate request process and why. I also took another look at the task and saw this -

For my simple test schema this dropped the query from 8 sec to 1 sec. However, the same call against a larger schema took closer to the original 8 sec.  Is there any precedent for applying session parameters to connections to specific databases? Is this something we could include OOB?

Our lead dev gave a technical answer I've attached for reference, but in short, this "fix" may be running into the issue mentioned above, and in order to ACTUALLY fix this, we will need some additional development. I believe if I were to add you as an affected client to the task, we could get some traction on finishing the work on this one. Let me know what you think.

Thanks,

Eric

Hi Peter,

Just wanted to check in to see if you wanted to push for a proper fix in this case?

Thanks,

Eric

Hi Eric,

Sorry that i have not replied earlier..

As I am doing this on behalf of our customer, I believe they would like this to be fixed as soon as possible.

They are not pushing me for a fix, but this issue must be a common problem for other customers who uses Snowflake?

br Peter

Hi Peter,

Got an update for you - we've got a fix for how Yellowfin looks up Snowflake schemas, that is targeted against upcoming builds 8.0.7 and 9.2. This should improve the performance of getting the table fields in the View Builder. I'll follow up on release to make sure this fixes things for you. I'll keep this ticket marked as Defect Logged in the meantime.

Thanks,

Eric

Hi Eric,

I assume this is being targeted for the September build? Any idea of a rough release date?

We're planning on updating to 9.2 from 9.1 but will likely hold back for this one

Thanks

Dean

Hi Dean,

This is looking like it's on the cards for 8.0.7 and 9.3 releases due out late September/mid October but I will keep you updated on the release date and if anything changes.

Cheers,

Neal

Thanks Neal, please do

Hi Dean,

Not sure if this info has come to you yet, but just giving you a heads up that 9.3 has been released which includes the fix of this issue.

Please let me know how your upgrade goes.

Cheers,

Neal