Questa pagina descrive 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 BigQuery.
Il nome della tabella è platinum_genomes_deepvariant_variants_20180823.
Se i dati sulle varianti si trovano in una tabella BigQuery che utilizza lo schema delle varianti BigQuery, è facile applicare le query in 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 mostra come:
- Visualizza una panoramica dei dati genomici.
- Scopri come vengono rappresentati i segmenti non variabili.
- Scopri come vengono rappresentate le chiamate alle varianti.
- Scopri come vengono rappresentati i filtri per la qualità delle chiamate delle varianti.
- Aggregare colonne gerarchiche.
- Condensa le query.
- Conta le 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 chiamate per ogni sample
- 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
La tabella platinum_genomes_deepvariant_variants_20180823
Illumina Platinum Genomes è disponibile pubblicamente.
Varianti e non varianti nella tabella
I dati dei genomi Illumina Platinum 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 delle varianti sono generalmente rappresentati nel seguente modo:
- 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 non delle varianti dipende in genere dall'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 tabelle seguenti mostrano alcune righe contenenti valori che rappresentano segmenti non variabili. I segmenti mostrano le seguenti informazioni:
- Un blocco di riferimento di
10basi sul cromosoma1 - Il blocco di riferimento inizia nella posizione
1000 - La base di riferimento nella posizione
1000è unA - Le basi di riferimento nelle altre posizioni del blocco non vengono 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 utilizzano le rappresentazioni nelle tabelle precedenti.
Per ulteriori informazioni sulla rappresentazione delle posizioni non varianti nel genoma, consulta la specifica VCF.
Visualizzazione dei dati della tabella
Per visualizzare i dati nella tabella platinum_genomes_deepvariant_variants_20180823:
compila 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 oltre 105.000.000 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 delle sue righe, inizia a eseguire query e analizzare i dati. Prima di continuare, assicurati di conoscere la sintassi delle query SQL standard utilizzata da BigQuery.
Contare il numero totale di righe 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 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
Contare le chiamate alle 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,
che è un ARRAY di chiamate di varianti. Ogni colonna call include il valore name e altri valori, come il genotipo, le colonne di qualità, la profondità di lettura e altri valori in genere presenti in un file VCF.
Per conteggiare il numero di chiamate alle varianti, esegui una 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 nel set di dati esistono in media 1,7 chiamate di varianti per riga.
Somma delle lunghezze degli array call
Conta il numero totale di chiamate alle varianti in tutti i sample 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 |
JOINing ogni riga
Conta il numero totale di chiamate alle varianti in tutti i sample utilizzando un JOIN su ogni riga con la colonna call. La query utilizza l'operatore virgola (,), che è una notazione abbreviata utilizzata per JOIN.
L'unione alla colonna call esegue un'operazione implicita di
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 | number_of_calls |
|---|---|
| 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 colonna call deve avere un singolo valore name, quindi 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 dei segmenti con e senza varianti
Per contare il numero di segmenti con 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 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 nella tabella, esistono almeno tre modi per classificare una riga di variante come 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, ovvero
non è un valore di 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 diverso da <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 |
L'aggiunta del numero di varianti reali (38.549.388) al numero di segmenti senza varianti (143.555.264) corrisponde al numero totale di chiamate alle varianti.
Conteggio delle varianti chiamate da ogni sample
Dopo aver esaminato le righe di primo livello della tabella, puoi iniziare a eseguire query sulle righe secondarie. Queste righe includono dati quali i singoli sample per i quali sono state effettuate chiamate alle varianti.
Ogni variante nella tabella ha zero o più valori per call.name. Un valore call.name specifico può comparire in più righe.
Per conteggiare il numero di righe in cui viene visualizzato ogni insieme di chiamate, esegui la 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 singolo individuo umano sottoposto a sequenziamento:
| 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. Filtra i segmenti senza variazioni
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 ai 6 milioni, un numero più tipico per un essere umano. Vai alla sezione successiva per filtrare le varianti vere per 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 i singoli, pertanto devi escluderle. Le varianti vere possono includere solo chiamate con genotipi maggiori di zero. Se una chiamata include solo genotipi che sono chiamate senza dati (-1) o di riferimento (0), non si tratta di varianti vere.
Per filtrare le varianti in base al genotipo, esegui la 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
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 ottenere il valore del numero di righe, esegui la seguente 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 |
Contare le varianti per cromosoma
Per conteggiare il numero di varianti per cromosoma, esegui la seguente query. La query esegue le seguenti operazioni:
- 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 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 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 le 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à.
FILTERing for high quality variant calls
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. La query omette anche 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.
Contare 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 |
Contare tutte le chiamate di varianti vere di alta qualità per ogni campione
La seguente query mostra come conteggiare tutte le chiamate (con e senza varianti) per ogni sample. Omette qualsiasi chiamata con un filtro diverso da PASS e include solo le chiamate con almeno una variante vera, ovvero 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
Condensare le query
Man mano che le query diventano più complesse, è importante mantenerle concise per assicurarti che la loro logica sia corretta e facile da seguire.
Il seguente esempio mostra come partire da una query che conteggia il numero di varianti per cromosoma e, passo passo, la comprime 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. 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 fosse una tabella. La funzione restituisce una riga per ogni elemento di un
ARRAY. Inoltre, non modifica 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 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 l'output in ordine numerico, 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 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 funzione SAFE_CAST, che restituisce NULL per i cromosomi X, Y e M anziché un errore.
Come ultimo miglioramento dell'output, mostra di nuovo la colonna reference_name instead of impostandola 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 Contare le varianti per cromosoma.
Scrivere 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, ad esempio 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)
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 la quantità 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 varie 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 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:
- 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.
- Analizza le varianti in BigQuery utilizzando R, RMarkdown o JavaScript.