Se usó la API de Cloud Translation para traducir esta página.
Switch to English

Compactar la tabla de variantes de BigQuery

En esta página, se muestra cómo compactar una tabla de BigQuery que utiliza el esquema de variantes de BigQuery.

Cuando se compacta una tabla de BigQuery se quitan las filas repetidas. Compactar puede ser útil en las siguientes situaciones:

  • Cuando se trabaja con herramientas de terceros, como las que se utilizan para la visualización de datos, que requieren una tabla con una estructura compactada
  • Si consultas varios campos repetidos de forma independiente o calculas el producto cruzado de dichos campos y aparece el mensaje de error Cannot query the cross product of repeated fields ...

Compacta una tabla

Imagina que tienes una tabla en BigQuery que contiene la siguiente fila. La fila contiene dos bases alternativas, C y T (en la columna alternate_bases.alt), y dos llamadas, NA12890 y NA12878 (en la columna call.name).

Fila reference_name start_position end_position reference_bases alternate_bases.alt alternate_bases.AC quality filtro call.name call.genotype
1 chrY 9909316 9909317 A C 1 577.59 LowMQ NA12890 1
T 1 2
NA12878 1
2

Ejecuta la siguiente consulta a fin de compactar la tabla en el registro de llamada repetido y mostrar una fila por cada llamada. Observa cómo se usa el operador de coma (,), que es una notación abreviada para JOIN.

#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

La consulta anterior utiliza una ruta de acceso completamente calificada en t.call, pero puedes usar el operador UNNEST en lugar de t.call, de la siguiente manera:

#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

En ambos casos, las consultas muestran una fila para cada llamada:

Fila reference_name start_position end_position reference_bases call_name
1 chrY 9909316 9909317 A NA12890
2 chrY 9909316 9909317 A NA12878

Compacta campos adicionales

Puedes incluir información adicional para cada llamada; para ello, agrega campos a la cláusula SELECT. Por ejemplo, la siguiente consulta agrega los genotipos de la llamada (como un arreglo de números enteros):

#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

Cuando ejecutes la consulta, se mostrará el siguiente resultado:

Fila 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

Para compactar aún más la tabla de BigQuery y mostrar un genotipo por fila, agrega otra JOIN con el campo call.genotype:

#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

Cuando ejecutes la consulta, se mostrará el siguiente resultado:

Fila 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

Ten en cuenta que, en el resultado, cada call_name está duplicado, porque cada llamada contiene dos valores genotype.

En la siguiente consulta, se muestra qué sucede cuando agregas un registro repetido de forma independiente (alternate_bases) a la cláusula SELECT:

#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

Cuando ejecutes la consulta, se mostrará el siguiente resultado:

Fila 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

Si bien la columna genotype se compactó, el resultado contiene ambas columnas alternate_bases. Para mostrar la base alternativa particular, que coincide con el índice de la columna genotype, utiliza la función ORDINAL, como en la siguiente consulta:

#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

Cuando ejecutes la consulta, se mostrará el siguiente resultado:

Fila 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

La semántica de la columna genotype en la cláusula SELECT cambió debido a que cada fila contiene solamente un único alelo alternativo.

En la siguiente consulta, se muestra cómo modificar el formato de la columna genotype con IF(genotype > 0, 1, genotype) AS alt_genotype, cuyo resultado es que el valor del genotipo se establezca de la siguiente manera:

  • 1 si coincide con la base alternativa
  • 0 si coincide con la base de referencia
  • -1 si no se lo llamó. Ten en cuenta que la herramienta Variant Transforms utiliza -1 para indicar los genotipos que no se llaman (como . en el archivo VCF).

La siguiente consulta muestra cómo incluir solo la columna alternate_bases.alt mediante la compactación del registro alternate_bases y el uso del índice como criterios de filtrado:

#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)

Cuando ejecutes la consulta, se mostrará el siguiente resultado:

Fila 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

La consulta utiliza un LEFT JOIN para incluir todas las filas que no tengan una base alternativa. Para incluir solamente registros que tengan una base alternativa como mínimo, puedes realizar una de las siguientes acciones:

  • Utiliza un INNER JOIN.
  • Incluye t.alternate_bases AS alts WITH OFFSET as a_index en la cláusula FROM.

Además, la consulta muestra filas adicionales para el valor de genotipo 0 en caso de un sitio multialélico (es decir, varias bases alternativas). Esto ocurre porque LEFT JOIN realiza una operación CROSS JOIN entre cada genotipo y las bases alternativas. Luego, filtra según a_index. Como resultado, mientras que los genotipos que no sean 0 se filtran según a_index + 1, los genotipos de valor 0 se mostrarán para todas las combinaciones en las que haya una alternate_base y un genotipo con el valor 0.

Consulta de ejemplo

La siguiente consulta es un ejemplo completo de cómo compactar una tabla de variantes de BigQuery a fin de que no contenga registros repetidos. El valor del genotipo se establece de la siguiente manera:

  • 1 si coincide con la base alternativa
  • 0 si coincide con la base de referencia
  • -1 si no está configurado

La consulta se lleva a cabo en la siguiente tabla:

Fila reference_name start_position end_position reference_bases alternate_bases.alt alternate_bases.AC quality filtro 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 nulo 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
NA1878 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)

Cuando ejecutes la consulta, se mostrará el siguiente resultado:

Fila reference_name start_position end_position reference_bases alt quality filtro call_name alt_genotype phaseset
1 chr9 139825375 139825376 T nulo 120.24 LowGQX PASS NA12878 0 nulo
2 chr9 139825375 139825376 T nulo 120.24 LowGQX PASS NA12877 -1 nulo
3 chr9 139825375 139825376 T nulo 120.24 LowGQX PASS NA12890 -1 nulo
4 chr9 139825375 139825376 T nulo 120.24 LowGQX PASS NA12889 -1 nulo
5 chr9 139825375 139825376 T nulo 120.24 LowGQX PASS NA12891 0 nulo
6 chr9 139825375 139825376 T nulo 120.24 LowGQX PASS NA12891 0 nulo
7 chr4 4070556 4070558 A nulo nulo LowGQX LowMQ MaxDepth NA12878 0 nulo
8 chr4 4070556 4070558 A nulo nulo LowGQX LowMQ MaxDepth NA12878 0 nulo
9 chrX 61702219 61702220 T nulo 5.72 LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 NA12877 0 nulo
10 chrX 61702219 61702220 T C 5.72 LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 NA12877 1 nulo
11 chrY 9909316 9909317 A C 577.59 LowMQ NA12890 1 nulo
12 chrY 9909316 9909317 A T 577.59 LowMQ NA12890 1 nulo
13 chrY 9909316 9909317 A C 577.59 LowMQ NA12878 1 nulo
14 chrY 9909316 9909317 A T 577.59 LowMQ NA12878 1 nulo

Para otros campos repetidos, puedes realizar una de las siguientes acciones:

  • Concatenarlos como un solo campo (con una función como ARRAY_TO_STRING).
  • Agregarlos a la cláusula FROM o LEFT JOIN para compactarlos.

A fin de materializar el resultado de la consulta en una tabla nueva, consulta Crea una tabla a partir de un resultado de consulta.