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_IDThe 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 Google Cloud CLI) - Whether you have set a value for the
writeDisposition
enum. If you use thewriteDisposition
enum, do not set theforce
field.
- If the destination table already exists and
force
is set totrue
or the--overwrite-table
flag is specified, the export operation overwrites the existing table. - If the destination table already exists and
force
is set tofalse
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. ThewriteDisposition
enum performs behavior similar to the options when usingforce
, 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:
- In the Google Cloud console, go to the Datasets page.
Go to the Datasets page - Click the dataset that contains the DICOM store from which you are exporting DICOM metadata.
- In the list of data stores, choose Export from the Actions list for the DICOM store.
- On the Export DICOM Store page that appears, select BigQuery Table.
- In the Project list, select the BigQuery project.
- In the Dataset ID list, select the dataset.
- In the Table name field, enter a new table name.
BigQuery checks each selection to ensure that the destination table is valid. - In the BigQuery output options section, select
one of the following:
- Append data to existing tables: this is equivalent to the
WRITE_APPEND
value for thewriteDisposition
parameter. - Delete existing data in target DICOM tables in the dataset before exporting
resources: this is equivalent to the
WRITE_TRUNCATE
value for thewriteDisposition
parameter.
- Append data to existing tables: this is equivalent to the
- Click Export to export DICOM metadata to the defined destination in BigQuery.
- 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.
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/query/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/query/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/query/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/query/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:
-
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]
- 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.
- 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 thegsutil 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/query/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/query/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/query/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.