Stream: hapi
Topic: Blob data within HAPI DB
Rik Smithies (Apr 23 2019 at 18:08):
I can see records in my HAPI database in HFJ_RESOURCE, with I think the actual data itself in HFJ_RES_VER.RES_TEXT. This is a "LONGBLOB" field. Is that the right place to extract this via SQL and how is it possible to convert the blob back to text?
Rob Hausam (Apr 23 2019 at 19:55):
I'm not sure how you would (or if you can) do that in Derby
as Kevin suggested, moving to Mysql or Postgresql probably is something you will want to consider (if you haven't)
Postgresql works well for me
Rik Smithies (Apr 23 2019 at 19:59):
Hi Rob, sorry, should have pointed out - this is HAPI on MySQL in fact. I can see all the tables in a GUI viewer, but just cannot extract the actual resource data as text. It's encoded somehow. What can you see in HFJ_RES_VER.RES_TEXT?
Rik Smithies (Apr 24 2019 at 00:03):
I've now tried Postgres. This shows the same field as a oid datatype. Which I assume is a link to the actual blob of data. But again I cannot find a way to access the actual resource data via SQL.
Rob Hausam (Apr 24 2019 at 04:13):
I don't know the answer offhand, but maybe I can take a look and see if I can find something on it
Kevin Mayfield (Apr 24 2019 at 08:02):
if it helps, the java code is:
public static String decodeResource(byte[] theResourceBytes, ResourceEncodingEnum theResourceEncoding) {
String resourceText = null;
switch (theResourceEncoding) {
case JSON:
resourceText = new String(theResourceBytes, Charsets.UTF_8);
break;
case JSONC:
resourceText = GZipUtil.decompress(theResourceBytes);
break;
case DEL:
break;
}
return resourceText;
}
Kevin Mayfield (Apr 24 2019 at 08:11):
On SQL Server 2017 if the encoding is JSONC. The following should work:
SELECT TOP 1000 [PID]
,Convert(varchar(max),DECOMPRESS(RES_TEXT))
FROM [dbo].[HFJ_RES_VER]
Rik Smithies (Apr 24 2019 at 10:21):
thanks Kevin. What I am looking for is a Postgres way to do that SQL statement. I have tried lots of variations of decode, case and lo_get functions but have not yet managed to get a plain text version of the resource. But now I know it might be gzip and not just base64 or something, that may help my searching.
Rik Smithies (Apr 24 2019 at 10:23):
I thing the format in Postgres might be "bytea" but I don't know how to convert that to plain text.
Patrick Werner (Apr 24 2019 at 11:11):
Rik, the decoding is done here: https://github.com/jamesagnew/hapi-fhir/blob/706af5d5794e117159b635614c72bcb8799df492/hapi-fhir-jpaserver-base/src/main/java/ca/uhn/fhir/jpa/dao/BaseHapiFhirDao.java#L1142
Patrick Werner (Apr 24 2019 at 11:11):
The loading from the DB (via JPA) here: https://github.com/jamesagnew/hapi-fhir/blob/c8f2e4bbc94538c582400f002d536d85f81ca011/hapi-fhir-jpaserver-base/src/main/java/ca/uhn/fhir/jpa/dao/BaseHapiFhirResourceDao.java#L898
Rik Smithies (Apr 24 2019 at 12:00):
Thanks Patrick, I will have a look at that. Looks like beautiful code! I am really looking for a non-code based way to do this, via SQL, but this may help work that out. Use case is an SQL enabled but non-FHIR enabled system needs to read data.
Kevin Mayfield (Apr 24 2019 at 12:48):
Most of the out the box FHIR servers store data in a similar way. When I was at NHS Digital we went a more traditional SQL focused route but that meant we had less coverage of FHIR. https://github.com/nhsconnect/careconnect-reference-implementation Its not that hard to extend but it has to be coded.
Rik Smithies (Apr 24 2019 at 12:59):
If there is no easy SQL to get the data directly we will probably implement a workaround shim that gets the data via REST and feeds it into the SQL based product some other way. Or perhaps using that JPA routine from the source.
Rik Smithies (Sep 26 2019 at 19:16):
Coming back to an old thread. HAPI appears to allow the data to be stored in RES_TEXT field as JSON or JSONC (which is gzip encoded). At least the decodeResource function (shown above) can retrieve those two formats. Is there a server config option to store data in unencoded json?
James Agnew (Sep 26 2019 at 20:30):
There isn't currently, although I do think there should be.
Last updated: Apr 12 2022 at 19:14 UTC
