In questa pagina vengono descritti i metodi avanzati per l'utilizzo BigQuery per analizzare le varianti.
I dati di questo tutorial provengono dal progetto Illumina Platinum Genomes.
I dati sono stati caricati in una tabella BigQuery che utilizza
Schema delle varianti di BigQuery.
Il nome della tabella è platinum_genomes_deepvariant_variants_20180823.
Se i dati delle varianti si trovano in una tabella BigQuery che utilizza la proprietà Schema delle varianti BigQuery, è facile applicare di questo tutorial ai tuoi dati. Per informazioni su come caricare la variante in BigQuery, consulta la documentazione sull'utilizzo pipeline di trasformazione.
Obiettivi
Questo tutorial mostra come:
- Ottieni una panoramica dei dati genomici.
- Scopri come vengono rappresentati i segmenti non varianti.
- Scopri come vengono rappresentate le chiamate delle varianti.
- Scopri come vengono rappresentati i filtri per la qualità delle chiamate delle varianti.
- Aggregare colonne gerarchiche.
- Condensa le query.
- Conta righe distinte.
- Raggruppa le righe.
- Scrivi funzioni definite dall'utente.
Questo tutorial mostra anche come trovare le seguenti informazioni:
- Numero di righe nella tabella
- Numero di chiamate alle varianti
- Varianti richiamate per ogni campione
- Numero di campioni
- Varianti per cromosoma
- Varianti di alta qualità per campione
Costi
In questo documento utilizzi i seguenti componenti fatturabili di Google Cloud:
- BigQuery
Per generare una stima dei costi basata sull'utilizzo previsto,
utilizza il Calcolatore prezzi.
Prima di iniziare
- 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.
- Devi conoscere lo schema delle varianti BigQuery.
Visualizzazione dello schema e dei dati della tabella
Accedi alla tabella e visualizza lo schema
I genomi di Platino Illumina platinum_genomes_deepvariant_variants_20180823
sia disponibile pubblicamente.
Varianti e non varianti nella tabella
I dati di Illumina Platinum Genomes utilizzano il formato gVCF, il che significa che esistono righe nella tabella che includono non varianti. Queste non varianti sono note anche come "chiamate di riferimento".
Nella tabella, i segmenti non varianti sono generalmente rappresentati nel tag nei seguenti modi:
- Con un valore
alternate_basesdi lunghezza pari a zero - Con la stringa di testo
<NON_REF>come valorealternate_bases.alt - Con la stringa di testo
<*>come valorealternate_bases.alt
Il modo in cui vengono rappresentati i segmenti senza varianti dipende in genere
la variante del chiamante che ha generato i dati di origine. Le varianti in
platinum_genomes_deepvariant_variants_20180823 tabella è stata chiamata utilizzando
DeepVariant, che usa la notazione <*>.
Le tabelle seguenti mostrano alcune righe contenenti valori che rappresentano segmenti non variabili. I segmenti mostrano le seguenti informazioni:
- Un blocco di riferimento di
10si basa sul cromosoma1 - Il blocco di riferimento inizia dalla posizione
1000 - La base di riferimento nella posizione
1000è unaA - Le basi di riferimento nelle altre posizioni del blocco non sono mostrate
Nella tabella seguente, la colonna alternate_bases REPEATED RECORD non contiene valori, il che significa che si tratta di un ARRAY di lunghezza 0.
| reference_name | start_position | end_position | reference_bases | alternate_bases.alt |
|---|---|---|---|---|
| 1 | 1000 | 1010 | A |
Nella tabella seguente, la colonna alternate_bases REPEATED RECORD ha una lunghezza pari a 1 e contiene la stringa di testo letterale <*>.
| reference_name | start_position | end_position | reference_bases | alternate_bases.alt |
|---|---|---|---|---|
| 1 | 1000 | 1010 | A | <*> |
Le query utilizzate in questa guida usano le rappresentazioni nelle tabelle precedenti.
Consulta la specifica VCF per ulteriori informazioni sulla rappresentazione delle variabili diverse posizioni nel genoma.
Visualizzazione dei dati della tabella
Per visualizzare i dati nella tabella platinum_genomes_deepvariant_variants_20180823:
completa i seguenti passaggi:
Visualizza la tabella nella pagina BigQuery della console Google Cloud.
Vengono visualizzate le informazioni sulla tabella. La tabella contiene 19,6 GB di dati e ha più di 105.000.000 di righe.
Fai clic su Anteprima per visualizzare alcune delle righe della tabella.
Eseguire query sulla tabella
Dopo aver visualizzato lo schema della tabella e alcune sue righe, inizia l'invio di query e l'analisi dei dati. Prima di continuare, assicurati di avere familiarità con Sintassi standard delle query SQL usati da BigQuery.
Conteggio delle righe totali nella tabella
Per visualizzare il numero di righe nella tabella:
Vai alla pagina BigQuery nella console Google Cloud.
Fai clic su Componi query.
Copia e incolla questa query nell'area di testo Nuova query:
#standardSQL SELECT COUNT(1) AS number_of_rows FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`
Fai clic su Esegui query. La query restituisce il seguente risultato:
Riga number_of_rows 1 105923159
Contare le chiamate delle varianti nella tabella
Ogni riga della tabella ha una posizione genomica che è un segmento di variante o non variante.
Ogni riga contiene anche una colonna call,
ovvero un ARRAY delle chiamate di varianti. Ogni colonna call include
name e altri valori, come genotipo, colonne di qualità, lettura
e altre si trovano solitamente in un file VCF.
Per conteggiare il numero di chiamate alle varianti, esegui una query sul numero di elementi all'interno delle colonne ARRAY. Esistono diversi modi per farlo, che sono mostrati di seguito.
Ogni query restituisce il valore 182.104.652, il che significa che nel set di dati esistono in media 1,7 chiamate di varianti per riga.
Somma delle lunghezze di call array
Conta il numero totale di chiamate delle varianti in tutti gli esempi aggiungendo
la lunghezza di ogni array call:
#standardSQL
SELECT
SUM(ARRAY_LENGTH(call)) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`
La query restituisce il seguente risultato:
| Riga | numero_di_chiamate |
|---|---|
| 1 | 182104652 |
JOINing ogni riga
Conta il numero totale di chiamate delle varianti in tutti gli esempi
utilizzando un file JOIN
su ogni riga con la colonna call. La query utilizza l'operatore virgola (,), che è una notazione abbreviata utilizzata per JOIN.
Il join alla colonna call rende implicito
UNNEST
sulla colonna call.
#standardSQL
SELECT
COUNT(call) AS number_of_calls
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
La query restituisce il seguente risultato:
| Riga | numero_di_chiamate |
|---|---|
| 1 | 182104652 |
Conteggio di name in una colonna call
Un terzo modo per conteggiare il numero totale di chiamate alle varianti in tutti i campioni è conteggiare i valori name nella colonna call. Ogni call
deve avere un singolo valore name, quindi puoi eseguire quanto segue:
query:
#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
La query restituisce il seguente risultato:
| Riga | numero_di_chiamate |
|---|---|
| 1 | 182104652 |
Conteggio di segmenti di varianti e non varianti
Per conteggiare il numero di segmenti di varianti e non varianti nella tabella, prima esecuzione una query per filtrare i segmenti senza varianti:
#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>", "<*>"))
La query restituisce il seguente risultato:
| Riga | number_of_real_variants |
|---|---|
| 1 | 38549388 |
Come mostrato in Contare le chiamate alle varianti, il numero totale di chiamate alle varianti nella tabella è 182.104.652, pertanto il risultato mostra che la maggior parte delle righe nella tabella sono segmenti non varianti.
Come mostrato nella sezione Varianti e non varianti nei
tabella,
esistono almeno tre modi per classificare una riga di varianti come
con segmento senza variazioni. Nella query precedente, la clausola WHERE include le righe
in cui la colonna alternate_bases ha un valore che è una variante vera, il che significa
che non è un valore indicatore speciale come <*> o <NON_REF>.
Per ogni riga della tabella, viene eseguita una sottoquery per alternate_bases
colonna di quella riga, che restituisce il valore 1 per ogni valore di
alternate_bases che non è <NON_REF> o <*>. Il numero di righe
che la sottoquery restituisce è il numero di segmenti di variante.
La seguente query mostra come ottenere il conteggio dei segmenti non varianti:
#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>", "<*>"))
La query restituisce il seguente risultato:
| Riga | number_of_non_variants |
|---|---|
| 1 | 143555264 |
Sommando il numero di varianti reali (38.549.388) al numero di varianti non varianti (143.555.264) equivale al numero totale di chiamate delle varianti.
Conteggio delle varianti chiamate da ogni sample
Dopo aver esaminato le righe di primo livello nella tabella, puoi iniziare a eseguire query per righe secondarie. Queste righe includono dati come i singoli campioni che hanno ricevuto chiamate rispetto alle varianti.
Ogni variante nella tabella ha zero o più valori per call.name. R
un determinato valore di call.name può essere visualizzato in più righe.
Per contare il numero di righe in cui compare ogni serie di chiamate, esegui la funzione seguente query:
#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
L'esecuzione della query restituisce sei righe. Ogni call_name corrisponde a un
umano individuale sequenziato:
| Riga | 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 |
In genere, le persone non hanno le 30 milioni di varianti mostrate nei valori per call_count_for_call_set. Escludi i segmenti senza varianti
per contare solo le righe delle varianti:
#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
La query restituisce il seguente risultato:
| Riga | 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 |
Il numero di varianti ora si avvicina ai 6 milioni, un valore più tipico per un essere umano. Vai alla sezione successiva per filtrare le varianti vere in base al genotipo.
Filtrare le varianti vere per genotipo
Le varianti nella tabella includono le chiamate senza risposta, che sono rappresentate da un valore genotype pari a -1. Queste varianti non sono considerate vere varianti per
singoli utenti, quindi devi escluderli. Le varianti reali possono includere solo chiamate con
genotipi maggiori di zero. Se una chiamata include solo genotipi
senza chiamate (-1) o riferimenti (0), non sono varianti vere.
Per filtrare le varianti in base al genotipo, esegui questa query:
#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
La query restituisce il seguente risultato:
| Riga | 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 |
Conteggio dei campioni nella tabella
In Contare le varianti chiamate da ogni sample,
ogni query ha restituito sei righe con i valori per call_name. Per eseguire una query
per e ottenere il valore per il numero di righe, esegui questo comando
query:
#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
La query restituisce il seguente risultato:
| Riga | number_of_callsets |
|---|---|
| 1 | 6 |
Conteggio delle varianti per cromosoma
Per contare il numero di varianti per cromosoma, esegui questo comando: query. La query effettua le seguenti operazioni:
- Conta tutte le righe in cui è presente almeno una chiamata di variante con almeno di un genotipo maggiore di 0.
- Raggruppa le righe delle varianti per cromosoma e conteggia ogni gruppo.
#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
L'esecuzione della query restituisce il nome del cromosoma (reference_name) e il numero di righe di variante per ogni cromosoma:
| Riga | reference_name | number_of_variant_rows |
|---|---|---|
| 1 | chr1 | 615000 |
| 2 | chr2 | 646401 |
| 3 | chr3 | 542315 |
| 4 | chr4 | 578600 |
| 5 | chr5 | 496202 |
| … | … | … |
Conteggio delle varianti di alta qualità per campione
Eseguire query sulle chiamate con più valori FILTER
La specifica VCF descrive la colonna FILTER
che puoi utilizzare per etichettare chiamate di varianti di qualità diverse.
La seguente query mostra come visualizzare i valori FILTER per chiamata di variante
per il set di dati:
#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
La query restituisce il seguente risultato:
| Riga | call_filter | number_of_calls |
|---|---|---|
| 1 | RefCall | 11681534 |
| 2 | PASS | 26867854 |
Il valore PASS indica che una chiamata di variante è di alta qualità.
FILTER per le chiamate delle varianti di alta qualità
Quando analizzi le varianti, ti consigliamo di escludere quelle di qualità inferiore.
Se la colonna FILTER contiene il valore PASS, è probabile che la colonna non contenga altri valori. Puoi verificarlo eseguendo la seguente query. Nella query vengono omesse anche tutte le chiamate che non contengono un valore PASS.
inferiore a 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
Come previsto, l'esecuzione della query non restituisce risultati.
Conteggiando tutte le chiamate di alta qualità per ogni campione.
La seguente query mostra come conteggiare tutte le chiamate (con e senza varianti) per ogni insieme di chiamate e omette qualsiasi chiamata con un filtro diverso da 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
La query restituisce il seguente risultato:
| Riga | call_name | number_of_calls |
|---|---|---|
| 1 | NA12877 | 29795946 |
| 2 | NA12878 | 26118774 |
| 3 | NA12889 | 29044992 |
| 4 | NA12890 | 28717437 |
| 5 | NA12891 | 31395995 |
| 6 | NA12892 | 25349974 |
Conteggiando tutte le chiamate di varianti true di alta qualità per ogni campione
La seguente query mostra come conteggiare tutte le chiamate (varianti e non varianti)
per ogni campione. Omette qualsiasi chiamata con un filtro non PASS e include solo
con almeno una variante true, il che significa che 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
La query restituisce il seguente risultato:
| Riga | call_name | number_of_calls |
|---|---|---|
| 1 | NA12877 | 4486610 |
| 2 | NA12878 | 4502017 |
| 3 | NA12889 | 4422706 |
| 4 | NA12890 | 4528725 |
| 5 | NA12891 | 4424094 |
| 6 | NA12892 | 4495753 |
Best practice
Query a condensazione
Man mano che le query diventano più complesse, è importante mantenerle concise per assicurarti che la loro logica sia corretta e facile da seguire.
L'esempio seguente mostra come iniziare da una query che conteggia il numero di varianti per cromosoma e, passo dopo passo, la condensa utilizzando la sintassi SQL e le funzioni definite dall'utente.
Come spiegato nella sezione sul conteggio delle varianti per cromosoma, la query ha i seguenti requisiti:
- Conta tutte le righe in cui è presente almeno una chiamata di variante con almeno un genotype maggiore di 0.
- Raggruppa le righe delle varianti per cromosoma e conteggia ogni gruppo.
La scrittura di questa query può essere complicata perché, per completare la prima attività, devi esaminare un ARRAY (genotype) all'interno di un ARRAY (call) mantenendo il contesto di esecuzione della query a livello di riga. Puoi mantenere
contesto di esecuzione della query a livello di riga
perché vuoi produrre un risultato per variante, anziché un risultato per call
o per genotype risultato.
La funzione UNNEST ti consente di eseguire query su una colonna ARRAY come se fosse una tabella. La funzione restituisce una riga per ogni elemento di una
ARRAY. Inoltre, non cambia il contesto della query. Utilizza una funzione UNNEST in una sottoquery EXISTS in una clausola WHERE:
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call
WHERE EXISTS (SELECT 1
FROM UNNEST(call.genotype) AS gt
WHERE gt > 0))
GROUP BY
reference_name
ORDER BY
reference_name
La query restituisce gli stessi risultati dell'esempio in Contare le varianti per cromosoma:
| Riga | reference_name | number_of_variant_rows |
|---|---|---|
| 1 | chr1 | 615000 |
| 2 | chr10 | 396773 |
| 3 | chr11 | 391260 |
| 4 | chr12 | 382841 |
| 5 | chr13 | 298044 |
| … | … | … |
Puoi rendere la query più concisa cambiando la clausola EXISTS in un
JOIN della colonna call con la colonna call.genotype. L'operatore della virgola
è una notazione abbreviata utilizzata per JOIN.
#standardSQL
SELECT
reference_name,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
reference_name
ORDER BY
reference_name
La query funziona ed è concisa, ma non consente di ordinare l'output in ordine numerico crescente dei cromosomi (reference_name) perché i valori in reference_name sono di tipo stringa e ogni valore contiene il prefisso "chr."
Per ordinare l'output in ordine numerico, rimuovi prima il prefisso "chr" dalla colonna reference_name e assegnale l'alias chromosome:
#standardSQL
SELECT
REGEXP_REPLACE(reference_name, '^chr', '') AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
La query utilizza la funzione REGEXP_REPLACE per sostituire la stringa del prefisso "chr" con una stringa vuota. La query poi
modifica le funzioni GROUP BY e ORDER BY in modo da utilizzare l'alias chromosome calcolato. L'output viene comunque ordinato in base alla stringa:
| Riga | cromosoma | number_of_variant_rows |
|---|---|---|
| 1 | 1 | 615000 |
| 2 | 10 | 396773 |
| 3 | 11 | 391260 |
| 4 | 12 | 382841 |
| 5 | 13 | 298044 |
| … | … | … |
Per ordinare numericamente l'output, trasmetti la colonna chromosome da
da una stringa a un numero intero:
#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
La query restituisce un errore perché non tutti i nomi dei cromosomi, ad esempio "X", "Y" e "M", sono numerici. Utilizza la funzione
CASE
per anteporre un "0" ai cromosomi da 1 a 9 e rimuovere il prefisso "chr":
#standardSQL
SELECT
CASE
WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
ELSE REGEXP_REPLACE(reference_name, '^chr', '')
END AS chromosome,
COUNT(reference_name) AS number_of_variant_rows
FROM
`bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
EXISTS (SELECT 1
FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
WHERE gt > 0)
GROUP BY
chromosome
ORDER BY
chromosome
La query restituisce l'output corretto:
| Riga | cromosoma | number_of_variant_rows |
|---|---|---|
| 1 | 01 | 615000 |
| 2 | 02 | 646401 |
| 3 | 03 | 542315 |
| 4 | 04 | 578600 |
| 5 | 05 | 496202 |
| … | … | … |
La query utilizza la classe SAFE_CAST
, che restituisce NULL per i cromosomi X, Y e M anziché restituire
un errore.
Come ultimo miglioramento all'output, visualizza di nuovo la colonna reference_name
anziché impostarlo sull'alias chromosome. Per farlo, sposta la clausola CASE
nella funzione 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
Questa query finale è la stessa mostrata in Conteggio delle varianti per cromosomico.
Scrittura di funzioni definite dall'utente
BigQuery supporta le funzioni definite dall'utente. Puoi usare funzioni definite dall'utente per creare una funzione utilizzando un'altra espressione SQL o un'altra come JavaScript.
L'esempio in Condensing queries mostra come costruire una query complessa, ma la query è eccessivamente complessa.
La query seguente mostra come renderla più concisa spostando la logica CASE in una funzione:
#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)
Anche la seguente query mostra come rendere la query più concisa, utilizza una funzione definita in 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)
Entrambe le query restituiscono il risultato corretto e la loro logica è più concisa.
Migliorare le prestazioni delle query e ridurre i costi
I prezzi di BigQuery si basano sul numero di byte elaborati per una query. Le prestazioni delle query migliorano di dati elaborati viene ridotta. BigQuery fornisce dati su quanti secondi sono trascorsi dall'avvio di una query e su quanti byte la query ha elaborato. Per informazioni sull'ottimizzazione delle query, consulta la spiegazione del piano di query di BigQuery.
Alcuni degli esempi in questa pagina, come Contare le chiamate delle varianti in una tabella, mostrano diversi modi per scrivere una query. Per determinare quale metodo di query è più adatto a te, esamina la durata delle diverse query e controlla quanti byte di dati vengono elaborati.
Esegui la pulizia
Al termine del tutorial, puoi eliminare le risorse che hai creato in modo che smettano di utilizzare la quota e di generare addebiti. Le seguenti sezioni descrivono come eliminare o disattivare queste risorse.
Il modo più semplice per eliminare la fatturazione creato per il tutorial.
Per eliminare il progetto:
- 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.
Passaggi successivi
- Esegui gli altri tutorial di Cloud Life Sciences.
- Analizzare le varianti in BigQuery utilizzando R, RMarkdown o JavaScript.