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.
Ziele
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.
Hinweise
- Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
- 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üralternate_bases.alt - Mit dem Textstring
<*>als Wert füralternate_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
10Basen auf Chromosom1 - Der Referenzblock beginnt ab Position
1000. - Die Referenzbase an Position
1000ist einA. - 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:
Zeigen Sie in der Google Cloud Console die Tabelle auf der Seite „BigQuery“ an.
Informationen zur Tabelle werden angezeigt. Die Tabelle enthält 19,6 GB Daten und enthält über 105.000.000 Zeilen.
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:
Rufen Sie in der Google Cloud Console die Seite "BigQuery" auf.
Klicken Sie auf Abfrage erstellen.
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`
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:
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
Nächste Schritte
- Weitere Cloud Life Sciences-Anleitungen durchgehen
- Varianten in BigQuery mit R, RMarkdown oder JavaScript analysieren