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 il parametro Schema delle varianti BigQuery, è facile applicare query 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 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 vengono utilizzati 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
- 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
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. Questi le varianti 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 seguenti tabelle mostrano alcune righe contenenti valori che rappresentano segmenti senza 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 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 contiene
nessun valore, il che significa che si tratta di 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 alternate_bases REPEATED RECORD ha 1 lunghezza 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.
Esecuzione di query sulla tabella
Dopo aver visualizzato lo schema della tabella e alcune delle 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 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 con segmento senza variazioni.
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 contare il numero di chiamate di varianti, esegui una query sul numero di elementi all'interno di
ARRAY colonne. Esistono diversi modi per farlo, che sono mostrati di seguito.
Ogni query restituisce il valore 182.104.652, il che significa che è presente
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 delle varianti in tutti gli esempi
utilizzando un file 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 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 | 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
consiste nel contare 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 | number_of_calls |
|---|---|
| 1 | 182104652 |
Conteggio di segmenti di varianti e non varianti
Per conteggiare il numero di segmenti di varianti e non di 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 Conteggio delle chiamate delle varianti, il numero totale di chiamate di varianti nella tabella è 182.104.652, quindi il risultato indica che la maggior parte delle righe della 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 righe
dove la colonna alternate_bases ha un valore che è una variante vera, 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 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
restituito 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 |
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.
Conta le varianti richiamate da ciascun campione
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, gli esseri umani non hanno i 30 milioni di varianti mostrate nell'
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 riportate nella tabella includono quelle che non includono chiamate, 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 corso...
In Conteggio delle varianti richiamate da ogni campione,
ogni query ha restituito sei righe con 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 |
Conteggiare le 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)
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
non contiene 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 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 insieme 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
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 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, passo dopo passo, la condensa utilizzando la sintassi SQL e le funzioni definite dall'utente.
Come spiegato nella sezione sul conteggio delle varianti per cromosomico, la query presenta quanto segue requisiti:
- 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.
Scrivere questa query può essere complicato perché, per completare la prima attività, devi
devi esaminare un ARRAY (genotype) all'interno di un ARRAY (call) mentre
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 consente di eseguire una query su una colonna ARRAY come se
era 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 nel conteggio di varianti per cromosomico:
| 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 stringhe e ogni valore
contiene il prefisso "chr."
Per ordinare numericamente l'output, rimuovi prima "chr" dal prefisso
reference_name e assegna 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 classe REGEXP_REPLACE
funzione per sostituire "chr" con una stringa vuota. La query
cambia le funzioni GROUP BY e ORDER BY per utilizzare il valore chromosome calcolato
alias. 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
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
come "X", "Y" e "M" sono numerici. Utilizza la
CASE
funzione per anteporre lo "0" ai cromosomi da 1 a 9 e rimuovere il "chr"
prefisso:
#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 CASE
alla 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 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)
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 quanti byte l'elaborazione della query. Consulta la spiegazione del piano di query BigQuery per informazioni sull'ottimizzazione delle query.
Alcuni esempi in questa pagina, come Conteggio delle chiamate delle varianti in una tabella, dimostrare vari modi per scrivere una query. Per determinare quale metodo l'esecuzione di query più adatta a te, esamina la durata di diverse query e verifica la quantità di byte di dati che elaborano.
Esegui la pulizia
Al termine del tutorial, puoi eseguire la pulizia delle risorse che hai creato in modo che smettono di usare la quota e comportano 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:
- 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.
- Analizzare le varianti in BigQuery utilizzando R, RMarkdown o JavaScript.