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 alternativa0
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áusulaFROM
.
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 alternativa0
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
oLEFT 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.