Questo documento illustra a ricercatori, data scientist e business analyst i processi e le considerazioni per analizzare i dati delle risorse di Fast Healthcare Interoperability (FHIR) in BigQuery.
In particolare, questo documento è incentrato sui dati delle risorse dei pazienti esportati dall'archivio FHIR nell'API Cloud Healthcare. Questo documento descrive inoltre una serie di query che mostrano come funzionano i dati di schema FHIR in un formato relazionale e ti mostra come accedere a queste query per riutilizzarle tramite le viste.
Utilizzo di BigQuery per l'analisi dei dati FHIR
L'API specifica per FHIR dell'API Cloud Healthcare è progettata per l'interazione transazionale in tempo reale con i dati FHIR a livello di una singola risorsa FHIR o di una raccolta di risorse FHIR. Tuttavia, l'API FHIR non è progettata per i casi d'uso di analisi. Per questi casi d'uso, ti consigliamo di esportare i dati dall'API FHIR a BigQuery. BigQuery è un data warehouse serverless e scalabile che consente di analizzare grandi quantità di dati in modo retrospettivo o prospettico.
Inoltre, BigQuery è conforme all'ANSI:2011 SQL, che rende i dati accessibili a data scientist e analisti aziendali tramite strumenti che generalmente utilizzano, come Tableau, Looker o Vertex AI Workbench.
In alcune applicazioni, come Vertex AI Workbench, hai accesso tramite client integrati, come la libreria client di Python per BigQuery. In questi casi, i dati restituiti all'applicazione sono disponibili tramite strutture di dati di linguaggio integrate.
Accesso a BigQuery
Puoi accedere a BigQuery tramite l'interfaccia utente web di BigQuery nella console Google Cloud, oltre che con i seguenti strumenti:
- Lo strumento a riga di comando di BigQuery
- L'API REST o le librerie client di BigQuery
- Driver ODBC e JDBC
Utilizzando questi strumenti, puoi integrare BigQuery in quasi tutte le applicazioni.
utilizzo della struttura di dati FHIR
La struttura di dati FHIR standard 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.
Array e strutture sono indicati anche come tipi di dati complessi nei database relazionali. La struttura di dati standard FHIR integrata funziona bene come file XML o JSON in un sistema orientato ai documenti, ma la struttura può essere difficile da utilizzare quando viene tradotta in database relazionali.
Il seguente screenshot mostra una vista parziale di un tipo di dati FHIR patient resource
che illustra la natura complessa della struttura di dati standard FHIR integrata.
Lo screenshot precedente mostra i componenti principali di un tipo di dati patient
resource
FHIR. 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 comprendono il tipo di dati patient
resource
. Ognuna di queste righe può essere registrata più volte, come
un array di strutture.
Utilizzo di array e strutture
BigQuery supporta array e STRUCT
tipi di dati, strutture di dati ripetute e nidificate, così come sono rappresentati nelle risorse FHIR, il che rende possibile 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 creare array di tipi di dati semplici, come il tipo di dati INT64
, e tipi di dati complessi, come il tipo di dati STRUCT
. L'eccezione è il tipo di dati ARRAY
, perché gli array di array al momento non sono supportati. In BigQuery un array di strutture
viene visualizzato come record ripetibile.
Puoi specificare dati nidificati o dati nidificati e ripetuti nell'interfaccia utente di BigQuery o in un file di schema JSON. Per specificare le colonne nidificate o ripetute e nidificate, utilizza il tipo di dati RECORD (STRUCT)
.
L'API Cloud Healthcare supporta lo schema SQL su FHIR in BigQuery. Questo schema di analisi è lo schema predefinito nel metodo ExportResources()
ed è supportato dalla community FHIR.
BigQuery supporta i dati denormalizzati. Ciò significa che quando archivi i dati, anziché creare uno schema relazionale come uno a stella o a fiocco di neve, puoi denormalizzare i dati e utilizzare colonne nidificate e ripetute. Le colonne nidificate e ripetute mantengono le relazioni tra gli elementi di dati senza l'impatto sulle prestazioni di uno schema relazionale (normalizzato).
Accesso ai tuoi dati tramite l'operatore UNNEST
Ogni risorsa FHIR nell'API FHIR viene esportata in BigQuery come una riga di dati. Un array o una struttura all'interno di qualsiasi riga possono essere paragonati a una tabella incorporata. Puoi accedere ai dati di quella "tabella" nella clausola SELECT
o nella clausola WHERE
della query suddividendo l'array o la struttura utilizzando l'operatore UNNEST
.
L'operatore UNNEST
accetta un array e restituisce una tabella con una singola riga per
ogni elemento dell'array. Per saperne di più, 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 WITH OFFSET
facoltativa. 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 unisce 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, che è la tabella che precede direttamente la chiamata a UNNEST
nella clausola FROM
. Per ogni riga nella tabella di origine, l'operazione UNNEST
unisce l'array da quella riga a 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.
Analizzare lo schema con le query
Per eseguire query sui dati FHIR in BigQuery, è importante comprendere lo schema creato tramite il processo di esportazione. BigQuery consente di esaminare la struttura delle colonne di ogni tabella nel set di dati tramite la funzionalità INFORMATION_SCHEMA
, una serie di viste che mostrano i metadati. La parte restante di questo documento fa riferimento allo schema SQL su 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 nello schema SQL su FHIR. La query fa riferimento al set di dati pubblico Synthea Generati dati sintetici in FHIR, che ospita oltre 1 milione di record di pazienti sintetici generati 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) in una tabella. 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
.
Utilizzo della risorsa FHIR paziente in BigQuery
Il numero di cartelle cliniche del paziente (MRN), un'informazione critica archiviata nei tuoi dati FHIR, viene utilizzato nei sistemi di dati clinici e operativi di un'organizzazione per tutti i pazienti. Qualsiasi metodo di accesso ai dati per un singolo paziente o un insieme di pazienti deve filtrare o restituire l'MRN, oppure eseguire entrambe le operazioni.
La seguente query di esempio restituisce l'identificatore del server FHIR interno alla risorsa del paziente stessa, inclusi l'MRN e la data di nascita per tutti i pazienti. È incluso anche il filtro per eseguire una query su un MRN specifico, ma è commentato in questo esempio.
In questa query, annulli due volte la nidificazione del tipo di dati complessi identifier
. Puoi anche utilizzare
le operazioni CROSS JOIN
correlate per unire dati non nidificati alla relativa tabella di origine.
La tabella bigquery-public-data.fhir_synthea.patient
nella query è stata creata utilizzando SQL sulla versione dello schema FHIR dell'esportazione FHIR in 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:
Nella query precedente, il set di valori identifier.type.coding.code
è il
set di valori identifier
FHIR
che elenca i tipi di dati di identità disponibili, ad esempio MRN (tipo di dati sull'identità MR
), patente di guida (tipo di dati sull'identità DL
) e numero di passaporto
(PPN
tipo di dati di identità). Poiché l'insieme di valori identifier.type.coding
è un
array, è possibile elencare un numero qualsiasi di identificatori per un paziente. Ma in questo caso, vuoi il MRN (MR
tipo di dati di identità).
Unione della tabella dei pazienti con altre tabelle
Utilizzando la query sulla tabella dei pazienti, puoi unire la tabella dei pazienti ad altre tabelle in questo set di dati, ad esempio la tabella delle condizioni. Nella tabella delle condizioni 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:
Nella query precedente, riutilizzi il metodo UNNEST
per suddividere il campo code.coding
. Gli elementi di codice abatement.dateTime
e onset.dateTime
nell'istruzione SELECT
hanno un alias perché entrambi terminano con
dateTime
, il che comporterebbe nomi di colonna ambigui nell'output di un'istruzione
SELECT
. Quando selezioni il codice Hypertension
, devi anche dichiarare il sistema terminologico da cui proviene il codice, in questo caso il sistema terminologico clinico SNOMED CT.
Come passaggio finale, utilizzerai la chiave subject.patientid
per unire la tabella della condizione alla tabella dei pazienti. Questa chiave punta all'identificatore della risorsa paziente stessa all'interno del server FHIR.
Raggruppare le query
Nella seguente query di esempio, puoi utilizzare le query delle due sezioni precedenti e unirle usando la clausola 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:
Nella query di esempio precedente, la clausola WITH
consente di isolare le sottoquery
in segmenti specifici. Questo approccio migliora la leggibilità, che diventa
più importante man mano che la query aumenta. In questa query, isoli
la sottoquery per pazienti e patologie nei relativi segmenti WITH
e
li unisci al segmento SELECT
principale.
Puoi anche applicare i calcoli ai dati non elaborati. Il seguente codice campione, un'istruzione SELECT
, mostra come calcolare l'età del paziente all'inizio 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
. Innanzitutto,
seleziona il componente data della stringa con il
valore SUBSTR
. Quindi puoi convertire la stringa in un tipo di dati DATE
utilizzando la sintassi
CAST
. Puoi anche convertire il campo patient.birthDate
nel campo DATE
.
Infine, puoi calcolare la differenza tra le due date utilizzando la funzione DATE_DIFF
.
Passaggi successivi
- Analizza i dati clinici utilizzando BigQuery e AI Platform Notebooks.
- Visualizzazione dei dati di BigQuery in un blocco note Jupyter.
- Sicurezza dell'API Cloud Healthcare.
- Controllo dell'accesso a BigQuery.
- Soluzioni per sanità e scienze biologiche su Google Cloud Marketplace.
- Esplora le architetture di riferimento, i diagrammi e le best practice su Google Cloud. Visita il nostro Cloud Architecture Center.