Loading Snowflake datasource into a view is slow

Peter Wiese shared this problem 48 days ago
Awaiting Reply

Hi,

Our customer experiences long load times when they open a view. They use Snowflake and as we can see, it is the load of schema information that takes a long time. We see many:

WARN (ViewCache:loadDataSources) - Tag not found: xxx in the yellowfin.log

We connect with JDBC

Everything works fine - it is just slow... When we open up a table in the view it says "loading" for some seconds before showing approx 10 tablefields... When we use another connection eg. on the Yellowfin Repository DB - the responsetime is acceptable....


Any clues ?

Comments (13)

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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.

photo
1

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

photo
1

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

photo
1

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:

 <your yf install>/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;”

<your yf install>/appserver/webapps/ROOT/WEB-INF/web.xml

   <servlet>

    <servlet-name>SnowflakeAuthRegistrationServlet</servlet-name>

    <servlet-class>com.hof.pool.auth.adapters.thirdparty.SnowflakeAuthRegistrationServlet</servlet-class>

    <load-on-startup>9</load-on-startup>

  </servlet>

Thanks,

Eric

photo
1

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

photo
1

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.

photo
1

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?

65e4e2feb438511be2e4b19c34c55993

Thanks,

Eric

photo
1

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

photo
1

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