analisi dei dati FHIR in BigQuery

Last reviewed 2024-02-29 UTC

Questo documento spiega a ricercatori, data scientist e analisti aziendali i processi e le considerazioni per analizzare FHIR (Fast Healthcare Interoperability Resources) dati in BigQuery.

Nello specifico, questo documento si concentra sui dati delle risorse per i pazienti esportate dal Negozio FHIR nel API Cloud Healthcare. Questo documento illustra anche una serie di query che dimostrano come FHIR funzionano in formato relazionale e ti mostrano come accedervi per riutilizzarle tramite le visualizzazioni.

Utilizzo di BigQuery per l'analisi dei dati FHIR

La API specifica FHIR dell'API Cloud Healthcare è progettata per servizi interazione con i dati FHIR a livello di una singola risorsa FHIR o un di risorse FHIR. Tuttavia, l'API FHIR non è progettata per i casi d'uso di analisi. Per questi casi d'uso, consigliamo esportazione dei dati dall'API FHIR a BigQuery. BigQuery è un data warehouse serverless e scalabile che ti consente analizzare grandi quantità di dati in modo retrospettivo o prospettico.

Inoltre, BigQuery è conforme ANSI:2011 SQL, rendendo i dati accessibili a data scientist e analisti aziendali tramite gli strumenti che usa abitualmente, come Tableau, Looker Vertex AI Workbench

In alcune applicazioni, come Vertex AI Workbench, puoi accedere tramite come client integrati Libreria client Python per BigQuery. In questi casi, i dati restituiti all'applicazione sono disponibili tramite le strutture di dati linguistiche integrate.

Accesso a BigQuery

Puoi accedere a BigQuery tramite l'UI web di BigQuery nella console Google Cloud e anche con i seguenti strumenti:

Utilizzando questi strumenti, puoi integrare BigQuery in quasi qualsiasi applicazione.

Utilizzo della struttura di dati FHIR

La struttura di dati standard FHIR integrata è complessa, con tipi di dati FHIR nidificati e incorporati in qualsiasi risorsa FHIR. Questi tipi di dati FHIR incorporabili sono indicati come tipi di dati complessi. Gli array e le strutture sono anche chiamati tipi di dati complessi o Microsoft SQL Server. La struttura dei dati standard FHIR integrata funziona bene File XML o JSON in un sistema orientato ai documenti, ma la struttura può essere difficili da utilizzare quando vengono tradotti in database relazionali.

Il seguente screenshot mostra una visualizzazione parziale di un FHIR patient resource tipo di dati che illustra la natura complessa dei dati standard FHIR integrati alla struttura del centro di costo.

Tipo di dati "patient resource" FHIR.

Lo screenshot precedente mostra i componenti principali di un tipo di dati FHIR patient resource. Ad esempio, la colonna Cardinalità (indicata nella tabella come Card) mostra diversi elementi che possono avere zero, una o più voci. La colonna Tipo mostra i tipi di dati Identificatore, HumanName e Address, che sono esempi di tipi di dati complessi che compongono il tipo di dati patient resource. Ognuna di queste righe può essere registrata più volte, come un array di strutture.

Utilizzare array e strutture

BigQuery supporta array e STRUCT tipi di dati: nidificati, strutture di dati ripetute, rappresentate nelle risorse FHIR, il che rende la conversione dei dati da FHIR a BigQuery.

In BigQuery, un array è un elenco ordinato composto da zero o più valori dello stesso tipo di dati. Puoi costruire array di dati semplici di grandi dimensioni, come il tipo di dati INT64, e tipi di dati complessi, come Tipo di dati STRUCT. L'eccezione è il tipo di dati ARRAY, perché gli array di array non sono attualmente supportati. In BigQuery, un array di strutture viene visualizzato come un record ripetibile.

Puoi specificare dati nidificati, o dati nidificati e ripetuti, nel nella UI di BigQuery o in un file di schema JSON. Per specificare colonne nidificate o colonne nidificate e ripetute, utilizza il tipo di dati RECORD (STRUCT).

L'API Cloud Healthcare supporta SQL su schema FHIR in BigQuery. Questo schema di analisi è lo schema predefinito nella ExportResources() ed è supportato dalla community FHIR.

BigQuery supporta i dati denormalizzati. Ciò significa che quando memorizzi i dati, anziché creare uno schema relazionale come uno schema a stella o a fiocco di neve, puoi denormalizzarli e utilizzare colonne nidificate e ripetute. Le colonne nidificate e ripetute mantengono le relazioni tra i dati elementi senza l'impatto in termini di prestazioni della conservazione di un'architettura relazionale (normalizzata) .

Accedere ai dati tramite l'operatore UNNEST

Ogni risorsa FHIR nell'API FHIR viene esportata in BigQuery come una riga di dati. Puoi considerare un array o una struttura all'interno di una riga come una tabella incorporata. Puoi accedere ai dati di questa "tabella" in SELECT o la clausola WHERE della query appiattimento dell'array o della struttura mediante l'operatore UNNEST. L'operatore UNNEST prende un array e restituisce una tabella con una singola riga per ogni elemento dell'array. Per ulteriori informazioni, consulta Utilizzo degli array in SQL standard.

L'operazione UNNEST non conserva l'ordine degli elementi dell'array, ma puoi riordinare la tabella utilizzando la clausola facoltativa WITH OFFSET. Questo restituisce una colonna aggiuntiva con la clausola OFFSET per ogni elemento array. Puoi quindi utilizzare la clausola ORDER BY per ordinare le righe in base all'offset.

Quando unisci i dati non nidificati, BigQuery utilizza un'operazione CROSS JOIN correlata che fa riferimento alla colonna degli array di ogni elemento dell'array con la tabella di origine, ovvero la tabella che precede direttamente la chiamata a UNNEST nella clausola FROM. Per ogni riga della tabella di origine, l'operazione UNNEST appiattisce l'array della riga in un insieme di righe contenenti gli elementi dell'array. L'operazione CROSS JOIN correlata unisce questo nuovo insieme di righe alla singola riga della tabella di origine.

Analisi dello schema con query

Per eseguire query sui dati FHIR in BigQuery, è importante comprendere il schema creato mediante il processo di esportazione. BigQuery ti consente di esaminare la struttura delle colonne di ogni tabella del set di dati tramite la funzionalità INFORMATION_SCHEMA, una serie di viste che mostrano i metadati. Il resto di questo documento fa riferimento allo schema SQL on FHIR, progettato per essere accessibile per il recupero dei dati.

La seguente query di esempio esplora i dettagli delle colonne per la tabella dei pazienti in il SQL su FHIR schema. La query fa riferimento Dati sintetici generati sintetici in FHIR che ospita oltre 1 milione di cartelle cliniche sintetiche, generate nei formati Synthea e FHIR.

Quando esegui una query sulla vista INFORMATION_SCHEMA.COLUMNS, i risultati della query contengono una riga per ogni colonna (campo) di una tabella. La La seguente query restituisce tutte le colonne nella tabella dei pazienti:

SELECT *
FROM `bigquery-public-data.fhir_synthea.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name='patient'

Il seguente screenshot del risultato della query mostra il tipo di dati identifier. e l'array all'interno del tipo di dati che contiene i tipi di dati STRUCT.

Tipo di dati "Identifier" e l'array all'interno del tipo di dati che contiene il tipo di dati "STRUCT".

Utilizzo della risorsa paziente FHIR in BigQuery

Il numero del record sanitario del paziente (MRN), un'informazione fondamentale memorizzata nei dati FHIR, viene utilizzato in tutti i sistemi di dati clinici e operativi di un'organizzazione per tutti i pazienti. Qualsiasi metodo di accesso ai dati per un singolo individuo un paziente o un insieme di pazienti devono filtrare o restituire l'MRN o eseguire entrambe le operazioni.

La seguente query di esempio restituisce l'identificatore del server FHIR interno al per il paziente stesso, inclusi l'MRN e la data di nascita di tutti i pazienti. È incluso anche il filtro per eseguire la query su un MRN specifico, ma viene commentato in questo esempio.

In questa query, estrai due volte il tipo di dati complessi identifier. Utilizzi anche le operazioni CROSS JOIN correlate per unire i dati non nidificati con la relativa tabella di origine. La tabella bigquery-public-data.fhir_synthea.patient nella query è stata creata utilizzando la versione dello schema SQL su FHIR dell'esportazione da FHIR a BigQuery.

SELECT id, i.value as MRN, birthDate
FROM `bigquery-public-data.fhir_synthea.patient`
#This is a correlated cross join
,UNNEST(identifier) i
,UNNEST(i.type.coding) it
WHERE
# identifier.type.coding.code
it.code = "MR"
#uncomment to get data for one patient, this MRN exists
#AND i.value = "a55c8c2f-474b-4dbd-9c84-effe5c0aed5b"

L'output è simile al seguente:

Output che mostra ID, numero di cartella clinica e data di nascita per il tipo di identificatore impostato su MR

Nella query precedente, il set di valori identifier.type.coding.code è il set di valori FHIR identifier che enumera i tipi di dati di identità disponibili, come il codice identificativo del paziente (MR tipo di dato di identità), la patente di guida (DL tipo di dato di identità) e il numero di passaporto (PPN tipo di dato di identità). Poiché il valore impostato per identifier.type.coding è un array, è possibile elencare un numero qualsiasi di identificatori per un paziente. Ma in questo nel caso in cui tu voglia utilizzare l'MRN (tipo di dati di identità MR).

Unione della tabella dei pazienti con altre tabelle

Sulla base della query della tabella dei pazienti, puoi unire la tabella dei pazienti ad altre tabelle di questo set di dati, ad esempio la tabella delle condizioni. La tabella delle condizioni è dove vengono registrate le diagnosi dei pazienti.

La seguente query di esempio recupera tutte le voci relative alla condizione medica dell'ipertensione.

SELECT abatement.dateTime as abatement_dateTime, assertedDate, category, clinicalStatus, code, onset.dateTime as onset_dateTime, subject.patientid
FROM
`bigquery-public-data.fhir_synthea.condition`
,UNNEST(code.coding) as code
WHERE
code.system = 'http://snomed.info/sct'
#snomed code for Hypertension
AND code.code = '38341003'

L'output è simile al seguente:

Output che mostra i record relativi alla condizione medica di ipertensione

Nella query precedente, riutilizzi il metodo UNNEST per appiattire il code.coding campo. Il codice abatement.dateTime e onset.dateTime nell'istruzione SELECT hanno un alias perché entrambi terminano con dateTime, il che causerebbe nomi di colonna ambigui nell'output di un Informativa SELECT. Quando selezioni il codice Hypertension, devi anche dichiarare il sistema di terminologia da cui proviene il codice, in questo caso il sistema di terminologia clinica SNOMED CT.

Come passaggio finale, utilizzerai la chiave subject.patientid per unire la condizione con quella dei pazienti. Questa chiave punta all'identificatore del paziente all'interno del server FHIR.

Combinazione delle query

Nella seguente query di esempio, utilizzerai le query delle due e unisciti a loro utilizzando WITH , mentre esegui alcuni semplici calcoli.

WITH patient AS (
SELECT id as patientid, i.value as MRN, birthDate
FROM
`bigquery-public-data.fhir_synthea.patient`
#This is a correlated cross join
,UNNEST(identifier) i
,UNNEST(i.type.coding) it
WHERE
# identifier.type.coding.code
it.code = "MR"
#uncomment to get data for one patient, this MRN exists
#AND i.value = "a55c8c2f-474b-4dbd-9c84-effe5c0aed5b"
),
condition AS (
SELECT abatement.dateTime as abatement_dateTime, assertedDate, category, clinicalStatus, code, onset.dateTime as onset_dateTime, subject.patientid
FROM
`bigquery-public-data.fhir_synthea.condition`
,UNNEST(code.coding) as code
WHERE
code.system = 'http://snomed.info/sct'
#snomed code for Hypertension
AND code.code = '38341003'
)
SELECT patient.patientid, patient.MRN, patient.birthDate as birthDate_string,
#current patient age. now - birthdate
CAST(DATE_DIFF(CURRENT_DATE(),CAST(patient.birthDate AS DATE),MONTH)/12 AS INT) as patient_current_age_years,
CAST(DATE_DIFF(CURRENT_DATE(),CAST(patient.birthDate AS DATE),MONTH) AS INT) as patient_current_age_months,
CAST(DATE_DIFF(CURRENT_DATE(),CAST(patient.birthDate AS DATE),DAY) AS INT) as patient_current_age_days,
#age at onset. onset date - birthdate
DATE_DIFF(CAST(SUBSTR(condition.onset_dateTime,1,10) AS DATE),CAST(patient.birthDate AS DATE),YEAR)as patient_age_at_onset,
condition.onset_dateTime, condition.code.code, condition.code.display, condition.code.system
FROM patient JOIN condition
ON patient.patientid = condition.patientid

L'output è simile al seguente:

Output che mostra l'ID, il numero di riferimento del paziente e la data di nascita per la condizione medica di ipertensione.

Nella query di esempio precedente, la clausola WITH consente di isolare le sottoquery nei rispettivi segmenti definiti. Questo approccio può contribuire alla leggibilità, che diventa più importante man mano che la query aumenta di dimensioni. In questa query, isolare la sottoquery per pazienti e patologie nei propri segmenti WITH; e e unisciti a loro nel segmento SELECT principale.

Puoi anche applicare calcoli a dati non elaborati. Il seguente codice di esempio, un statement SELECT, mostra come calcolare l'età del paziente all'insorgenza della malattia.

DATE_DIFF(CAST(SUBSTR(condition.onset_dateTime,1,10) AS DATE),CAST(patient.birthDate AS DATE),YEAR)as patient_age_at_onset

Come indicato nell'esempio di codice precedente, puoi eseguire una serie di operazioni sulla stringa dateTime fornita, condition.onset_dateTime. Per prima cosa, selezioni il componente data della stringa con SUBSTR valore. Poi converti la stringa in un tipo di dati DATE utilizzando la sintassi CAST. Puoi anche convertire il campo patient.birthDate nel campo DATE. Infine, calcola la differenza tra le due date utilizzando la funzione DATE_DIFF.

Passaggi successivi