Questa pagina descrive i metodi avanzati per utilizzare 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 lo 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 lo schema delle varianti di BigQuery, è semplice applicare le query di questo tutorial ai tuoi dati. Per informazioni su come caricare i dati delle varianti in BigQuery, consulta la documentazione sull'utilizzo della pipeline di trasformazione.
Obiettivi
Questo tutorial illustra come svolgere le seguenti operazioni:
- 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 della qualità della chiamata delle varianti.
- Aggrega le colonne gerarchiche.
- Comprimi le query.
- Conta righe distinte.
- Raggruppa le righe.
- Scrivere funzioni definite dall'utente.
Questo tutorial mostra anche come trovare le seguenti informazioni:
- Numero di righe nella tabella
- Numero di chiamate di 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 in base all'utilizzo previsto, utilizza il Calcolatore prezzi.
Prima di iniziare
- Accedi al tuo account Google Cloud. Se non conosci Google Cloud, crea un account per valutare le prestazioni dei nostri prodotti in scenari reali. I nuovi clienti ricevono anche 300 $di crediti gratuiti per l'esecuzione, il test e il deployment dei carichi di lavoro.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.
- Dovresti conoscere lo schema delle varianti di BigQuery.
Visualizzazione dello schema e dei dati della tabella
Accedi alla tabella e visualizza lo schema
La tabella di Illumina Platinum Genomes platinum_genomes_deepvariant_variants_20180823
è disponibile pubblicamente.
Varianti e non varianti nella tabella
I dati di Illumina Platinum Genomes utilizzano il formato gVCF, il che significa che nella tabella sono presenti righe che includono non varianti. Queste non varianti sono note anche come "chiamate di riferimento".
Nella tabella, i segmenti non varianti sono generalmente rappresentati come segue:
- Con un valore
alternate_bases
di 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 non varianti dipende in genere dal chiamante variante che ha generato i dati di origine. Le varianti nella tabella platinum_genomes_deepvariant_variants_20180823
sono state chiamate utilizzando
DeepVariant, che usa la notazione <*>
.
Le seguenti tabelle mostrano alcune righe contenenti valori che rappresentano segmenti senza varianti. I segmenti mostrano le seguenti informazioni:
- Un blocco di riferimento di
10
si 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 è una 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 di 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 posizioni non varianti nel genoma.
Visualizzazione dei dati della tabella
Per visualizzare i dati nella tabella platinum_genomes_deepvariant_variants_20180823
,
completa questi 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 oltre 105.000.000 di righe.
Fai clic su Anteprima per visualizzare alcune delle righe della tabella.
Esecuzione di query sulla tabella
Dopo aver visualizzato lo schema della tabella e alcune delle sue righe, inizia a inviare query e analizzare i dati. Prima di continuare, assicurati di acquisire familiarità con la sintassi delle query SQL standard utilizzata 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 Crea 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
Conteggio delle chiamate delle varianti nella tabella
Ogni riga della tabella ha una posizione genomica che è una variante o un segmento senza variazioni.
Ogni riga contiene anche una colonna call
, che corrisponde a un ARRAY
delle chiamate di varianti. Ogni colonna call
include name
e altri valori, come genotipo, colonne di qualità, profondità di lettura e altri valori generalmente presenti in un file VCF.
Per contare il numero di chiamate di 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 c'è una media di 1,7 chiamate di varianti per riga nel set di dati.
Somma delle lunghezze di call
array
Conta il numero totale di chiamate delle varianti in tutti i campioni 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 | number_of_calls |
---|---|
1 | 182104652 |
JOIN
su ogni riga
Conta il numero totale di chiamate di varianti in tutti gli esempi utilizzando JOIN
su ogni riga con la colonna call
. La query utilizza l'operatore della virgola (,
), che è una notazione abbreviata utilizzata per JOIN
.
Il join alla colonna call
rende un'operazione
UNNEST
implicita nella 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 | number_of_calls |
---|---|
1 | 182104652 |
Conteggio di name
in una colonna call
Un terzo modo per conteggiare il numero totale di chiamate delle varianti in tutti gli esempi è conteggiare i valori name
nella colonna call
. Ogni colonna call
deve avere un singolo valore name
, per cui puoi eseguire la seguente 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 | number_of_calls |
---|---|
1 | 182104652 |
Conteggio di segmenti di varianti e non varianti
Per conteggiare il numero di segmenti di varianti e senza varianti nella tabella, esegui prima 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 Conteggio delle chiamate di varianti, il numero totale di chiamate di varianti nella tabella è 182.104.652, quindi il risultato mostra che la maggior parte delle righe della tabella sono segmenti non di varianti.
Come mostrato nella sezione Varianti e non varianti nella tabella,
esistono almeno tre modi per classificare una riga di varianti comesegmento senza variazioniti. Nella query precedente, la clausola WHERE
include le righe in cui la colonna alternate_bases
ha un valore che è una variante true, il che significa che non si tratta di un valore indicatore speciale come <*>
o <NON_REF>
.
Per ogni riga della tabella, viene eseguita una sottoquery sulla colonna alternate_bases
di quella riga, che restituisce il valore 1
per ogni valore di
alternate_bases
che non è <NON_REF
> o <*>
. Il numero di righe restituite dalla sottoquery è il numero di segmenti di varianti.
La seguente query mostra come ottenere il conteggio dei segmenti senza 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 |
L'aggiunta del numero di varianti reali (38.549.388) al numero di segmenti non varianti (143.555.264) equivale al numero totale di chiamate delle varianti.
Conta le varianti richiamate da ciascun campione
Dopo aver esaminato le righe di primo livello della tabella, puoi iniziare a eseguire query per le righe figlio. 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
. Un
determinato valore di call.name
può essere presente in più righe.
Per contare il numero di righe in cui compare ogni set di chiamate, 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
GROUP BY
call_name
ORDER BY
call_name
L'esecuzione della query restituisce sei righe. Ogni call_name
corrisponde a un
singolo essere umano in sequenza:
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 |
Per gli esseri umani in genere non sono presenti i 30 milioni di varianti indicate nei valori di call_count_for_call_set
. Filtra i segmenti non varianti per
conteggiare 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 si avvicina ai 6 milioni, un valore più tipico per gli esseri umani. Vai alla sezione successiva per filtrare le varianti vere per genotipo.
Filtrare le varianti vere per genotipo
Le varianti nella tabella includono nessuna chiamata, rappresentate da un
valore genotype
pari a -1. Queste non sono considerate varianti vere e proprie
per gli utenti, quindi devi escluderle. Le varianti reali possono includere solo
chiamate con genotipi maggiori di zero. Se una chiamata include solo genotipi che sono
senza chiamate (-1) o di riferimento (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 corso...
In Conteggio delle varianti richiamate da ogni campione,
ogni query ha restituito sei righe con valori per call_name
. Per eseguire una query e ottenere il valore del numero di righe, esegui questa 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 |
Conteggiare le varianti per cromosoma
Per contare il numero di varianti per cromosoma, esegui questa query. La query effettua le seguenti operazioni:
- Conta tutte le righe in cui è presente almeno una chiamata di variante con almeno 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 varianti per ciascun cromosoma:
Riga | reference_name | number_of_variant_rows |
---|---|---|
1 | chr1 | 615000 |
2 | chr2 | 646401 |
3 | chr3 | 542315 |
4 | chr4 | 578600 |
5 | chr5 | 496202 |
... | ... | ... |
Contare le varianti di alta qualità per campione
Esecuzione di 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 la chiamata di una 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 questa query. La query omette anche tutte le chiamate che non contengono un valore
PASS
in 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 alcun risultato.
Conteggiando tutte le chiamate di alta qualità per ogni campione.
La seguente query mostra come conteggiare tutte le chiamate (varianti e non varianti)
per ogni set di chiamate e omette qualsiasi chiamata con un filtro non 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
le chiamate 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 assicurare che la logica sia corretta e semplice 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 genotipo maggiore di 0.
- Raggruppa le righe delle varianti per cromosoma e conteggia ogni gruppo.
Scrivere questa query può essere complicato perché per completare la prima attività devi esaminare un elemento ARRAY
(genotype
) all'interno di una query ARRAY
(call
), mantenendo il contesto di esecuzione della query a livello di riga. Mantieni il
contesto di esecuzione della query a livello di riga
perché vuoi produrre un risultato per variante, anziché un risultato per call
o per genotype
.
La funzione UNNEST
consente di eseguire una query su una colonna ARRAY
come se la colonna fosse una tabella. La funzione restituisce una riga per ogni elemento di un
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 nel conteggio delle 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 modificando la clausola EXISTS
in una
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 tipi di stringa e ogni valore contiene il prefisso "chr".
Per ordinare numericamente l'output, rimuovi prima il prefisso "chr" dalla colonna reference_name
e assegnagli 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 modifica quindi le funzioni GROUP BY
e ORDER BY
per utilizzare l'alias chromosome
calcolato. L'output viene comunque ordinato per 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
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 di cromosomi, ad esempio "X", "Y" e "M", sono numerici. Utilizza la funzione CASE
per anteporre uno "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 funzione 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é impostarla 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 cromosoma.
Scrittura di funzioni definite dall'utente
BigQuery supporta le funzioni definite dall'utente. Puoi utilizzare funzioni definite dall'utente per creare una funzione utilizzando un'altra espressione SQL o un altro linguaggio di programmazione, come JavaScript.
L'esempio in Condensazione delle query mostra come creare una query complessa, che però è troppo complessa.
La seguente query mostra come rendere la query 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)
La seguente query mostra anche come rendere la query più concisa, ma 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 quando la quantità di dati elaborati si riduce. BigQuery fornisce i dati su quanti secondi sono trascorsi dall'avvio di una query e su quanti byte la query è stata elaborata. Consulta la spiegazione del piano di query BigQuery per informazioni sull'ottimizzazione delle query.
Alcuni esempi in questa pagina, come il conteggio delle chiamate di varianti in una tabella, mostrano diversi modi per scrivere una query. Per determinare qual è il metodo di query migliore per te, esamina la durata delle diverse query e scopri quanti byte di dati vengono elaborati.
Esegui la pulizia
Dopo aver completato il tutorial, puoi eseguire la pulizia delle risorse che hai creato in modo che smettano di utilizzare la quota e generino addebiti. Le sezioni seguenti descrivono come eliminare o disattivare queste risorse.
Il modo più semplice per eliminare la fatturazione è quello di eliminare il progetto che hai creato per il tutorial.
Per eliminare il progetto:
- Nella console Google Cloud, vai alla pagina Gestisci risorse.
- Nell'elenco dei progetti, seleziona il progetto che vuoi eliminare, quindi fai clic su Elimina.
- Nella finestra di dialogo, digita l'ID del progetto e fai clic su Chiudi per eliminare il progetto.
Passaggi successivi
- Consulta gli altri tutorial di Cloud Life Sciences.
- Analizza le varianti in BigQuery utilizzando R, RMarkdown o JavaScript.