En esta página, se describen métodos avanzados sobre cómo utilizar BigQuery para analizar variantes.
Los datos presentes en este instructivo provienen del proyecto Platinum Genomes de Illumina.
Estos datos se cargaron en una tabla de BigQuery que utiliza el esquema de variantes de BigQuery.
El nombre de la tabla es platinum_genomes_deepvariant_variants_20180823.
Si tus datos de variantes están en una tabla de BigQuery que utiliza el esquema de variantes de BigQuery, será sencillo aplicar las consultas de este instructivo a tus datos. Si deseas obtener más información sobre cómo cargar datos de variantes en BigQuery, consulta la documentación sobre cómo utilizar la canalización de transformación.
Objetivos
En este instructivo, se muestra cómo hacer lo siguiente:
- Obtener una descripción general de los datos genómicos.
- Descubrir cómo se representan los segmentos no variantes.
- Descubrir cómo se representan las llamadas de variantes.
- Descubrir cómo se representan los filtros de calidad de las llamadas de variantes.
- Agregar columnas jerárquicas.
- Simplificar las consultas.
- Contar filas diferentes.
- Agrupar filas.
- Escribir funciones definidas por el usuario.
En este instructivo, también se muestra cómo encontrar la siguiente información:
- Cantidad de filas en la tabla
- Cantidad de llamadas de variantes
- Llamadas de variantes para cada muestra
- Cantidad de muestras
- Variantes según cromosoma
- Variantes de calidad alta por muestra
Costos
En este documento, usarás los siguientes componentes facturables de Google Cloud:
- BigQuery
Para generar una estimación de costos en función del uso previsto, usa la calculadora de precios.
Antes de comenzar
- Accede a tu cuenta de Google Cloud. Si eres nuevo en Google Cloud, crea una cuenta para evaluar el rendimiento de nuestros productos en situaciones reales. Los clientes nuevos también obtienen $300 en créditos gratuitos para ejecutar, probar y, además, implementar cargas de trabajo.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Asegúrate de que la facturación esté habilitada para tu proyecto de Google Cloud.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Asegúrate de que la facturación esté habilitada para tu proyecto de Google Cloud.
- Debes estar familiarizado con el esquema de variantes de BigQuery.
Cómo ver los datos y el esquema de la tabla
Accede a la tabla y ve el esquema
La tabla platinum_genomes_deepvariant_variants_20180823 de Illumina Platinum Genomes está disponible de forma pública.
Variantes y no variantes en la tabla
Los datos de Platinum Genomes de Illumina utilizan el formato gVCF, lo cual significa que la tabla tiene filas que incluyen no variantes. Estas no variantes se conocen como "llamadas de referencia".
En esta tabla, los segmentos coincidentes suelen representarse de las siguientes maneras:
- Con un valor
alternate_basesde longitud cero - Con la string de texto
<NON_REF>como valoralternate_bases.alt - Con la string de texto
<*>como valoralternate_bases.alt
La representación de los segmentos coincidentes suele depender del llamador de variantes que generó los datos de origen. Las variantes de la tabla platinum_genomes_deepvariant_variants_20180823 se llamaron con DeepVariant, que usa la notación <*>.
En las siguientes tablas, se muestran algunas filas con valores que representan segmentos coincidentes. Los segmentos muestran la siguiente información:
- Un bloque de referencia de
10se basa en el cromosoma1. - El bloque de referencia comienza en la posición
1000 - La base de referencia en la posición
1000es unaA - No se muestran las bases de referencia en las otras posiciones del bloque
En la siguiente tabla, la columna alternate_bases REPEATED RECORD no contiene ningún valor, lo que significa que es un ARRAY de longitud 0.
| reference_name | start_position | end_position | reference_bases | alternate_bases.alt |
|---|---|---|---|---|
| 1 | 1000 | 1010 | A |
En la siguiente tabla, la columna alternate_bases REPEATED RECORD tiene la longitud 1 y contiene la string de texto literal <*>.
| reference_name | start_position | end_position | reference_bases | alternate_bases.alt |
|---|---|---|---|---|
| 1 | 1000 | 1010 | A | <*> |
Las consultas que se utilizan en esta guía usan las representaciones de las tablas anteriores.
Consulta las especificaciones de VCF para obtener más información sobre cómo representar posiciones no variantes en el genoma.
Visualiza los datos de la tabla
Para ver los datos en la tabla platinum_genomes_deepvariant_variants_20180823, haz lo siguiente:
completa los siguientes pasos:
Visualiza la tabla en la página de BigQuery en la consola de Google Cloud.
Aparecerá información acerca de la tabla. La tabla contiene 19.6 GB de datos y tiene más de 105,000,000 filas.
Haz clic en Vista previa para ver algunas de las filas de la tabla.
Consulta la tabla
Después de ver el esquema de la tabla y algunas de sus filas, comienza a emitir consultas y a analizar los datos. Antes de continuar, asegúrate de estar familiarizado con el Sintaxis de consultas en SQL estándar que usa BigQuery.
Cómo contar el total de filas en la tabla
Para ver la cantidad de filas en la tabla, sigue estos pasos:
Ve a la página de BigQuery en la consola de Google Cloud.
Haz clic en Redactar consulta.
Copia y pega la siguiente consulta en el cuadro de texto Consulta nueva:
#standardSQL SELECT COUNT(1) AS number_of_rows FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`
Haga clic en Run query. La consulta muestra el siguiente resultado:
Fila number_of_rows 1 105923159
Cómo contar las llamadas de variantes en la tabla
Cada fila de la tabla tiene una posición genómica que es un segmento variante o coincidente.
Cada fila también contiene una columna call, que es un ARRAY de llamadas de variantes. Cada columna call incluye el name y otros valores, como el genotipo, las columnas de calidad, la profundidad de lectura y otras que normalmente se encuentran en un archivo VCF.
Para contar la cantidad de llamadas de variantes, consulta la cantidad de elementos dentro de las columnas ARRAY. Puedes hacerlo de varias maneras, que se muestran a continuación.
Cada consulta muestra el valor 182,104,652, lo cual significa que hay un promedio de 1.7 llamadas de variantes por fila en el conjunto de datos.
Cómo sumar las longitudes de los arrays de call
Para contar la cantidad total de llamadas de variantes en todas las muestras, agrega la longitud de cada array call:
#standardSQL
SELECT
SUM(ARRAY_LENGTH(call)) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`
La consulta muestra el siguiente resultado:
| Fila | number_of_calls |
|---|---|
| 1 | 182104652 |
Usa una operación JOIN en cada fila
Para contar la cantidad total de llamadas de variantes en todas las muestras, usa una operación JOIN en cada fila con la columna call. La consulta usa el operador de coma (,), que es una notación abreviada para JOIN.
La unión con la columna call realiza una operación UNNEST implícita en la columna call.
#standardSQL
SELECT
COUNT(call) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
La consulta muestra el siguiente resultado:
| Fila | number_of_calls |
|---|---|
| 1 | 182104652 |
Cómo contar name en una columna call
Una tercera forma de contar la cantidad total de llamadas de variantes en todas las muestras es contar los valores name en la columna call. Cada columna call debe tener un único valor name para que puedas ejecutar la siguiente consulta:
#standardSQL
SELECT
COUNT(call.name) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
La consulta muestra el siguiente resultado:
| Fila | number_of_calls |
|---|---|
| 1 | 182104652 |
Cómo contar segmentos variantes y no variantes
Para contar la cantidad de segmentos variantes y no variantes en la tabla, primero ejecuta una consulta a fin de filtrar los segmentos no variantes:
#standardSQL
SELECT
COUNT(1) AS number_of_real_variants
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.alternate_bases) AS alt
WHERE
alt.alt NOT IN ("<NON_REF>", "<*>"))
La consulta muestra el siguiente resultado:
| Fila | number_of_real_variants |
|---|---|
| 1 | 38549388 |
Como se señala en Cómo contar las llamadas de variantes, la cantidad total de llamadas de variantes en la tabla es de 182,104,652, por lo que este resultado muestra que la mayoría de filas en la tabla son segmentos coincidentes.
Como se muestra en la sección Variantes y no variantes en la tabla, existen por lo menos tres formas de clasificar una fila variante como un segmento no variante. En la consulta anterior, la cláusula WHERE incluye filas donde la columna alternate_bases tiene un valor que es una variante verdadera, es decir, no es un valor de marcador especial como <*> o <NON_REF>.
Para cada fila de la tabla, se realiza una subconsulta sobre la columna alternate_bases de esa fila, que muestra el valor 1 de cada valor de alternate_bases distinto de <NON_REF> o <*>. La cantidad de filas que muestra la subconsulta es la cantidad de segmentos variantes.
La siguiente consulta muestra cómo obtener el recuento de segmentos no variantes:
#standardSQL
SELECT
COUNT(1) AS number_of_non_variants
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
NOT EXISTS (SELECT 1
FROM UNNEST(v.alternate_bases) AS alt
WHERE
alt.alt NOT IN ("<NON_REF>", "<*>"))
La consulta muestra el siguiente resultado:
| Fila | number_of_non_variants |
|---|---|
| 1 | 143555264 |
La suma entre la cantidad de variantes reales (38,549,388) y la cantidad de segmentos no variantes (143,555,264) es igual a la cantidad total de llamadas de variantes.
Cómo contar las variantes que llama cada muestra
Tras haber examinado las filas del nivel superior de la tabla, puedes comenzar a consultar las filas secundarias. Estas filas incluyen datos como las muestras individuales en las que se llamaron las variantes.
Cada variante de la tabla tiene cero o más valores para call.name. Un valor call.name específico puede aparecer en varias filas.
Para contar la cantidad de filas en las cuales aparece cada conjunto de llamadas, ejecuta la siguiente consulta:
#standardSQL
SELECT
call.name AS call_name,
COUNT(call.name) AS call_count_for_call_set
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
GROUP BY
call_name
ORDER BY
call_name
Si ejecutas la consulta obtendrás seis filas como resultado. Cada call_name corresponde a un ser humano individual secuenciado:
| Fila | call_name | call_count_for_call_set |
|---|---|---|
| 1 | NA12877 | 31592135 |
| 2 | NA12878 | 28012646 |
| 3 | NA12889 | 31028550 |
| 4 | NA12890 | 30636087 |
| 5 | NA12891 | 33487348 |
| 6 | NA12892 | 27347886 |
Generalmente, los seres humanos no tienen las 30 millones de variantes que se muestran en los valores de call_count_for_call_set. Filtra los segmentos no variantes para contar solamente las filas variantes:
#standardSQL
SELECT
call.name AS call_name,
COUNT(call.name) AS call_count_for_call_set
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.alternate_bases) AS alt
WHERE
alt.alt NOT IN ("<NON_REF>", "<*>"))
GROUP BY
call_name
ORDER BY
call_name
La consulta muestra el siguiente resultado:
| Fila | call_name | call_count_for_call_set |
|---|---|---|
| 1 | NA12877 | 6284275 |
| 2 | NA12878 | 6397315 |
| 3 | NA12889 | 6407532 |
| 4 | NA12890 | 6448600 |
| 5 | NA12891 | 6516669 |
| 6 | NA12892 | 6494997 |
La cantidad de variantes ahora se aproxima más a los 6 millones, que es más normal para un humano. Pasa a la siguiente sección para filtrar variantes verdaderas por genotipo.
Filtra variantes verdaderas por genotipo
Las variantes de la tabla incluyen llamadas no realizadas, que se representan mediante un valor genotype de -1. Estas no se consideran variantes verdaderas para individuos, así que debes filtrarlas. Las variantes verdaderas solo pueden incluir llamadas con genotipos mayores que cero. Si una llamada incluye solo genotipos que son llamadas no realizadas (-1) o de referencia (0), entonces no son variantes verdaderas.
Para filtrar las variantes por genotipo, ejecuta la siguiente consulta:
#standardSQL
SELECT
call.name AS call_name,
COUNT(call.name) AS call_count_for_call_set
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt > 0)
AND NOT EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt < 0)
GROUP BY
call_name
ORDER BY
call_name
La consulta muestra el siguiente resultado:
| Fila | call_name | call_count_for_call_set |
|---|---|---|
| 1 | NA12877 | 4486610 |
| 2 | NA12878 | 4502017 |
| 3 | NA12889 | 4422706 |
| 4 | NA12890 | 4528725 |
| 5 | NA12891 | 4424094 |
| 6 | NA12892 | 4495753 |
Cómo contar las muestras en la tabla
En Cómo contar las variantes que llama cada muestra, cada consulta mostró seis filas con valores para call_name. Para consultar y obtener el valor de la cantidad de filas, ejecuta la siguiente consulta:
#standardSQL
SELECT
COUNT(DISTINCT call.name) AS number_of_callsets
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
La consulta muestra el siguiente resultado:
| Fila | number_of_callsets |
|---|---|
| 1 | 6 |
Cómo contar las variantes por cromosoma
Para contar la cantidad de variantes por cromosoma, ejecuta la siguiente consulta. La consulta hace lo siguiente:
- Cuenta todas las filas en las cuales haya por lo menos una llamada de variante con un genotipo mayor que 0.
- Agrupa las filas de variantes por cromosoma y cuenta cada grupo.
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END
Cuando se ejecuta la consulta, se muestra el nombre del cromosoma (reference_name) y el número de filas de variantes de cada cromosoma:
| Fila | reference_name | number_of_variant_rows |
|---|---|---|
| 1 | chr1 | 615000 |
| 2 | chr2 | 646401 |
| 3 | chr3 | 542315 |
| 4 | chr4 | 578600 |
| 5 | chr5 | 496202 |
| … | … | … |
Cómo contar las variantes de alta calidad por muestra
Consulta llamadas con diversos valores FILTER
La especificación de VCF describe la columna FILTER.
que puedes usar para etiquetar llamadas de variantes de diferentes calidades.
La siguiente consulta muestra cómo ver los valores de FILTER por llamada variante para el conjunto de datos:
#standardSQL
SELECT
call_filter,
COUNT(call_filter) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,
v.call,
UNNEST(call.FILTER) AS call_filter
GROUP BY
call_filter
ORDER BY
number_of_calls
La consulta muestra el siguiente resultado:
| Fila | call_filter | number_of_calls |
|---|---|---|
| 1 | RefCall | 11681534 |
| 2 | PASS | 26867854 |
El valor PASS significa que una llamada de variante es de alta calidad.
Cómo usar FILTER en llamadas de variantes de alta calidad
Cuando analices variantes, te recomendamos filtrar las variantes de baja calidad.
Si la columna FILTER contiene el valor PASS, es probable que la columna no contenga ningún otro valor. Para verificarlo, ejecuta la siguiente consulta: Además, la consulta omite cualquier llamada que no contenga un valor PASS en FILTER.
#standardSQL
SELECT
reference_name,
start_position,
end_position,
reference_bases,
call.name AS call_name,
(SELECT STRING_AGG(call_filter) FROM UNNEST(call.FILTER) AS call_filter) AS filters,
ARRAY_LENGTH(call.FILTER) AS filter_count
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter = 'PASS')
AND ARRAY_LENGTH(call.FILTER) > 1
ORDER BY
filter_count DESC, reference_name, start_position, end_position, reference_bases, call_name
LIMIT
10
Como es de esperar, si ejecutas la consulta no obtendrás ningún resultado.
Cómo contar todas las llamadas de alta calidad de cada muestra
La siguiente consulta muestra cómo contar todas las llamadas (variantes y no variantes) para cada conjunto de llamadas y omite cualquier llamada con un filtro que no sea PASS.
#standardSQL
SELECT
call.name AS call_name,
COUNT(1) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
GROUP BY
call_name
ORDER BY
call_name
La consulta muestra el siguiente resultado:
| Fila | call_name | number_of_calls |
|---|---|---|
| 1 | NA12877 | 29795946 |
| 2 | NA12878 | 26118774 |
| 3 | NA12889 | 29044992 |
| 4 | NA12890 | 28717437 |
| 5 | NA12891 | 31395995 |
| 6 | NA12892 | 25349974 |
Cómo contar todas las llamadas de variantes verdaderas de alta calidad para cada muestra
La siguiente consulta muestra cómo contar todas las llamadas (variantes y segmentos coincidentes) de cada muestra. Omite cualquier llamada con un filtro que no sea PASS y solo incluye llamadas con al menos una variante verdadera, por lo que genotype es mayor que 0:
#standardSQL
SELECT
call.name AS call_name,
COUNT(1) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
AND EXISTS (SELECT 1 FROM UNNEST(call.genotype) as gt WHERE gt > 0)
GROUP BY
call_name
ORDER BY
call_name
La consulta muestra el siguiente resultado:
| Fila | call_name | number_of_calls |
|---|---|---|
| 1 | NA12877 | 4486610 |
| 2 | NA12878 | 4502017 |
| 3 | NA12889 | 4422706 |
| 4 | NA12890 | 4528725 |
| 5 | NA12891 | 4424094 |
| 6 | NA12892 | 4495753 |
Recomendaciones
Simplifica las consultas
A medida que las consultas se vuelven más complejas, es importante que las mantengas concisas a fin de garantizar que su lógica sea correcta y simple de seguir.
En el siguiente ejemplo, se muestra cómo comenzar desde una consulta que cuenta la cantidad de variantes por cromosoma y, paso a paso, simplificarla mediante la sintaxis SQL y funciones definidas por el usuario.
Tal como se explicó en la sección Cómo contar variantes por cromosoma, la consulta tiene los siguientes requisitos:
- Cuenta todas las filas en las cuales haya por lo menos una llamada de variante con un genotipo mayor que 0.
- Agrupa las filas de variantes por cromosoma y cuenta cada grupo.
Escribir esta consulta puede ser complicado porque, para completar la primera tarea, es necesario analizar un ARRAY (genotype) dentro de otro ARRAY (call) y mantener el contexto de ejecución de la consulta en el nivel de la fila. Mantienes el contexto de ejecución de la consulta a nivel de la fila porque deseas producir un resultado por variante, no por call o genotype.
La función UNNEST te permite consultar una columna de ARRAY como si esta fuera una tabla. La función muestra una fila por cada elemento de un ARRAY. Tampoco cambia el contexto de la consulta. Usa una función UNNEST en una subconsulta EXISTS en una cláusula WHERE:
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call
WHERE EXISTS (SELECT 1
FROM UNNEST(call.genotype) AS gt
WHERE gt > 0))
GROUP BY
reference_name
ORDER BY
reference_name
La consulta muestra los mismos resultados que el ejemplo de Cómo contar variantes por cromosoma:
| Fila | reference_name | number_of_variant_rows |
|---|---|---|
| 1 | chr1 | 615000 |
| 2 | chr10 | 396773 |
| 3 | chr11 | 391260 |
| 4 | chr12 | 382841 |
| 5 | chr13 | 298044 |
| … | … | ... |
Para que la consulta sea más concisa, cambia la cláusula EXISTS por una JOIN de la columna call con la columna call.genotype. El operador de coma es una notación abreviada para JOIN.
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY
reference_name
La consulta funciona y es concisa, pero no te permite ordenar el resultado en orden numérico ascendente de cromosomas (reference_name). Esto se debe a que los valores de reference_name son tipos de string y cada valor contiene el prefijo "chr".
Para ordenar el resultado numéricamente, primero quita el prefijo "chr" de la columna reference_name y asígnale el alias chromosome:
#standardSQL
SELECT
REGEXP_REPLACE(reference_name, '^chr', '') AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
La consulta utiliza la función REGEXP_REPLACE para reemplazar la string de prefijo "chr" con una string vacía. Luego, la consulta cambia las funciones GROUP BY y ORDER BY para usar el alias calculado chromosome. El resultado se sigue ordenando según la cadena:
| Fila | cromosoma | number_of_variant_rows |
|---|---|---|
| 1 | 1 | 615000 |
| 2 | 10 | 396773 |
| 3 | 11 | 391260 |
| 4 | 12 | 382841 |
| 5 | 13 | 298044 |
| … | … | … |
Si, en cambio, deseas ordenar los resultados de forma numérica, convierte la columna chromosome de una string en un número entero:
#standardSQL
SELECT
CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
La consulta devuelve un error porque no todos los nombres de cromosomas, como
como "X", "Y", y "M" son numéricos. Usa la función CASE para anteponer un "0" a los cromosomas del 1 al 9 y quita el prefijo "chr":
#standardSQL
SELECT
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
La consulta muestra el resultado correcto:
| Fila | cromosoma | number_of_variant_rows |
|---|---|---|
| 1 | 01 | 615000 |
| 2 | 02 | 646401 |
| 3 | 03 | 542315 |
| 4 | 04 | 578600 |
| 5 | 05 | 496202 |
| … | … | ... |
La consulta usa la función SAFE_CAST, que muestra NULL para los cromosomas X, Y y M en vez de mostrar un error.
Como última mejora en el resultado, muestra la columna reference_name nuevamente en vez de configurarla como el alias de chromosome. Para hacerlo, mueve la cláusula CASE a la función ORDER BY:
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END
Esta última consulta es la misma que la que se muestra en Cómo contar variantes por cromosoma.
Escribe funciones definidas por el usuario
BigQuery admite funciones definidas por el usuario. Puedes usar funciones definidas por el usuario para crear una función con otra expresión SQL o con otro lenguaje de programación, como JavaScript.
En el ejemplo incluido en Simplifica las consultas, se muestra cómo crear una consulta compleja, pero que es demasiado compleja.
En la siguiente consulta, se muestra cómo hacer que una consulta sea más concisa, para lo cual se mueve la lógica CASE a una función:
#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
RETURNS STRING AS (
-- Remove the leading "chr" (if any) in the reference_name
-- If the chromosome is 1 - 9, prepend a "0" since
-- "2" sorts after "10", but "02" sorts before "10".
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END
);
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY SortableChromosome(reference_name)
Además, la siguiente consulta también demuestra cómo hacer que una sea más concisa, pero utiliza una función definida en JavaScript:
#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
RETURNS STRING LANGUAGE js AS """
// Remove the leading "chr" (if any) in the reference_name
var chr = reference_name.replace(/^chr/, '');
// If the chromosome is 1 - 9, prepend a "0" since
// "2" sorts after "10", but "02" sorts before "10".
if (chr.length == 1 && '123456789'.indexOf(chr) >= 0) {
return '0' + chr;
}
return chr;
""";
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY SortableChromosome(reference_name)
Ambas consultas muestran el resultado correcto, pero su lógica es más concisa.
Mejora el rendimiento de las consultas y reduce los costos
El precio de BigQuery se basa en la cantidad de bytes procesados por consulta. El rendimiento de la consulta también mejora cuando la cantidad de datos procesados se reduce. La IU de BigQuery proporciona datos sobre cuántos segundos han pasado desde que se inició la consulta y cuántos bytes procesó esta. Consulta la Explicación del plan de consultas de BigQuery para obtener información sobre cómo optimizar tus consultas.
Algunos de los ejemplos presentes en esta página, como el de Cómo contar las llamadas de variantes en una tabla, muestran diversas formas de escribir una consulta. A fin de determinar qué método de consultas es el ideal para ti, analiza la duración de diferentes consultas y fíjate cuántos bytes de datos procesaron.
Limpia
Una vez que completes el instructivo, puedes limpiar los recursos que creaste para que dejen de usar la cuota y generar cargos. En las siguientes secciones, se describe cómo borrar o desactivar estos recursos.
La manera más fácil de eliminar la facturación es borrar el proyecto que creaste para el instructivo.
Para borrar el proyecto, sigue estos pasos:
- En la consola de Google Cloud, ve a la página Administrar recursos.
- En la lista de proyectos, elige el proyecto que quieres borrar y haz clic en Borrar.
- En el diálogo, escribe el ID del proyecto y, luego, haz clic en Cerrar para borrar el proyecto.
¿Qué sigue?
- Lee los otros instructivos de Cloud Life Sciences.
- Analiza variantes en BigQuery mediante R, RMarkdown o JavaScript.