Erweiterter Leitfaden zur Analyse von Varianten mit BigQuery

Auf dieser Seite werden erweiterte Methoden für die Analyse von Varianten mit BigQuery beschrieben. Einige der auf dieser Seite verlinkten Ressourcen stehen ggf. nur auf Englisch zur Verfügung.

Die Daten in dieser Anleitung stammen aus dem Projekt Illumina Platinum Genomes. Die Daten wurden in eine BigQuery-Tabelle geladen, in der das BigQuery-Variantenschema verwendet wird. Der Name der Tabelle lautet platinum_genomes_deepvariant_variants_20180823.

Wenn sich die Variantendaten in einer BigQuery-Tabelle befinden, die das BigQuery-Variantenschema nutzt, lassen sich die Abfragen in dieser Anleitung direkt auf Ihre Daten anwenden. Weitere Informationen zum Laden von Variantendaten in BigQuery finden Sie in der Dokumentation zur Verwendung der Transformationspipeline.

Lernziele

Diese Anleitung erläutert folgende Schritte:

• Eine Übersicht der genomischen Daten abrufen
• Herausfinden, wie Nicht-Varianten-Segmente dargestellt werden.
• Herausfinden, wie Variantenaufrufe dargestellt werden.
• Herausfinden, wie Qualitätsfilter für Variantenaufrufe dargestellt werden.
• Hierarchische Spalten zusammenführen.
• Abfragen zusammenführen.
• Unterschiedliche Zeilen zählen.
• Zeilen gruppieren.
• Benutzerdefinierte Funktionen erstellen.

Außerdem erfahren Sie in dieser Anleitung, wie Sie die folgenden Informationen finden:

• Anzahl der Zeilen in der Tabelle.
• Anzahl der Variantenaufrufe
• Die Anzahl der für jede Stichprobe aufgerufenen Varianten
• Anzahl der Stichproben
• Die Anzahl der Varianten pro Chromosom
• Die Anzahl der hochwertigen Varianten pro Stichprobe

Kosten

In diesem Dokument verwenden Sie die folgenden kostenpflichtigen Komponenten von Google Cloud:

• BigQuery

Mit dem Preisrechner können Sie eine Kostenschätzung für Ihre voraussichtliche Nutzung vornehmen. Neuen Google Cloud-Nutzern steht möglicherweise eine kostenlose Testversion zur Verfügung.

Hinweise

1. Melden Sie sich bei Ihrem Google Cloud-Konto an. Wenn Sie mit Google Cloud noch nicht vertraut sind, erstellen Sie ein Konto, um die Leistungsfähigkeit unserer Produkte in der Praxis sehen und bewerten zu können. Neukunden erhalten außerdem ein Guthaben von 300 $, um Arbeitslasten auszuführen, zu testen und bereitzustellen.

2. Wählen Sie in der Google Cloud Console auf der Seite der Projektauswahl ein Google Cloud-Projekt aus oder erstellen Sie eines.

   Hinweis: Wenn Sie die Ressourcen, die Sie in diesem Verfahren erstellen, nicht behalten möchten, erstellen Sie ein Projekt, anstatt ein vorhandenes Projekt auszuwählen. Wenn Sie fertig sind, können Sie das Projekt löschen und dadurch alle mit dem Projekt verknüpften Ressourcen entfernen.

   Zur Projektauswahl

3. Die Abrechnung für das Google Cloud-Projekt muss aktiviert sein.

4. Wählen Sie in der Google Cloud Console auf der Seite der Projektauswahl ein Google Cloud-Projekt aus oder erstellen Sie eines.

   Hinweis: Wenn Sie die Ressourcen, die Sie in diesem Verfahren erstellen, nicht behalten möchten, erstellen Sie ein Projekt, anstatt ein vorhandenes Projekt auszuwählen. Wenn Sie fertig sind, können Sie das Projekt löschen und dadurch alle mit dem Projekt verknüpften Ressourcen entfernen.

   Zur Projektauswahl

5. Die Abrechnung für das Google Cloud-Projekt muss aktiviert sein.

6. Sie sollten mit dem BigQuery-Variantenschema vertraut sein.

Tabellenschemas und -daten ansehen

Auf die Tabelle zugreifen und das Schema ansehen

Die Illumina Platinum Genomes-Tabelle platinum_genomes_deepvariant_variants_20180823 ist öffentlich verfügbar.

Varianten und Nicht-Varianten in der Tabelle

Die Illumina Platinum Genomes-Daten basieren auf dem gVCF. Die Zeilen in der Tabelle können deshalb auch Nicht-Varianten enthalten. Diese Nicht-Varianten werden auch als "Referenzaufrufe" bezeichnet.

Die Nicht-Varianten-Segmente werden in der Tabelle in der Regel so dargestellt:

• Mit einem Wert der Länge null für alternate_bases
• Mit dem Textstring <NON_REF> als Wert für alternate_bases.alt
• Mit dem Textstring <*> als Wert für alternate_bases.alt

Die Darstellung von Nicht-Varianten-Segmenten hängt normalerweise vom Variantenaufruf ab, von dem die Quelldaten erstellt wurden. Die Varianten in der Tabelle platinum_genomes_deepvariant_variants_20180823 wurde mit DeepVariant aufgerufen, wobei die Notation <*> verwendet wird.

Die folgenden Tabellen enthalten einige Zeilen mit Werten, die Nicht-Varianten-Segmente darstellen. In den Segmenten werden folgende Informationen angezeigt:

• Ein Referenzblock aus 10 Basen auf Chromosom 1
• Der Referenzblock beginnt ab Position 1000.
• Die Referenzbase an Position 1000 ist ein A.
• Die Referenzbasen an den anderen Positionen des Blocks werden nicht angezeigt.

Die Spalte alternate_bases REPEATED RECORD in der folgenden Tabelle enthält keine Werte. Sie ist also ein ARRAY der Länge 0.

reference_name	start_position	end_position	reference_bases	alternate_bases.alt
1	1000	1010	A

In der folgenden Tabelle hat die Spalte alternate_bases REPEATED RECORD die Länge 1 und enthält die literale Zeichenkette <*>.

reference_name	start_position	end_position	reference_bases	alternate_bases.alt
1	1000	1010	A	<*>

Die in diesem Leitfaden verwendeten Abfragen verwenden die Darstellungen in den vorherigen Tabellen.

Weitere Informationen zur Darstellung von Nicht-Varianten-Positionen im Genom finden Sie in der VCF-Spezifikation.

Tabellendaten ansehen

Führen Sie die folgenden Schritte aus, um die Daten in der Tabelle platinum_genomes_deepvariant_variants_20180823 aufzurufen:

1. Sehen Sie sich die Tabelle auf der BigQuery-Seite in der Google Cloud Console an.

   Zur Seite „BigQuery“

   Informationen zur Tabelle werden angezeigt. Die Tabelle enthält 19,6 GB Daten und enthält über 105.000.000 Zeilen.

2. Klicken Sie auf Vorschau, um ein paar Zeilen der Tabelle aufzurufen.

Tabelle abfragen

Nachdem Sie sich das Tabellenschema und einige Zeilen angesehen haben, können Sie Abfragen erstellen und die Daten analysieren. Bevor Sie fortfahren, sollten Sie sich mit der Standard-SQL-Abfragesyntax vertraut machen, die von BigQuery verwendet wird.

Zeilen in der Tabelle zählen

So rufen Sie die Anzahl der Zeilen in der Tabelle auf:

1. Rufen Sie in der Google Cloud Console die Seite "BigQuery" auf.

   Zur Seite „BigQuery“

2. Klicken Sie auf Abfrage erstellen.

3. Kopieren Sie die folgende Abfrage und fügen Sie sie in den Textbereich Neue Abfrage ein:

    #standardSQL
    SELECT
      COUNT(1) AS number_of_rows
    FROM
      `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

4. Klicken Sie auf Abfrage ausführen. Die Abfrage gibt das folgende Ergebnis zurück:

   Row	number_of_rows
   1	105923159

Variantenaufrufe in der Tabelle zählen

Jede Zeile in der Tabelle hat eine genomische Position, die entweder ein Varianten- oder ein Nicht-Varianten-Segment ist.

Außerdem enthält jede Zeile eine Spalte call, bei der es sich um ein ARRAY von Variantenaufrufen handelt. Jede Spalte call enthält den name und andere Werte wie Genotyp, Qualitätsspalten, Lesetiefe sowie weitere Werte, die in der Regel in einer VCF-Datei aufgeführt sind.

Wenn Sie die Anzahl der Variantenaufrufe zählen möchten, können Sie die Anzahl der Elemente in den ARRAY-Spalten abfragen. Dazu haben Sie mehrere Möglichkeiten, die als nächstes gezeigt werden. Jede Abfrage gibt den Wert 182.104.652 zurück. Durchschnittlich liegen im Dataset also 1,7 Variantenaufrufe pro Zeile vor.

Längen der call-Arrays summieren

Zählen Sie die Gesamtzahl der Variantenaufrufe in allen Stichproben. Fügen Sie dazu die Länge jedes einzelnen call-Arrays hinzu:

#standardSQL
SELECT
  SUM(ARRAY_LENGTH(call)) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

Die Abfrage gibt das folgende Ergebnis zurück:

Row	number_of_calls
1	182104652

JOIN für jede Zeile

Zählen Sie die Gesamtzahl der Variantenaufrufe in allen Stichproben. Verwenden Sie dazu für jede Zeile mit der Spalte call ein JOIN. Die Abfrage verwendet den Kommaoperator (,), der eine Kurzschreibweise für JOIN ist. Beim Zusammenführen mit der Spalte call wird außerdem ein impliziter UNNEST-Vorgang für die Spalte call ausgeführt:

#standardSQL
SELECT
  COUNT(call) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call

Die Abfrage gibt das folgende Ergebnis zurück:

Row	number_of_calls
1	182104652

name in einer call-Spalte zählen

Eine dritte Möglichkeit, die Anzahl der Variantenaufrufe in allen Stichproben zu ermitteln, besteht darin, die Werte für name in der Spalte call zu zählen. Damit Sie die folgende Abfrage ausführen können, muss in jeder Spalte call ein einziger Wert für name vorhanden sein:

#standardSQL
SELECT
  COUNT(call.name) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call

Die Abfrage gibt das folgende Ergebnis zurück:

Row	number_of_calls
1	182104652

Varianten- und Nicht-Varianten-Segmente zählen

Wenn Sie die Anzahl der Varianten- und Nicht-Varianten-Segmente in der Tabelle zählen möchten, müssen Sie zuerst eine Abfrage ausführen, um die Nicht-Varianten-Segmente herauszufiltern:

#standardSQL
SELECT
  COUNT(1) AS number_of_real_variants
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.alternate_bases) AS alt
          WHERE
            alt.alt NOT IN ("<NON_REF>", "<*>"))

Die Abfrage gibt das folgende Ergebnis zurück:

Row	number_of_real_variants
1	38549388

Wie unter Variantenaufrufe zählen angegeben, beträgt die Gesamtzahl der Variantenaufrufe in der Tabelle 182.104.652. Dieses Ergebnis zeigt, dass die Mehrheit der Zeilen in der Tabelle Nicht-Varianten-Segmente sind.

Wie im Abschnitt Varianten und Nicht-Varianten in der Tabelle gezeigt, gibt es mindestens drei Möglichkeiten für die Klassifikation einer Variantenzeile als Nicht-Varianten-Segment. In der obigen Abfrage sind in der WHERE-Klausel Zeilen enthalten, in denen die Spalte alternate_bases einen Wert aufweist, der eine echte Variante ist. Es handelt sich also nicht um einen Sonderwert zur Kennzeichnung wie <*> oder <NON_REF>.

Für jede Zeile der Tabelle wird eine Unterabfrage über die jeweilige alternate_bases-Spalte gesendet, die für jeden Wert von alternate_bases, der nicht <NON_REF> oder <*> lautet, den Wert 1 zurückgibt. Die Anzahl der Zeilen, die von der Unterabfrage zurückgegeben wird, ist die Anzahl der Variantensegmente.

In der folgenden Abfrage wird veranschaulicht, wie Sie die Anzahl der Nicht-Varianten-Segmente (number_of_non_variants) ermitteln:

#standardSQL
SELECT
  COUNT(1) AS number_of_non_variants
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
  NOT EXISTS (SELECT 1
                FROM UNNEST(v.alternate_bases) AS alt
              WHERE
                alt.alt NOT IN ("<NON_REF>", "<*>"))

Die Abfrage gibt das folgende Ergebnis zurück:

Row	number_of_non_variants
1	143555264

Die Summe aus der Anzahl der echten Varianten (38.549.388) und der Anzahl der Nicht-Varianten-Segmente (143.555.264) entspricht der Gesamtzahl der Variantenaufrufe.

Für jede Stichprobe aufgerufene Varianten zählen

Nachdem Sie die oberen Zeilen in der Tabelle untersucht haben, können Sie Abfragen für untergeordnete Zeilen ausführen. Diese Zeilen enthalten Daten wie die individuellen Stichproben, in denen Aufrufe für die Varianten durchgeführt wurden.

Jede Variante in der Tabelle hat null oder mehr Werte für call.name. Ein bestimmter Wert für call.name kann in mehreren Zeilen angezeigt werden.

Wenn Sie die Anzahl der Zeilen ermitteln möchten, in denen jedes einzelne Aufruf-Set vorkommt, können Sie die folgende Abfrage ausführen:

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(call.name) AS call_count_for_call_set
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
GROUP BY
  call_name
ORDER BY
  call_name

Sechs Zeilen werden zurückgegeben. Jeder call_name entspricht dem sequenzierten Genom eines menschlichen Individuums:

Zeile	call_name	call_count_for_call_set
1	NA12877	31592135
2	NA12878	28012646
3	NA12889	31028550
4	NA12890	30636087
5	NA12891	33487348
6	NA12892	27347886

Menschen haben in der Regel keine 30 Millionen Varianten, wie in den Werten für call_count_for_call_set dargestellt. Filtern Sie die Nicht-Varianten-Segmente heraus, um nur die Variantenzeilen zu zählen:

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(call.name) AS call_count_for_call_set
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.alternate_bases) AS alt
          WHERE
            alt.alt NOT IN ("<NON_REF>", "<*>"))
GROUP BY
  call_name
ORDER BY
  call_name

Die Abfrage gibt das folgende Ergebnis zurück:

Row	call_name	call_count_for_call_set
1	NA12877	6284275
2	NA12878	6397315
3	NA12889	6407532
4	NA12890	6448600
5	NA12891	6516669
6	NA12892	6494997

Die Anzahl der Varianten liegt jetzt näher an 6 Millionen. Dieser Wert ist für einen Menschen üblicher. Im nächsten Abschnitt können Sie die echten Varianten nach Genotyp filtern.

Echte Varianten nach Genotyp filtern

Die Varianten in der Tabelle umfassen Nicht-Aufrufe, die durch einen genotype-Wert von -1 dargestellt werden. Diese Varianten gelten nicht als echte Varianten für Einzelwerte, weshalb Sie sie herausfiltern müssen. Echte Varianten können nur Aufrufe mit Genotypen enthalten, die größer als null sind. Enthält ein Aufruf ausschließlich Genotypen, die Nicht-Aufrufe (-1) oder Referenzwerte (0) sind, sind es keine echten Varianten.

Führen Sie die folgende Abfrage aus, um die Varianten nach Genotyp zu filtern:

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(call.name) AS call_count_for_call_set
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt > 0)
  AND NOT EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt < 0)
GROUP BY
  call_name
ORDER BY
  call_name

Die Abfrage gibt das folgende Ergebnis zurück:

Row	call_name	call_count_for_call_set
1	NA12877	4486610
2	NA12878	4502017
3	NA12889	4422706
4	NA12890	4528725
5	NA12891	4424094
6	NA12892	4495753

Proben in der Tabelle zählen

Im Abschnitt Für jede Stichprobe aufgerufene Varianten zählen gab jede Abfrage sechs Zeilen mit Werten für call_name zurück. Führen Sie die folgende Abfrage aus, um den Wert für die Anzahl der Zeilen abzufragen und zu erhalten:

#standardSQL
SELECT
  COUNT(DISTINCT call.name) AS number_of_callsets
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,  v.call

Die Abfrage gibt das folgende Ergebnis zurück:

Row	number_of_callsets
1	6

Varianten pro Chromosom zählen

Wenn Sie die Anzahl der Varianten pro Chromosom ermitteln möchten, führen Sie folgende Abfrage aus. Die Abfrage führt folgende Schritte durch:

• Zählt alle Zeilen, in denen mindestens ein Variantenaufruf mit mindestens einem Genotyp vorliegt, der größer als 0 ist.
• Gruppiert die Variantenzeilen nach Chromosom und zählt jede Gruppe.

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END

Der Name des Chromosoms (reference_name) und die Anzahl der Variantenzeilen für jedes Chromosom werden zurückgegeben:

Zeile	reference_name	number_of_variant_rows
1	chr1	615000
2	chr2	646401
3	chr3	542315
4	chr4	578600
5	chr5	496202
...	...	...

Hochwertige Varianten pro Stichprobe zählen

Aufrufe mit mehreren FILTER-Werten abfragen

In der VCF-Spezifikation wird die Spalte FILTER beschrieben, mit der Variantenaufrufe unterschiedlicher Qualität gekennzeichnet werden können.

In der folgenden Abfrage wird veranschaulicht, wie Sie die FILTER-Werte einzelner Variantenaufrufe für das Dataset einsehen:

#standardSQL
SELECT
  call_filter,
  COUNT(call_filter) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,
  v.call,
  UNNEST(call.FILTER) AS call_filter
GROUP BY
  call_filter
ORDER BY
  number_of_calls

Die Abfrage gibt das folgende Ergebnis zurück:

Row	call_filter	number_of_calls
1	RefCall	11681534
2	PASS	26867854

Der Wert PASS gibt an, dass ein Variantenaufruf von hoher Qualität ist.

FILTER für hochwertige Variantenaufrufe anwenden

Beim Analysieren von Varianten kann es nützlich sein, solche mit geringerer Qualität herauszufiltern. Wenn die Spalte FILTER den Wert PASS aufweist, enthält sie wahrscheinlich keine anderen Werte. Dies können Sie verifizieren, wenn Sie die folgende Abfrage ausführen. Die Abfrage lässt außerdem alle Aufrufe aus, die unter FILTER keinen PASS-Wert enthalten.

#standardSQL
SELECT
  reference_name,
  start_position,
  end_position,
  reference_bases,
  call.name AS call_name,
  (SELECT STRING_AGG(call_filter) FROM UNNEST(call.FILTER) AS call_filter) AS filters,
  ARRAY_LENGTH(call.FILTER) AS filter_count
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter = 'PASS')
  AND ARRAY_LENGTH(call.FILTER) > 1
ORDER BY
  filter_count DESC, reference_name, start_position, end_position, reference_bases, call_name
LIMIT
  10

Wie erwartet wird kein Ergebnis zurückgegeben.

Alle hochwertigen Aufrufe für jede Stichprobe zählen

Mit der folgenden Abfrage wird veranschaulicht, wie Sie alle Aufrufe (Varianten und Nicht-Varianten) für jedes Aufruf-Set ermitteln. Dabei wird ein Filter angewendet, um alle Aufrufe mit Nicht-PASS-Werten auszulassen:

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(1) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
GROUP BY
  call_name
ORDER BY
  call_name

Die Abfrage gibt das folgende Ergebnis zurück:

Row	call_name	number_of_calls
1	NA12877	29795946
2	NA12878	26118774
3	NA12889	29044992
4	NA12890	28717437
5	NA12891	31395995
6	NA12892	25349974

Alle hochwertigen, echten Variantenaufrufe für jede Stichprobe zählen

Mit der folgenden Abfrage wird veranschaulicht, wie Sie alle Aufrufe (Varianten und Nicht-Varianten) für jede Stichprobe ermitteln. Dabei wird ein Filter angewendet, um alle Aufrufe mit Nicht-PASS-Werten auszulassen, und nur Aufrufe mit mindestens einer echten Variante bzw. genotype > 0 werden abgefragt:

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(1) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
  AND EXISTS (SELECT 1 FROM UNNEST(call.genotype) as gt WHERE gt > 0)
GROUP BY
  call_name
ORDER BY
  call_name

Die Abfrage gibt das folgende Ergebnis zurück:

Row	call_name	number_of_calls
1	NA12877	4486610
2	NA12878	4502017
3	NA12889	4422706
4	NA12890	4528725
5	NA12891	4424094
6	NA12892	4495753

Best Practices

Abfragen zusammenfassen

Da Ihre Abfragen immer komplexer werden, sollten Sie sie möglichst kurz halten. So sorgen Sie dafür, dass die Abfragelogik fehlerfrei und nachvollziehbar bleibt.

Im folgenden Beispiel wird eine Abfrage, die die Anzahl der Varianten pro Chromosom zählt, mithilfe von SQL-Syntax und benutzerdefinierten Funktionen Schritt für Schritt zusammengefasst.

Wie im Abschnitt Varianten pro Chromosom zählen erläutert wurde, werden bei dieser Abfrage

• Zählt alle Zeilen, in denen mindestens ein Variantenaufruf mit mindestens einem Genotyp vorliegt, der größer als 0 ist.
• Gruppiert die Variantenzeilen nach Chromosom und zählt jede Gruppe.

Das Erstellen dieser Abfrage kann kompliziert sein, da Sie als Erstes ein ARRAY (genotype) in einem ARRAY (call) untersuchen müssen, die Ausführung der Abfrage jedoch auf der Zeilenebene erfolgen muss. Sie behalten den Ausführungskontext der Abfrage auf Zeilenebene bei, weil Sie ein Ergebnis pro Variante und nicht ein Ergebnis pro call oder pro genotype erhalten möchten.

Mit der Funktion UNNEST können Sie eine Abfrage für eine ARRAY-Spalte ausführen, als handle es sich dabei um eine Tabelle. Die Funktion gibt für jedes Element eines ARRAY je eine Zeile zurück. Der Abfragekontext bleibt unverändert. Verwenden Sie eine UNNEST-Funktion in einer EXISTS-Unterabfrage in einer WHERE-Klausel:

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call
          WHERE EXISTS (SELECT 1
                          FROM UNNEST(call.genotype) AS gt
                        WHERE gt > 0))
GROUP BY
  reference_name
ORDER BY
  reference_name

Die Abfrage gibt dieselben Ergebnisse zurück wie das Beispiel im Abschnitt Varianten pro Chromosom zählen:

Zeile	reference_name	number_of_variant_rows
1	chr1	615000
2	chr10	396773
3	chr11	391260
4	chr12	382841
5	chr13	298044
...	...	...

Sie können die Abfrage zusätzlich verkürzen, wenn die EXISTS-Klausel in einen JOIN der call-Spalte mit der call.genotype-Spalte geändert wird. Der Kommaoperator ist eine Kurzschreibweise für JOIN.

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY
  reference_name

Die Abfrage funktioniert und ist verkürzt, aber Sie können die Ausgabe nicht in aufsteigender numerischer Reihenfolge der Chromosomen (reference_name) sortieren. Dies liegt daran, dass die Werte in reference_name String-Typen sind und jeder Wert das Präfix "chr" enthält.

Wenn Sie die Ausgabe numerisch sortieren möchten, müssen Sie zuerst das Präfix "chr" aus der Spalte reference_name entfernen und ihr den Alias chromosome zuweisen:

#standardSQL
SELECT
  REGEXP_REPLACE(reference_name, '^chr', '') AS chromosome,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  chromosome
ORDER BY
  chromosome

Mit der Funktion REGEXP_REPLACE wird der Präfixstring "chr" in der Abfrage durch einen leeren String ersetzt. Anschließend ändert die Abfrage die Funktionen GROUP BY und ORDER BY, um den berechneten Alias chromosome zu verwenden. Die Ausgabe wird immer noch nach String sortiert:

Row	chromosome	number_of_variant_rows
1	1	615000
2	10	396773
3	11	391260
4	12	382841
5	13	298044
...	...	...

Damit die Ausgabe stattdessen numerisch sortiert wird, wandeln Sie die Spalte chromosome von einem String in eine Ganzzahl um:

#standardSQL
SELECT
  CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) AS chromosome,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  chromosome
ORDER BY
  chromosome

Die Abfrage gibt einen Fehler aus, da nicht alle Chromosomnamen numerisch sind, z. B. "X", "Y" und "M". Sie können mit der Funktion CASE den Chromosomen 1 bis 9 "0" voranstellen und das Präfix "chr" entfernen:

#standardSQL
SELECT
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END AS chromosome,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  chromosome
ORDER BY
  chromosome

Die Abfrage liefert die richtige Ausgabe:

Zeile	chromosome	number_of_variant_rows
1	01	615000
2	02	646401
3	03	542315
4	04	578600
5	05	496202
...	...	...

Die Abfrage verwendet die Funktion SAFE_CAST, die für die Chromosomen X, Y und M NULL zurückgibt, anstatt einen Fehler zu melden.

Als letzte Verbesserung der Ausgabe können Sie wieder die Spalte reference_name anzeigen lassen, statt den Alias chromosome festzulegen. Dazu verschieben Sie die CASE-Klausel in die ORDER BY-Funktion:

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END

Diese endgültige Abfrage entspricht der Abfrage im Abschnitt Varianten pro Chromosom zählen.

Benutzerdefinierte Funktionen schreiben

BigQuery unterstützt benutzerdefinierte Funktionen. Sie können benutzerdefinierte Funktionen verwenden, um eine Funktion mit einem anderen SQL-Ausdruck oder einer anderen Programmiersprache wie JavaScript zu erstellen.

In dem Beispiel in Abfragen zusammenfassen wird gezeigt, wie Sie eine komplexe Abfrage erstellen. Dabei ist die Abfrage jedoch zu komplex.

Anhand der folgenden Abfrage wird gezeigt, wie Sie sie durch das Verschieben der CASE-Logik in eine Funktion verkürzen können:

#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
  RETURNS STRING AS (
  -- Remove the leading "chr" (if any) in the reference_name
  -- If the chromosome is 1 - 9, prepend a "0" since
  -- "2" sorts after "10", but "02" sorts before "10".
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END
);

SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY SortableChromosome(reference_name)

Anhand der folgenden Abfrage wird ebenfalls gezeigt, wie die Abfrage verkürzt werden kann. Dabei kommt jedoch eine in JavaScript definierte Funktion zum Einsatz:

#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
  RETURNS STRING LANGUAGE js AS """
  // Remove the leading "chr" (if any) in the reference_name
  var chr = reference_name.replace(/^chr/, '');

  // If the chromosome is 1 - 9, prepend a "0" since
  // "2" sorts after "10", but "02" sorts before "10".
  if (chr.length == 1 && '123456789'.indexOf(chr) >= 0) {
    return '0' + chr;
  }

  return chr;
""";

SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY SortableChromosome(reference_name)

Beide Abfragen liefern das richtige Ergebnis und die verwendete Logik ist bündiger.

Abfrageleistung erhöhen und Kosten senken

BigQuery-Preise basieren auf der Anzahl der Bytes, die für eine Abfrage verarbeitet werden. Die Abfrageleistung nimmt zu, wenn die Menge der zu verarbeitenden Daten reduziert wird. In BigQuery wird mithilfe von Daten angezeigt, wie viele Sekunden seit dem Start einer Abfrage verstrichen sind und wie viele Bytes die Abfrage verarbeitet hat. Weitere Informationen zum Optimieren von Abfragen finden Sie in den Erläuterung des BigQuery-Abfrageplans.

Bei einigen Beispielen auf dieser Seite, z. B. beim Zählen der Variantenaufrufe in einer Tabelle, werden mehrere Möglichkeiten zum Erstellen einer Abfrage beschrieben. Sie können die Dauer unterschiedlicher Abfragen und die Menge der verarbeiteten Datenbytes prüfen, um die am besten geeignete Methode für Ihre Anforderungen zu bestimmen.

Bereinigen

Nachdem Sie die Anleitung abgeschlossen haben, können Sie die erstellten Ressourcen bereinigen, damit sie keine Kontingente mehr nutzen und keine Gebühren mehr anfallen. In den folgenden Abschnitten erfahren Sie, wie Sie diese Ressourcen löschen oder deaktivieren.

Am einfachsten vermeiden Sie weitere Kosten durch Löschen des für die Anleitung erstellten Projekts.

So löschen Sie das Projekt:

Achtung: Das Löschen von Projekten hat folgende Auswirkungen:

• Alle Inhalte des Projekts werden gelöscht. Wenn Sie für die Aufgaben in diesem Dokument ein bereits bestehendes Projekt verwendet haben und dieses löschen, werden auch alle anderen im Rahmen des Projekts erstellten Daten gelöscht.
• Benutzerdefinierte Projekt-IDs gehen verloren. Beim Erstellen dieses Projekts haben Sie möglicherweise eine benutzerdefinierte Projekt-ID erstellt, die Sie weiterhin verwenden möchten. Damit die URLs, die die Projekt-ID nutzen, z. B. eine appspot.com-URL, erhalten bleiben, sollten Sie ausgewählte Ressourcen innerhalb des Projekts löschen, statt das gesamte Projekt.

Wenn Sie mehrere Architekturen, Anleitungen und Kurzanleitungen durcharbeiten möchten, können Sie die Überschreitung von Projektkontingenten verhindern, indem Sie Projekte wiederverwenden.

1. Wechseln Sie in der Google Cloud Console zur Seite Ressourcen verwalten.

   Zur Seite „Ressourcen verwalten“

2. Wählen Sie in der Projektliste das Projekt aus, das Sie löschen möchten, und klicken Sie dann auf Löschen.
3. Geben Sie im Dialogfeld die Projekt-ID ein und klicken Sie auf Shut down (Beenden), um das Projekt zu löschen.

Nächste Schritte

• Weitere Cloud Life Sciences-Anleitungen durchgehen
• Varianten in BigQuery mit R, RMarkdown oder JavaScript analysieren