Exporting DICOM metadata to BigQuery

This page explains how to export DICOM metadata to BigQuery for exploration and analysis. For information on the BigQuery schema that is created when exporting DICOM metadata, see Understanding the BigQuery DICOM schema.

Setting BigQuery permissions

Before exporting DICOM metadata to BigQuery, you must grant extra permissions to the Cloud Healthcare Service Agent service account. For more information, see DICOM store BigQuery permissions.

Setting the BigQuery destination

When setting the BigQuery destination, use the fully qualified URI, like so: 
bq://PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID
The behavior of the export operation can vary depending on:
  • Whether the destination table already exists
  • Whether you have set the force field (using the API) or the --overwrite-table flag (using the gcloud command-line tool)
  • Whether you have set a value for the writeDisposition enum. If you use the writeDisposition enum, do not set the force field.
The behavior in each of these cases is as follows:
  • If the destination table already exists and force is set to true or the --overwrite-table flag is specified, the export operation overwrites the existing table.
  • If the destination table already exists and force is set to false or the --overwrite-table flag is not specified, an error occurs.
  • If the destination table does not already exist, then a new table is created, regardless of whether you specify the force field or the --overwrite-table flag.
  • The behavior for writeDisposition is described in its documentation. The writeDisposition enum performs behavior similar to the options when using force, with the following exception: if the destination table already exists, and is empty, the export operation completes instead of returning an error.

Exporting DICOM metadata

The following samples show how to export DICOM metadata to a BigQuery table. In these samples, the DICOM store and the BigQuery table are in the same project. To export DICOM metadata to another project, see Exporting DICOM metadata to a different project.

Console

To export DICOM metadata to BigQuery, complete the following steps:

  1. In the Cloud Console, go to the Datasets page.
    Go to the Datasets page
  2. Click the dataset that contains the DICOM store from which you are exporting DICOM metadata.
  3. In the list of data stores, choose Export from the Actions list for the DICOM store.
  4. On the Export DICOM Store page that appears, select BigQuery Table.
  5. In the Project list, select the BigQuery project.
  6. In the Dataset ID list, select the dataset.
  7. In the Table name field, enter a new table name.
    BigQuery checks each selection to ensure that the destination table is valid.
  8. Click Export to export DICOM metadata to the defined destination in BigQuery.
  9. To track the status of the operation, click the Operations tab. After the operation completes, the following indications appear:
    • The Long-running operation status section has a green check mark under the OK heading.
    • The Overview section has a green check mark and an OK indicator in the same row as the operation ID.
    If you encounter any errors, click Actions, and then click View details in Cloud Logging.

gcloud

To export DICOM metadata to BigQuery, use the gcloud healthcare dicom-stores export bq command. Specify the following information:

  • The name of the parent dataset
  • The name of the DICOM store
  • The name of an existing BigQuery dataset
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how to customize the export operation's behavior.

The following sample shows the gcloud dicom-stores export bq command.

gcloud healthcare dicom-stores export bq DICOM_STORE_ID \
  --dataset=DATASET_ID \
  --location=LOCATION \
  --bq-table=bq://PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID \
  [--overwrite-table]

After you run the command, it returns the operation name:

name: projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID

To view more details about the operation, run the gcloud healthcare operations describe command and provide OPERATION_ID from the response:

gcloud healthcare operations describe OPERATION_ID \
  --dataset=DATASET_ID

When the operation finishes, the response includes done: true.

done: true
metadata:
  '@type': type.googleapis.com/google.cloud.healthcare.v1.OperationMetadata
  apiMethodName: google.cloud.healthcare.v1.dicom.DicomService.ExportDicomData
  createTime: 'CREATE_TIME'
  endTime: 'END_TIME'
name: projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID
response:
  '@type': '...'

API

To export DICOM metadata to BigQuery, use the projects.locations.datasets.dicomStores.export method.

curl

To export DICOM metadata, make a POST request and specify the following information:

  • The name and location of the parent dataset
  • The name of the DICOM store
  • The name of an existing BigQuery dataset
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how to customize the export operation's behavior.
  • One of the following values for the writeDisposition enum:
    • WRITE_EMPTY: only export data if the destination tables are empty. This is the default.
    • WRITE_TRUNCATE: erase all existing data in the tables before writing the instances.
    • WRITE_APPEND: append data to the existing tables.

The following sample shows a POST request using curl.

curl -X POST \
    -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
    -H "Content-Type: application/json; charset=utf-8" \
    --data "{
      'bigqueryDestination': {
        'tableUri': 'bq://PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID'
      },
      'writeDisposition': '{WRITE_EMPTY|WRITE_TRUNCATE|WRITE_APPEND}'
    }" "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID:export"

If the request is successful, the server returns the response in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID"
}

The response contains an operation name. To track the status of the operation, you can use the Operation get method:

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/operations/OPERATION_ID"

If the request is successful, the server returns a response with the status of the operation in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID",
  "metadata": {
    "@type": "type.googleapis.com/google.cloud.healthcare.v1.OperationMetadata",
    "apiMethodName": "google.cloud.healthcare.v1.dicom.DicomService.ExportDicomData",
    "createTime": "CREATE_TIME",
    "endTime": "END_TIME",
    "logsUrl": "https://console.cloud.google.com/logs/viewer/CLOUD_LOGGING_URL"
  },
  "done": true,
  "response": {
    "@type": "..."
  }
}

PowerShell

To export DICOM metadata, make a POST request and specify the following information:

  • The name and location of the parent dataset
  • The name of the DICOM store
  • The name of an existing BigQuery dataset
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how to customize the export operation's behavior.
  • One of the following values for the writeDisposition enum:
    • WRITE_EMPTY: only export data if the destination tables are empty. This is the default.
    • WRITE_TRUNCATE: erase all existing data in the tables before writing the instances.
    • WRITE_APPEND: append data to the existing tables.

The following sample shows a POST request using Windows PowerShell.

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

Invoke-WebRequest `
  -Method Post `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -Body "{
    'bigqueryDestination': {
      'tableUri': 'bq://PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID'
    },
    'writeDisposition': '{WRITE_EMPTY|WRITE_TRUNCATE|WRITE_APPEND}'
  }" `
  -Uri "https://healthcare.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID:export" | Select-Object -Expand Content

If the request is successful, the server returns the response in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID"
}

The response contains an operation name. To track the status of the operation, you can use the Operation get method:

$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/operations/OPERATION_ID" | Select-Object -Expand Content

If the request is successful, the server returns a response with the status of the operation in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID",
  "metadata": {
    "@type": "type.googleapis.com/google.cloud.healthcare.v1.OperationMetadata",
    "apiMethodName": "google.cloud.healthcare.v1.dicom.DicomService.ExportDicomData",
    "createTime": "CREATE_TIME",
    "endTime": "END_TIME",
    "logsUrl": "https://console.cloud.google.com/logs/viewer/CLOUD_LOGGING_URL"
  },
  "done": true,
  "response": {
    "@type": "..."
  }
}

Exporting DICOM metadata to a different project

To export DICOM metadata from one project to another project, first see Exporting DICOM metadata to a different project permissions for information on how to set IAM permissions in the destination project.

gcloud

To export DICOM metadata from a DICOM store in one project to a BigQuery table in another project, use the gcloud healthcare dicom-stores export bq command. Specify the following information:

  • The name of the parent dataset
  • The name of the DICOM store
  • The destination project
  • The name of an existing BigQuery dataset in the destination project
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how the Cloud Healthcare API writes data to the BigQuery table.

The following sample shows the gcloud dicom-stores export bq command.

gcloud healthcare dicom-stores export bq DICOM_STORE_ID \
  --dataset=DATASET_ID \
  --location=LOCATION \
  --bq-table=bq://DESTINATION_PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID \
  [--overwrite-table]

The command line displays the operation ID and, after the operation completes, done:

Request issued for: [DICOM_STORE_ID]
Waiting for operation [projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/OPERATION_ID] to complete...done.
name: projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID

To view more details about the operation, run the gcloud healthcare operations describe command and provide OPERATION_ID from the response:

gcloud healthcare operations describe OPERATION_ID \
  --dataset=DATASET_ID

When the operation finishes, the response includes done: true.

done: true
metadata:
  '@type': type.googleapis.com/google.cloud.healthcare.v1.OperationMetadata
  apiMethodName: google.cloud.healthcare.v1.dicom.DicomService.ExportDicomData
  createTime: 'CREATE_TIME'
  endTime: 'END_TIME'
name: projects/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID
response:
  '@type': '...'

API

To export DICOM metadata to BigQuery, use the projects.locations.datasets.dicomStores.export method.

curl

To export DICOM metadata from a DICOM store in one project to a BigQuery table in another project, make a POST request and specify the following information:

  • The source project
  • The name and location of the parent dataset
  • The name of the DICOM store
  • The destination project
  • The name of an existing BigQuery dataset in the destination project
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how the Cloud Healthcare API writes data to the BigQuery table.
  • One of the following values for the writeDisposition enum:
    • WRITE_EMPTY: only export data if the destination tables are empty. This is the default.
    • WRITE_TRUNCATE: erase all existing data in the tables before writing the instances.
    • WRITE_APPEND: append data to the existing tables.

The following sample shows a POST request using curl.

curl -X POST \
    -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
    -H "Content-Type: application/json; charset=utf-8" \
    --data "{
      'bigqueryDestination': {
        'tableUri': 'bq://DESTINATION_PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID'
      },
      'writeDisposition': '{WRITE_EMPTY|WRITE_TRUNCATE|WRITE_APPEND}'
    }" "https://healthcare.googleapis.com/v1/projects/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID:export"

If the request is successful, the server returns the response in JSON format:

{
  "name": "projects/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID"
}

The response contains an operation name. To track the status of the operation, you can use the Operation get method:

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

If the request is successful, the server returns a response with the status of the operation in JSON format:

{
  "name": "projects/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID",
  "metadata": {
    "@type": "type.googleapis.com/google.cloud.healthcare.v1.OperationMetadata",
    "apiMethodName": "google.cloud.healthcare.v1.dicom.DicomService.ExportDicomData",
    "createTime": "CREATE_TIME",
    "endTime": "END_TIME",
    "logsUrl": "https://console.cloud.google.com/logs/viewer/CLOUD_LOGGING_URL"
  },
  "done": true,
  "response": {
    "@type": "..."
  }
}

PowerShell

To export DICOM metadata from a DICOM store in one project to a BigQuery table in another project, make a POST request and specify the following information:

  • The source project
  • The name and location of the parent dataset
  • The name of the DICOM store
  • The destination project
  • The name of an existing BigQuery dataset in the destination project
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how the Cloud Healthcare API writes data to the BigQuery table.
  • One of the following values for the writeDisposition enum:
    • WRITE_EMPTY: only export data if the destination tables are empty. This is the default.
    • WRITE_TRUNCATE: erase all existing data in the tables before writing the instances.
    • WRITE_APPEND: append data to the existing tables.

The following sample shows a POST request using Windows PowerShell.

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

Invoke-WebRequest `
  -Method Post `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -Body "{
    'bigqueryDestination': {
      'tableUri': 'bq://DESTINATION_PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID'
    },
    'writeDisposition': '{WRITE_EMPTY|WRITE_TRUNCATE|WRITE_APPEND}'
  }" `
  -Uri "https://healthcare.googleapis.com/v1/projects/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID:export" | Select-Object -Expand Content

If the request is successful, the server returns the response in JSON format:

{
  "name": "projects/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID"
}

The response contains an operation name. To track the status of the operation, you can use the Operation get method:

$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/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID" | Select-Object -Expand Content

If the request is successful, the server returns a response with the status of the operation in JSON format:

{
  "name": "projects/SOURCE_PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID",
  "metadata": {
    "@type": "type.googleapis.com/google.cloud.healthcare.v1.OperationMetadata",
    "apiMethodName": "google.cloud.healthcare.v1.dicom.DicomService.ExportDicomData",
    "createTime": "CREATE_TIME",
    "endTime": "END_TIME",
    "logsUrl": "https://console.cloud.google.com/logs/viewer/CLOUD_LOGGING_URL"
  },
  "done": true,
  "response": {
    "@type": "..."
  }
}

Exporting DICOM metadata using filters

By default, when you export DICOM files to Cloud Storage, all of the DICOM files in the specified DICOM store are exported. Similarly, when you export DICOM metadata to BigQuery, the metadata for all of the DICOM data in the specified DICOM store is exported.

You can export a subset of DICOM data or metadata using a filter. You define the filter in a filter file.

Configuring filter files

A filter file defines which DICOM files to export to Cloud Storage or BigQuery. You can configure filter files at the following levels:

  • At the study level
  • At the series level
  • At the instance level

The filter file is made up of multiple lines with each line defining the study, series, or instance you want to export. Each line uses the format /studies/STUDY_UID[/series/SERIES_UID[/instances/INSTANCE_UID]].

If a study, series, or instance is not specified in the filter file when you pass in the filter file, that study, series, or instance will not be exported.

Only the /studies/STUDY_UID portion of the path is required. You can export an entire study by specifying /studies/STUDY_UID, or you can export an entire series by specifying /studies/STUDY_UID/series/SERIES_UID.

Consider the following filter file. The filter file will result in one study, two series, and three individual instances being exported: 

/studies/1.123.456.789
/studies/1.666.333.111/series/123.456
/studies/1.666.333.111/series/567.890
/studies/1.888.999.222/series/123.456/instances/111
/studies/1.888.999.222/series/123.456/instances/222
/studies/1.888.999.222/series/123.456/instances/333

Creating a filter file using BigQuery

You typically create a filter file by first exporting the metadata from a DICOM store to BigQuery. This lets you use BigQuery to view the study, series, and instance UIDs of the DICOM data in your DICOM store. You can then complete the following steps:

  1. Query for the study, series, and instance UIDs you are interested in. For example, after exporting DICOM metadata to BigQuery, you could run the following query to concatenate the study, series, and instance UIDs to a format that's compatible with the filter file requirements: 
    SELECT CONCAT
    ('/studies/', StudyInstanceUID, '/series/', SeriesInstanceUID, '/instances/', SOPInstanceUID)
FROM
    [PROJECT_ID:BIGQUERY_DATASET.BIGQUERY_TABLE]
  2. If the query returns a large result set, you can materialize a new table by saving the query results to a destination table in BigQuery.
  3. If you saved the query results to a destination table, you can save the contents of the destination table to a file and export it to Cloud Storage. For steps on how to do so, see Exporting table data. The exported file is your filter file. You use the location of the filter file in Cloud Storage when specifying the filter in the export operation.

Creating a filter file manually

You can create a filter file with custom content and upload it to a Cloud Storage bucket. You use the location of the filter file in Cloud Storage when specifying the filter in the export operation. The following sample shows how to upload a filter file to a Cloud Storage bucket using the gsutil cp command: 
gsutil cp PATH/TO/FILTER_FILE gs://BUCKET/DIRECTORY

Passing in the filter file

After you create a filter file, you can call the DICOM export operation and pass in the filter file using the REST API. The following samples show how to export DICOM metadata using a filter.

gcloud

To export DICOM metadata to BigQuery using a filter, use the gcloud beta healthcare dicom-stores export bq command:

gcloud beta healthcare dicom-stores export bq DICOM_STORE_ID \
  --dataset=DATASET_ID \
  --location=LOCATION \
  --bq-table=bq://PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID \
  --filter-config-gcs-uri=gs://BUCKET/DIRECTORY/FILTER_FILE

Replace the following:

  • DICOM_STORE_ID: the identifier for the DICOM store
  • DATASET_ID: the name of the DICOM store's parent dataset
  • LOCATION: the location of the DICOM store's parent dataset
  • PROJECT_ID: the identifier of the project that contains the BigQuery dataset
  • BIGQUERY_DATASET_ID: the name of the BigQuery dataset
  • BIGQUERY_TABLE_ID: a name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how the Cloud Healthcare API writes data to the BigQuery table.
  • BUCKET/DIRECTORY/FILTER_FILE: the location of the filter file in a Cloud Storage bucket

The output is the following:

Request issued for: [DICOM_STORE_ID]
Waiting for operation [projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID] to complete...done.
name: projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID

To view the status of the operation, run the gcloud healthcare operations describe command and provide OPERATION_ID from the response:

gcloud healthcare operations describe OPERATION_ID \
  --location=LOCATION \
  --dataset=DATASET_ID

Replace the following:

  • OPERATION_ID: the ID number returned from the previous response
  • DATASET_ID: the name of the DICOM store's parent dataset
  • LOCATION: the location of the DICOM store's parent dataset

The output is the following:

done: true
metadata:
  '@type': type.googleapis.com/google.cloud.healthcare.v1beta1.OperationMetadata
  apiMethodName: google.cloud.healthcare.v1beta1.dicom.DicomService.ExportDicomData
  createTime: 'CREATE_TIME'
  endTime: 'END_TIME',
  logsUrl: 'https://console.cloud.google.com/logs/viewer/CLOUD_LOGGING_URL'
name: projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID
response:
  '@type': '...'

API

To export DICOM metadata to BigQuery using a filter, use the projects.locations.datasets.dicomStores.export method.

curl

To export DICOM metadata using a filter, make a POST request and specify the following information:

  • The name and location of the parent dataset
  • The name of the DICOM store
  • The location of the filter file in a Cloud Storage bucket
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how the Cloud Healthcare API writes data to the BigQuery table.
  • One of the following values for the writeDisposition enum:
    • WRITE_EMPTY: only export data if the destination tables are empty. This is the default.
    • WRITE_TRUNCATE: erase all existing data in the tables before writing the instances.
    • WRITE_APPEND: append data to the existing tables.

The following sample shows a POST request using curl.

curl -X POST \
    -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
    -H "Content-Type: application/json; charset=utf-8" \
    --data "{
      'bigqueryDestination': {
        'tableUri': 'bq://PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID'
      },
      'filterConfig': {
        'resourcePathsGcsUri': 'gs://BUCKET/DIRECTORY/FILTER_FILE'
      },
      'writeDisposition': '{WRITE_EMPTY|WRITE_TRUNCATE|WRITE_APPEND}'
    }" "https://healthcare.googleapis.com/v1beta1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID:export"

If the request is successful, the server returns the response in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID"
}

The response contains an operation name. To track the status of the operation, you can use the Operation get method:

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

If the request is successful, the server returns a response with the status of the operation in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID",
  "metadata": {
    "@type": "type.googleapis.com/google.cloud.healthcare.v1beta1.OperationMetadata",
    "apiMethodName": "google.cloud.healthcare.v1beta1.dicom.DicomService.ExportDicomData",
    "createTime": "CREATE_TIME",
    "endTime": "END_TIME",
    "logsUrl": "https://console.cloud.google.com/logs/viewer/CLOUD_LOGGING_URL"
  },
  "done": true,
  "response": {
    "@type": "..."
  }
}

PowerShell

To export DICOM metadata using a filter, make a POST request and specify the following information:

  • The name and location of the parent dataset
  • The name of the DICOM store
  • The location of the filter file in a Cloud Storage bucket.
  • A name for the BigQuery export table. The name can contain only letters (upper or lower case), numbers, and underscores. See Setting the BigQuery destination for an explanation of how the Cloud Healthcare API writes data to the BigQuery table.
  • One of the following values for the writeDisposition enum:
    • WRITE_EMPTY: only export data if the destination tables are empty. This is the default.
    • WRITE_TRUNCATE: erase all existing data in the tables before writing the instances.
    • WRITE_APPEND: append data to the existing tables.

The following sample shows a POST request using Windows PowerShell.

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

Invoke-WebRequest `
  -Method Post `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -Body "{
    'bigqueryDestination': {
      'tableUri': 'bq://PROJECT_ID.BIGQUERY_DATASET_ID.BIGQUERY_TABLE_ID'
    },
    'filterConfig': {
      'resourcePathsGcsUri': 'gs://BUCKET/DIRECTORY/FILTER_FILE'
    },
    'writeDisposition': '{WRITE_EMPTY|WRITE_TRUNCATE|WRITE_APPEND}'
  }" `
  -Uri "https://healthcare.googleapis.com/v1beta1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/dicomStores/DICOM_STORE_ID:export" | Select-Object -Expand Content

If the request is successful, the server returns the response in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID"
}

The response contains an operation name. To track the status of the operation, you can use the Operation get method:

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

Invoke-WebRequest `
  -Method Get `
  -Headers $headers `
  -Uri "https://healthcare.googleapis.com/v1beta1/projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID" | Select-Object -Expand Content

If the request is successful, the server returns a response with the status of the operation in JSON format:

{
  "name": "projects/PROJECT_ID/locations/LOCATION/datasets/DATASET_ID/operations/OPERATION_ID",
  "metadata": {
    "@type": "type.googleapis.com/google.cloud.healthcare.v1beta1.OperationMetadata",
    "apiMethodName": "google.cloud.healthcare.v1beta1.dicom.DicomService.ExportDicomData",
    "createTime": "CREATE_TIME",
    "endTime": "END_TIME",
    "logsUrl": "https://console.cloud.google.com/logs/viewer/CLOUD_LOGGING_URL"
  },
  "done": true,
  "response": {
    "@type": "..."
  }
}

Querying and analyzing DICOM data in BigQuery

After exporting the DICOM metadata to BigQuery, you can run sample queries to get more details about the metadata. The following samples show how to run queries for some common use cases.

Searching across metadata

Suppose that you want to search across a large amount of metadata that is difficult to search for in other systems, such as a Picture Archiving and Communication System (PACS) or Vendor Neutral Archive (VNA). The following query shows how to query on a patient's PatientID value and use the DICOMweb path to retrieve specific image instances. The sample uses the chc-nih-chest-xray.nih_chest_xray.nih_chest_xray table from the NIH Chest X-ray dataset.

#standardSQL
SELECT CONCAT('/dicomWeb/studies/', StudyInstanceUID, '/series/', SeriesInstanceUID, '/instances/', SOPInstanceUID) as DICOMwebPath
FROM `chc-nih-chest-xray.nih_chest_xray.nih_chest_xray`
WHERE PatientID = '19045';

The query returns the studies associated with the patient. The following response uses JSON formatting:

[
  {
    "DICOMwebPath": "/dicomWeb/studies/1.3.6.1.4.1.11129.5.5.169629990647803559688464142879817265366193/series/1.3.6.1.4.1.11129.5.5.141990184899344268273968625887396932057061/instances/1.3.6.1.4.1.11129.5.5.162448513493627342869165322873398445570578"
  },
  {
    "DICOMwebPath": "/dicomWeb/studies/1.3.6.1.4.1.11129.5.5.114160532832901355654444239008681456919023/series/1.3.6.1.4.1.11129.5.5.178361108150351071908200174504411112440700/instances/1.3.6.1.4.1.11129.5.5.145959606905209488520697484018030440952428"
  },
  {
    "DICOMwebPath": "/dicomWeb/studies/1.3.6.1.4.1.11129.5.5.177801331756958922168115732894519725643007/series/1.3.6.1.4.1.11129.5.5.134128639331055702643451404466208677561042/instances/1.3.6.1.4.1.11129.5.5.148534317486838863760908141408862094292875"
  },
  {
    "DICOMwebPath": "/dicomWeb/studies/1.3.6.1.4.1.11129.5.5.119570482687494886334491471870663517807852/series/1.3.6.1.4.1.11129.5.5.148050768676645373034111775531663876425927/instances/1.3.6.1.4.1.11129.5.5.111153708388576066195389700503245704293300"
  },
  {
    "DICOMwebPath": "/dicomWeb/studies/1.3.6.1.4.1.11129.5.5.144704399171290022427247626928443085419319/series/1.3.6.1.4.1.11129.5.5.190285793344339390593165731988543561416633/instances/1.3.6.1.4.1.11129.5.5.110245902713751842026864359179754889505217"
  },
  {
    "DICOMwebPath": "/dicomWeb/studies/1.3.6.1.4.1.11129.5.5.172276126220388966649736649950361623806435/series/1.3.6.1.4.1.11129.5.5.171512371498506519035489729484464872160452/instances/1.3.6.1.4.1.11129.5.5.111721417729733087384317002785068394901165"
  }
]

Querying for the latest studies

Suppose that you want to populate a PACS reading worklist with the latest studies from your dataset.

The following query shows how to retrieve and display the latest studies along with instance counts and surrounding metadata. The sample uses the chc-tcia:lungct_diagnosis.lungct_diagnosis table from the TCIA LungCT-Diagnosis dataset.

#standardSQL
SELECT MIN(CONCAT(StudyDate, ' ', StudyTime)) as StudyDateTime, MIN(PatientID) as PatientID, StudyInstanceUID, COUNT(*) as InstanceCount
FROM `chc-tcia.lungct_diagnosis.lungct_diagnosis`
GROUP BY StudyInstanceUID
ORDER BY StudyDateTime DESC
LIMIT 20;

The query returns the following information:

  • The 20 latest studies that entered the system and when they entered the system
  • The patient associated with each study
  • The study's UID
  • The number of instances associated with the study

The following response uses JSON formatting:

[
  {
    "StudyDateTime": "1998-09-24 07:59:11",
    "PatientID": "R_006",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.203059346048546067166621241946",
    "InstanceCount": "130"
  },
  {
    "StudyDateTime": "1998-09-19 15:02:00",
    "PatientID": "R_168",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.339960057327890022720983572187",
    "InstanceCount": "73"
  },
  {
    "StudyDateTime": "1998-09-03 13:59:23",
    "PatientID": "R_232",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.339835546587977846672632754158",
    "InstanceCount": "74"
  },
  {
    "StudyDateTime": "1998-08-20 09:54:23",
    "PatientID": "R_210",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.296738593990263872210071427126",
    "InstanceCount": "108"
  },
  {
    "StudyDateTime": "1998-08-17 15:22:14",
    "PatientID": "R_053",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.517417975270684537124932347957",
    "InstanceCount": "104"
  },
  {
    "StudyDateTime": "1998-08-03 08:53:02",
    "PatientID": "R_043",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.227672022111453893046049224932",
    "InstanceCount": "111"
  },
  {
    "StudyDateTime": "1998-07-24 10:01:17",
    "PatientID": "R_141",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.179196140853257709306370614304",
    "InstanceCount": "110"
  },
  {
    "StudyDateTime": "1998-06-29 09:18:16",
    "PatientID": "R_069",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.272642986942028254801481747252",
    "InstanceCount": "118"
  },
  {
    "StudyDateTime": "1998-06-27 12:47:58",
    "PatientID": "R_233",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.681962696010777092272412255441",
    "InstanceCount": "65"
  },
  {
    "StudyDateTime": "1998-06-13 11:25:35",
    "PatientID": "R_075",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.905893527127370577828717624475",
    "InstanceCount": "112"
  },
  {
    "StudyDateTime": "1998-06-06 12:16:24",
    "PatientID": "R_029",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.125721920632162119492941634336",
    "InstanceCount": "109"
  },
  {
    "StudyDateTime": "1998-04-30 10:52:34",
    "PatientID": "R_116",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.140526923029511055644251155499",
    "InstanceCount": "115"
  },
  {
    "StudyDateTime": "1998-04-11 08:55:15",
    "PatientID": "R_014",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.231039938881330096566986102847",
    "InstanceCount": "76"
  },
  {
    "StudyDateTime": "1998-04-06 13:48:50",
    "PatientID": "R_061",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.148444392206166653380348096858",
    "InstanceCount": "70"
  },
  {
    "StudyDateTime": "1998-04-05 12:57:54",
    "PatientID": "R_126",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.803397998355063686023109438391",
    "InstanceCount": "71"
  },
  {
    "StudyDateTime": "1998-03-21 13:23:15",
    "PatientID": "R_093",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.201986527949877334068747060981",
    "InstanceCount": "65"
  },
  {
    "StudyDateTime": "1998-03-06 13:27:51",
    "PatientID": "R_065",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.233872568071694592857630274388",
    "InstanceCount": "69"
  },
  {
    "StudyDateTime": "1998-03-06 09:09:43",
    "PatientID": "R_191",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.243097352990517043299166705830",
    "InstanceCount": "76"
  },
  {
    "StudyDateTime": "1998-01-14 14:59:23",
    "PatientID": "R_237",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.213658277730427015624893071198",
    "InstanceCount": "68"
  },
  {
    "StudyDateTime": "1998-01-02 14:00:00",
    "PatientID": "R_078",
    "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.4320.5030.200669196334798686049957852894",
    "InstanceCount": "87"
  }
]

Limitations and additional behavior

DICOM tags will be listed in a separate column (called DroppedTags.TagName) in the destination BigQuery table if the DICOM tag does not have a supported type in BigQuery (listed in Excluded VRs).

Troubleshooting DICOM export requests

If errors occur during a DICOM export metadata to BigQuery request, the errors are logged to Cloud Logging. For more information, see Viewing error logs in Cloud Logging.