JDBC URL issue when creating Data Source using Teradata JDBC (16.20.00.12)

Ashwin Nelluri shared this problem 4 months ago
Defect Logged

Hello,


I am experiencing an issue when attempting to create a Data Source (for a View) using my Teradata 16.20.00.12 JDBC driver. Just for background, I am working with a 60-day development licensed Yellowfin environment which I have installed on a Linux VM and am attempting to source data from a Teradata 16.20 Express VM running on the same host.


I have already uploaded the latest Teradata JDBC driver (16.20.00.12) to the Plugin Manager:

/AsiREBUCi5QAAAABJRU5ErkJggg==


Here is a screenshot of the error I am receiving after I hit the 'Test Connection' button when creating/filling out the Data Source form in Yellowfin:

/8AAAAASUVORK5CYII=

I've also attached the yellowfin.log capturing the same error as well as the java error stacktrace.


One thing I understand very well is that the Teradata JDBC connection parameter for Database Port is supposed to be 'DBS_PORT' with an underscore, and not 'DBSPORT'. (as referenced here: https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html), and that is what I suspect is the root cause.


At first I wondered where Yellowfin was storing the JDBC URL once it attempts to create the Data Source, because I could not find it in anywhere in the UI. Then I noticed that the metadata for the Data Sources for a View are stored within the ReportViewSource table (here is what I see in my instance of MySQL 8 below).

/h+fVn6HvbfNCwAAAABJRU5ErkJggg==

Within the ReportViewSource table, I noticed that the DatabaseURL string for my Teradata JDBC connection indeed showed the parameter as: jdbc:teradata://192.168.1.7/DATABASE=DBC,DBSPORT=1025


So I decided to try something bold and decided to run the following UPDATE statement to replace the string with the correct 'DBS_PORT' parameter as such:

UPDATE YELLOWFIN_DB.ReportViewSource

SET DatabaseURL = REPLACE(DatabaseURL, 'DBSPORT', 'DBS_PORT')

WHERE SourceId = 70018 -- specific SourceID that was generated on my environment.


With the correct parameter now reflected in the DatabaseURL in the metadata, I then restarted my YellowFin Tomcat instance hoping that it would refresh the application metadata into the cache.

However I am still receiving the same error:

/wMlt8G9ubgxoAAAAABJRU5ErkJggg==


Based on this troubleshooting attempt, I have a few questions:

  1. Have I stumbled upon a bug? In other words, is there a particular reason why my actions in using the Teradata JDBC Driver creates a DatabaseURL with the incorrect parameter of 'DBSPORT' and not 'DBS_PORT'?
  2. Do I have the right idea to try and resolve, by updating the string within the ReportsViewSource.DatabaseURL field? Is the reason why this is not working is because I haven't properly/completely evicted the metadata cache from the server? Is there a better way to workaround this?
  3. Is there any reason why Teradata 16.20 drivers wouldn't be supported yet on Yellowfin? (I looked at the Confluence pages, and noticed that although there was no JDBC page available for 'Teradata' under the data sources section, 'Teradata' was listed as a supported driver in the larger list: https://wiki.yellowfinbi.com/display/user80/JDBC+Connections )

Any help/insight would be appreciated, and I'd be happy to share more information about my environment if needed. Thanks!

Comments (4)

photo
1

Hello Ashwin,

Thanks for reaching out with this issue.

I wanted to let you know that I am actively working on this and setting up an appropriate instance to replicate.

Something you can try is using the generic jdbc driver and attempting to connect that way. I looked into a driver to attempt to find the url string to see if the parameter was wrong therein but was unable to locate it.

Please let me know if there is anything else regarding this that needs mentioned. Or if I misunderstood your question.

Regards,

Jared

photo
1

Thank you for looking into this Jared, much appreciated.

In case it helps, I've also attached here the exact JDBC driver I am using...as you saw in the screenshot of Plugin-Manager, there didn't seem to be any issue in recognizing the Teradata driver itself.

Another thing I just tried today was leaving the 'Database Host' field blank to see if it removes the parameter altogether from the JDBC URL string. I was thinking since I am using the default Teradata DB port of 1025 anyway it might not need to be specified explicitly into the JDBC URL as a parameter. However it was in vain as I still ended up with the same error even leaving the field blank and doing a 'Test Connection'.

I am curious if anyone knows the answer to my second question above, of whether forcing an update to the DatabaseURL in the ReportsViewSource table of the Yellowfin Repository Database could work. Again I suspect that the reason I am still getting the error even after that update, is because maybe that DatabaseURL is being cached somewhere else on the server, and I wasn't sure exactly how to flush out the Yellowfin Application Server cache (I tried restarting my Tomcat instance but that didn't seem to flush out cache). I was hoping you or someone might know if its possible to refresh the server's metadata cache and how, so I can test it and see if it works.


Please do let me know what you find ultimately. Thanks!

photo
1

Hello Ashwin,

I Suspect that you can subvert this behavior by using the generic jdbc driver option when creating the connection.

The documentation on the generic jdbc driver is a little more sparse than preferable.

The important thing to make sure is correct is the connection string which we know from the back end screenshot you provided should be:

jdbc:teradata://192.168.1.7/DATABASE=DBC,DBS_PORT=1025

Then you select the teradata driver you uploaded as bellow:

/8DpUIxRsm0yF0AAAAASUVORK5CYII=

It seems that Yellowfin is incorrectly creating the connection string when selecting Teradata as your database type. By selecting Generic JDBC data source you are able to specify the connection string which, hopefully, solves the problem we are seeing here.

Please let me know if this helps.

Thanks,

Jared

photo
1

Hello Jared,

Thank you for the tip, using the Generic JDBC Data Source option with a Connection String, appeared to return a successful 'Test Connection'. (small note: my Teradata VM is setup with DHCP hence the different IP now below in my screenshot) .

If we consider this method the 'workaround', would you still plan on looking into the issue discovered when using the Teradata database type? Or would the Generic JDBC data source method be the ultimate solution to this issue?

Either way, now I can resume testing my environment with this newly created Teradata (Generic) JDBC data source and let the community know if I run into any further issues/incompatibility. Thanks again for your timely assistance with this, greatly appreciated!


/j8azDQ63TeqhwAAAABJRU5ErkJggg==

/c70PODIpYtQAAAABJRU5ErkJggg==

photo
1

Hello Ashwin,

Glad to see that this worked for you, It seems the issue that Yellowfin is building the teradata connection string incorrectly.

I will attempt to set up an instance to replicating this to raise this as defective behavior to our development team. I will also update you with my progress on this through this ticket.


Please let me know if you have any other questions or issues in regards to this.

Thanks,

Jared

photo
photo
1

Hello Ashwin,

I was able to replicate this behavior and have logged it as a defect and raised it to our development team.

image-2019-08-08-13-29-16-406

I will update this ticket when updates become available to me.

In the meantime please feel free to reach out with any issues and questions.

Thanks,

Jared