Stream and synchronize FHIR resources with BigQuery

This tutorial explains scenarios in which you can use BigQuery streaming to keep a FHIR store in sync with a BigQuery dataset in near real time.

Objectives

  1. Configure BigQuery permissions.
  2. Create a FHIR store and add Patient resources.
  3. Configure BigQuery streaming on the FHIR store.
  4. Verify streaming configuration to BigQuery.
  5. Export existing FHIR resources to BigQuery.
  6. Stream resources from multiple FHIR stores to the same BigQuery dataset.

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  4. Enable the Cloud Healthcare API.

    Enable the API

  5. Install and initialize the Cloud SDK.

Step 1: Configure BigQuery permissions

To stream FHIR resource changes to BigQuery, you must grant additional permissions to the Cloud Healthcare Service Agent service account. For more information, see FHIR store BigQuery permissions.

Step 2: Configure and verify BigQuery streaming

To enable streaming to BigQuery, follow these instructions:

Create a FHIR store and add Patient resources

To create a FHIR store and add two Patient resources, follow these steps:

  1. Create the FHIR store:

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • FHIR_STORE_ID: an identifier for the FHIR store. The FHIR store ID must be unique in the dataset. The FHIR store ID can be any Unicode string from 1 through 256 characters consisting of numbers, letters, underscores, dashes, and periods.
    • VERSION: the FHIR store version: DSTU2, STU3, or R4

    Request JSON body:

    {
      "version": "VERSION"
    }
    

    To send your request, choose one of these options:

    curl

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      cat > request.json << 'EOF'
      {
        "version": "VERSION"
      }
      EOF
    2. Execute the following command in the terminal. It references the request.json file you just created.
      curl -X POST \
      -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
      -H "Content-Type: application/json; charset=utf-8" \
      -d @request.json \
      "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores?fhirStoreId=FHIR_STORE_ID"

    PowerShell

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      @'
      {
        "version": "VERSION"
      }
      '@  | Out-File -FilePath request.json -Encoding utf8
    2. Execute the following command in the terminal. It references the request.json file you just created.
      $cred = gcloud auth application-default print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
      -Method POST `
      -Headers $headers `
      -ContentType: "application/json; charset=utf-8" `
      -InFile request.json `
      -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores?fhirStoreId=FHIR_STORE_ID" | Select-Object -Expand Content

    API Explorer

    Copy the request body and open the method reference page. The API Explorer panel opens on the right side of the page. You can interact with this tool to send requests. Paste the request body in this tool, complete any other required fields, and click Execute.

    You should receive a JSON response similar to the following:

  2. Create the first Patient resource in the FHIR store:

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • FHIR_STORE_ID: the FHIR store ID

    Request JSON body:

    {
      "name": [
        {
          "use": "official",
          "family": "Smith",
          "given": [
            "Darcy"
          ]
        }
      ],
      "gender": "female",
      "birthDate": "1970-01-01",
      "resourceType": "Patient"
    }
    

    To send your request, choose one of these options:

    curl

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      cat > request.json << 'EOF'
      {
        "name": [
          {
            "use": "official",
            "family": "Smith",
            "given": [
              "Darcy"
            ]
          }
        ],
        "gender": "female",
        "birthDate": "1970-01-01",
        "resourceType": "Patient"
      }
      EOF
    2. Execute the following command in the terminal. It references the request.json file you just created.
      curl -X POST \
      -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
      -H "Content-Type: application/fhir+json" \
      -d @request.json \
      "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient"

    PowerShell

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      @'
      {
        "name": [
          {
            "use": "official",
            "family": "Smith",
            "given": [
              "Darcy"
            ]
          }
        ],
        "gender": "female",
        "birthDate": "1970-01-01",
        "resourceType": "Patient"
      }
      '@  | Out-File -FilePath request.json -Encoding utf8
    2. Execute the following command in the terminal. It references the request.json file you just created.
      $cred = gcloud auth application-default print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
      -Method POST `
      -Headers $headers `
      -ContentType: "application/fhir+json" `
      -InFile request.json `
      -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient" | Select-Object -Expand Content

    You should receive a JSON response similar to the following:

  3. Create the second Patient resource in the FHIR store:

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • FHIR_STORE_ID: the FHIR store ID

    Request JSON body:

    {
      "name": [
        {
          "use": "official",
          "family": "Zhang",
          "given": [
            "Michael"
          ]
        }
      ],
      "gender": "male",
      "birthDate": "1980-01-01",
      "resourceType": "Patient"
    }
    

    To send your request, choose one of these options:

    curl

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      cat > request.json << 'EOF'
      {
        "name": [
          {
            "use": "official",
            "family": "Zhang",
            "given": [
              "Michael"
            ]
          }
        ],
        "gender": "male",
        "birthDate": "1980-01-01",
        "resourceType": "Patient"
      }
      EOF
    2. Execute the following command in the terminal. It references the request.json file you just created.
      curl -X POST \
      -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
      -H "Content-Type: application/fhir+json" \
      -d @request.json \
      "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient"

    PowerShell

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      @'
      {
        "name": [
          {
            "use": "official",
            "family": "Zhang",
            "given": [
              "Michael"
            ]
          }
        ],
        "gender": "male",
        "birthDate": "1980-01-01",
        "resourceType": "Patient"
      }
      '@  | Out-File -FilePath request.json -Encoding utf8
    2. Execute the following command in the terminal. It references the request.json file you just created.
      $cred = gcloud auth application-default print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
      -Method POST `
      -Headers $headers `
      -ContentType: "application/fhir+json" `
      -InFile request.json `
      -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient" | Select-Object -Expand Content

    You should receive a JSON response similar to the following:

  4. Search for Patient resources in the FHIR store and verify that the store contains the two Patient resources:

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • FHIR_STORE_ID: the FHIR store ID

    To send your request, choose one of these options:

    curl

    Execute the following command:

    curl -X GET \
    -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
    "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient"

    PowerShell

    Execute the following command:

    $cred = gcloud auth application-default print-access-token
    $headers = @{ "Authorization" = "Bearer $cred" }

    Invoke-WebRequest `
    -Method GET `
    -Headers $headers `
    -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient" | Select-Object -Expand Content

    You should receive a JSON response similar to the following:

Configure BigQuery streaming on the FHIR store

Update the FHIR store to configure BigQuery streaming. After configuring streaming, the Cloud Healthcare API streams any resource changes to the BigQuery dataset.

  1. Update your existing FHIR store to add the location of the BigQuery dataset:

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • FHIR_STORE_ID: your FHIR store ID
    • BIGQUERY_PROJECT_ID: the Google Cloud project containing the BigQuery dataset for streaming FHIR resource changes
    • BIGQUERY_DATASET_ID: the BigQuery dataset where you are streaming FHIR resource changes

    Request JSON body:

    {
      "streamConfigs": [
        {
          "bigqueryDestination": {
            "datasetUri": "bq://BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID",
            "schemaConfig": {
              "schemaType": "ANALYTICS"
            }
          }
        }
      ]
    }
    

    To send your request, choose one of these options:

    curl

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      cat > request.json << 'EOF'
      {
        "streamConfigs": [
          {
            "bigqueryDestination": {
              "datasetUri": "bq://BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID",
              "schemaConfig": {
                "schemaType": "ANALYTICS"
              }
            }
          }
        ]
      }
      EOF
    2. Execute the following command in the terminal. It references the request.json file you just created.
      curl -X PATCH \
      -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
      -H "Content-Type: application/json; charset=utf-8" \
      -d @request.json \
      "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID?updateMask=streamConfigs"

    PowerShell

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      @'
      {
        "streamConfigs": [
          {
            "bigqueryDestination": {
              "datasetUri": "bq://BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID",
              "schemaConfig": {
                "schemaType": "ANALYTICS"
              }
            }
          }
        ]
      }
      '@  | Out-File -FilePath request.json -Encoding utf8
    2. Execute the following command in the terminal. It references the request.json file you just created.
      $cred = gcloud auth application-default print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
      -Method PATCH `
      -Headers $headers `
      -ContentType: "application/json; charset=utf-8" `
      -InFile request.json `
      -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID?updateMask=streamConfigs" | Select-Object -Expand Content

    API Explorer

    Copy the request body and open the method reference page. The API Explorer panel opens on the right side of the page. You can interact with this tool to send requests. Paste the request body in this tool, complete any other required fields, and click Execute.

    You should receive a JSON response similar to the following:

Verify streaming configuration to BigQuery

Verify that streaming is configured correctly by completing the following steps:

  1. Create a third Patient resource in the FHIR store:

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • FHIR_STORE_ID: the FHIR store ID

    Request JSON body:

    {
      "name": [
        {
          "use": "official",
          "family": "Lee",
          "given": [
            "Alex"
          ]
        }
      ],
      "gender": "male",
      "birthDate": "1990-01-01",
      "resourceType": "Patient"
    }
    

    To send your request, choose one of these options:

    curl

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      cat > request.json << 'EOF'
      {
        "name": [
          {
            "use": "official",
            "family": "Lee",
            "given": [
              "Alex"
            ]
          }
        ],
        "gender": "male",
        "birthDate": "1990-01-01",
        "resourceType": "Patient"
      }
      EOF
    2. Execute the following command in the terminal. It references the request.json file you just created.
      curl -X POST \
      -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
      -H "Content-Type: application/fhir+json" \
      -d @request.json \
      "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient"

    PowerShell

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      @'
      {
        "name": [
          {
            "use": "official",
            "family": "Lee",
            "given": [
              "Alex"
            ]
          }
        ],
        "gender": "male",
        "birthDate": "1990-01-01",
        "resourceType": "Patient"
      }
      '@  | Out-File -FilePath request.json -Encoding utf8
    2. Execute the following command in the terminal. It references the request.json file you just created.
      $cred = gcloud auth application-default print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
      -Method POST `
      -Headers $headers `
      -ContentType: "application/fhir+json" `
      -InFile request.json `
      -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/FHIR_STORE_ID/fhir/Patient" | Select-Object -Expand Content

    You should receive a JSON response similar to the following:

  2. Query the BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.Patient table by running bq query. BigQuery organizes tables by FHIR resource type. The third Patient resource you created is in the Patient table.

    bq query \
       --project_id=BIGQUERY_PROJECT_ID \
       --use_legacy_sql=false \
       'SELECT COUNT(*) FROM `BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.Patient`'
    

    The query returns the following result. The result shows that there is one Patient resource record in the BigQuery table, because you added the Patient resource after configuring streaming on the FHIR store.

    +-----+
    | f0_ |
    +-----+
    |   1 |
    +-----+
    

Step 3: Export existing FHIR resources to BigQuery

If you have an existing FHIR store containing data that you want to sync with a BigQuery dataset, you must complete the following steps to ensure that the existing data is in BigQuery:

  1. Configure streaming to BigQuery.
  2. Export the existing data to the BigQuery dataset.

To export the two Patient resources that existed in the FHIR store before you configured streaming to the BigQuery dataset, complete the following steps:

  1. To export the resources in the FHIR store to BigQuery, run the gcloud healthcare fhir-stores export bq command. The command uses the --write-disposition=write-append flag, which appends data to the existing BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.Patient table.

    gcloud healthcare fhir-stores export bq FHIR_STORE_ID \
       --dataset=DATASET_ID \
       --location=LOCATION \
       --bq-dataset=bq://BIGQUERY_PROJECT_ID.BIGQUERY_DATASET \
       --schema-type=analytics \
       --write-disposition=write-append
    
  2. Query the BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.Patient table to verify the number of Patient resources in the BigQuery dataset:

    bq query \
       --project_id=BIGQUERY_PROJECT_ID \
       --use_legacy_sql=false \
       'SELECT COUNT(*) FROM `BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.Patient`'
    

    The query returns the following result, showing that there are 4 Patient resource records in the BigQuery table:

    +-----+
    | f0_ |
    +-----+
    |   4 |
    +-----+
    

    The actual number of Patient resources in the BigQuery table is 3, but the query returns 4. Inconsistencies might occur when a resource contains duplicates from different operations. In this case, the first Patient resource was added to the BigQuery table these two times:

    • When the Patient resource creation was streamed
    • When the resources in the FHIR store were exported to BigQuery

    The BigQuery table also contains a mutation history of the first Patient resource. For example, if you delete the Patient resource using fhir.delete, the BigQuery table has a meta.tag.code column with the value DELETE.

  3. To get the latest snapshot of the data in the FHIR store, query the view. The Cloud Healthcare API constructs the view by only looking at the latest version of each resource. Querying views is the most accurate way to keep a FHIR store and its corresponding BigQuery table in sync.

    To query the view, run the following command:

    bq query \
       --project_id=BIGQUERY_PROJECT_ID \
       --use_legacy_sql=false \
       'SELECT COUNT(*) FROM `BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.PatientView`'
    

    The query returns the following result, which correctly shows that there are 3 Patient resources in the BigQuery table:

    +-----+
    | f0_ |
    +-----+
    |   3 |
    +-----+
    

Step 4: Stream resources from multiple FHIR stores to the same BigQuery dataset

In some cases, you might want to stream FHIR resources from multiple FHIR stores to the same BigQuery dataset to perform analytics on the aggregated FHIR resources from the FHIR stores.

In the following steps, you create a second FHIR store in the same Cloud Healthcare API dataset as the first FHIR store, but you can use FHIR stores from different datasets when aggregating FHIR resources.

  1. Create a second FHIR store with BigQuery streaming enabled and use the same BigQuery dataset that you used in Configure BigQuery streaming on the FHIR store.

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • SECOND_FHIR_STORE_ID: an identifier for the second FHIR store. The FHIR store ID must be unique in the dataset. The FHIR store ID can be any Unicode string from 1 through 256 characters consisting of numbers, letters, underscores, dashes, and periods.
    • VERSION: the FHIR store version: DSTU2, STU3, or R4
    • BIGQUERY_PROJECT_ID: the Google Cloud project containing the BigQuery dataset for streaming FHIR resource changes
    • BIGQUERY_DATASET_ID: the BigQuery dataset where you are streaming FHIR resource changes

    Request JSON body:

    {
      "version": "VERSION"
      "streamConfigs": [
        {
          "bigqueryDestination": {
            "datasetUri": "bq://BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID",
            "schemaConfig": {
              "schemaType": "ANALYTICS"
            }
          }
        }
      ]
    }
    

    To send your request, choose one of these options:

    curl

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      cat > request.json << 'EOF'
      {
        "version": "VERSION"
        "streamConfigs": [
          {
            "bigqueryDestination": {
              "datasetUri": "bq://BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID",
              "schemaConfig": {
                "schemaType": "ANALYTICS"
              }
            }
          }
        ]
      }
      EOF
    2. Execute the following command in the terminal. It references the request.json file you just created.
      curl -X POST \
      -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
      -H "Content-Type: application/json; charset=utf-8" \
      -d @request.json \
      "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores?fhirStoreId=SECOND_FHIR_STORE_ID"

    PowerShell

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      @'
      {
        "version": "VERSION"
        "streamConfigs": [
          {
            "bigqueryDestination": {
              "datasetUri": "bq://BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID",
              "schemaConfig": {
                "schemaType": "ANALYTICS"
              }
            }
          }
        ]
      }
      '@  | Out-File -FilePath request.json -Encoding utf8
    2. Execute the following command in the terminal. It references the request.json file you just created.
      $cred = gcloud auth application-default print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
      -Method POST `
      -Headers $headers `
      -ContentType: "application/json; charset=utf-8" `
      -InFile request.json `
      -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores?fhirStoreId=SECOND_FHIR_STORE_ID" | Select-Object -Expand Content

    API Explorer

    Copy the request body and open the method reference page. The API Explorer panel opens on the right side of the page. You can interact with this tool to send requests. Paste the request body in this tool, complete any other required fields, and click Execute.

    You should receive a JSON response similar to the following:

  2. Create a Patient resource in the second FHIR store:

    REST & CMD LINE

    Before using any of the request data, make the following replacements:

    • PROJECT_ID: your Google Cloud project ID
    • LOCATION: the location of the parent dataset
    • DATASET_ID: the FHIR store's parent dataset
    • SECOND_FHIR_STORE_ID: the second FHIR store ID

    Request JSON body:

    {
      "name": [
        {
          "use": "official",
          "family": "Lee",
          "given": [
            "Alex"
          ]
        }
      ],
      "gender": "male",
      "birthDate": "1990-01-01",
      "resourceType": "Patient"
    }
    

    To send your request, choose one of these options:

    curl

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      cat > request.json << 'EOF'
      {
        "name": [
          {
            "use": "official",
            "family": "Lee",
            "given": [
              "Alex"
            ]
          }
        ],
        "gender": "male",
        "birthDate": "1990-01-01",
        "resourceType": "Patient"
      }
      EOF
    2. Execute the following command in the terminal. It references the request.json file you just created.
      curl -X POST \
      -H "Authorization: Bearer "$(gcloud auth application-default print-access-token) \
      -H "Content-Type: application/fhir+json" \
      -d @request.json \
      "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/SECOND_FHIR_STORE_ID/fhir/Patient"

    PowerShell

    1. Save the request body in a file called request.json. Copy the following command and run it in the terminal to create this file.
      @'
      {
        "name": [
          {
            "use": "official",
            "family": "Lee",
            "given": [
              "Alex"
            ]
          }
        ],
        "gender": "male",
        "birthDate": "1990-01-01",
        "resourceType": "Patient"
      }
      '@  | Out-File -FilePath request.json -Encoding utf8
    2. Execute the following command in the terminal. It references the request.json file you just created.
      $cred = gcloud auth application-default print-access-token
      $headers = @{ "Authorization" = "Bearer $cred" }

      Invoke-WebRequest `
      -Method POST `
      -Headers $headers `
      -ContentType: "application/fhir+json" `
      -InFile request.json `
      -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/fhirStores/SECOND_FHIR_STORE_ID/fhir/Patient" | Select-Object -Expand Content

    You should receive a JSON response similar to the following:

  3. Query the BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.Patient table to verify the number of Patient resources in the BigQuery table:

    bq query \
       --project_id=BIGQUERY_PROJECT_ID \
       --use_legacy_sql=false \
       'SELECT COUNT(*) FROM `BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.Patient`'
    

    When streaming the data about the new Patient resource, BigQuery used the existing Patient table in the BigQuery dataset. The query returns the following result, showing that there are 5 Patient resource records in the BigQuery table. See Export existing FHIR resources to BigQuery for an explanation of why the table contains 5 resources instead of 4.

    +-----+
    | f0_ |
    +-----+
    |   5 |
    +-----+
    
  4. Run the following command to query the view:

    bq query \
       --project_id=BIGQUERY_PROJECT_ID \
       --use_legacy_sql=false \
       'SELECT COUNT(*) FROM `BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID.PatientView`'
    

    The query returns the following result, showing that there are 4 Patient resources in the combined first and second FHIR stores and in the BigQuery table:

    +-----+
    | f0_ |
    +-----+
    |   4 |
    +-----+
    

Clean up

If you created a new project for this tutorial, follow the steps in Delete the project. To only delete the Cloud Healthcare API and BigQuery resources, complete the steps in Delete the Cloud Healthcare API dataset and Delete the BigQuery dataset.

Delete the project

  1. In the Cloud Console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Delete the Cloud Healthcare API dataset

If you no longer need the Cloud Healthcare API dataset created in this tutorial, you can delete it. Deleting a dataset permanently deletes the dataset and any FHIR stores it contains.

  1. To delete a dataset, use the gcloud healthcare datasets delete command:

    gcloud healthcare datasets delete DATASET_ID \
    --location=LOCATION \
    --project=PROJECT_ID
    

    Replace the following:

    • DATASET_ID: the Cloud Healthcare API dataset
    • LOCATION: the location of the dataset
    • PROJECT_ID: your Google Cloud project ID
  2. To confirm, type Y.

The output is the following:

Deleted dataset [DATASET_ID].

Delete the BigQuery dataset

If you no longer need the BigQuery dataset created in this tutorial, you can delete it. Deleting a dataset permanently deletes the dataset and any tables it contains.

  1. Remove the BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID dataset by running the bq rm command:

    bq rm --recursive=true BIGQUERY_PROJECT_ID.BIGQUERY_DATASET_ID
    

    The --recursive flag deletes all tables in the dataset, including the Patient table.

  2. To confirm, type Y.

What's next