Materialized Views in PostgreSQL are not visible in Spotfire Information Services
When using Information Designer, the PostgreSQL Materialized Views are not seen, only the PostgreSQL tables. Postgres JDBC driver and data source template are in place and are working in general to retrieve data.
Materialized Views were introduced in PostgresSQL in 9.3, which means that PosgreSQL JDBC driver version 9.3 or newer (postgresql-9.4-1201.jdbc41.jar tested) will be needed to make them available to work with in Spotfire.
Once the updated JDBC driver is installed in Spotfire Server, in the data source template, add "<table-types>TABLE,VIEW,MATERIALIZED VIEW</table-types>".
Here is the PostgreSQL Data Source Template XML:
<jdbc-type-settings>
<type-name>postgreSQL</type-name>
<driver>org.postgresql.Driver</driver>
<connection-url-pattern>jdbc:postgresql://host:port/database</connection-url-pattern>
<supports-catalogs>true</supports-catalogs>
<supports-schemas>true</supports-schemas>
<table-types>TABLE,VIEW,MATERIALIZED VIEW</table-types>
<use-ansii-style-outer-join>true</use-ansii-style-outer-join>
<metadata-provider>com.spotfire.ws.im.ds.sql.BasicJDBCMetadataProvider</metadata-provider>
<supports-procedures>true</supports-procedures>
<sql-runtime>com.spotfire.ws.im.ds.sql.postgresql.PostgresSQLRuntime</sql-runtime>
</jdbc-type-settings>
To modify the PostgreSQL data source template, open the Spotfire Server Configuration Tool, and go to Configuration > Data Source Templates. Once the template is modified, save the configuration to the database and restart the Spotfire Server service.
Any new data sources created in the Information Designer will now use this template. For any already existing data sources in the library, those must be edited and re-saved for the template changes to be reflected.
Comments
0 comments
Article is closed for comments.