---
title: "BigQuery Guide for IDC"
description: "**Tested with:** IDC data version v23"
type: tutorial
canonical_url: https://claudary.paisolsolutions.com/tutorials/bigquery-guide
source: "Claudary"
difficulty: intermediate
author: "Claude Code Knowledge Pack"
date: 2026-07-10T11:08:27.776Z
license: CC-BY-4.0
attribution: "BigQuery Guide for IDC — Claudary (https://claudary.paisolsolutions.com/tutorials/bigquery-guide)"
---

# BigQuery Guide for IDC
**Tested with:** IDC data version v23

## Overview

# BigQuery Guide for IDC

**Tested with:** IDC data version v23

For most queries and downloads, use `idc-index` (see main SKILL.md). This guide covers BigQuery for advanced use cases requiring full DICOM metadata or complex joins.

## Prerequisites

**Requirements:**
1. Google account
2. Google Cloud project with billing enabled (first 1 TB/month free)
3. `google-cloud-bigquery` Python package or BigQuery console access

**Authentication setup:**
```bash
# Install Google Cloud SDK, then:
gcloud auth application-default login
```

## When to Use BigQuery

Use BigQuery instead of `idc-index` when you need:
- Full DICOM metadata (all 4000+ tags, not just the ~50 in idc-index)
- Complex joins across clinical data tables
- DICOM sequence attributes (nested structures)
- Queries on fields not in the idc-index mini-index
- Private DICOM elements (vendor-specific tags in OtherElements column)
- **Per-segment detail from DICOM Segmentation objects** — `idc-index` `seg_index` gives series-level metadata, but not individual segment anatomy codes; use `segmentations` BigQuery table to query by structure name
- **Quantitative measurements from DICOM SR** — radiomics features (volume, diameter, shape descriptors) without downloading and parsing SR files; no idc-index equivalent
- **Qualitative measurements from DICOM SR** — coded evaluations (malignancy rating, texture, margin) without parsing SR files; no idc-index equivalent

## Accessing IDC in BigQuery

### Dataset Structure

All IDC tables are in the `bigquery-public-data` BigQuery project.

**Current version (recommended for exploration):**
- `bigquery-public-data.idc_current.*`
- `bigquery-public-data.idc_current_clinical.*`

**Versioned datasets (recommended for reproducibility):**

- `bigquery-public-data.idc_v{IDC version}.*`
- `bigquery-public-data.idc_v{IDC version}_clinical.*`

Always use versioned datasets for reproducible research!

## Key Tables

### dicom_all
Primary table joining complete DICOM metadata with IDC-specific columns (collection_id, gcs_url, license). Contains all DICOM tags from `dicom_metadata` plus collection and administrative metadata. See [dicom_all.sql](https://github.com/ImagingDataCommons/etl_flow/blob/master/bq/generate_tables_and_views/derived_tables/BQ_Table_Building/derived_data_views/sql/dicom_all.sql) for the exact derivation.

```sql
SELECT 
  collection_id,
  PatientID,
  StudyInstanceUID, 
  SeriesInstanceUID,
  Modality,
  BodyPartExamined,
  SeriesDescription,
  gcs_url,
  license_short_name
FROM `bigquery-public-data.idc_current.dicom_all`
WHERE Modality = 'CT'
  AND BodyPartExamined = 'CHEST'
LIMIT 10
```

### Derived Tables

These tables are derived from DICOM objects (Segmentation and Structured Report) and have **no equivalent in idc-index**. Use them to query per-segment anatomy, radiomics features, and qualitative assessments without downloading DICOM files.

**segmentations** — one row per segment within a DICOM SEG object. Lets you search by anatomical structure name or DICOM coded concept. The `idc-index` `seg_index` gives series-level metadata; this table gives per-segment detail.

**measurement_groups** — one row per SR TID1500 measurement group. The parent grouping for quantitative and qualitative measurements; links measurements to segmentations and source images.

**quantitative_measurements** — one row per numeric measurement within an SR TID1500 group. Contains radiomics features (volume, diameter, shape descriptors, texture) extracted from DICOM SR without downloading or parsing SR files.

**qualitative_measurements** — one row per coded evaluation within an SR TID1500 group. Contains assessed findings (malignancy likelihood, texture, margin type) using coded concept values.

See the [Derived Tables: Detailed Documentation](#derived-tables-detailed-documentation) section below for schemas, column descriptions, and query examples.

### Collection Metadata

**original_collections_metadata** - Collection-level descriptions

```sql
SELECT
  collection_id,
  CancerTypes,
  TumorLocations,
  Subjects,
  src.source_doi,
  src.ImageTypes,
  src.license.license_short_name
FROM `bigquery-public-data.idc_current.original_collections_metadata`,
UNNEST(Sources) AS src
WHERE CancerTypes LIKE '%Lung%'
```

## Common Query Patterns

### Find Collections by Criteria

```sql
SELECT 
  collection_id,
  COUNT(DISTINCT PatientID) as patient_count,
  COUNT(DISTINCT SeriesInstanceUID) as series_count,
  ARRAY_AGG(DISTINCT Modality) as modalities
FROM `bigquery-public-data.idc_current.dicom_all`
WHERE BodyPartExamined LIKE '%BRAIN%'
GROUP BY collection_id
HAVING patient_count > 50
ORDER BY patient_count DESC
```

### Get Download URLs

```sql
SELECT
  SeriesInstanceUID,
  gcs_url
FROM `bigquery-public-data.idc_current.dicom_all`
WHERE collection_id = 'rider_pilot'
  AND Modality = 'CT'
```

### Find Studies with Multiple Modalities

```sql
SELECT
  StudyInstanceUID,
  ARRAY_AGG(DISTINCT Modality) as modalities,
  COUNT(DISTINCT SeriesInstanceUID) as series_count
FROM `bigquery-public-data.idc_current.dicom_all`
GROUP BY StudyInstanceUID
HAVING ARRAY_LENGTH(ARRAY_AGG(DISTINCT Modality)) > 1
LIMIT 100
```

### License Filtering

```sql
SELECT
  collection_id,
  license_short_name,
  COUNT(*) as instance_count
FROM `bigquery-public-data.idc_current.dicom_all`
WHERE license_short_name = 'CC BY 4.0'
GROUP BY collection_id, license_short_name
```

### Find Segmentations with Source Images

```sql
SELECT
  src.collection_id,
  seg.SeriesInstanceUID as seg_series,
  seg.SegmentedPropertyType,
  src.SeriesInstanceUID as source_series,
  src.Modality as source_modality
FROM `bigquery-public-data.idc_current.segmentations` seg
JOIN `bigquery-public-data.idc_current.dicom_all` src
  ON seg.segmented_SeriesInstanceUID = src.SeriesInstanceUID
WHERE src.collection_id = 'qin_prostate_repeatability'
LIMIT 10
```

## Derived Tables: Detailed Documentation

### segmentations

One row per segment within a DICOM Segmentation (SEG) object. Unlike `idc-index` `seg_index` (one row per SEG series), this table exposes each labeled region individually so you can search by anatomical structure or finding type.

**Key columns:**

| Column | Type | Description |
|--------|------|-------------|
| `SeriesInstanceUID` | STRING | SEG series UID |
| `SOPInstanceUID` | STRING | SEG instance UID |
| `PatientID` | STRING | Patient identifier |
| `StudyInstanceUID` | STRING | Study UID |
| `SegmentNumber` | INTEGER | Segment index within the SEG (starting from 1) |
| `SegmentedPropertyCategory` | RECORD | Coded category (e.g., "Anatomical Structure", "Morphologically Altered Structure") |
| `SegmentedPropertyType` | RECORD | Specific structure (e.g., "Liver", "Kidney", "Neoplasm") |
| `AnatomicRegion` | RECORD | Optional anatomic region modifier |
| `SegmentAlgorithmType` | STRING | AUTOMATIC, SEMIAUTOMATIC, or MANUAL |
| `SegmentAlgorithmName` | STRING (REPEATED) | Algorithm name array (e.g., ["TotalSegmentator"]) |
| `TrackingUID` | STRING | Links segment to SR measurements |
| `TrackingID` | STRING | Human-readable tracking label |
| `segmented_SeriesInstanceUID` | STRING | Source image series UID — join to `dicom_all` to get collection/modality |
| `viewer_url` | STRING | Direct IDC viewer link for the SEG |

`SegmentedPropertyCategory` and `SegmentedPropertyType` are RECORD types with sub-fields `CodeValue`, `CodingSchemeDesignator`, and `CodeMeaning`. Use `.CodeMeaning` for human-readable filtering.

**idc-index gap:** `seg_index` in idc-index has `total_segments`, `AlgorithmName`, and aggregated codes, but does not expose individual segment anatomy per row. Use this BigQuery table when you need to find SEG series that contain a specific structure (e.g., all series with a "Liver" segment).

**Discover what structures are segmented across IDC:**

```sql
SELECT
  SegmentedPropertyCategory.CodeMeaning AS category,
  SegmentedPropertyType.CodeMeaning AS structure,
  SegmentAlgorithmType,
  COUNT(DISTINCT SeriesInstanceUID) AS seg_series_count
FROM `bigquery-public-data.idc_current.segmentations`
GROUP BY 1, 2, 3
ORDER BY seg_series_count DESC
LIMIT 20
```

**Find all SEG series containing a specific structure, with source image context:**

```sql
SELECT
  seg.SeriesInstanceUID AS seg_series,
  seg.SegmentNumber,
  seg.SegmentedPropertyType.CodeMeaning AS structure,
  seg.SegmentAlgorithmType,
  seg.SegmentAlgorithmName,
  img.collection_id,
  img.PatientID,
  img.Modality,
  seg.viewer_url
FROM `bigquery-public-data.idc_current.segmentations` seg
JOIN `bigquery-public-data.idc_current.dicom_all` img
  ON seg.segmented_SeriesInstanceUID = img.SeriesInstanceUID
WHERE seg.SegmentedPropertyType.CodeMeaning = 'Liver'
  AND seg.SegmentAlgorithmType = 'AUTOMATIC'
LIMIT 20
```

**Find all segment types present in a collection:**

```sql
SELECT
  seg.SegmentedPropertyType.CodeMeaning AS structure,
  seg.SegmentAlgorithmType,
  COUNT(DISTINCT seg.SeriesInstanceUID) AS seg_series_count
FROM `bigquery-public-data.idc_current.segmentations` seg
JOIN `bigquery-public-data.idc_current.dicom_all` img
  ON seg.segmented_SeriesInstanceUID = img.SeriesInstanceUID
WHERE img.collection_id = 'nlst'
GROUP BY 1, 2
ORDER BY seg_series_count DESC
```

**Link segments to SR measurements using TrackingUID:**

```sql
-- Find segments that have corresponding SR measurements
SELECT
  seg.SeriesInstanceUID AS seg_series,
  seg.SegmentNumber,
  seg.SegmentedPropertyType.CodeMeaning AS structure,
  qm.Quantity.CodeMeaning AS measurement,
  ROUND(CAST(qm.Value AS FLOAT64), 2) AS value,
  qm.Units.CodeMeaning AS units
FROM `bigquery-public-data.idc_current.segmentations` seg
JOIN `bigquery-public-data.idc_current.quantitative_measurements` qm
  ON seg.SeriesInstanceUID = qm.segmentationSeriesUID
  AND seg.SegmentNumber = qm.segmentationSegmentNumber
WHERE seg.SegmentedPropertyType.CodeMeaning = 'Neoplasm'
  AND qm.Quantity.CodeMeaning = 'Volume from Voxel Summation'
LIMIT 10
```

---

### quantitative_measurements

One row per numeric measurement in a DICOM SR TID1500 Measurement Report. Contains radiomics features (shape, intensity, texture) and clinical measurements (volume, diameter, SUV). These measurements are pre-extracted from SR — no download or DICOM parsing needed.

**No idc-index equivalent.** This table is only accessible via BigQuery.

**Key columns:**

| Column | Type | Description |
|--------|------|-------------|
| `SOPInstanceUID` | STRING | SR instance UID |
| `SeriesInstanceUID` | STRING | SR series UID — join to `dicom_all` for collection/modality |
| `SeriesDescription` | STRING | SR series description (e.g., "TotalSegmentator(v1.5.6) shape Measurements") |
| `PatientID` | STRING | Patient identifier |
| `measurementGroup_number` | INTEGER | Group index within the SR (0-based); join key with `measurement_groups` and `qualitative_measurements` |
| `Quantity` | RECORD | What was measured — `CodeValue`, `CodingSchemeDesignator`, `CodeMeaning` (e.g., "Volume from Voxel Summation") |
| `Value` | NUMERIC | The numeric measurement value |
| `Units` | RECORD | Units — `CodeMeaning` (e.g., "cubic millimeter", "no units", "Hounsfield Unit") |
| `derivationModifier` | RECORD | How the value was derived (e.g., "Mean", "Minimum", "Maximum") |
| `lateralityModifier` | RECORD | Laterality qualifier |
| `finding` | RECORD | What finding was measured — `CodeMeaning` (e.g., "Nodule", "Organ", "Anatomical Structure") |
| `findingSite` | RECORD | Where the finding is — `CodeMeaning` (e.g., "Liver", "Esophagus", "Lung") |
| `trackingIdentifier` | STRING | Human-readable tracking label (e.g., "Nodule 1", "Measurements group 26") |
| `trackingUniqueIdentifier` | STRING | Tracking UID — links back to `segmentations.TrackingUID` |
| `segmentationInstanceUID` | STRING | SOPInstanceUID of the referenced SEG object |
| `segmentationSeriesUID` | STRING | SeriesInstanceUID of the referenced SEG object |
| `segmentationSegmentNumber` | INTEGER | Segment number within the SEG — join to `segmentations.SegmentNumber` |
| `sourceSegmentedSeriesUID` | STRING | Source image series — join to `dicom_all.SeriesInstanceUID` |

**Discover available measurement types:**

```sql
SELECT
  Quantity.CodeMeaning AS measurement,
  Units.CodeMeaning AS units,
  COUNT(*) AS measurement_count,
  COUNT(DISTINCT SeriesInstanceUID) AS sr_series_count
FROM `bigquery-public-data.idc_current.quantitative_measurements`
GROUP BY 1, 2
ORDER BY measurement_count DESC
LIMIT 20
```

**Query measurements for a specific structure (e.g., liver volume across collections):**

```sql
SELECT
  qm.PatientID,
  ROUND(CAST(qm.Value AS FLOAT64) / 1000, 1) AS volume_cm3,
  img.collection_id,
  qm.segmentationSeriesUID
FROM `bigquery-public-data.idc_current.quantitative_measurements` qm
JOIN `bigquery-public-data.idc_current.dicom_all` img
  ON qm.sourceSegmentedSeriesUID = img.SeriesInstanceUID
WHERE qm.Quantity.CodeMeaning = 'Volume from Voxel Summation'
  AND qm.findingSite.CodeMeaning = 'Liver'
ORDER BY volume_cm3 DESC
LIMIT 20
```

**Retrieve all measurements for a specific patient and finding:**

```sql
SELECT
  qm.measurementGroup_number,
  qm.finding.CodeMeaning AS finding,
  qm.findingSite.CodeMeaning AS finding_site,
  qm.lateralityModifier.CodeMeaning AS laterality,
  qm.Quantity.CodeMeaning AS feature,
  ROUND(CAST(qm.Value AS FLOAT64), 3) AS value,
  qm.Units.CodeMeaning AS units
FROM `bigquery-public-data.idc_current.quantitative_measurements` qm
WHERE qm.PatientID = 'LIDC-IDRI-0001'
  AND qm.finding.CodeMeaning = 'Nodule'
ORDER BY qm.measurementGroup_number, qm.Quantity.CodeMeaning
```

---

### qualitative_measurements

One row per coded evaluation in a DICOM SR TID1500 Measurement Report. Instead of numeric values, these record assessed characteristics using coded concept pairs (e.g., Quantity="Malignancy", Value="4 out of 5 (Moderately Suspicious for Cancer)").

**No idc-index equivalent.** This table is only accessible via BigQuery.

**Key columns:**

| Column | Type | Description |
|--------|------|-------------|
| `SOPInstanceUID` | STRING | SR instance UID |
| `SeriesInstanceUID` | STRING | SR series UID — join to `dicom_all` for collection/modality |
| `PatientID` | STRING | Patient identifier |
| `measurementGroup_number` | INTEGER | Group index within the SR — join key with `quantitative_measurements` |
| `Quantity` | RECORD | What was assessed — `CodeMeaning` (e.g., "Malignancy", "Calcification", "Texture") |
| `Value` | RECORD | The coded answer — `CodeMeaning` (e.g., "4 out of 5 (Moderately Suspicious for Cancer)") |
| `finding` | RECORD | What finding was assessed — `CodeMeaning` (e.g., "Nodule") |
| `findingSite` | RECORD | Anatomic site — `CodeMeaning` (e.g., "Lung") |
| `trackingIdentifier` | STRING | Human-readable tracking label |
| `segmentationInstanceUID` | STRING | SOPInstanceUID of the referenced SEG object |
| `segmentationSeriesUID` | STRING | SeriesInstanceUID of the referenced SEG object |
| `segmentationSegmentNum

---

Source: [Claudary](https://claudary.paisolsolutions.com/tutorials/bigquery-guide) · https://claudary.paisolsolutions.com