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

Depois de concluir este tutorial, você saberá como:

  • Ter uma visão geral dos dados
  • Descobrir como os segmentos não variantes são representados
  • Descobrir como as chamadas de variantes são representadas
  • Descobrir como os filtros de qualidade de chamadas de variantes são representados
  • Colunas hierárquicas agregadas
  • Condensar consultas
  • Contar linhas distintas
  • Agrupar linhas
  • Gravar funções definidas pelo usuário

Este tutorial também mostra como encontrar:

  • o número de linhas na tabela
  • o número de chamadas de variantes
  • variantes chamadas para cada amostra
  • o 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 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 mostradas neste guia usam as três representações acima.

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:

  1. Acesse a página Detalhes na IU do BigQuery.

    As informações sobre a tabela são exibidas. Você pode ver que ela 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, você pode começar a enviar consultas e analisar dados. Antes de continuar, você precisa estar familiarizado com a sintaxe de 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 IU do BigQuery.

    Acessar a IU do BigQuery

  2. Clique em Escrever consulta.

  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 Executar consulta. A execução da consulta retorna:

    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 o name e outros valores, como genótipo, colunas de qualidade, profundidade de leitura e outros valores normalmente 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, como as mostradas abaixo. 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

Uma maneira de contar o número total de chamadas variantes em todas as amostras é somar o tamanho 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 execução da consulta retorna o valor correto (182.104.652):

Row number_of_calls
1 182104652

Como mesclar (JOIN) as linhas

Uma segunda forma de contabilizar o número total de chamadas variantes em todas as amostras é JOIN cada linha com uma coluna call. Observe o uso do operador vírgula (,), que é uma notação abreviada utilizada para JOIN. Observe também que a junção à 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

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 call precisa ter um único name para que você possa 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 execução da consulta retorna o valor correto (182.104.652):

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 execução da consulta retorna:

Row number_of_real_variants
1 38549388

Conforme indicado em Como contar as chamadas de variantes, o número total de chamadas de variantes na tabela é 182.104.652. Portanto, esse resultado mostra que a grande maioria das linhas na tabela são segmentos não 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 (ou seja, não é um valor de marcador especial como <*> ou <NON_REF>).

Para cada linha na tabela, uma subconsulta é emitida sobre a coluna alternate_bases dessa linha, que retorna o valor 1 para cada valor de alternate_bases que 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 execução da consulta retorna:

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 de 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 execução da consulta retorna:

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. Essas variantes não são consideradas verdadeiras para indivíduos. Por isso, filtre-as. 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:

#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 execução da consulta retorna:

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. Em vez disso, para consultar e receber o valor desse 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 execução da consulta retorna:

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, que:

  • conta todas as linhas em que haja pelo menos uma chamada de variante com pelo menos um genótipo maior que 0;
  • agrupa as linhas de variantes pelo 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 (em inglês) 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 execução da consulta retorna:

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, espera-se que a coluna não contenha outros valores. Para confirmar, execute a seguinte consulta: A consulta também omite chamadas que não contenham 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 execução da consulta retorna:

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 execução da consulta retorna:

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:

  • Contar todas as linhas em que há pelo menos uma chamada de variante com pelo menos um genótipo maior que 0.
  • agrupa as linhas de variantes pelo 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) e manter o contexto de execução da consulta no nível da linha. Isso ocorre porque você quer produzir um resultado por variante, em vez de um resultado por call ou por genotype.

A função UNNEST permite que você consulte uma coluna ARRAY como se ela fosse uma tabela. A função retorna uma linha para cada elemento de ARRAY. Ela também não altera o contexto da consulta. Portanto, é possível começar usando 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. Lembre-se de que 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 que você classifique a saída em ordem numérica crescente de cromossomos (reference_name). Isso ocorre porque os valores em reference_name são tipos de string, e cada 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, ela altera as funções GROUP BY e ORDER BY para usar o alias computado chromosome. No entanto, 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

Nesse caso, a consulta retorna um erro porque nem todos os nomes de cromossomo, como "X", "Y" e "M", são numéricos. Em vez disso, use a função CASE para adicionar um "0" ao início dos 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
...

Observe o uso da 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 aceita funções definidas pelo usuário. Elas permitem que você crie 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 se torna excessivamente detalhada.

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, mas 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 também melhora quando a quantidade de dados processados ​​é reduzida. A IU do BigQuery fornece dados sobre o tempo decorrido desde o início de uma consulta até 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 diferentes consultas e veja quantos bytes de dados elas processam.

Limpar

Após concluir este tutorial, é possível limpar os recursos que você criou no Google Cloud para que não sejam faturados no futuro. Nas seções a seguir, você aprenderá a excluir e desativar esses recursos.

Excluir o projeto

A maneira mais fácil de evitar a cobrança é excluir o projeto usado no tutorial.

Para excluir o projeto, faça o seguinte:

  1. No Console do Cloud, acesse a página "Projetos".

    Acessar a página "Projetos"

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

A seguir