Spotfire Server: How to read compressed data in LIB_DATA.DATA table column

Description

Spotfire Server's database LIB_DATA.DATA column, previously readable as plain text, was changed to a compressed BLOB/VARBINARY format after upgrading to Spotfire 14.0 LTS. This article provides a query that can be used to decompress this data column, so that it can be used in external queries or reports created in Spotfire.

Solution

The following SQL query can be used to decompress and extract data in LIB_DATA.DATA column in SQL Server database:

SELECT libitems.ITEM_ID,
       libitems.TITLE, 
       CONVERT(varchar(max), DECOMPRESS(data.DATA), 0) as decompressed_data
FROM [LIB_ITEMS] libitems
     JOIN [LIB_DATA] data ON libitems.DATA_ID = data.DATA_ID
WHERE libitems.ITEM_TYPE = '<local item type>' 
     AND data.DATA IS NOT NULL;

Make sure that the "local item type" value corresponds to the LIB_ITEMS.ITEM_TYPE in your environment. This could be item type of DXP files, Information Links or other data items.
While this query is SQL Server specific, the data in LIB_DATA.DATA column is compressed using gzip format in all supported Spotfire Server databases, so it should be possible to decompress it using similar method in those other databases.