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

BigQuery-Variantentabelle vereinfachen

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 die FROM-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- oder LEFT JOIN-Klausel hinzufügen, um sie vereinfachen.

Informationen zum Erfassen der Abfrageergebnisse in einer neuen Tabelle finden Sie unter Tabelle aus einem Abfrageergebnis erstellen.