You are not logged in.
Pages: 1
I am trying to add snowflake as a datasource. I got my groovy file set up and executed and can get it to show up, but whenever I restart the tomcat server, I end up getting a cypher error message. "Could not decrypt ciphertext".
Here is my groovy text.
package databasehelper;
import net.datenwerke.rs.scripting.service.scripting.scriptservices.GlobalsWrapper;
import net.datenwerke.rs.base.service.dbhelper.DatabaseHelper
import net.datenwerke.rs.base.service.dbhelper.hooks.DatabaseHelperProviderHook
import net.datenwerke.rs.base.service.dbhelper.queries.LimitQuery
import net.datenwerke.rs.base.service.dbhelper.queries.OffsetQuery
import net.datenwerke.rs.base.service.dbhelper.queries.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder
class Snowflake extends DatabaseHelper {
public static final String DB_NAME = "Snowflake";
public static final String DB_DRIVER = "net.snowflake.client.jdbc.SnowflakeDriver";
public static final String DB_DESCRIPTOR = "DBHelper_Snowflake";
@Override
public String getDescriptor() {
return DB_DESCRIPTOR;
}
@Override
public String getDriver() {
return DB_DRIVER;
}
@Override
public String getName() {
return DB_NAME;
}
@Override
public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
return new LimitQuery(nestedQuery, queryBuilder){
@Override
public void appendToBuffer(StringBuffer buf) {
buf.append("SELECT ");
buf.append(" * FROM (");
nestedQuery.appendToBuffer(buf);
buf.append(") limitQry LIMIT ");
buf.append(queryBuilder.getLimit());
}
}
}
@Override
public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
return new OffsetQuery(nestedQuery, queryBuilder, columnNamingService){
@Override
public void appendToBuffer(StringBuffer buf) {
buf.append("SELECT ");
buf.append(" * FROM (");
nestedQuery.appendToBuffer(buf);
buf.append(") limitQry LIMIT ");
buf.append(queryBuilder.getLimit());
buf.append(" OFFSET ");
buf.append(queryBuilder.getOffset());
}
}
}
}
def HOOK_NAME = "DATASOURCE_HELPER_SNOWFLAKE"
def callback = [
provideDatabaseHelpers : {
return Collections.singletonList(new Snowflake());
}
] as DatabaseHelperProviderHook;
GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, DatabaseHelperProviderHook.class, callback)
Where should I start looking to diagnose?
Offline
Hello abraun,
to better understand the problem, could you please let me know which ReportServer version you are using and whether there is an error stack?
Does the datasource work otherwise?
If you delete the script, does ReportServer start without errors?
Best regards,
Alexander
Offline
Hi abraun,
if this works, we may consider adding Snowflake support out-of-the-box, so the topic is a very interesting one.
Regards,
Eduardo
Offline
I got this to work.
When trying to figure out how to get this to work, I put the snowflake jdbc driver in the apache tomcat/lib folder in addition to the apache-tomcat/webapps/reportserver/web-inf/lib.
My best guess is that it was trying to load the driver and didn't know what to do with it. Once I removed it, everything started working again.
@eduardo, my groovy script above worked to query the database if that is a config you would like to add for the future.
Here is my connection string jdbc:snowflake://<my snowflake instance>.snowflakecomputing.com/?warehouse=<my_wh>&db=<my_db>&role=<my_role>
@alexander, thanks for the suggestion that lead me to try re-starting the application without the file to see I had an issue before.
In general, I think adding to the documentation where to add the jdbc driver would be helpful.
Thanks,
Alex
Offline
Another question. In the documentation, it mentions adding the script to the onstartup.d folder in bin. I didn't have the folder so I created it, then I tried restarting the services, but it didn't run the script. How do I get it run on startup without that folder?
Thanks,
Alex
Offline
Hi Alex,
thanks for the information.
Are you completely sure that the folder is named onstartup.d (no spaces) and that it is located in the correct location? Also pls check what is your configured folder here: https://reportserver.net/en/guides/conf … xtensions/
<rs>fileserver/bin/onstartup.groovy</rs>
Maybe create a simple script that just prints something into the logs, and try with this script first.
E.g. println "this is a test line"
Regards,
Eduardo
Offline
Eduardo,
I have confirmed that my folder is onstartup.d and it is in the fileserver/bin/onstartup.d
Looks like the scripting file had it set to <rs>fileserver/bin/onstartup.d</rs> instead of <rs>fileserver/bin/onstartup.groovy</rs>
Should I also see the onstartup.groovy script in the bin folder as well? I don't have that script.
Thanks,
Alex
Offline
Hi Alex,
the correct settings are:
<startup>
<login>fileserver/bin/onlogin.d</login>
<rs>fileserver/bin/onstartup.d</rs>
</startup>We will fix this in the documentation.
If you have this, the onstartup.d should work. Did you try with a simple script?
Regards,
Eduardo
Offline
I got this to work.
When trying to figure out how to get this to work, I put the snowflake jdbc driver in the apache tomcat/lib folder in addition to the apache-tomcat/webapps/reportserver/web-inf/lib.
My best guess is that it was trying to load the driver and didn't know what to do with it. Once I removed it, everything started working again.
@eduardo, my groovy script above worked to query the database if that is a config you would like to add for the future.
Here is my connection string jdbc:snowflake://<my snowflake instance>.snowflakecomputing.com/?warehouse=<my_wh>&db=<my_db>&role=<my_role>@alexander, thanks for the suggestion that lead me to try re-starting the application without the file to see I had an issue before.
In general, I think adding to the documentation where to add the jdbc driver would be helpful.
Thanks,
Alex
Hi Alex,
does this configuration allow you to create a dynamic list on your Snowflake datasource? Could you please test the pagination buttons (< and >) and (<<, >>) to see if they work as well?
Regards,
Eduardo
Offline
I tried a simple script, but didn't see it show up in any of the logs, I checked reportserver, reportserverenterpisetomcat-stderr, and reportserverenterprisetomcat-stdout.
Here is my full scripting.config, could the restrict cause the issue? does it need to be fileserver/bin maybe?
<configuration>
<scripting>
<enable>true</enable>
<restrict>
<location>bin</location>
</restrict>
<startup>
<login>fileserver/bin/onlogin.d</login>
<rs>fileserver/bin/onstartup.d</rs>
</startup>
</scripting>
</configuration>
Offline
I tested this and it worked.
One issue with snowflake that I have found to be aware of is how columns are defined. Snowflake can be kind of picky.
If its standard text such as 'column', it can be addressed as
select column from table
But if you have any special capitals or special characters. if you name the column as Column or Column# or ColumnName, it treats it as a literal text name and needs to be selected with double quotes.
select "Column", "Column#", "ColumnName" from table
These are 3 valid ways to select a "standard" column in snowflake.
select column, COLUMN, "COLUMN" from table
I don't necessarily know how to address this, other than someone just has to know the nuances of snowflake if they use it.
Hi Alex,
does this configuration allow you to create a dynamic list on your Snowflake datasource? Could you please test the pagination buttons (< and >) and (<<, >>) to see if they work as well?
Regards,
Eduardo
Offline
I tried a simple script, but didn't see it show up in any of the logs, I checked reportserver, reportserverenterpisetomcat-stderr, and reportserverenterprisetomcat-stdout.
just to make sure: do you see any logs when you execute the script manually?
Regards,
Eduardo
Offline
Pages: 1