Auf dieser Seite wird gezeigt, wie Sie eine BigQuery-Tabelle vereinfachen, in der das BigQuery-Variantenschema verwendet wird.
Durch das Vereinfachen einer BigQuery-Tabelle werden Zeilenwiederholungen entfernt. Dies kann in den folgenden Situationen hilfreich sein:
- Wenn Sie mit Tools von Drittanbietern arbeiten, z. B. für die Datenvisualisierung, die eine Tabelle mit einer vereinfachten Struktur erfordern.
- Wenn Sie mehrere unabhängig wiederkehrende Felder abfragen oder das Mengenprodukt solcher Felder berechnen und die Fehlermeldung
Cannot query the cross product of repeated fields ...angezeigt wird.
Tabellen vereinfachen
Angenommen, Sie haben eine Tabelle in BigQuery, die die folgende Zeile enthält. Die Zeile enthält die beiden alternativen Basen C und T (in der Spalte alternate_bases.alt) sowie die beiden Aufrufe NA12890 und NA12878 (in der Spalte call.name).
| Row | reference_name | start_position | end_position | reference_bases | alternate_bases.alt | alternate_bases.AC | quality | filter | call.name | call.genotype |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | C | 1 | 577,59 | LowMQ | NA12890 | 1 |
| T | 1 | 2 | ||||||||
| NA12878 | 1 | |||||||||
| 2 |
Führen Sie die folgende Abfrage aus, um die Tabelle anhand des wiederholten Aufrufeintrags zu vereinfachen und eine Zeile für jeden Aufruf zurückzugeben.
Beachten Sie die Verwendung des Kommaoperators (,), der eine Kurzschreibweise für JOIN ist.
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call
In der Abfrage oben wird ein vollständig qualifizierter Pfad in t.call verwendet. Sie können jedoch anstelle von t.call den Operator UNNEST verwenden:
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE`, UNNEST(call) AS call
In beiden Fällen geben die Abfragen eine Zeile für jeden Aufruf zurück:
| Zeile | reference_name | start_position | end_position | reference_bases | call_name |
|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 |
| 2 | chrY | 9909316 | 9909317 | A | NA12878 |
Weitere Felder vereinfachen
Sie können jeden Aufruf um zusätzliche Informationen ergänzen, indem Sie der Klausel SELECT Felder hinzufügen. Mit der folgenden Abfrage werden z. B. die Aufrufgenotypen als Array von Ganzzahlen hinzugefügt:
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, call.genotype FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call
Die Abfrage gibt folgendes Ergebnis zurück:
| Zeile | reference_name | start_position | end_position | reference_bases | call_name | genotype |
|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 |
| 2 | ||||||
| 2 | chrY | 9909316 | 9909317 | A | NA12878 | 1 |
| 2 |
Wenn Sie die BigQuery-Tabelle noch weiter vereinfachen möchten, sodass ein Genotyp pro Zeile zurückgegeben wird, können Sie einen weiteren JOIN mit dem Feld call.genotype einfügen:
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, genotype FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype
Die Abfrage gibt folgendes Ergebnis zurück:
| Zeile | reference_name | start_position | end_position | reference_bases | call_name | genotype |
|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 |
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 2 |
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 |
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 2 |
In der Ausgabe ist der call_name jeweils dupliziert, weil jeder Aufruf zwei Werte für genotype enthält.
In der folgenden Abfrage wird gezeigt, was passiert, wenn Sie der Klausel SELECT einen unabhängig wiederholten Eintrag (alternate_bases) hinzufügen:
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, genotype, alternate_bases FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype
Die Abfrage gibt folgendes Ergebnis zurück:
| Zeile | reference_name | start_position | end_position | reference_bases | call_name | genotype | alternate_bases.alt | alternate_bases.AC |
|---|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | C | 1 |
| T | 1 | |||||||
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 2 | C | 1 |
| T | 1 | |||||||
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | C | 1 |
| T | 1 | |||||||
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 2 | C | 1 |
| T | 1 |
Obwohl die Spalte genotype vereinfacht wurde, enthält die Ausgabe beide alternate_bases-Spalten. Um die bestimmte alternative Base zurückzugeben, die mit dem Index der Spalte genotype übereinstimmt, verwenden Sie die Funktion ORDINAL wie in der folgenden Abfrage:
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, genotype, IF(genotype > 0, alternate_bases[ORDINAL(genotype)], NULL) AS alternate_bases FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype
Die Abfrage gibt folgendes Ergebnis zurück:
| Zeile | reference_name | start_position | end_position | reference_bases | call_name | genotype | alternate_bases.alt | alternate_bases.AC |
|---|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | C | 1 |
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 2 | T | 1 |
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | C | 1 |
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 2 | T | 1 |
Die Semantik der Spalte genotype in der SELECT-Klausel wurde geändert, da jede Zeile nur ein einzelnes alternatives Allel enthält.
Mit der nächsten Abfrage wird gezeigt, wie Sie die Spalte genotype mit IF(genotype > 0, 1, genotype) AS alt_genotype neu formatieren. Dabei wird der Genotypwert so festgelegt:
1, wenn er der alternativen Basis entspricht.0, wenn er der Referenzbasis entspricht.-1, wenn er nicht aufgerufen wird. Das Tool Variant Transforms verwendet-1zur Kennzeichnung von Genotypen, die nicht aufgerufen werden (z. B..in der VCF-Datei).
Die folgende Abfrage zeigt, wie nur die Spalte alternate_bases.alt einbezogen wird, indem der Eintrag alternate_bases vereinfacht und der Index als Filterkriterium verwendet wird:
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, call.name AS call_name, IF(genotype > 0, 1, genotype) AS alt_genotype, IF(genotype > 0, alts.alt, NULL) AS alt FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype LEFT JOIN t.alternate_bases AS alts WITH OFFSET AS a_index WHERE genotype IN (a_index + 1, 0, -1)
Die Abfrage gibt folgendes Ergebnis zurück:
| Zeile | reference_name | start_position | end_position | reference_bases | call_name | alt_genotype | alt |
|---|---|---|---|---|---|---|---|
| 1 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | C |
| 2 | chrY | 9909316 | 9909317 | A | NA12890 | 1 | T |
| 3 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | C |
| 4 | chrY | 9909316 | 9909317 | A | NA12878 | 1 | T |
Mit einem LEFT JOIN werden Zeilen in die Abfrage aufgenommen, die keine alternative Base haben. Wenn Sie nur Einträge einbeziehen möchten, die mindestens eine alternative Basis haben, können Sie folgende Möglichkeiten nutzen:
- Sie können einen
INNER JOINverwenden. - Sie können
t.alternate_bases AS alts WITH OFFSET as a_indexin dieFROM-Klausel aufnehmen.
Außerdem gibt die Abfrage zusätzliche Zeilen für den Genotypwert 0 zurück, wenn eine multiallelische Site vorhanden ist (also mehrere alternative Basen). Dies liegt daran, dass LEFT JOIN zwischen jedem Genotyp und den alternativen Basen einen CROSS JOIN durchführt.
Anschließend wird nach a_index gefiltert. Folglich werden Nicht-0-Genotypen nach a_index + 1 gefiltert und die Genotypen mit Wert 0 für alle Kombinationen zurückgegeben, bei denen eine alternate_base und ein Genotyp mit dem Wert 0 vorliegt.
Beispielabfrage
Die folgende Abfrage ist ein umfassendes Beispiel dafür, wie Sie eine BigQuery-Variantentabelle so vereinfachen können, dass sie keine wiederholten Einträge enthält. Der Genotypwert wird so festgelegt:
1, wenn er der alternativen Basis entspricht.0, wenn er der Referenzbasis entspricht.-1, wenn er nicht festgelegt ist.
Die Abfrage wird mit der folgenden Tabelle ausgeführt:
| Zeile | reference_name | start_position | end_position | reference_bases | alternate_bases.alt | alternate_bases.AC | quality | filter | call.name | call.genotype |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | chr9 | 139825375 | 139825376 | T | 120,24 | LowGQX | NA12878 | 0 | ||
| PASS | NA12877 | -1 | ||||||||
| NA12890 | -1 | |||||||||
| NA12889 | -1 | |||||||||
| NA12891 | 0 | |||||||||
| 0 | ||||||||||
| 2 | chr4 | 4070556 | 4070558 | A | Null | LowGQX | NA12878 | 0 | ||
| LowMQ | 0 | |||||||||
| MaxDepth | ||||||||||
| 3 | chrX | 61702219 | 61702220 | T | C | 1 | 5,72 | LowGQX | NA12877 | 0 |
| LowMQ | 1 | |||||||||
| LowQD | ||||||||||
| TruthSensitivityTranche99.90to100.00 | ||||||||||
| 4 | chrY | 9909316 | 9909317 | A | C | 1 | 577,59 | LowMQ | NA12890 | 1 |
| T | 1 | 2 | ||||||||
| – | 1 | |||||||||
| 2 |
#standardSQL SELECT reference_name, start_position, end_position, reference_bases, IF(genotype > 0, alts.alt, NULL) AS alt, t.quality, ARRAY_TO_STRING(t.filter, ' ') AS filter, call.name AS call_name, IF(genotype > 0, 1, genotype) AS alt_genotype, call.phaseset FROM `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t, t.call AS call, call.genotype AS genotype LEFT JOIN t.alternate_bases AS alts WITH OFFSET AS a_index WHERE genotype IN (a_index + 1, 0, -1)
Die Abfrage gibt folgendes Ergebnis zurück:
| Zeile | reference_name | start_position | end_position | reference_bases | alt | quality | filter | call_name | alt_genotype | phaseset |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | chr9 | 139825375 | 139825376 | T | Null | 120,24 | LowGQX PASS | NA12878 | 0 | Null |
| 2 | chr9 | 139825375 | 139825376 | T | Null | 120,24 | LowGQX PASS | NA12877 | -1 | Null |
| 3 | chr9 | 139825375 | 139825376 | T | Null | 120,24 | LowGQX PASS | NA12890 | -1 | Null |
| 4 | chr9 | 139825375 | 139825376 | T | Null | 120,24 | LowGQX PASS | NA12889 | -1 | Null |
| 5 | chr9 | 139825375 | 139825376 | T | Null | 120,24 | LowGQX PASS | NA12891 | 0 | Null |
| 6 | chr9 | 139825375 | 139825376 | T | Null | 120,24 | LowGQX PASS | NA12891 | 0 | Null |
| 7 | chr4 | 4070556 | 4070558 | A | Null | Null | LowGQX LowMQ MaxDepth | NA12878 | 0 | Null |
| 8 | chr4 | 4070556 | 4070558 | A | Null | Null | LowGQX LowMQ MaxDepth | NA12878 | 0 | Null |
| 9 | chrX | 61702219 | 61702220 | T | Null | 5,72 | LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 | NA12877 | 0 | Null |
| 10 | chrX | 61702219 | 61702220 | T | C | 5,72 | LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 | NA12877 | 1 | Null |
| 11 | chrY | 9909316 | 9909317 | A | C | 577,59 | LowMQ | NA12890 | 1 | Null |
| 12 | chrY | 9909316 | 9909317 | A | T | 577,59 | LowMQ | NA12890 | 1 | Null |
| 13 | chrY | 9909316 | 9909317 | A | C | 577,59 | LowMQ | NA12878 | 1 | Null |
| 14 | chrY | 9909316 | 9909317 | A | T | 577,59 | LowMQ | NA12878 | 1 | Null |
Für andere wiederholte Felder haben Sie folgende Möglichkeiten:
- Sie können sie in einem einzelnen Feld verketten (mit einer Funktion wie
ARRAY_TO_STRING). - Sie können sie der
FROM- oderLEFT JOIN-Klausel hinzufügen, um sie vereinfachen.
Informationen zum Erfassen der Abfrageergebnisse in einer neuen Tabelle finden Sie unter Tabelle aus einem Abfrageergebnis erstellen.