Retrieve BEWSD data from the local database of HD4DP v2

Last updated: 2024-02-15 15:36

Warning

The person with the login for the local database of "HD4DP v2 local" has access to all the data stored in the database. This means that the personal data of the patients will be VISIBLE to that user.

Requirements

URL Local DWH Database: postgresql://<server_ip>:5432/localdwh. If this is not the case, the IT department hosting HD4DP v2 needs to open the port and allow traffic to this port.

URL NIPPIN Database: postgresql://<server_ip>:5432/nippin

Username/Password: The service desk of healthdata.be will forward, via a secure link, the username and password.

Client: Download one of the clients that support PostgreSQL . A list is available here.

Subnet: Provide us with the subnet from where you will contact the database. The database only accepts incoming traffic of known subnets.

Granted privileges

databaseuserprivileges
localdwhdpuserCONNECT/local_dwhmessage:SELECT/local_dwhmessage_key_value:SELECT/local_dwhmessage_key_value_plus:SELECT
nippindpuserCONNECT/nippin_message:SELECT
Privileges table

"data_collection_name" in local database

  • Belgian Early Warning System for Drugs = BEWSD

Query examples

With the "data_collection_name" and the following information, you will be able to link multiple tables with each other.

  • local_dwhmessage_key_value: Key value table with more information about the registration
  • msg_document_id: document id of your message located in local_dwhmessage table
  • document_id: document id of your registration
  • local_dwhmessage: table where you can find all the registrations
  • local_dwhmessage_key_value_plus: Extra table to define attribute type and value of a key value
  • key_value_id: Key value id linked to the id of the local_dwh_message_key_value
  • local_dwhmessage_key_value:

"local_dwhmessage_key_value" column "msg_document_id" refer to the "document_id" of "local_dwhmessage".

"local_dwhmessage_key_value_plus" column "key_value_id" refer to the id of "local_dwhmessage_key_value".

Query 1: Get all registrations from the last 15 days.

SELECT * from local_dwhmessage WHERE data_collection_name = 'add data_collection_name' and created_on > current_date - interval '15' day;

Query 2: Get all registrations and key value.

SELECT * from local_dwhmessage as ldm left join local_dwhmessage_key_value as ldmkv on ldmkv.msg_document_id = ldm.document_id WHERE ldm.data_collection_name = 'add data_collection_name';

Query 3: Get all registrations, key value and key value plus from.

SELECT * from local_dwhmessage as ldm left join local_dwhmessage_key_value as ldmkv on ldmkv.msg_document_id = ldm.document_id left join local_dwhmessage_key_value_plus as ldmkvp on ldmkvp.key_value_id = ldmkv.id WHERE ldm.data_collection_name = 'add data_collection_name';