Cómo 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 estás consultando varios campos repetidos de forma independiente o calculando el producto cruzado de esos campos, y te encuentras con el mensaje de error Cannot query the cross product of repeated fields ....

Cómo compactar 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 calidad filtro call.name call.genotype
1 chrY 9909316 9909317 A C 1 577.59 LowMQ NA12890 1
J 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 para cada llamada. Ten en cuenta el uso del operador con coma (,), que es una notación abreviada que se utiliza 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 siguiente consulta utiliza una ruta totalmente calificada en t.call, pero en su lugar puedes utilizar el operador UNNEST en vez de t.call, como se muestra a continuación:

#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

Cómo compactar 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

Una vez que hayas ejecutado 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

Una vez que hayas ejecutado 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

Una vez que hayas ejecutado 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
J 1
2 chrY 9909316 9909317 A NA12890 2 C 1
J 1
3 chrY 9909316 9909317 A NA12878 1 C 1
J 1
4 chrY 9909316 9909317 A NA12878 2 C 1
J 1

Si bien la columna genotype se compactó, el resultado contiene ambas columnas alternate_bases. Para que el resultado sea la base alternativa particular que coincide con el índica 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

Una vez que hayas ejecutado 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 J 1
3 chrY 9909316 9909317 A NA12878 1 C 1
4 chrY 9909316 9909317 A NA12878 2 J 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).

En la siguiente consulta se muestra cómo incluir solamente la columna alternate_bases.alt; para ello, se compacta el registro alternate_bases y se utiliza el índice como criterio 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)

Una vez que hayas ejecutado 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 J
3 chrY 9909316 9909317 A NA12878 1 C
4 chrY 9909316 9909317 A NA12878 1 J

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 debido a que LEFT JOIN realiza una CROSS JOIN entre cada genotipo y las bases alternativas. Luego, filtra por a_index. Como resultado, mientras que los genotipos que no sean 0 se filtran por 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.

Ejemplo de consulta

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 calidad filtro call.name call.genotype
1 chr9 139825375 139825376 J 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 J 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
J 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)

Una vez que hayas ejecutado la consulta, se mostrará el siguiente resultado:

Fila reference_name start_position end_position reference_bases alt calidad filtro call_name alt_genotype phaseset
1 chr9 139825375 139825376 J nulo 120.24 LowGQX PASS NA12878 0 nulo
2 chr9 139825375 139825376 J nulo 120.24 LowGQX PASS NA12877 -1 nulo
3 chr9 139825375 139825376 J nulo 120.24 LowGQX PASS NA12890 -1 nulo
4 chr9 139825375 139825376 J nulo 120.24 LowGQX PASS NA12889 -1 nulo
5 chr9 139825375 139825376 J nulo 120.24 LowGQX PASS NA12891 0 nulo
6 chr9 139825375 139825376 J 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 J nulo 5.72 LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 NA12877 0 nulo
10 chrX 61702219 61702220 J 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 J 577.59 LowMQ NA12890 1 nulo
13 chrY 9909316 9909317 A C 577.59 LowMQ NA12878 1 nulo
14 chrY 9909316 9909317 A J 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 a fin de compactarlos.

A fin de materializar el resultado de la consulta en una nueva tabla, consulta Cómo crear una tabla a partir de un resultado de consulta.

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...