Guia avançado para analisar variantes usando o BigQuery

Nesta página, você verá métodos avançados de como usar o BigQuery para analisar variantes.

Os dados deste tutorial são provenientes do projeto Illumina Platinum Genomes. Os dados foram carregados em uma tabela do BigQuery que usa o esquema de variantes do BigQuery. O nome da tabela é platinum_genomes_deepvariant_variants_20180823.

Se os dados da sua variante estiverem em uma tabela do BigQuery que usa o esquema de variantes dele, a aplicação das consultas neste tutorial aos seus dados será direta. Para informações sobre como carregar dados de variantes no BigQuery, consulte a documentação sobre o uso do canal de transformação.

Objetivos

Neste tutorial, mostramos como fazer o seguinte:

  • Tenha uma visão geral dos dados do Genomics.
  • Descubra como os segmentos não variantes são representados.
  • Descubra como as chamadas de variantes são representadas.
  • Descubra como os filtros de qualidade das chamadas de variantes são representados.
  • Agregue colunas hierárquicas.
  • Condensar consultas.
  • Contar linhas distintas.
  • Agrupar linhas.
  • Escreva funções definidas pelo usuário.

Neste tutorial, também mostramos como encontrar as seguintes informações:

  • Número de linhas na tabela
  • Número de chamadas de variantes
  • Variantes chamadas para cada amostra
  • Número de amostras
  • Variantes por cromossomo
  • variantes de alta qualidade por amostra

Custos

Neste tutorial, há componentes faturáveis do Google Cloud, entre eles:

  • BigQuery

Use a Calculadora de preços para gerar uma estimativa de custo com base no uso previsto. Usuários novos do Cloud Platform podem ter direito a uma avaliação gratuita.

Antes de começar

  1. Faça login na sua conta do Google Cloud. Se você começou a usar o Google Cloud agora, crie uma conta para avaliar o desempenho de nossos produtos em situações reais. Clientes novos também recebem US$ 300 em créditos para executar, testar e implantar cargas de trabalho.
  2. No Console do Google Cloud, na página do seletor de projetos, selecione ou crie um projeto do Google Cloud.

    Acessar o seletor de projetos

  3. Verifique se o faturamento está ativado para seu projeto na nuvem. Saiba como confirmar se o faturamento está ativado para o projeto.

  4. Você precisa estar familiarizado com o esquema de variantes do BigQuery.

Como visualizar o esquema e os dados da tabela

Acessar a tabela e ver o esquema

A tabela platinum_genomes_deepvariant_variants_20180823 do Platinum Genomes da Illumina está disponível publicamente.

Variantes e não variantes na tabela

Os dados do projeto Illumina Platinum Genomes usam o formato gVCF, o que significa que há linhas na tabela que incluem não variantes. Essas não variantes também são conhecidas como "chamadas de referência".

Na tabela, os segmentos não variantes são geralmente representados das seguintes maneiras:

  • Com um valor alternate_bases de comprimento zero
  • Com a string de texto <NON_REF> como um valor alternate_bases.alt
  • Com a string de texto <*> como um valor alternate_bases.alt

A maneira como os segmentos não variantes são representados normalmente depende da chamada de variante que gerou os dados de origem. As variantes na tabela platinum_genomes_deepvariant_variants_20180823 foram chamadas usando DeepVariant, que usa a notação <*>.

As tabelas a seguir mostram algumas linhas contendo valores que representam segmentos não variantes. Os segmentos mostram as seguintes informações:

  • Um bloco de referência de 10 bases no cromossomo 1
  • O bloco de referência começa na posição 1000
  • A base de referência na posição 1000 é um A
  • As bases de referência em outras posições do bloco não são mostradas

Na tabela a seguir, a coluna alternate_bases REPEATED RECORD não contém valores, o que significa que é um ARRAY de tamanho 0.

reference_name start_position end_position reference_bases alternate_bases.alt
1 1000 1010 A

Na tabela a seguir, a coluna alternate_bases REPEATED RECORD tem comprimento 1 e contém a string de texto literal <*>.

reference_name start_position end_position reference_bases alternate_bases.alt
1 1000 1010 A <*>

As consultas usadas neste guia usam as representações nas tabelas anteriores.

Consulte a especificação do VCF para mais informações sobre como representar posições não variantes no genoma.

Como visualizar os dados da tabela

Para visualizar os dados na tabela platinum_genomes_deepvariant_variants_20180823, siga estes passos:

  1. Veja a tabela na página do BigQuery no Console do Cloud.

    Acesse a página do BigQuery

    As informações sobre a tabela são exibidas. A tabela contém 19,6 GB de dados e mais de 105 milhões de linhas.

  2. Clique em Visualizar para ver algumas das linhas na tabela.

Como consultar a tabela

Depois de visualizar o esquema da tabela e algumas das linhas dela, comece a emitir consultas e analisar dados. Antes de continuar, conheça a sintaxe da consulta SQL padrão usada pelo BigQuery.

Como contar as linhas totais na tabela

Para ver o número de linhas na tabela:

  1. Acesse a página do BigQuery no Console do Cloud.

    Acesse a página do BigQuery

  2. Clique em Compose query.

  3. Copie e cole a seguinte consulta na área de texto Nova consulta:

     #standardSQL
     SELECT
       COUNT(1) AS number_of_rows
     FROM
       `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

  4. Clique em Run query. A consulta retorna o seguinte resultado:

    Row number_of_rows
    1 105923159

Como contar as chamadas de variantes na tabela

Cada linha na tabela tem uma posição genômica que é um segmento variante ou não variante.

Cada linha também contém uma coluna call, que é uma ARRAY de chamadas variantes. Cada coluna call inclui name e outros valores, como genótipo, colunas de qualidade, profundidade de leitura e outros dados tipicamente encontrados em um arquivo VCF.

Para contar o número de chamadas de variantes, consulte o número de elementos dentro das colunas ARRAY. É possível fazer isso de várias maneiras, que são mostradas a seguir. Cada consulta retorna o valor 182.104.652, o que significa que há uma média de 1,7 chamadas de variantes por linha no conjunto de dados.

Como somar os comprimentos de matrizes call

Conte o número total de chamadas de variantes em todas as amostras adicionando o comprimento de cada matriz call:

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

A consulta retorna o seguinte resultado:

Row number_of_calls
1 182104652

Como mesclar (JOIN) as linhas

Contar o número total de chamadas de variantes em todas as amostras usando um JOIN em cada linha com a coluna call. A consulta usa o operador de vírgula (,), que é uma notação abreviada usada para JOIN. A junção com a coluna call faz uma operação UNNEST implícita na coluna call.

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

A consulta retorna o seguinte resultado:

Row number_of_calls
1 182104652

Contagem de name em uma coluna call

Uma terceira maneira de contar o número total de chamadas variantes em todas as amostras é contar os valores name na coluna call. Cada coluna call precisa ter um único valor name. Assim, você pode executar a seguinte 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

A consulta retorna o seguinte resultado:

Row number_of_calls
1 182104652

Como contar segmentos de variantes e não variantes

Para contar o número de segmentos de variantes e não variantes na tabela, primeiro execute uma consulta para filtrar os segmentos de não 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>", "<*>"))

A consulta retorna o seguinte resultado:

Row number_of_real_variants
1 38549388

Conforme mostrado em Como contar chamadas de variantes, o número total de chamadas de variantes na tabela é 182.104.652. Portanto, o resultado mostra que a maioria das linhas na tabela não é segmentos variantes.

Conforme mostrado na seção Variantes e não variantes na tabela, há pelo menos três maneiras de classificar uma linha de variantes como um segmento não variante. Na consulta acima, a cláusula WHERE inclui linhas em que a coluna alternate_bases tem um valor que é uma variante verdadeira, o que significa que não é um valor de marcador especial, como <*> ou <NON_REF>.

Para cada linha na tabela, uma subconsulta é emitida naalternate_bases coluna dessa linha, que retorna o valor1 para cada valor de alternate_bases não é<NON_REF> ou<*> , O número de linhas que a subconsulta retorna é o número de segmentos de variantes.

A consulta a seguir mostra como receber a contagem de segmentos de não 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>", "<*>"))

A consulta retorna o seguinte resultado:

Row number_of_non_variants
1 143555264

A adição do número de variantes reais, 38.549.388, ao número de segmentos não variantes, 143.555.264, é igual ao número total de chamadas de variantes.

Como contar as variantes chamadas por cada amostra

Depois de examinar as linhas de nível superior na tabela, você pode começar a consultar linhas filhas. Essas linhas incluem dados, como as amostras individuais que tiveram chamadas feitas nas variantes.

Cada variante na tabela tem zero ou mais valores para call.name. Um determinado valor call.name pode aparecer em várias linhas.

Para contar o número de linhas em que cada conjunto de chamadas é exibido, execute a seguinte 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

A execução da consulta retorna seis linhas. Cada call_name corresponde a um indivíduo humano sequenciado:

Row 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

Os humanos normalmente não têm as 30 milhões de variantes mostradas nos valores para call_count_for_call_set. Filtre os segmentos não variantes para contar apenas as linhas de 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

A consulta retorna o seguinte resultado:

Row 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

O número de variantes agora está mais próximo de 6 milhões, o que é mais típico para um ser humano. Continue na próxima seção para filtrar variantes verdadeiras por genótipo.

Como filtrar variantes verdadeiras por genótipo

As variantes na tabela incluem no-calls, que são representadas por um valor genotype de -1. Como essas variantes não são consideradas verdadeiras para indivíduos, é preciso filtrá-las. As variantes verdadeiras só podem incluir chamadas com genótipos maiores que zero. Se uma chamada incluir apenas genótipos sem chamadas (-1) ou referência (0), eles não serão variantes verdadeiras.

Para filtrar as variantes por genótipo, execute a seguinte 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

A consulta retorna o seguinte resultado:

Row 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

Como contar amostras na tabela

Em Como contar as variantes chamadas por cada amostra, cada consulta retornou seis linhas com valores para call_name. Para consultar e receber o valor do número de linhas, execute a seguinte 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

A consulta retorna o seguinte resultado:

Row number_of_callsets
1 6

Como contar variantes por cromossomo

Para contar o número de variantes por cromossomo, execute a consulta a seguir. A consulta faz o seguinte:

  • Conta todas as linhas em que há pelo menos uma chamada de variante com pelo menos um genótipo maior que 0.
  • Agrupa as linhas variantes por cromossomo e conta 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

A execução da consulta retorna o nome do cromossomo (reference_name) e o número de linhas variantes para cada cromossomo:

Row reference_name number_of_variant_rows
1 chr1 615000
2 chr2 646401
3 chr3 542315
4 chr4 578600
5 chr5 496202
...

Como contar variantes de alta qualidade por amostra

Consultando chamadas com vários valores FILTER

A especificação do VCF descreve a coluna FILTER que pode ser usada para rotular chamadas de variantes de qualidades diferentes.

A consulta a seguir mostra como exibir os valores de FILTER por chamada de variante para o conjunto de dados:

#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

A consulta retorna o seguinte resultado:

Row call_filter number_of_calls
1 RefCall 11681534
2 PASS 26867854

O valor PASS indica que uma chamada de variante é de alta qualidade.

Como filtrar (FILTER) chamadas de variantes de alta qualidade

Ao analisar variantes, convém filtrar variantes de qualidade inferior. Se a coluna FILTER contiver o valor PASS, é provável que a coluna não contenha outros valores. Para confirmar, execute a seguinte consulta: Na consulta, também são omitidas as chamadas que não contêm um valor PASS em 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 esperado, a execução da consulta retorna zero resultados.

Como contar todas as chamadas de alta qualidade para cada amostra

A consulta a seguir mostra como contar todas as chamadas (variantes e não variantes) para cada conjunto de chamadas e omite qualquer chamada com um filtro não 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

A consulta retorna o seguinte resultado:

Row call_name number_of_calls
1 NA12877 29795946
2 NA12878 26118774
3 NA12889 29044992
4 NA12890 28717437
5 NA12891 31395995
6 NA12892 25349974

Como contar todas as chamadas de variantes verdadeiras de alta qualidade para cada amostra

A consulta a seguir mostra como contar todas as chamadas (variantes e não variantes) para cada amostra. Ela omite qualquer chamada com um filtro diferente de PASS e inclui somente chamadas com pelo menos uma variante verdadeira, o que significa que genotype > 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

A consulta retorna o seguinte resultado:

Row call_name number_of_calls
1 NA12877 4486610
2 NA12878 4502017
3 NA12889 4422706
4 NA12890 4528725
5 NA12891 4424094
6 NA12892 4495753

Práticas recomendadas

Como condensar consultas

À medida que suas consultas se tornam mais complexas, é importante mantê-las concisas para garantir que sua lógica seja correta e simples de seguir.

O exemplo a seguir demonstra como iniciar a partir de uma consulta que conta o número de variantes por cromossomo e, passo a passo, o condensa usando sintaxe SQL e funções definidas pelo usuário.

Conforme explicado na seção sobre como contar variantes por cromossomo, a consulta tem os seguintes requisitos:

  • Conta todas as linhas em que há pelo menos uma chamada de variante com pelo menos um genótipo maior que 0.
  • Agrupa as linhas variantes por cromossomo e conta cada grupo.

Gravar essa consulta pode ser complicado, já que, para concluir a primeira tarefa, você precisa analisar ARRAY (genotype) em ARRAY (call) enquanto mantém o contexto de execução da consulta no nível da linha; Você mantém o contexto de execução da consulta no nível da linha porque quer produzir um resultado por variante, em vez de um resultado por call ou por genotype.

A função UNNEST permite consultar uma coluna ARRAY como se a coluna fosse uma tabela. A função retorna uma linha para cada elemento de ARRAY. Ela também não altera o contexto da consulta. Use uma função UNNEST em uma subconsulta EXISTS em uma 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

A consulta retorna os mesmos resultados do exemplo na seção sobre como contar variantes por cromossomo:

Row reference_name number_of_variant_rows
1 chr1 615000
2 chr10 396773
3 chr11 391260
4 chr12 382841
5 chr13 298044
...

Para tornar a consulta mais concisa, altere a cláusula EXISTS para JOIN da coluna call com a coluna call.genotype. O operador vírgula é uma notação abreviada usada 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

A consulta funciona e é concisa, mas não permite classificar a saída em ordem numérica crescente de cromossomos (reference_name) porque os valores em reference_name são tipos de string, e cada um valor contém o prefixo "chr".

Para classificar a saída em ordem numérica, primeiro remova o prefixo "chr" da coluna reference_name e informe o 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

A consulta usa a função REGEXP_REPLACE para substituir a string de prefixo "chr" por uma string vazia. Em seguida, a consulta altera as funções GROUP BY e ORDER BY para usar o alias chromosome calculado. A saída ainda é classificada por string:

Row chromosome number_of_variant_rows
1 1 615000
2 10 396773
3 11 391260
4 12 382841
5 13 298044
...

Para classificar a saída em ordem numérica, transfira a coluna chromosome de uma string para um número inteiro:

#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

A consulta retorna um erro porque nem todos os nomes de cromossomo, como "X", "Y" e "M", são numéricos. Use a função CASE para prefixar um "0" para os cromossomos de 1 a 9 e remover o prefixo "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

A consulta retorna a saída correta:

Row chromosome number_of_variant_rows
1 01 615000
2 02 646401
3 03 542315
4 04 578600
5 05 496202
...

A consulta usa a função SAFE_CAST, que retorna NULL para os cromossomos X, Y e M em vez de retornar um erro.

Como última melhoria na saída, exiba a coluna reference_name novamente em vez de defini-la como o alias chromosome. Para fazer isso, mova a cláusula CASE para a função 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

Essa consulta final é a mesma mostrada em Como contar variantes por cromossomo.

Como gravar funções definidas pelo usuário

O BigQuery é compatível com funções definidas pelo usuário. É possível usar funções definidas pelo usuário para criar uma função usando outra expressão SQL ou outra linguagem de programação, como JavaScript.

O exemplo em Como condensar consultas mostra como criar uma consulta complexa, mas a consulta é excessivamente complexa.

A consulta a seguir demonstra como tornar a consulta mais concisa movendo a lógica CASE para uma função:

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

A consulta a seguir também demonstra como tornar a consulta mais concisa, mas usa uma função definida em 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 as consultas retornam o resultado correto, e sua lógica é mais concisa.

Como melhorar o desempenho de consultas e reduzir custos

Os preços do BigQuery baseiam-se no número de bytes processados para uma consulta. O desempenho da consulta melhora quando a quantidade de dados processados é reduzida. O BigQuery fornece dados sobre quantos segundos se passaram desde que uma consulta foi iniciada e quantos bytes a consulta processou. Consulte a explicação do plano de consulta do BigQuery para informações sobre como otimizar suas consultas.

Alguns dos exemplos nesta página, como na seção Como contar as chamadas de variantes em uma consulta, demonstram várias maneiras de criar uma consulta. Para determinar qual método de consulta é melhor para você, examine a duração de consultas diferentes e veja quantos bytes de dados elas processam.

Limpeza

Depois de concluir o tutorial, limpe os recursos que você criou para que eles deixem de usar a cota e gerem cobranças. Nas seções a seguir, você aprenderá a excluir e desativar esses recursos.

O jeito mais fácil de evitar o faturamentos é excluindo o projeto criado para este tutorial.

Para excluir o projeto:

  1. No Console do Cloud, acesse a página Gerenciar recursos:

    Acessar "Gerenciar recursos"

  2. Na lista de projetos, selecione o projeto que você quer excluir e clique em Excluir .
  3. Na caixa de diálogo, digite o ID do projeto e clique em Encerrar para excluí-lo.

A seguir