Cloud Life Sciences se dio de baja y dejará de estar disponible en Google Cloud después del 8 de julio de 2025. Los casos de uso de Cloud Life Sciences ahora son compatibles con Batch. Si quieres obtener información para migrar tu carga de trabajo, consulta Migra a Batch.

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

Guía avanzada para analizar variantes con BigQuery

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. Es posible que los usuarios nuevos de Google Cloud califiquen para obtener una prueba gratuita.

Antes de comenzar

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

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_bases de longitud cero
Con la string de texto <NON_REF> como valor alternate_bases.alt
Con la string de texto <*> como valor alternate_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 10 se basa en el cromosoma 1.
El bloque de referencia comienza en la posición 1000
La base de referencia en la posición 1000 es una A
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.

Ir a la página de BigQuery

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.

Ir a la página de BigQuery
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

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:

Precaución: Borrar un proyecto tiene las siguientes consecuencias:

Se borra todo en el proyecto. Si usaste un proyecto existente para las tareas de este documento, cuando lo borres, también se borrará cualquier otro trabajo que hayas realizado en el proyecto.
Se pierden los ID personalizados de proyectos. Cuando creaste este proyecto, es posible que hayas creado un ID del proyecto personalizado que desees utilizar en el futuro. Para conservar las URL que utilizan el ID del proyecto, como una URL appspot.com, borra los recursos seleccionados dentro del proyecto en lugar de borrar todo el proyecto.

Si planeas explorar varias infraestructuras, instructivos y guías de inicio rápido la reutilización de proyectos puede ayudarte a evitar exceder los límites de las cuotas del proyecto.

In the Google Cloud console, go to the Manage resources page.
Go to Manage resources
In the project list, select the project that you want to delete, and then click Delete.
In the dialog, type the project ID, and then click Shut down to delete the project.

Guía avanzada para analizar variantes con BigQuery

Objetivos

Costos

Antes de comenzar

Cómo ver los datos y el esquema de la tabla

Accede a la tabla y ve el esquema

Variantes y no variantes en la tabla

Visualiza los datos de la tabla

Consulta la tabla

Cómo contar el total de filas en la tabla

Cómo contar las llamadas de variantes en la tabla

Cómo sumar las longitudes de los arrays de call

Usa una operación JOIN en cada fila

Cómo contar name en una columna call

Cómo contar segmentos variantes y no variantes

Cómo contar las variantes que llama cada muestra

Filtra variantes verdaderas por genotipo

Cómo contar las muestras en la tabla

Cómo contar las variantes por cromosoma

Cómo contar las variantes de alta calidad por muestra

Consulta llamadas con diversos valores FILTER

Cómo usar FILTER en llamadas de variantes de alta calidad

Cómo contar todas las llamadas de alta calidad de cada muestra

Cómo contar todas las llamadas de variantes verdaderas de alta calidad para cada muestra

Recomendaciones

Simplifica las consultas

Escribe funciones definidas por el usuario

Mejora el rendimiento de las consultas y reduce los costos

Limpia

¿Qué sigue?

Cómo sumar las longitudes de los arrays de `call`

Usa una operación `JOIN` en cada fila

Cómo contar `name` en una columna `call`

Consulta llamadas con diversos valores `FILTER`

Cómo usar `FILTER` en llamadas de variantes de alta calidad