Intervall-JOINs mit BigQuery ausführen

Sie können BigQuery verwenden, um eine JOIN-Abfrage über Varianten mit Daten durchzuführen, die durch Intervalle in Genomregionen – sogenannte Überlappungen (Overlaps) – beschrieben werden. Auf dieser Seite wird gezeigt, wie Sie mithilfe einer komplexen JOIN-Abfrage anhand einer Liste mit Genbezeichnungen

  • die seltenen SNPs überlappenden Gene finden,
  • über alle DNA-Proben hinweg 100.000 Basenpaare auf jeder Seite eines Gens finden.

Es werden drei Abfragen vorgestellt, die jeweils zeigen, wie BigQuery für verschiedene Mengen von genomischen Daten skaliert:

Die Daten stammen aus der fast neun Milliarden Zeilen umfassenden Tute Genomics Annotation-Tabelle und dem Illumina Platinum Genomes-Dataset. Weitere Informationen zu diesen Datasets finden Sie hier:

Intervall-JOIN mit einer Inline-Tabelle ausführen

In diesem Beispiel wird die in der Abfrage definierte Intervalltabelle intervals verwendet. Es wird gezeigt, wie Sie für eine Tabelle, die Varianten aus Illumina Platinum Genomes enthält, eine JOIN-Abfrage ausführen:

  1. Rufen Sie die BigQuery-UI auf.

    Zur BigQuery-UI

  2. Klicken Sie auf Abfrage erstellen.

  3. Geben Sie im Feld Neue Abfrage folgende Abfrage ein:

    #standardSQL
WITH
  --
  -- Retrieve the variants in this cohort, flattening by alternate bases and
  -- counting affected alleles.
  variants AS (
  SELECT
    REPLACE(reference_name, 'chr', '') as reference_name,
    start_position,
    end_position,
    reference_bases,
    alternate_bases.alt AS alt,
    (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles,
    (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles
  FROM
    `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,
    UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ),
  --
  -- Define an inline table that uses five rows
  -- selected from silver-wall-555.TuteTable.hg19.
  intervals AS (
    SELECT * FROM UNNEST ([
    STRUCT<Gene STRING, Chr STRING, gene_start INT64, gene_end INT64, region_start INT64, region_end INT64>
    ('PRCC', '1', 156736274, 156771607, 156636274, 156871607),
    ('NTRK1', '1', 156785541, 156852640, 156685541, 156952640),
    ('PAX8', '2', 113972574, 114037496, 113872574, 114137496),
    ('FHIT', '3', 59734036, 61238131, 59634036, 61338131),
    ('PPARG', '3', 12328349, 12476853, 12228349, 12576853)
  ])),
  --
  -- JOIN the variants with the genomic intervals overlapping
  -- the genes of interest.
  --
  -- The JOIN criteria is complicated because the task is to see if
  -- an SNP overlaps an interval.  With standard SQL you can use complex
  -- JOIN predicates, including arbitrary expressions.
  gene_variants AS (
  SELECT
    reference_name,
    start_position,
    reference_bases,
    alt,
    num_variant_alleles,
    total_num_alleles
  FROM
    variants
  INNER JOIN
    intervals ON
    variants.reference_name = intervals.Chr
    AND intervals.region_start <= variants.start_position
    AND intervals.region_end >= variants.end_position )
  --
  -- And finally JOIN the variants in the regions of interest
  -- with annotations for rare variants.
SELECT DISTINCT
  Chr,
  annots.Start AS Start,
  Ref,
  annots.Alt,
  Func,
  Gene,
  PopFreqMax,
  ExonicFunc,
  num_variant_alleles,
  total_num_alleles
FROM
  `silver-wall-555.TuteTable.hg19` AS annots
INNER JOIN
  gene_variants AS vars
ON
  vars.reference_name = annots.Chr
  AND vars.start_position = annots.Start
  AND vars.reference_bases = annots.Ref
  AND vars.alt = annots.Alt
WHERE
  -- Retrieve annotations for rare variants only.
  PopFreqMax <= 0.01
ORDER BY
  Chr,
  Start;

  4. Klicken Sie auf Abfrage ausführen. Die Abfrage dauert ca. zehn Sekunden. Dabei werden rund 334 GB an Daten verarbeitet. Die Abfrage liefert als Ergebnis seltene Varianten innerhalb der Kohorte, die mit den betrachteten Regionen überlappen.

    Eine ähnliche Abfrage mit Daten aus 1000 Genomes Phase 3 dauert ca. 90 Sekunden, wobei rund 3,38 TB an Daten verarbeitet werden.

Materialisierte Tabelle verwenden

Wenn Sie mit sehr großen Big-Data-Datasets arbeiten, können Sie eine Intervalltabelle materialisieren und eine JOIN-Abfrage über die neue Tabelle ausführen.

Intervall-JOIN für bestimmte Gene ausführen

Im folgenden Beispiel wird eine neue Intervalltabelle materialisiert, die eine Liste bestimmter Gene aus der Tabelle silver-wall-555:TuteTable.hg19 enthält:

  1. Rufen Sie die BigQuery-UI auf.

    Zur BigQuery-UI

  2. Gehen Sie so vor, um ein Dataset zu erstellen:

    1. Klicken Sie im Navigationsbereich neben Ihrem Google Cloud-Projekt auf den Abwärtspfeil und anschließend auf Neues Dataset erstellen.

    2. Geben Sie unter Dataset ID genomics ein.

    3. Behalten Sie die anderen Standardeinstellungen bei und klicken Sie auf OK.

  3. Gehen Sie so vor, um die Intervalltabelle zu erstellen:

    1. Klicken Sie in BigQuery auf Abfrage erstellen.

    2. Geben Sie im Feld Neue Abfrage die folgende Abfrage ein. Damit wird ein Ausschnitt der Tabelle "silver-wall-555:TuteTable.hg19" in der neuen Intervalltabelle genomics.myIntervalTable materialisiert:

      #standardSQL
CREATE TABLE `genomics.myIntervalTable` AS (
SELECT
  Gene,
  Chr,
  MIN(Start) AS gene_start,
  MAX(`End`) AS gene_end,
  MIN(Start)-100000 AS region_start,
  MAX(`End`)+100000 AS region_end
FROM
  `silver-wall-555.TuteTable.hg19`
WHERE
  Gene IN ('APC', 'ATM', 'BMPR1A', 'BRCA1', 'BRCA2', 'CDK4',
  'CDKN2A', 'CREBBP', 'EGFR', 'EP300', 'ETV6', 'FHIT', 'FLT3',
  'HRAS', 'KIT', 'MET', 'MLH1', 'NTRK1', 'PAX8', 'PDGFRA',
  'PPARG', 'PRCC', 'PRKAR1A', 'PTEN', 'RET', 'STK11',
  'TFE3', 'TGFB1', 'TGFBR2', 'TP53', 'WWOX')
GROUP BY
  Chr,
  Gene );
      1. Klicken Sie auf Abfrage ausführen.

  4. Geben Sie im Feld Neue Abfrage die folgende Abfrage ein:

    #standardSQL
WITH
  --
  -- Retrieve the variants in this cohort, flattening by alternate bases and
  -- counting affected alleles.
  variants AS (
  SELECT
    REPLACE(reference_name, 'chr', '') as reference_name,
    start_position,
    end_position,
    reference_bases,
    alternate_bases.alt AS alt,
    (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles,
    (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles
  FROM
    `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,
    UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ),
  --
  -- JOIN the variants with the genomic intervals overlapping
  -- the genes of interest.
  --
  -- The JOIN criteria is complicated because the task is to see if
  -- an SNP overlaps an interval.  With standard SQL you can use complex
  -- JOIN predicates, including arbitrary expressions.
  gene_variants AS (
  SELECT
    reference_name,
    start_position,
    reference_bases,
    alt,
    num_variant_alleles,
    total_num_alleles
  FROM
    variants
  INNER JOIN
    `genomics.myIntervalTable` AS intervals ON
    variants.reference_name = intervals.Chr
    AND intervals.region_start <= variants.start_position
    AND intervals.region_end >= variants.end_position )
  --
  -- And finally JOIN the variants in the regions of interest
  -- with annotations for rare variants.
SELECT DISTINCT
  Chr,
  annots.Start AS Start,
  Ref,
  annots.Alt,
  Func,
  Gene,
  PopFreqMax,
  ExonicFunc,
  num_variant_alleles,
  total_num_alleles
FROM
  `silver-wall-555.TuteTable.hg19` AS annots
INNER JOIN
  gene_variants AS vars
ON
  vars.reference_name = annots.Chr
  AND vars.start_position = annots.Start
  AND vars.reference_bases = annots.Ref
  AND vars.alt = annots.Alt
WHERE
  -- Retrieve annotations for rare variants only.
  PopFreqMax <= 0.01
ORDER BY
  Chr,
  Start;

  5. Klicken Sie auf Abfrage ausführen. Die Abfrage dauert ca. zehn Sekunden. Dabei werden rund 334 GB an Daten verarbeitet. Die Abfrage liefert als Ergebnis seltene Varianten innerhalb der Kohorte, die mit den betrachteten Regionen überlappen.

    Eine ähnliche Abfrage mit Daten aus 1000 Genomes Phase 3 dauert ca. 90 Sekunden, wobei rund 3,38 TB an Daten verarbeitet werden.

Intervall-JOIN für zufällige Gene ausführen

Im folgenden Beispiel wird ein Intervall-JOIN über eine materialisierte Tabelle ausgeführt, die 250 zufällig aus der Tabelle silver-wall-555:TuteTable.hg19 ausgewählte Gene enthält:

  1. Rufen Sie die BigQuery-UI auf.

    Zur BigQuery-UI

  2. Gehen Sie so vor, um ein Dataset zu erstellen:

    1. Klicken Sie im Navigationsbereich neben Ihrem Google Cloud-Projekt auf den Abwärtspfeil und anschließend auf Neues Dataset erstellen.

    2. Geben Sie unter Dataset ID genomics ein.

    3. Behalten Sie die anderen Standardeinstellungen bei und klicken Sie auf OK.

  3. Gehen Sie so vor, um die Intervalltabelle zu erstellen:

    1. Klicken Sie in BigQuery auf Abfrage erstellen.

    2. Geben Sie im Feld Neue Abfrage die folgende Abfrage ein. Damit wird ein Ausschnitt der Tabelle "silver-wall-555:TuteTable.hg19" in der neuen Intervalltabelle genomics.randomGenesIntervalTable materialisiert:

      #standardSQL
CREATE TABLE `genomics.randomGenesIntervalTable` AS (
SELECT
  Gene,
  Chr,
  MIN(Start) AS gene_start,
  MAX(`End`) AS gene_end,
  MIN(Start) - 100000 AS region_start,
  MAX(`End`) + 100000 AS region_end
FROM
  `silver-wall-555.TuteTable.hg19`
WHERE
  Gene IN (SELECT Gene FROM `silver-wall-555.TuteTable.hg19` GROUP BY Gene LIMIT 250)
GROUP BY
  Chr,
  Gene );
      1. Klicken Sie auf Abfrage ausführen.

  4. Geben Sie im Feld Neue Abfrage die folgende Abfrage ein:

    #standardSQL
WITH
  --
  -- Retrieve the variants in this cohort, flattening by alternate bases and
  -- counting affected alleles.
  variants AS (
  SELECT
    REPLACE(reference_name, 'chr', '') as reference_name,
    start_position,
    end_position,
    reference_bases,
    alternate_bases.alt AS alt,
    (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles,
    (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles
  FROM
    `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,
    UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ),
  --
  -- JOIN the variants with the genomic intervals overlapping
  -- the genes of interest.
  --
  -- The JOIN criteria is complicated because the task is to see if
  -- an SNP overlaps an interval.  With standard SQL you can use complex
  -- JOIN predicates, including arbitrary expressions.
  gene_variants AS (
  SELECT
    reference_name,
    start_position,
    reference_bases,
    alt,
    num_variant_alleles,
    total_num_alleles
  FROM
    variants
  INNER JOIN
    `genomics.randomGenesIntervalTable` AS intervals ON
    variants.reference_name = intervals.Chr
    AND intervals.region_start <= variants.start_position
    AND intervals.region_end >= variants.end_position )
  --
  -- And finally JOIN the variants in the regions of interest
  -- with annotations for rare variants.
SELECT DISTINCT
  Chr,
  annots.Start AS Start,
  Ref,
  annots.Alt,
  Func,
  Gene,
  PopFreqMax,
  ExonicFunc,
  num_variant_alleles,
  total_num_alleles
FROM
  `silver-wall-555.TuteTable.hg19` AS annots
INNER JOIN
  gene_variants AS vars
ON
  vars.reference_name = annots.Chr
  AND vars.start_position = annots.Start
  AND vars.reference_bases = annots.Ref
  AND vars.alt = annots.Alt
WHERE
  -- Retrieve annotations for rare variants only.
  PopFreqMax <= 0.01
ORDER BY
  Chr,
  Start;

  5. Klicken Sie auf Abfrage ausführen. Die Abfrage dauert ca. zehn Sekunden. Dabei werden rund 334 GB an Daten verarbeitet. Die Abfrage liefert als Ergebnis seltene Varianten innerhalb der Kohorte, die mit den betrachteten Regionen überlappen.

    Eine ähnliche Abfrage mit Daten aus 1000 Genomes Phase 3 dauert ca. 90 Sekunden, wobei rund 3,38 TB an Daten verarbeitet werden.