Diese Seite wurde von der Cloud Translation API übersetzt.
Switch to English

Erweiterter Leitfaden zur Analyse von Varianten mit BigQuery

Auf dieser Seite werden erweiterte Methoden für die Analyse von Varianten mit BigQuery beschrieben.

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.

Ziele

Nach Abschluss dieser Anleitung können Sie die folgenden Aufgaben ausführen:

  • Eine Übersicht der 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 lernen Sie in dieser Anleitung, folgende Dinge zu ermitteln:

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

Kosten

In dieser Anleitung werden kostenpflichtige Komponenten von Google Cloud verwendet, darunter:

  • BigQuery

Der Preisrechner kann eine Kostenschätzung anhand Ihrer voraussichtlichen Nutzung generieren. Neuen Cloud Platform-Nutzern steht möglicherweise eine kostenlose Testversion zur Verfügung.

Hinweis

  1. Melden Sie sich bei Ihrem Google-Konto an.

    Wenn Sie noch kein Konto haben, melden Sie sich hier für ein neues Konto an.

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

    Zur Projektauswahl

  3. Die Abrechnung für das Cloud-Projekt muss aktiviert sein. So prüfen Sie, ob die Abrechnung für Ihr Projekt aktiviert ist.

  4. 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-Format. 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 wird ein Referenzblock aus 10­ Basen auf Chromosom 1 angezeigt. 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 <*>

Für die Abfragen in dieser Anleitung werden die drei oben aufgeführten Darstellungen verwendet.

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

Tabellendaten ansehen

So sehen Sie sich die Daten in der Tabelle platinum_genomes_deepvariant_variants_20180823 an:

  1. Rufen Sie in der BigQuery-UI die Seite Details auf.

    Informationen zur Tabelle werden angezeigt. Sie können sehen, dass sie 19,6 GB Daten und mehr als 105.000.000 Zeilen enthält.

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

Tabelle abfragen

Nachdem Sie sich das Tabellenschema und einige der Zeilen angesehen haben, können Sie damit beginnen, Abfragen zu senden und Daten zu 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 die BigQuery-UI auf.

    Zur BigQuery-UI

  2. Klicken Sie auf Neue 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 folgendes Ergebnis zurück:

    Zeile 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 unten 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

Eine Möglichkeit zum Zählen der Variantenaufrufe in allen Stichproben besteht darin, die Länge jedes einzelnen call-Arrays zu summieren:

#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 den richtigen Wert zurück (182.104.652):

Zeile number_of_calls
1 182104652

JOIN für jede Zeile

Es gibt eine zweite Möglichkeit, Variantenaufrufe in allen Proben zu zählen. Dabei werden die einzelnen Zeilen per JOIN mit der Spalte call zusammengeführt. Beachten Sie die Verwendung des Kommaoperators (,), 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

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 den richtigen Wert zurück (182.104.652):

Zeile 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>", "<*>"))

Folgender Wert wird zurückgegeben:

Zeile 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 überwiegende 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>", "<*>"))

Folgender Wert wird zurückgegeben:

Zeile 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

Folgende Werte werden zurückgegeben:

Zeile 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 sollten. 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.

So filtern Sie die Varianten nach Genotyp:

#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

Folgende Werte werden zurückgegeben:

Zeile 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. Sie können die folgende Abfrage ausführen, um stattdessen den Wert für diese Anzahl von Zeilen abzufragen und abzurufen:

#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

Folgender Wert wird zurückgegeben:

Zeile number_of_callsets
1 6

Varianten pro Chromosom zählen

Wenn Sie die Anzahl der Varianten pro Chromosom ermitteln möchten, können Sie folgende Abfrage ausführen. Dabei werden

  • alle Zeilen gezählt, in denen mindestens ein Variantenaufruf mit mindestens einem Genotyp vorliegt, der größer als 0 ist, und
  • die Variantenzeilen nach Chromosom gruppiert und jede Gruppe gezählt.
#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

Folgende Werte werden zurückgegeben:

Zeile 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 FILTER-Spalte den Wert PASS aufweist, enthält sie voraussichtlich keine anderen Werte. Dies können Sie verifizieren, indem 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

Folgende Werte werden zurückgegeben:

Zeile 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

Folgende Werte werden zurückgegeben:

Zeile 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

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

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. Der Grund hierfür ist, dass Sie ein Ergebnis pro Variante statt 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. Deshalb können Sie mit dem Einsatz einer UNNEST-Funktion in einer EXISTS-Unterabfrage in einer WHERE-Klausel beginnen:

#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

Die Abfrage kann zusätzlich verkürzt werden, wenn die EXISTS-Klausel in einen JOIN der call-Spalte mit der call.genotype-Spalte geändert wird. Wie bereits erwähnt ist der Kommaoperator 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 werden die Funktionen GROUP BY und ORDER BY geändert, sodass sie den neuen Alias chromosome verwenden. Die Ausgabe wird jedoch immer noch nach String sortiert:

Zeile 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

In diesem Fall gibt die Abfrage einen Fehler aus, da nicht alle Chromosomnamen numerisch sind, z. B. "X", "Y" und "M". Stattdessen können Sie 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 gibt das richtige Ergebnis zurück:

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

Die Funktion SAFE_CAST gibt für die Chromosomen X, Y und M statt eines Fehlers NULL zurück.

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, die Ihnen die Möglichkeit bieten, einen anderen SQL-Ausdruck oder eine andere Programmiersprache wie JavaScript zu verwenden, um eine Funktion zu erstellen.

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

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, doch 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. Außerdem nimmt die Abfrageleistung zu, wenn die Menge der zu verarbeitenden Daten reduziert wird. In der BigQuery-Benutzeroberfläche 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

Sie können nach Abschluss der Anleitung die von Ihnen in Google Cloud erstellten Ressourcen bereinigen, damit Ihnen diese nicht weiter in Rechnung gestellt werden. In den folgenden Abschnitten erfahren Sie, wie Sie diese Ressourcen löschen oder deaktivieren.

Projekt löschen

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

So löschen Sie das Projekt:

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

    Zur Seite "Projekte"

  2. Wählen Sie in der Projektliste das Projekt aus, das Sie löschen möchten, und klicken Sie auf Delete project (Projekt löschen) Klicken Sie auf das Kästchen neben dem Projektnamen und dann auf &quot;Delete project&quot; (Projekt 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