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

In dieser Anleitung wird Folgendes gezeigt:

  • Verschaffen Sie sich einen Überblick über die Genomdaten.
  • Darstellung von Nicht-Varianten-Segmenten
  • Weitere Informationen zur Darstellung von Variantenaufrufen
  • Informationen zur Darstellung der Variantenqualitätsfilter für Varianten.
  • Hierarchische Spalten aggregieren.
  • Abfragen zusammenfassen
  • Einzelne Zeilen zählen
  • Zeilen gruppieren
  • Benutzerdefinierte Funktionen schreiben

In dieser Anleitung erhalten Sie außerdem Informationen zum Auffinden der folgenden Informationen:

  • 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 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 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.

    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. Die Segmente enthalten die folgenden Informationen:

  • Ein Referenzblock aus 10 für Base 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 dieser Anleitung 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

So können Sie sich die Daten in der Tabelle platinum_genomes_deepvariant_variants_20180823 ansehen:

  1. Rufen Sie die Tabelle in der Cloud Console auf der Seite BigQueryBigQuery“ auf.

    Zur Seite "BigQuery"

    Informationen zur Tabelle werden angezeigt. Die Tabelle enthält 19,6 GB Daten und mehr als 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 der Zeilen angesehen haben, können Sie damit beginnen, Abfragen zu senden und Daten zu analysieren. Bevor Sie fortfahren, sollten Sie sich mit der von BigQuery verwendeten Standard-SQL-Abfragesyntax vertraut machen.

Zeilen in der Tabelle zählen

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

  1. Rufen Sie in der 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 Optionen, 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, indem Sie die Länge jedes einzelnen call-Arrays hinzufügen:

#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 mit einem JOIN in jeder Zeile mit der Spalte call die Gesamtzahl der Variantenaufrufe in allen Stichproben. Die Abfrage verwendet den Kommaoperator (,), eine Kurzschreibweise für JOIN. Der Join der Spalte call führt einen impliziten UNNEST-Vorgang für die Spalte call aus.

#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 dargestellt, beträgt die Gesamtzahl der Variantenaufrufe in der Tabelle 182.104.652. Das Ergebnis zeigt, dass die Mehrheit der Zeilen in der Tabelle Nicht- Variantensegmente verfügbar.

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 dasalternate_bases Spalte dieser Zeile, die den Wert zurückgibt1 für jeden Wert alternate_bases die nicht<NON_REF> oder<*> auf Ihrem Mobilgerät. 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 call.name-Wert kann in mehreren Zeilen vorkommen.

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 und müssen daher herausgefiltert werden. 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 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

Die Abfrage gibt das folgende Ergebnis zurück:

Row number_of_callsets
1 6

Varianten pro Chromosom zählen

Führen Sie die folgende Abfrage aus, um die Anzahl der Varianten pro Chromosom zu zählen. Die Abfrage funktioniert so:

  • Zählt alle Zeilen, in denen mindestens ein Variantenaufruf mit mindestens einem Genotyp größer als 0 vorhanden ist.
  • Die Variantenzeilen werden 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 Sie Variantenaufrufe unterschiedlicher Qualität kennzeichnen 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 enthält, enthält die Spalte 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 größer als 0 vorhanden ist.
  • Die Variantenzeilen werden nach Chromosom gruppiert und jede Gruppe gezählt.

Das Schreiben dieser Abfrage kann kompliziert sein. Um die erste Aufgabe abzuschließen, müssen Sie einen ARRAY (genotype) in einem ARRAY (call) betrachten und gleichzeitig beibehalten Den Ausführungskontext der Abfrage auf Zeilenebene Sie behalten den Ausführungskontext der Abfrage auf Zeilenebene bei, da Sie ein Ergebnis pro Variante anstelle eines Ergebnisses pro call oder genotype erhalten möchten.

Mit der Funktion UNNEST können Sie eine ARRAY-Spalte so abfragen, als wäre die Spalte eine Tabelle. Die Funktion gibt für jedes Element einer ARRAY eine Zeile zurück. Der Abfragekontext bleibt unverändert. Verwenden Sie in einer EXISTS-Unterabfrage in einer WHERE-Klausel eine UNNEST-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
          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 verkürzen, indem Sie die EXISTS-Klausel in einen JOIN der call-Spalte mit der call.genotype-Spalte ändern. 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 kurz, doch Sie können die Ausgabe nicht in aufsteigender numerischer Reihenfolge der Chromosomen (reference_name) sortieren, da die Werte in reference_name Stringtypen sind und jede Der Wert enthält das Präfix "chr."

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. Die Abfrage ändert dann die Funktionen GROUP BY und ORDER BY so, dass der berechnete chromosome-Alias verwendet wird. Die Ausgabe wird weiterhin 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 zurück, da nicht alle Chromosomnamen numerisch sind, z. B. "X", "Y" und "M". Verwenden Sie die Funktion CASE, um den Chromosomen 1 bis 9 "0" voranzustellen und das Präfix "chr" zu 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 NULL für die Chromosomen X, Y und M zurückgibt, anstatt einen Fehler zurückzugeben.

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 mit benutzerdefinierten Funktionen eine Funktion mit einem anderen SQL-Ausdruck oder einer anderen Programmiersprache wie JavaScript 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 ihre Logik ist kürzer.

Abfrageleistung erhöhen und Kosten senken

BigQuery-Preise basieren auf der Anzahl der Bytes, die für eine Abfrage verarbeitet werden. Die Abfrageleistung verbessert sich, wenn die Menge der verarbeiteten Daten reduziert wird. BigQuery gibt an, wie viele Sekunden seit dem Start der Abfrage vergangen sind und wie viele Byte von der Abfrage verarbeitet wurden. 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 verwenden 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:

  1. Wechseln Sie in der 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