Guida avanzata per analizzare le varianti con BigQuery

In questa pagina vengono descritti 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 di 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, applicare le query di questo tutorial ai tuoi dati è semplice. Per informazioni su come caricare i dati delle varianti in BigQuery, consulta la documentazione sull'utilizzo della trasformazione della pipeline.

Obiettivi

Questo tutorial illustra come:

• Visualizza 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.
• Aggregare colonne gerarchiche.
• Query compatte.
• Conteggia righe distinte.
• Raggruppa righe.
• Scrivi funzioni definite dall'utente.

Questo tutorial illustra anche come trovare le seguenti informazioni:

• Numero di righe nella tabella
• Numero di chiamate alle varianti
• Varianti chiamate per ogni campione
• Numero di esempi
• 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. I nuovi utenti di Google Cloud possono essere idonei a una prova senza costi aggiuntivi.

Prima di iniziare

1. 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.

2. Nella pagina del selettore di progetti della console Google Cloud, seleziona o crea un progetto Google Cloud.

   Nota: se non prevedi di conservare le risorse create in questa procedura, crea un progetto anziché selezionarne uno esistente. Dopo aver completato questi passaggi, puoi eliminare il progetto, rimuovendo tutte le risorse associate.

   Vai al selettore progetti

3. Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.

4. Nella pagina del selettore di progetti della console Google Cloud, seleziona o crea un progetto Google Cloud.

   Nota: se non prevedi di conservare le risorse create in questa procedura, crea un progetto anziché selezionarne uno esistente. Dopo aver completato questi passaggi, puoi eliminare il progetto, rimuovendo tutte le risorse associate.

   Vai al selettore progetti

5. Assicurati che la fatturazione sia attivata per il tuo progetto Google Cloud.

6. Dovresti conoscere lo schema di varianti di BigQuery.

Visualizzazione dello schema e dei dati della tabella

Accedere alla tabella e visualizzare lo schema

La tabella platinum_genomes_deepvariant_variants_20180823 Genome di platino Illumina è disponibile pubblicamente.

Varianti e non varianti nella tabella

I dati dei genomi del platino Illumina utilizzano il formato gVCF, il che significa che la tabella contiene righe che non includono varianti. Queste non varianti sono note anche come "chiamate di riferimento".

Nella tabella, i segmenti non varianti vengono generalmente rappresentati nei seguenti modi:

• Con un valore alternate_bases di lunghezza zero
• Con la stringa di testo <NON_REF> come valore alternate_bases.alt
• Con la stringa di testo <*> come valore alternate_bases.alt

Il modo in cui i segmenti senza varianti vengono rappresentati 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 10 basi sul cromosoma 1
• Il blocco del riferimento inizia dalla posizione 1000
• La base di riferimento nella posizione 1000 è una A
• Le basi di riferimento nelle altre posizioni del blocco non sono mostrate

Nella tabella seguente, la colonna REPEATED RECORD alternate_bases non contiene alcun valore, il che significa che è un ARRAY di lunghezza 0.

nome_riferimento	posizione_inizio	posizione_finale	basi_riferimento	alternativa_base.alt
1	1000	1010	A

Nella tabella seguente, la colonna REPEATED RECORD alternate_bases è di lunghezza 1 e contiene la stringa di testo letterale <*>.

nome_riferimento	posizione_inizio	posizione_finale	basi_riferimento	alternativa_base.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.

Visualizzare i dati della tabella

Per visualizzare i dati nella tabella platinum_genomes_deepvariant_variants_20180823, completa i seguenti passaggi:

1. Visualizza la tabella nella pagina BigQuery nella console Google Cloud.

   Vai alla pagina BigQuery

   Vengono visualizzate le informazioni sulla tabella. La tabella contiene 19,6 GB di dati e contiene oltre 105.000.000 di righe.

2. 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 conoscere la sintassi delle query SQL standard utilizzata da BigQuery.

Conteggio delle righe totali nella tabella

Per visualizzare il numero di righe nella tabella:

1. Vai alla pagina BigQuery nella console Google Cloud.

   Vai alla pagina BigQuery

2. Fai clic su Crea query.

3. Copia e incolla la query seguente nell'area di testo New Query:

    #standardSQL
    SELECT
      COUNT(1) AS number_of_rows
    FROM
      `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

4. Fai clic su Esegui query. La query restituisce il seguente risultato:

   Riga	numero_di_righe
   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 delle varianti. Ogni colonna call include name e altri valori, come il genotipo, le colonne relative alla qualità, la profondità di lettura e altre informazioni che in genere si trovano in un file VCF.

Per conteggiare il numero di chiamate delle varianti, esegui una query sul numero di elementi nelle colonne ARRAY. Puoi farlo in diversi modi, come mostrato di seguito. Ogni query restituisce il valore 182.104.652, il che significa che esiste una media di 1,7 chiamate delle varianti per riga nel set di dati.

Sommare le lunghezze degli array call

Conta il numero totale di chiamate delle varianti in tutti gli esempi aggiungendo la lunghezza di ogni array call:

#standardSQL
SELECT
  SUM(ARRAY_LENGTH(call)) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

La query restituisce il seguente risultato:

Riga	numero_di_chiamate
1	182104652

JOIN ogni riga

Conta il numero totale di chiamate delle varianti in tutti gli esempi utilizzando un elemento JOIN in ogni riga con la colonna call. La query utilizza l'operatore virgola (,), che è una notazione abbreviata utilizzata per JOIN. L'unione alla colonna call effettua un'operazione implicita UNNEST 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	numero_di_chiamate
1	182104652

Conteggio del numero 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, 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	numero_di_chiamate
1	182104652

Conteggio dei segmenti con varianti e senza varianti

Per conteggiare il numero di segmenti con varianti e non varianti nella tabella, innanzitutto esegui 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	numero_di_varianti_reali
1	38549388

Come mostrato in Conteggio delle chiamate delle varianti, il numero totale di chiamate delle varianti nella tabella è 182.104.652, pertanto il risultato mostra che la maggior parte delle righe della tabella è un segmento non variante.

Come mostrato nella sezione Varianti e non varianti della tabella, esistono almeno tre modi per classificare una riga di varianti come segmento senza variazioni. Nella query precedente, la clausola WHERE include righe in cui la colonna alternate_bases ha un valore che è una variante effettiva, il che significa che non è un valore di 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 diverso da <NON_REF> o <*>. Il numero di righe restituito dalla sottoquery è il numero di segmenti delle 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	numero_di_varianti_non
1	143555264

Sommando il numero di varianti reali (38.549.388) al numero di segmenti senza varianti (143.555.264) equivale al numero totale di chiamate delle varianti.

Conteggio delle varianti richiamate da ogni campione

Dopo aver esaminato le righe di primo livello nella tabella, puoi iniziare a eseguire query per le righe secondarie. Queste righe includono dati come i singoli campioni in cui sono state effettuate chiamate rispetto alle varianti.

Ogni variante della tabella ha zero o più valori per call.name. Un valore call.name particolare può essere presente 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 individuo sequenziale:

Riga	nome_chiamata	numero_chiamate_per_impostazione_chiamata
1	N. 12877	31592135
2	N. 12878	28012646
3	N. 12889	31028550
4	N. 12890	30636087
5	N. 12891	33487348
6	N. 12892	27347886

In genere, gli esseri umani non hanno le 30 milioni di varianti mostrate nei valori per call_count_for_call_set. Filtra i segmenti senza 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	nome_chiamata	numero_chiamate_per_impostazione_chiamata
1	N. 12877	6284275
2	N. 12878	6397315
3	N. 12889	6407532
4	N. 12890	6448600
5	N. 12891	6516669
6	N. 12892	6494997

Il numero di varianti è ora più vicino a 6 milioni, il che è più tipico per un essere umano. Vai alla sezione successiva per filtrare le varianti true per genotype.

Filtrare le varianti vere in base al genotipo

Le varianti nella tabella includono le chiamate senza invito, che sono rappresentate da un valore genotype pari a -1. Queste varianti non sono considerate vere per gli individui, pertanto devi filtrarle. Le varianti True possono includere solo chiamate con genotipi maggiori di zero. Se una chiamata include solo genotipi che non sono chiamate (-1) o riferimento (0), non sono 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	nome_chiamata	numero_chiamate_per_impostazione_chiamata
1	N. 12877	4486610
2	N. 12878	4502017
3	N. 12889	4422706
4	N. 12890	4528725
5	N. 12891	4424094
6	N. 12892	4495753

Conteggio dei campioni nella tabella

In Conteggio delle varianti chiamate da ogni campione, ogni query ha restituito sei righe con valori per call_name. Per eseguire una query e ottenere il valore per il 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	numero_di_set_di_chiamate
1	6

Conteggio delle varianti per cromosoma

Per conteggiare il numero di varianti per cromosoma, esegui la query seguente. La query esegue le seguenti operazioni:

• Conta tutte le righe in cui è presente almeno una chiamata 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 delle varianti per ciascun cromosoma:

Riga	nome_riferimento	numero_di_righe_variante
1	cr 1	615000
2	CHR 2	646401
3	cr 3	542315
4	cr 4	578600
5	5 chr	496202
…	…	…

Conteggio delle 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 le chiamate delle varianti di qualità diversa.

La seguente query mostra come visualizzare i valori FILTER per variante di chiamata 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	filtro_chiamata	numero_di_chiamate
1	Chiamata di riferimento	11681534
2	PASS	26867854

Il valore PASS indica che la chiamata a una variante è di alta qualità.

FILTER per le chiamate alle varianti di alta qualità

Quando analizzi le varianti, ti consigliamo di filtrare 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 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 restituisce zero risultati.

Conteggio 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	nome_chiamata	numero_di_chiamate
1	N. 12877	29795946
2	N. 12878	26118774
3	N. 12889	29044992
4	N. 12890	28717437
5	N. 12891	31395995
6	N. 12892	25349974

Vengono conteggiate tutte le chiamate a varianti reali di alta qualità per ogni campione

La query seguente mostra come conteggiare tutte le chiamate (varianti e non) per ogni campione. Omette le chiamate con un filtro diverso da PASS e include solo le chiamate con almeno una variante vera e propria, pertanto 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	nome_chiamata	numero_di_chiamate
1	N. 12877	4486610
2	N. 12878	4502017
3	N. 12889	4422706
4	N. 12890	4528725
5	N. 12891	4424094
6	N. 12892	4495753

Best practice

Condensazione delle query

Man mano che le tue query diventano più complesse, è importante mantenerle concise per assicurarti che la loro 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, gradualmente, condensarla utilizzando la sintassi SQL e le funzioni definite dall'utente.

Come descritto nella sezione sul conteggio delle varianti per cromosoma, la query ha i seguenti requisiti:

• Conta tutte le righe in cui è presente almeno una chiamata 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 cercare un ARRAY (genotype) all'interno di un ARRAY (call) mantenendo il contesto di esecuzione della query a livello di riga. Manterrai 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, 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 riportato nella sezione sul conteggio delle varianti per i cromosomi:

Riga	nome_riferimento	numero_di_righe_variante
1	cr 1	615000
2	10 chr	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 di 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 ti 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 numericamente, 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 calcolato di chromosome. L'output continua a essere ordinato in base alla stringa:

Riga	cromosoma	numero_di_righe_variante
1	1	615000
2	10	396773
3	11	391260
4	12	382841
5	13	298044
…	…	…

Per ordinare l'output numericamente, 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 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	numero_di_righe_variante
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, invece di 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 è la stessa mostrata in Conteggio delle varianti per cromosomi.

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 Query di condensazione mostra come creare una query complessa, ma è eccessivamente 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 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 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 sui secondi trascorsi da quando è stata avviata una query e sul numero di byte elaborati. Per informazioni sull'ottimizzazione delle query, consulta la spiegazione del piano di query BigQuery.

Alcuni esempi in questa pagina, come Conteggio delle chiamate delle varianti in una tabella, mostrano più modi per scrivere una query. Per determinare quale metodo di query è più adatto a te, esamina la durata di query diverse e controlla il numero di byte di dati 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 vengano addebitati costi. Le seguenti sezioni 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:

Attenzione: l'eliminazione di un progetto ha i seguenti effetti:

• L'intero contenuto del progetto viene eliminato. Se hai utilizzato un progetto esistente per le attività in questo documento, quando lo elimini elimini anche qualsiasi altro lavoro svolto nel progetto.
• Gli ID progetto personalizzati non sono più disponibili. Quando hai creato questo progetto, potresti aver creato un ID progetto personalizzato che vuoi utilizzare in futuro. Per conservare gli URL che utilizzano l'ID progetto, ad esempio un URL appspot.com, elimina le risorse selezionate all'interno del progetto anziché eliminare l'intero progetto.

Se intendi esplorare più architetture, tutorial o guide rapide, puoi riutilizzare i progetti ed evitare così di superare i limiti di quota.

1. Nella console Google Cloud, vai alla pagina Gestisci risorse.

   Vai a Gestisci risorse

2. Nell'elenco dei progetti, seleziona il progetto che vuoi eliminare, quindi fai clic su Elimina.
3. Nella finestra di dialogo, digita l'ID del progetto e fai clic su Chiudi per eliminare il progetto.

Passaggi successivi

• Esplora gli altri tutorial di Cloud Life Sciences.
• Analizza le varianti in BigQuery utilizzando R, RMarkdown o JavaScript.