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

Después de completar el instructivo, sabrás cómo realizar las actividades que se describen a continuación:

  • Obtener una descripción general de los datos
  • 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 lo siguiente:

  • La cantidad de filas en la tabla
  • La cantidad de llamadas de variantes
  • Llamadas de variantes para cada muestra
  • La cantidad de muestras
  • Variantes según cromosoma
  • Variantes de calidad alta por muestra

Costos

En este instructivo se usan componentes facturables de Google Cloud Platform, como el que se indica a continuación:

  • BigQuery

Usa la calculadora de precios para generar una estimación de los costos según el uso previsto. Los usuarios nuevos de Cloud Platform podrían ser aptos para una prueba gratuita.

Antes de comenzar

  1. Accede a tu Cuenta de Google.

    Si todavía no tienes una cuenta, regístrate para obtener una nueva.

  2. Selecciona o crea un proyecto de GCP.

    Ir a la página Administrar recursos

  3. Comprueba que la facturación esté habilitada en tu proyecto.

    Descubre cómo puedes habilitar la facturación

  4. 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 de Illumina platinum_genomes_deepvariant_variants_20180823 está disponible para el público a través de este vínculo.

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, estos segmentos de no variantes suelen estar representados de las siguientes maneras:

  • Con un valor de longitud cero alternate_bases
  • Con la string de texto <NON_REF> como un valor alternate_bases.alt
  • Con la string de texto <*> como un valor alternate_bases.alt

La forma en que se representan los segmentos que no son variantes suele depender de la variante que hace la llamada y generó los datos de origen. Las variantes en la tabla platinum_genomes_deepvariant_variants_20180823 fueron llamadas mediante DeepVariant, que utiliza la notación <*>.

En las siguientes tablas, se muestran algunas filas con valores que representan segmentos no variantes. Los segmentos muestran un bloque de referencia de 10 bases en el cromosoma 1. El bloque de referencia comienza en la posición 1000. La base de referencia en la posición 1000 es A. Las bases de referencia en las otras posiciones del bloque no se muestran.

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 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 tres representaciones que se muestran arriba.

Consulta 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, sigue los pasos a continuación:

  1. Ve a la página Detalles en la IU de BigQuery.

    Aparecerá información acerca de la tabla. Puedes ver que contiene 19.6 GB de datos y que tiene más de 105,000,000 filas.

  2. Haz clic en Vista previa para ver algunas de las filas de la tabla.

Cómo consultar la tabla

Una vez que hayas visto el esquema de la tabla y algunas de sus filas, podrás comenzar 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 utiliza 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 IU de BigQuery.

    Ir a la IU 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. Haz clic en Ejecutar consulta. Una vez que hayas ejecutado la consulta, se mostrará 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 no variante.

Además, cada fila contiene una columna de call, que es un ARRAY de llamadas de variantes. Cada columna de call incluye el name y otros valores, como el genotipo, las columnas de calidad, la profundidad de lectura y otros 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.

Suma la longitud de los arreglos de call

Una forma de contar la cantidad total de llamadas de variantes en todas las muestras es sumar la longitud de cada arreglo de call:

#standardSQL
SELECT
  SUM(ARRAY_LENGTH(call)) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

Cuando se ejecuta la consulta se muestra el valor correcto (182,104,652):

Fila number_of_calls
1 182104652

Usa una operación JOIN en cada fila

Una segunda forma de contar la cantidad total de llamadas de variantes en todas las muestras es JOIN cada fila con la columna call. Ten en cuenta el uso del operador con coma (,) que es una notación abreviada utilizada para JOIN. Además, ten en cuenta que la unión a la columna de call realiza una operación UNNEST implícita en la columna de call:

#standardSQL
SELECT
  COUNT(call) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call

Cómo contar el name en una columna de call

Una tercera manera forma de contar la cantidad total de llamadas de variantes en todas las muestras es contar los valores de name en la columna call. Cada columna call debe tener un solo valor de 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

Cuando se ejecuta la consulta se muestra el valor correcto (182,104,652):

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>", "<*>"))

Una vez que hayas ejecutado la consulta, se mostrará 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 gran mayoría de filas en la tabla son segmentos no variantes.

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 en las cuales la columna alternate_bases tiene un valor que es una variante verdadera (es decir que no es un valor de marcador especial como <*> o <NON_REF>).

Para cada fila en la tabla, se emite una subconsulta sobre la columna alternate_bases de esa fila, que muestra el valor 1 para cada valor de alternate_bases que no sea <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>", "<*>"))

Una vez que hayas ejecutado la consulta, se mostrará 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 muestras individuales que han realizado llamadas a las variantes.

Cada variante en la tabla tiene cero o más valores para call.name. Un valor call.name particular 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 se corresponde con un 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 humanos no tienen los 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

Una vez que hayas ejecutado la consulta, se mostrará 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 en la tabla incluyen llamadas no realizadas, que se representan mediante un valor genotype de -1. Estas variantes 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, haz lo siguiente:

#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

Una vez que hayas ejecutado la consulta, se mostrará 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. Por otro lado, si deseas consultar y obtener el valor de esa cantidad de filas, puedes ejecutar 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

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

Fila number_of_callsets
1 6

Cómo contar las variantes por cromosoma

Para contar la cantidad de variantes por cromosoma, puedes ejecutar la siguiente consulta, que hace lo que se detallada a continuación:

  • 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 ejecutas la consulta se muestra el nombre del cromosoma (reference_name) y la cantidad de filas de variantes para 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 puede utilizarse 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

Una vez que hayas ejecutado la consulta, se mostrará 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 calidad alta.

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

Cuando analices variantes, te recomendamos filtrar las variantes de calidad baja. Si la columna FILTER contiene el valor PASS, se espera 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 el 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 para 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

Una vez que hayas ejecutado la consulta, se mostrará 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 no variantes) para cada muestra. Omite cualquier llamada con un filtro que no sea PASS y solo incluye llamadas con, por lo 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

Una vez que hayas ejecutado la consulta, se mostrará 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 la consulta puede ser complicado, ya que, para completar la primera tarea, debes buscar en un ARRAY (genotype) dentro de un ARRAY (call) mientras que, a su vez, mantienes el contexto de ejecución de la consulta a nivel de la fila. Eso se debe a que buscas producir un resultado por variante, en vez de por call o por 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. Además, no cambia el contexto de la consulta. Por lo tanto, puedes comenzar con una función UNNEST en una subconsulta EXISTS en una cláusula de 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

La consulta puede ser más concisa si cambias la cláusula EXISTS por una JOIN de la columna call con la columna call.genotype. Ten en cuenta que el operador con coma es una notación abreviada que se utiliza 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 los resultados en orden de cromosomas numérico ascendente (reference_name). Esto se debe a que los valores en reference_name son tipos de string y cada valor contiene el prefijo "chr".

Para ordenar los resultados numéricamente, primero quita el prefijo "chr" de la columna reference_name y otórgale 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 la string de prefijo "chr" con una string vacía. Entonces, cambian las funciones GROUP BY y ORDER BY para utilizar un alias chromosome calculado. Sin embargo, el resultado se sigue ordenando según la 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

En este caso, la consulta mostrará un error debido a que no todos los nombres de los cromosomas, como "X", "Y" y "M" son numéricos. En su lugar, 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

Ten en cuenta el uso de 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 es compatible con Funciones definidas por el usuario, que te permiten crear una función mediante otra expresión SQL o un lenguaje de programación diferente, como JavaScript.

En el ejemplo presente en Simplifica las consultas, se muestra cómo crear una consulta compleja, que se vuelve demasiado detallada.

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.

Limpieza

Sigue estos pasos para evitar que se generen cargos en tu cuenta de Google Cloud Platform por los recursos que se usaron en este instructivo:

Una vez que hayas terminado el instructivo Cómo analizar variantes con BigQuery, limpia los recursos creados en Google Cloud Platform para que no se facturen en el futuro. La siguiente sección describe cómo borrar o desactivar estos recursos.

Cómo borrar el proyecto

La manera más fácil de eliminar la facturación es borrar el proyecto que creaste para el instructivo.

Para borrar el proyecto, haz lo siguiente:

  1. En la GCP Console, dirígete a la página Proyectos.

    Ir a la página Proyectos

  2. En la lista de proyectos, selecciona el proyecto que deseas borrar y haz clic en Borrar.
  3. En el cuadro de diálogo, escribe el ID del proyecto y, luego, haz clic en Cerrar para borrar el proyecto.

¿Qué sigue?

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

Enviar comentarios sobre...