Spotfire: Information Services Data Source template for BLOB data in Snowflake with OAuth2 authentication

Details:

Resolution:

If one is reading BLOB data from Snowflake, like geospatial geometry data, then some additional configuration is needed in the data source template, and a custom jar needs to be deployed to the Spotfire Server, for example, SpotfireSnowflakeSQLRuntime.jar.

The file SpotfireSnowflakeSQLRuntime.jar is a custom extension for Spotfire Server. It is specifically designed to allow Spotfire Information Services to successfully read BLOB (Binary Large Object) data - such as geospatial geometry data - from a Snowflake database.

The Snowflake data source template needs to explicitly reference the custom SQLRuntime. This is done by adding the following property inside the Snowflake data source template configuration: 

<sql-runtime>com.tibco.spotfireps.server.is.SnowflakeSQLRuntime</sql-runtime>

The complete Snowflake data source template featuring both OAuth2 and BLOB data support is shown below:

<jdbc-type-settings>
  <type-name>snowflake_oauth2</type-name>
  <driver>net.snowflake.client.jdbc.SnowflakeDriver</driver>
  <connection-url-pattern>jdbc:snowflake://&lt;account_identifier&gt;.snowflakecomputing.com</connection-url-pattern>
  <connection-properties>
    <connection-property>
      <key>authenticator</key>
      <value>oauth</value>
    </connection-property>
    <connection-property>
      <key>application</key>
      <value>Spotfire_InformationServices</value>
    </connection-property>
  </connection-properties>
  <credentials-provider-class>com.spotfire.ws.im.ds.sql.TokenCredentialsProvider</credentials-provider-class>
  <credentials-provider-init-params>
    <parameter>
      <key>issuer</key>
      <value>&lt;issuer&gt;</value>
    </parameter>
    <parameter>
      <key>scope</key>
      <value></value>
    </parameter> 
    <parameter>
      <key>access_token_property_name</key>
      <value>token</value>
    </parameter>
  </credentials-provider-init-params>
  <supports-catalogs>true</supports-catalogs>
  <supports-procedures>false</supports-procedures>
  <supports-schemas>true</supports-schemas>
  <fetch-size>10000</fetch-size>
  <batch-size>100</batch-size>
  <use-ansii-style-outer-join>true</use-ansii-style-outer-join>
  <sql-runtime>com.tibco.spotfireps.server.is.SnowflakeSQLRuntime</sql-runtime>
</jdbc-type-settings>

Important: Additionally, make sure to place the SpotfireSnowflakeSQLRuntime.jar in the tomcat/custom-ext-informationservices directory on all Spotfire Servers. Once done, restart the Spotfire server service. 

To download the SpotfireSnowflakeSQLRuntime.jar, please refer to Spotfire® JDBC Data Access Connectivity Details. (The jar file is attached to this page in a zip file - SpotfireSnowflakeSQLRuntime.zip)