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 tue 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 spiega come effettuare le seguenti operazioni:
- Ottieni una panoramica dei dati genomici.
- Scopri come sono rappresentati i segmenti senza varianti.
- Scopri come vengono rappresentate le chiamate delle varianti.
- Scopri come vengono rappresentati i filtri per la qualità delle chiamate delle varianti.
- Colonne gerarchiche aggregate.
- Condensa le query.
- Conta righe distinte.
- Raggruppa righe.
- Scrivere funzioni definite dall'utente.
Questo tutorial mostra anche come trovare le seguenti informazioni:
- Numero di righe nella tabella
- Numero di chiamate delle varianti
- Varianti chiamate per ogni campione
- Numero di campioni
- Varianti per cromosoma
- Varianti di alta qualità per campione
Costi
In questo documento vengono utilizzati 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 già conoscere lo schema delle varianti di BigQuery.
Visualizzazione dello schema e dei dati della tabella
Accedi alla tabella e visualizza lo schema
La tabella platinum_genomes_deepvariant_variants_20180823 di Illumina Platinum Genomes è 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 varianti non varianti sono note anche come "chiamate di riferimento".
Nella tabella, i segmenti senza varianti sono generalmente rappresentati nei seguenti modi:
- Con un valore
alternate_basesdi lunghezza 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 dal chiamante della variante che ha generato i dati di origine. Le varianti nella tabella platinum_genomes_deepvariant_variants_20180823 sono state chiamate utilizzando DeepVariant, che utilizza la notazione <*>.
Le seguenti tabelle mostrano alcune righe contenenti valori che rappresentano segmenti non varianti. 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 alla posizione
1000è unA - Le basi di riferimento nelle altre posizioni del blocco non vengono mostrate
Nella tabella seguente, la colonna REPEATED RECORD alternate_bases non contiene valori, il che significa che è un ARRAY di lunghezza pari a 0.
| reference_name | start_position | end_position | reference_bases | alternate_bases.alt |
|---|---|---|---|---|
| 1 | 1000 | 1010 | A |
Nella tabella seguente, la colonna REPEATED RECORD alternate_bases 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 utilizzano 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 nella console Google Cloud.
Vengono visualizzate informazioni sulla tabella. La tabella contiene 19,6 GB di dati e ha oltre 105.000.000 righe.
Fai clic su Anteprima per visualizzare alcune righe della tabella.
Esecuzione di query sulla tabella
Dopo aver visualizzato lo schema della tabella e alcune righe, inizia a inviare query e ad analizzare i dati. Prima di continuare, assicurati di acquisire familiarità con la sintassi standard delle query SQL 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 la seguente 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 di varianti nella tabella
Ogni riga della tabella ha una posizione genomica, che è una variante o unsegmento senza variazionie.
Ogni riga contiene anche una colonna call,
che corrisponde a un ARRAY di 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 conteggiare il numero di chiamate delle varianti, esegui la query sul numero di elementi all'interno delle
colonne ARRAY. Puoi farlo in diversi modi, come illustrato di seguito.
Ogni query restituisce il valore 182,104,652, il che significa che esiste 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 di 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 in ogni riga
Conta il numero totale di chiamate delle varianti in tutti i campioni utilizzando un elemento JOIN in ogni riga con la colonna call. La query utilizza la virgola (,), che è una notazione abbreviata utilizzata per JOIN.
Il join alla colonna call effettua un'operazione
UNNEST
implicita 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 | number_of_calls |
|---|---|
| 1 | 182104652 |
Conteggio di name in una colonna call
Un terzo modo per conteggiare il numero totale di chiamate di varianti in tutti i campioni
consiste nel conteggiare i valori name nella colonna call. Ogni colonna call deve avere un singolo valore name per poter 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 dei segmenti di varianti e non varianti
Per conteggiare il numero di segmenti di varianti e non varianti nella tabella, esegui prima una query per filtrare i segmenti non 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 e il risultato mostra che la maggior parte delle righe della tabella sono segmenti non basati su varianti.
Come mostrato nella sezione Varianti e varianti senza varianti nella
tabella,
esistono almeno tre modi per classificare una riga di variante come
segmento senza variazioni. Nella query precedente, la clausola WHERE include righe
in cui la colonna alternate_bases contiene un valore che rappresenta una variante vera, ovvero
che non è un valore indicatore speciale come <*> o <NON_REF>.
Per ogni riga della tabella, viene eseguita una sottoquery sulla colonna alternate_bases della riga, che restituisce il valore 1 per ogni valore di alternate_bases che non sia <NON_REF> o <*>. Il numero di righe restituite dalla sottoquery corrisponde al 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 di varianti.
Contare le varianti richiamate da ciascun campione
Dopo aver esaminato le righe di primo livello nella tabella, puoi iniziare a eseguire query sulle righe figlio. Queste righe includono dati come i singoli campioni che hanno ricevuto chiamate effettuate alle varianti.
Ogni variante nella tabella ha zero o più valori per call.name. Un
determinato valore call.name può apparire in più righe.
Per conteggiare il numero di righe in cui viene visualizzato ogni set di chiamate, esegui la query seguente:
#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 |
Gli esseri umani in genere non hanno le 30 milioni di varianti mostrate 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 ora è più vicino a 6 milioni, un numero più tipico per un essere umano. Continua alla sezione successiva per filtrare le varianti reali per genotipo.
Filtrare le varianti vere per genotipo
Le varianti nella tabella includono le chiamate senza chiamata, rappresentate da un valore genotype pari a -1. Queste varianti non sono considerate varianti vere
per i privati, pertanto devi escluderle. Le varianti reali possono includere solo chiamate
con genotipi maggiori di zero. Se una chiamata include solo genotipi che sono
no-call (-1) o riferimento (0), non sono varianti vere.
Per filtrare le varianti per genotipo, esegui la query seguente:
#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 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 relativo al 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 |
Conteggio delle varianti per cromosoma
Per conteggiare il numero di varianti per cromosoma, esegui la seguente query. La query svolge 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 in base al cromosoma e conta ciascun 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 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
Esecuzione di query su chiamate con più valori FILTER
La specifica VCF descrive la colonna FILTER che puoi utilizzare per etichettare le chiamate delle varianti di qualità diverse.
La seguente query mostra come visualizzare i valori FILTER per chiamata 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 della variante è di alta qualità.
FILTERRicerca di chiamate di varianti di alta qualità
Quando analizzi le varianti, potresti voler escludere le varianti di qualità inferiore.
Se la colonna FILTER contiene il valore PASS, è probabile che la colonna non contenga altri valori. Puoi verificarlo eseguendo la query seguente. 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.
Conteggiamento di 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 |
Conteggiamento di tutte le chiamate di varianti reali di alta qualità per ogni campione
La seguente query mostra come conteggiare tutte le chiamate (varianti e non varianti)
per ogni campione. Omette tutte le chiamate con un filtro non PASS e include solo
le chiamate con almeno una variante vera, 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
Condensazione delle query
Man mano che le query diventano più complesse, è importante mantenerle concise per assicurarsi 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, nel dettaglio, lo condensa utilizzando la sintassi SQL e le funzioni definite dall'utente.
Come spiegato nella sezione relativa al conteggio delle varianti per cromosoma, la query presenta 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 in base al cromosoma e conta ciascun gruppo.
Scrivere questa query può essere complicato perché, per completare la prima attività, devi
esaminare un ARRAY (genotype) all'interno di un ARRAY (call),
mantenendo al contempo 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 query su una colonna ARRAY come se la colonna fosse una tabella. La funzione restituisce una riga per ogni elemento di ARRAY. Inoltre, il contesto della query non viene modificato. 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
JOIN della colonna call con la colonna call.genotype. L'operatore 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 con prefisso "chr" con una stringa vuota. La query modifica poi le funzioni GROUP BY e ORDER BY per 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 invece 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 "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 dell'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 è uguale a quella mostrata in Conteggio delle varianti per cromosoma.
Scrittura di funzioni definite dall'utente
BigQuery supporta le funzioni definite dall'utente. Puoi utilizzare le 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 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)
La seguente query mostra anche come renderla 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 si riduce la quantità di dati elaborati. BigQuery fornisce dati su quanti secondi sono trascorsi dall'avvio di una query e su quanti byte è stata elaborata. Per informazioni sull'ottimizzazione delle query, consulta la spiegazione del piano di query BigQuery.
Alcuni degli esempi in questa pagina, come il conteggio delle chiamate delle varianti in una tabella, mostrano diversi modi per scrivere una query. Per determinare il metodo di query più adatto alle tue esigenze, esamina la durata delle diverse query e scopri quanti byte di dati vengono elaborati.
Esegui la pulizia
Al termine del tutorial, puoi eseguire la pulizia delle risorse che hai creato in modo che smettano di utilizzare la quota e smettano di essere addebitati. Le sezioni seguenti descrivono come eliminare o disattivare queste risorse.
Il modo più semplice per eliminare la fatturazione è 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
- Esamina gli altri tutorial di Cloud Life Sciences.
- Analizza le varianti in BigQuery utilizzando R, RMarkdown o JavaScript.