Guia avançado para analisar variantes usando o BigQuery

Nesta página, descrevemos 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:

• Ter uma visão geral dos dados genômicos.
• 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.
• Agregar colunas hierárquicas.
• Condensar consultas.
• Contar linhas distintas.
• Agrupar linhas.
• Gravar 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 documento, você usará os seguintes componentes faturáveis do Google Cloud:

• BigQuery

Para gerar uma estimativa de custo baseada na projeção de uso deste tutorial, use a calculadora de preços. Novos usuários do Google Cloud podem estar qualificados para uma avaliação gratuita.

Antes de começar

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

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

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Verifique se a cobrança está ativada para o seu projeto do Google Cloud.

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

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Verifique se a cobrança está ativada para o seu projeto do Google Cloud.

6. 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 se baseia 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 no genoma.

Como visualizar os dados da tabela

Para visualizar os dados na tabela platinum_genomes_deepvariant_variants_20180823, conclua as seguintes etapas:

1. Confira a tabela na página do BigQuery no console do Google Cloud.

   Acessar 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.000.000 linhas.

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

Como consultar a tabela

Depois de ver o esquema da tabela e algumas das linhas, comece a emitir consultas e analisar dados. Antes de continuar, familiarize-se 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 página do BigQuery no console do Google Cloud.

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

Conte o número total de chamadas de variantes em todas as amostras adicionando 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 consulta retorna o seguinte resultado:

Row	number_of_calls
1	182104652

Como mesclar (JOIN) as linhas

Conte 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 vírgula (,), que é uma notação abreviada usada para JOIN. 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

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 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 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 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 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 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 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. Elas não são consideradas variantes verdadeiras para indivíduos, portanto, é 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, no mínimo, 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 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 ela 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 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, no mínimo, 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. 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 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. Use a função UNNEST em uma subconsulta EXISTS na 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 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, a consulta altera as funções GROUP BY e ORDER BY para usar o alias chromosome computado. A saída ainda classifica 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 adicionar um "0" ao início dos cromossomos de 1 a 9 e remova 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 é muito 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 a lógica delas é 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. O 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 consultas diferentes e veja quantos bytes de dados elas processam.

Limpar

Depois de concluir o tutorial, você pode limpar os recursos que criou para que eles parem de usar a cota e gerar 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:

Cuidado: excluir um projeto tem os seguintes efeitos:

• Tudo no projeto é excluído. Se você tiver usado um projeto existente para as tarefas neste documento, a exclusão dele incluirá a exclusão de quaisquer outros trabalhos feitos no projeto.
• Os IDs do projeto personalizados são perdidos. Ao criar o projeto, você pode ter criado um código do projeto personalizado para ser usado no futuro. Para preservar os URLs que usam o ID do projeto, como um URL appspot.com, exclua recursos específicos do projeto, em vez de excluir o projeto inteiro.

Se você planeja passar por várias arquiteturas, tutoriais ou guias de início rápido, a reutilização de projetos pode evitar que você exceda os limites da cota do projeto.

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

A seguir

• Veja outros tutoriais do Cloud Life Sciences.
• Analise as variantes no BigQuery usando R, RMarkdown ou JavaScript.