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-1
zur 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 JOIN
verwenden. - Sie können
t.alternate_bases AS alts WITH OFFSET as a_index
in 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.