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.
Antes de começar
- 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.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
- 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_basesde comprimento zero - Com a string de texto
<NON_REF>como um valoralternate_bases.alt - Com a string de texto
<*>como um valoralternate_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
10se baseia no cromossomo1 - O bloco de referência começa na posição
1000 - A base de referência na posição
1000é umA - 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,
conclua as seguintes etapas:
Confira a tabela na página do BigQuery no console do Google Cloud.
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.
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:
Acesse a página do BigQuery no console do Google Cloud.
Clique em Compose query.
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`
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:
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- 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.