Parquet Files¶

# The one published Parquet dataset, overwritten every release; manifest.json names its release.
BASE="https://data.clinvar-gkm.org/v1/1.1/parquet"
mkdir -p clinvar-gkm-parquet && cd clinvar-gkm-parquet
curl -O "${BASE}/manifest.json"

# Download specific sections
curl -O "${BASE}/scv.parquet"
curl -O "${BASE}/propositionPathogenicity.parquet"
curl -O "${BASE}/condition.parquet"

# Or download all 22 sections
for section in sequenceReference location allele copyNumberCount copyNumberChange \
               gene variation condition conditionSet therapy therapyGroup submitter \
               propositionPathogenicity propositionOncogenicity propositionSomaticClinicalImpact propositionClinvarCustom \
               evidenceLine vcv_evidenceLine rcv_evidenceLine \
               scv vcv rcv; do
  curl -O "${BASE}/${section}.parquet"
done

# Install DuckDB
brew install duckdb   # macOS
# or: pip install duckdb

# Query SCV statements
duckdb -c "
  SELECT id, classification.name AS classification, direction, strength.name AS strength, quality.name AS quality
  FROM 'scv.parquet'
  WHERE classification.name = 'Pathogenic'
  LIMIT 10;
"

# Count classifications across all SCVs
duckdb -c "
  SELECT classification.name AS classification, direction, COUNT(*) as n
  FROM 'scv.parquet'
  GROUP BY classification.name, direction
  ORDER BY n DESC;
"

# Join SCVs with propositions to find pathogenic variants for a specific condition
duckdb -c "
  SELECT s.id, s.classification.name AS classification, p.predicate, p.object_id
  FROM 'scv.parquet' s
  JOIN 'propositionPathogenicity.parquet' p ON s.proposition_id = p.id
  WHERE s.classification.name = 'Pathogenic'
    AND p.object_id = 'clinvar.trait:9580'
  LIMIT 10;
"

# Inspect the schema of any section
duckdb -c "DESCRIBE SELECT * FROM 'scv.parquet';"

import duckdb

df = duckdb.sql("""
    SELECT id, classification.name AS classification, direction, strength.name AS strength, quality.name AS quality
    FROM 'scv.parquet'
    WHERE classification.name = 'Pathogenic'
    LIMIT 100
""").df()

print(df)

import pandas as pd

# Load a section into a DataFrame
scv = pd.read_parquet("scv.parquet")

# Filter pathogenic SCVs — classification is a MappableConcept struct (dict), so read .name
pathogenic = scv[scv["classification"].map(lambda c: c and c.get("name")) == "Pathogenic"]
print(f"{len(pathogenic)} pathogenic SCVs")

# Access the full JSON when you need nested fields
import json
record = json.loads(pathogenic.iloc[0]["data"])
print(record["proposition"])

-- SCVs for BRCA1: classification, review status, condition
SELECT
    s.id AS scv_id,
    s.classification.name AS classification,
    s.direction,
    s.strength.name AS strength,
    s.quality.name AS review_status,
    p.gene_context_name AS gene,
    c.name AS condition_name,
    c.primaryCoding.code AS condition_code
FROM 'scv.parquet' s
JOIN 'propositionPathogenicity.parquet' p ON s.proposition_id = p.id
LEFT JOIN 'condition.parquet' c ON p.object_id = c.id
WHERE p.gene_context_name = 'BRCA1'
ORDER BY s.classification.name;

-- Count SCVs by classification and review status for BRCA2
SELECT
    p.gene_context_name AS gene,
    s.classification.name AS classification,
    s.quality.name AS review_status,
    s.direction,
    COUNT(*) AS scv_count
FROM 'scv.parquet' s
JOIN 'propositionPathogenicity.parquet' p ON s.proposition_id = p.id
WHERE p.gene_context_name = 'BRCA2'
GROUP BY ALL
ORDER BY scv_count DESC;

-- Only expert panel and criteria-provided SCVs for a gene
SELECT
    s.id AS scv_id,
    s.classification.name AS classification,
    s.quality.name AS review_status,
    c.name AS condition_name
FROM 'scv.parquet' s
JOIN 'propositionPathogenicity.parquet' p ON s.proposition_id = p.id
LEFT JOIN 'condition.parquet' c ON p.object_id = c.id
WHERE p.gene_context_name = 'TP53'
  AND s.quality.name IN ('criteria provided', 'reviewed by expert panel')
ORDER BY s.classification.name;

-- Compare pathogenicity classification distributions across genes
SELECT
    p.gene_context_name AS gene,
    s.classification.name AS classification,
    COUNT(*) AS n
FROM 'scv.parquet' s
JOIN 'propositionPathogenicity.parquet' p ON s.proposition_id = p.id
WHERE p.gene_context_name IN ('BRCA1', 'BRCA2', 'TP53', 'MLH1')
  AND s.quality.name = 'criteria provided'
GROUP BY gene, s.classification.name
ORDER BY gene, n DESC;

-- SCVs with submitter name and assertion method (from JSON)
SELECT
    s.id AS scv_id,
    s.classification.name AS classification,
    p.gene_context_name AS gene,
    json_extract_string(s.data, '$.contributions[0].agent.name') AS submitter,
    json_extract_string(s.data, '$.specifiedBy.name') AS method
FROM 'scv.parquet' s
JOIN 'propositionPathogenicity.parquet' p ON s.proposition_id = p.id
WHERE p.gene_context_name = 'BRCA1'
  AND s.classification.name = 'Pathogenic'
  AND s.quality.name = 'reviewed by expert panel'
LIMIT 20;