02_create_bq_tables.Rmd

---
title: "Create BigQuery Tables"
date: "`r Sys.Date()`"
output: html_document
---

## Summary

Queries to create a subset of synthetic patient data from [FHIR Synthea BigQuery Public Dataset](https://console.cloud.google.com/marketplace/product/mitre/synthea-fhir) for summurization by Vertex AI Generative AI Text. 

The output is a BigQuery dataset with 8 tables and 3 views. 

## List of Tables 

Below is a list of tables that will be created after runnning the queries within this document.

### Patients

1. `bigquery-public-data.fhir_synthea.patient` - public dataset
2. `fhir_synthea_patient_summary.vw_patient_narrow_flat` - view 
3. `fhir_synthea_patient_summary.patients` - table (output)

### Patient Registration

1. `bigquery-public-data.fhir_synthea.patient` - public dataset
2. `fhir_synthea_patient_summary.vw_patient_narrow_flat` - view
3. `fhir_synthea_patient_summary.patient_visit_reason` - table (input, generated and manually uploaded) 
3. `fhir_synthea_patient_summary.patient_registration` - table (output)

### Patient History

#### All patient history  

1. `bigquery-public-data.fhir_synthea.condition` - public dataset
2. `fhir_synthea_patient_summary.vw_condition_narrow_flat` - view 
3. `fhir_synthea_patient_summary.patient_history100` - table (output)

#### Previous 2 years patient history 

1. `bigquery-public-data.fhir_synthea.condition` - public dataset
2. `fhir_synthea_patient_summary.vw_condition_narrow_flat_2yrs` - view 
3. `fhir_synthea_patient_summary.patient_history_2yrs` - table (output)

### Outputs for App

#### Patient Registration and History

1. `fhir_synthea_patient_summary.patient_all` - table (output)
2. `fhir_synthea_patient_summary.patient_all_2yrs` - table (output)

#### Patients User Input 

1. `fhir_synthea_patient_summary.patients_user_input` - table (output)


## Setup 

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

## Set Constants 

```{r set-constants}
project_id <- Sys.getenv("PROJECT_ID")
dataset_id <- "fhir_synthea_patient_summary"
```

## Load packages 

```{r load-packages}
library(glue)
library(gargle)
library(DBI)
library(bigrquery)
```


## Authenticate with ADC 

```{r auth}
credentials_app_default(scopes="https://www.googleapis.com/auth/cloud-platform")

con <- dbConnect(
  bigquery(),
  project = project_id,
  billing = project_id,
  dataset = dataset_id)
```

### Confirm connection to BQ successful

```{sql connection=con, output.var="bq_test"}
SELECT z FROM `bigquery-public-data.blackhole_database.sdss_dr7` LIMIT 10
```

```{r}
head(`bq_test`)
```

refs  
* https://gist.github.com/isteves/aaf339505c82762e8747faa3efb29c89

## Create dataset


```{r}
bq_dataset <- bq_dataset(project_id, dataset_id)

if (!bq_dataset_exists(bq_dataset)) {
  print(glue("Dataset '{dataset_id}' does not exist, creating..."))
  bq_dataset_create(bq_dataset)
  print(glue("Dataset '{dataset_id}' created."))
} else {
  print(glue("Dataset '{dataset_id}' already exists."))
}
```

## Upload data 

for primary_visit_reason that could not be extracted from thje FHIR synthea dataset 

```{r}
data_raw <- read.csv("./data/patient_primary_reason.csv")

data <- subset(data_raw, select = -c(name))

table_id <- "patient_registration_visit_reason"

### set bq table name and schema----------------------------------------------
bq_table <- bq_table(project_id, dataset_id, table_id)
bq_fields <- as_bq_fields(
  list(
    list(name = "patient_id", type = "string"),
    list(name = "primary_reason_of_visit", type = "string")
  )
)

### execute create table 
# Delete the table if it exists
if(bq_table_exists(bq_table)) {
  print(glue("Table '{table_id}' already exists, deleting first..."))
  bq_table_delete(bq_table)
}
bq_table_upload(bq_table, values = data, fields = bq_fields)

```

## Create tables by data type

### Patients 

#### 1 fhir_synthea.patient

Public BQ dataset/table source

#### 2 fhir_synthea_patient_summary.vw_patient_narrow_flat view 

```{sql connection=con, output.var="bq_vw_patient_narrow_flat"}
CREATE OR REPLACE VIEW `fhir_synthea_patient_summary.vw_patient_narrow_flat` AS (
SELECT
  t.birthPlace.value.address.city AS birthPlace_value_address_city,
  t.birthPlace.value.address.country AS birthPlace_value_address_country,
  t.birthPlace.value.address.state AS birthPlace_value_address_state,
  t.disability_adjusted_life_years.value.decimal AS disability_adjusted_life_years_value_decimal,
  t.patient_mothersMaidenName.value.string AS patient_mothersMaidenName_value_string,
  t.quality_adjusted_life_years.value.decimal AS quality_adjusted_life_years_value_decimal,
  t.shr_actor_FictionalPerson_extension.value.boolean AS shr_actor_FictionalPerson_extension_value_boolean,
  t.shr_demographics_SocialSecurityNumber_extension.value.string AS shr_demographics_SocialSecurityNumber_extension_value_string,
  t.shr_entity_FathersName_extension.value.humanName.text AS shr_entity_FathersName_extension_value_humanName_text,
  t.shr_entity_Person_extension.value.reference.basicId AS shr_entity_Person_extension_value_reference_basicId,
  t.us_core_birthsex.value.code AS us_core_birthsex_value_code,
  t.us_core_ethnicity.ombCategory.value.coding.code AS us_core_ethnicity_ombCategory_value_coding_code,
  t.us_core_ethnicity.ombCategory.value.coding.display AS us_core_ethnicity_ombCategory_value_coding_display,
  t.us_core_ethnicity.ombCategory.value.coding.system AS us_core_ethnicity_ombCategory_value_coding_system,
  t.us_core_ethnicity.text.value.string AS us_core_ethnicity_text_value_string,
  t.us_core_race.ombCategory.value.coding.code AS us_core_race_ombCategory_value_coding_code,
  t.us_core_race.ombCategory.value.coding.display AS us_core_race_ombCategory_value_coding_display,
  t.us_core_race.ombCategory.value.coding.system AS us_core_race_ombCategory_value_coding_system,
  t.us_core_race.text.value.string AS us_core_race_text_value_string,
  address.geolocation.latitude.value.decimal AS address_geolocation_latitude_value_decimal,
  address.geolocation.longitude.value.decimal AS address_geolocation_longitude_value_decimal,
  address.city AS address_city,
  address.country AS address_country,
  address_line,
  address.postalCode AS address_postalCode,
  address.state AS address_state,
  t.birthDate AS birthDate,
  communication_language_coding.code AS communication_language_coding_code,
  communication_language_coding.display AS communication_language_coding_display,
  communication_language_coding.system AS communication_language_coding_system,
  communication.language.text AS communication_language_text,
  t.deceased.boolean AS deceased_boolean,
  t.deceased.dateTime AS deceased_dateTime,
  t.gender AS gender,
  t.id AS id,
  identifier_type_coding.code AS identifier_type_coding_code,
  identifier_type_coding.display AS identifier_type_coding_display,
  identifier_type_coding.system AS identifier_type_coding_system,
  identifier.type.text AS identifier_type_text,
  identifier.value AS identifier_value,
  maritalStatus_coding.userSelected AS maritalStatus_coding_userSelected,
  maritalStatus_coding.version AS maritalStatus_coding_version,
  maritalStatus_coding.code AS maritalStatus_coding_code,
  maritalStatus_coding.display AS maritalStatus_coding_display,
  maritalStatus_coding.system AS maritalStatus_coding_system,
  t.maritalStatus.text AS maritalStatus_text,
  t.multipleBirth.boolean AS multipleBirth_boolean,
  t.multipleBirth.integer AS multipleBirth_integer,
  name.period.start AS name_period_start,
  name.period.
END
  AS name_period_end,
  name.text AS name_text,
  name.family AS name_family,
  name_given,
  name_prefix,
  name_suffix,
  name.use AS name_use,
  telecom.period.start AS telecom_period_start,
  telecom.period.
END
  AS telecom_period_end,
  telecom.rank AS telecom_rank,
  telecom.system AS telecom_system,
  telecom.use AS telecom_use,
  telecom.value AS telecom_value,
  t.text.div AS text_div,
  t.text.status AS text_status
FROM
  `bigquery-public-data.fhir_synthea.patient` AS t
LEFT JOIN
  t.animal.species.coding AS animal_species_coding
LEFT JOIN
  t.animal.breed.coding AS animal_breed_coding
LEFT JOIN
  t.animal.genderStatus.coding AS animal_genderStatus_coding
LEFT JOIN
  t.contact AS contact
LEFT JOIN
  contact.relationship AS contact_relationship
LEFT JOIN
  contact_relationship.coding AS contact_relationship_coding
LEFT JOIN
  contact.name.given AS contact_name_given
LEFT JOIN
  contact.name.prefix AS contact_name_prefix
LEFT JOIN
  contact.name.suffix AS contact_name_suffix
LEFT JOIN
  contact.telecom AS contact_telecom
LEFT JOIN
  contact.address.line AS contact_address_line
LEFT JOIN
  contact.organization.identifier.type.coding AS contact_organization_identifier_type_coding
LEFT JOIN
  contact.organization.identifier.assigner.identifier.type.coding AS contact_organization_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.generalPractitioner AS generalPractitioner
LEFT JOIN
  generalPractitioner.identifier.type.coding AS generalPractitioner_identifier_type_coding
LEFT JOIN
  generalPractitioner.identifier.assigner.identifier.type.coding AS generalPractitioner_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.link AS link
LEFT JOIN
  link.other.identifier.type.coding AS link_other_identifier_type_coding
LEFT JOIN
  link.other.identifier.assigner.identifier.type.coding AS link_other_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.managingOrganization.identifier.type.coding AS managingOrganization_identifier_type_coding
LEFT JOIN
  t.managingOrganization.identifier.assigner.identifier.type.coding AS managingOrganization_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.photo AS photo
LEFT JOIN
  t.address AS address
LEFT JOIN
  address.line AS address_line
LEFT JOIN
  t.communication AS communication
LEFT JOIN
  communication.language.coding AS communication_language_coding
LEFT JOIN
  t.identifier AS identifier
LEFT JOIN
  identifier.assigner.identifier.type.coding AS identifier_assigner_identifier_type_coding
LEFT JOIN
  identifier.type.coding AS identifier_type_coding
LEFT JOIN
  t.maritalStatus.coding AS maritalStatus_coding
LEFT JOIN
  t.meta.security AS meta_security
LEFT JOIN
  t.meta.tag AS meta_tag
LEFT JOIN
  t.meta.profile AS meta_profile
LEFT JOIN
  t.name AS name
LEFT JOIN
  name.given AS name_given
LEFT JOIN
  name.prefix AS name_prefix
LEFT JOIN
  name.suffix AS name_suffix
LEFT JOIN
  t.telecom AS telecom
)
```

#### 3 patients

```{sql connection=con, output.var="bq_patients"}
CREATE OR REPLACE TABLE `fhir_synthea_patient_summary.patients` AS (
SELECT
  patient_id,
  Name
FROM (
  SELECT
    id AS patient_id,
    CONCAT(name_given," ",name_family) AS Name,
    COUNT(*) AS count_MR
  FROM
    `fhir_synthea_patient_summary.vw_patient_narrow_flat` AS p
  JOIN
    `fhir_synthea_patient_summary.patient_registration_visit_reason` AS b
  ON
    p.id=b.patient_id
  WHERE
    identifier_type_coding_code = 'MR'
  GROUP BY
    1,
    2
  HAVING
    count_MR = 1
  ORDER BY
    3 DESC
  LIMIT
    100 )
)
```

### Patient Registration 

#### 1 fhir_synthea.patient

Public BQ dataset/table source

#### 2 vw_patient_narrow_flat view 

See query above. 

#### 3 fhir_synthea_patient_summary.patient_registration_visit_reason

Created with uploaded data 

#### 4 patient registration table (output)

```{sql connection=con, output.var="bq_patient_registration"}
CREATE OR REPLACE TABLE `fhir_synthea_patient_summary.patient_registration` AS (
SELECT
  id AS patient_id,
  CONCAT('ACME New Patient Referral / Authorization Form All below information is required as well has prior medicals for scheduling',"\n",Name,"\n",'Primary Reason of Visit: ',primary_reason_of_visit,"\n",'Date of Birth:',birthDate,"\n",'Gender: ',gender,"\n",'Patient Mailing Address: ',Address,"\n",'Language of Communication: ',communication_language_text, "\n", 'Ethnicity: ',us_core_ethnicity_text_value_string,"\n", 'Race: ',us_core_race_text_value_string,"\n",'Type of ID Provided: ',identifier_type_text,"\n",'Contact Number: ',telecom_value) AS text_registration
FROM (
  SELECT
  id,
  CONCAT(name_given," ",name_family) AS Name,
  birthDate,
  gender,
  CONCAT(address_line,",",address_city,",",address_state,",",IFNULL(address_postalcode,'99999')) AS Address,
  communication_language_text,
  us_core_ethnicity_text_value_string,
  us_core_race_text_value_string,
  identifier_type_text,
  telecom_value,
  primary_reason_of_visit
  FROM
  `fhir_synthea_patient_summary.vw_patient_narrow_flat` AS p
  JOIN
  `fhir_synthea_patient_summary.patient_registration_visit_reason` AS b
  ON
  p.id=b.patient_id
  WHERE
  p.identifier_type_coding_code = 'MR')
)
```


### Patient History 

#### All

##### 1. bigquery-public-data.fhir_synthea.condition

##### 2. vw_condition_narrow_flat

```{sql connection=con, output.var="bq_vw_condition_narrow_flat"}
CREATE OR REPLACE VIEW `fhir_synthea_patient_summary.vw_condition_narrow_flat` AS (
SELECT
  t.assertedDate AS assertedDate,
  category_coding.code AS category_coding_code,
  category_coding.display AS category_coding_display,
  category_coding.system AS category_coding_system,
  t.clinicalStatus AS clinicalStatus,
  code_coding.userSelected AS code_coding_userSelected,
  code_coding.version AS code_coding_version,
  code_coding.code AS code_coding_code,
  code_coding.display AS code_coding_display,
  code_coding.system AS code_coding_system,
  t.code.text AS code_text,
  t.context.encounterId AS context_encounterId,
  t.id AS id,
  t.onset.dateTime AS onset_dateTime,
  t.subject.patientId AS subject_patientId,
  t.verificationStatus AS verificationStatus
FROM
  `bigquery-public-data.fhir_synthea.condition` AS t
LEFT JOIN
  t.asserter.identifier.type.coding AS asserter_identifier_type_coding
LEFT JOIN
  t.asserter.identifier.assigner.identifier.type.coding AS asserter_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.bodySite AS bodySite
LEFT JOIN
  bodySite.coding AS bodySite_coding
LEFT JOIN
  t.evidence AS evidence
LEFT JOIN
  evidence.code AS evidence_code
LEFT JOIN
  evidence_code.coding AS evidence_code_coding
LEFT JOIN
  evidence.detail AS evidence_detail
LEFT JOIN
  evidence_detail.identifier.type.coding AS evidence_detail_identifier_type_coding
LEFT JOIN
  evidence_detail.identifier.assigner.identifier.type.coding AS evidence_detail_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.identifier AS identifier
LEFT JOIN
  identifier.type.coding AS identifier_type_coding
LEFT JOIN
  identifier.assigner.identifier.type.coding AS identifier_assigner_identifier_type_coding
LEFT JOIN
  t.note AS note
LEFT JOIN
  note.author.reference.identifier.type.coding AS note_author_reference_identifier_type_coding
LEFT JOIN
  note.author.reference.identifier.assigner.identifier.type.coding AS note_author_reference_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.severity.coding AS severity_coding
LEFT JOIN
  t.stage.summary.coding AS stage_summary_coding
LEFT JOIN
  t.stage.assessment AS stage_assessment
LEFT JOIN
  stage_assessment.identifier.type.coding AS stage_assessment_identifier_type_coding
LEFT JOIN
  stage_assessment.identifier.assigner.identifier.type.coding AS stage_assessment_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.category AS category
LEFT JOIN
  category.coding AS category_coding
LEFT JOIN
  t.code.coding AS code_coding
LEFT JOIN
  t.context.identifier.type.coding AS context_identifier_type_coding
LEFT JOIN
  t.context.identifier.assigner.identifier.type.coding AS context_identifier_assigner_identifier_type_coding
LEFT JOIN
  t.meta.security AS meta_security
LEFT JOIN
  t.meta.tag AS meta_tag
LEFT JOIN
  t.meta.profile AS meta_profile
LEFT JOIN
  t.subject.identifier.type.coding AS subject_identifier_type_coding
LEFT JOIN
  t.subject.identifier.assigner.identifier.type.coding AS subject_identifier_assigner_identifier_type_coding
)
```

##### 3. patient history table (output)

```{sql connection=con, output.var="bq_patient_history100"}
CREATE OR REPLACE TABLE `fhir_synthea_patient_summary.patient_history` AS (
SELECT
  patient_id,
  STRING_AGG(text_history, "") AS text_history
FROM (
  SELECT
  subject_patientId AS patient_id,
  CONCAT('Date of Diagnosis: ',asserteddate, "\n",'Patient diagnosis category: ',category_coding_display,"\n",'Most Responsible Diagnosis Code:',code_coding_code,"\n", 'Active Issues Managed in Hospital: ',code_coding_display,"\n",'Patient Verification Status: ',verificationStatus, "\n") AS text_history
  FROM
  `fhir_synthea_patient_summary.vw_condition_narrow_flat` AS c
  JOIN
  `fhir_synthea_patient_summary.patients` AS b
  ON
  c.subject_patientId=b.patient_id
  ORDER BY
  c.onset_dateTime ASC)
  GROUP BY patient_id
)
```

#### Previous 2 years

##### 1. bigquery-public-data.fhir_synthea.condition

##### 2. fhir_synthea.vw_condition_narrow_flat_2yrs

```{sql connection=con, output.var="bq_vw_condition_narrow_flat_2yrs"}
CREATE OR REPLACE VIEW 
  `fhir_synthea_patient_summary.vw_condition_narrow_flat_2yrs` AS (
  SELECT
    a.*
  FROM
    `fhir_synthea_patient_summary.vw_condition_narrow_flat` a,
    (
    SELECT
      subject_patientId,
      MAX(EXTRACT(year
        FROM
          bigfunctions.us.parse_date(asserteddate))) max_year
    FROM
      `fhir_synthea_patient_summary.vw_condition_narrow_flat`
    GROUP BY
      1) b
  WHERE
    a.subject_patientId = b.subject_patientId
    AND EXTRACT(year
    FROM
      bigfunctions.us.parse_date(asserteddate)) >= max_year - 2 )
```

##### 3. fhir_synthea_patient_summary.patient_history_2yrs

```{sql connection=con, output.var="bq_patient_history_2yrs"}
CREATE OR REPLACE TABLE `fhir_synthea_patient_summary.patient_history_2yrs` AS (
SELECT
  patient_id,
  STRING_AGG(text_history, "") AS text_history
FROM (
  SELECT
  subject_patientId AS patient_id,
  CONCAT('Date of Diagnosis: ',asserteddate, "\n",'Patient diagnosis category: ',category_coding_display,"\n",'Most Responsible Diagnosis Code:',code_coding_code,"\n", 'Active Issues Managed in Hospital: ',code_coding_display,"\n",'Patient Verification Status: ',verificationStatus, "\n") AS text_history
  FROM
  `fhir_synthea_patient_summary.vw_condition_narrow_flat_2yrs` AS c
  JOIN
  `fhir_synthea_patient_summary.patients` AS b
  ON
  c.subject_patientId=b.patient_id
  ORDER BY
  c.onset_dateTime ASC )
GROUP BY
patient_id )
```

### Patient Registration and History

for Shiny app user input - all patient data 

#### Full History

```{sql connection=con, output.var="bq_patient_all"}
CREATE OR REPLACE TABLE
  `fhir_synthea_patient_summary.patient_all` AS (
  SELECT
    h.patient_id AS patient_id,
    text_registration,
    text_history
  FROM
    `fhir_synthea_patient_summary.patient_registration` AS r
  JOIN
    `fhir_synthea_patient_summary.patient_history` AS h
  ON
    r.patient_id=h.patient_id )
```


#### Previous 2 years History

```{sql connection=con, output.var="bq_patient_all_2yrs"}
CREATE OR REPLACE TABLE
  `fhir_synthea_patient_summary.patient_all_2yrs` AS (
  SELECT
    h.patient_id AS patient_id,
    text_registration,
    text_history
  FROM
    `fhir_synthea_patient_summary.patient_registration` AS r
  JOIN
    `fhir_synthea_patient_summary.patient_history_2yrs` AS h
  ON
    r.patient_id=h.patient_id )
```


### Patients User Input 

output for Shiny app - list of patients for user input selection and WebMD Search


```{sql connection=con, output.var="bq_patients_user_input"}
CREATE OR REPLACE TABLE
  `fhir_synthea_patient_summary.patients_user_input` AS (
  SELECT
    a.patient_id,
    a.Name AS name,
    b.primary_reason_of_visit
  FROM
    `fhir_synthea_patient_summary.patients` AS a
  JOIN
    `fhir_synthea_patient_summary.patient_registration_visit_reason` AS b
  ON
    a.patient_id = b.patient_id )
```