Guía avanzada para analizar variantes con BigQuery

En esta página, se describen métodos avanzados sobre el uso de 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

1. 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.

2. En la página del selector de proyectos de la consola de Google Cloud, selecciona o crea un proyecto de Google Cloud.

   Nota: Si no planeas conservar los recursos que creaste durante este procedimiento, crea un proyecto en lugar de seleccionar uno existente. Cuando termines, puedes borrar el proyecto y quitar todos los recursos asociados con él.

   Ir al selector de proyectos

3. Asegúrate de que la facturación esté habilitada para tu proyecto de Google Cloud.

4. En la página del selector de proyectos de la consola de Google Cloud, selecciona o crea un proyecto de Google Cloud.

   Nota: Si no planeas conservar los recursos que creaste durante este procedimiento, crea un proyecto en lugar de seleccionar uno existente. Cuando termines, puedes borrar el proyecto y quitar todos los recursos asociados con él.

   Ir al selector de proyectos

5. Asegúrate de que la facturación esté habilitada para tu proyecto de Google Cloud.

6. 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 utilizadas en esta guía usan las representaciones de las tablas anteriores.

Consulta la especificación de VCF para obtener más información sobre la representación de posiciones no variantes en el genoma.

Visualiza los datos de la tabla

Para ver los datos en la tabla platinum_genomes_deepvariant_variants_20180823, completa los siguientes pasos:

1. Consulta la tabla en la página de BigQuery en Google Cloud Console.

   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.

2. 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 analizar los datos. Antes de continuar, asegúrate de estar familiarizado con la sintaxis de consulta de 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:

1. Ve a la página de BigQuery en la consola de Google Cloud.

   Ir a la página de BigQuery

2. Haz clic en Redactar consulta.

3. 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`

4. 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

Cuenta la cantidad total de llamadas de variantes en todas las muestras mediante un 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 a la columna call realiza una operación implícita UNNEST 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, por lo 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 utilizar para etiquetar llamadas de variantes de calidades distintas.

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

Prácticas recomendadas

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. Debes mantener el contexto de ejecución de la consulta en el nivel de la fila porque deseas producir un resultado por variante, en lugar de hacerlo 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
…	…	…

Puedes hacer que la consulta sea más concisa si cambias 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 por string:

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 muestra un error porque no todos los nombres de los cromosomas, como “X”, “Y” y “M” son numéricos. Utiliza 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 lugar 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 que se brinda en Simplifica las consultas, se muestra cómo crear una consulta compleja, 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, haga lo siguiente:

Precaución: Borrar un proyecto tiene estas 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.

1. En la consola de Google Cloud, ve a la página Administrar recursos.

   Ir a Administrar recursos

2. En la lista de proyectos, elige el proyecto que quieres borrar y haz clic en Borrar.
3. 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.