Cloud Life Sciences è ritirato e non sarà più disponibile su Google Cloud dopo l'8 luglio 2025. Ora i casi d'uso di Cloud Life Sciences sono supportati da Batch. Per scoprire come eseguire la migrazione del tuo carico di lavoro, consulta Eseguire la migrazione a Batch.

Questa pagina è stata tradotta dall'API Cloud Translation.

Guida avanzata per analizzare le varianti con BigQuery

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 BigQuery. Il nome della tabella è platinum_genomes_deepvariant_variants_20180823.

Se i tuoi dati sulle varianti si trovano in una tabella BigQuery che utilizza lo schema delle varianti 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 mostra come:

Visualizza una panoramica dei dati genomici.
Scopri come vengono rappresentati i segmenti non varianti.
Scopri come vengono rappresentate le chiamate di varianti.
Scopri come vengono rappresentati i filtri per la qualità delle chiamate delle varianti.
Aggrega le colonne gerarchiche.
Comprimi le query.
Conteggia 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 di varianti
Varianti richieste per ogni campione
Numero di campioni
Varianti per cromosoma
Varianti di alta qualità per campione

Costi

In questo documento, utilizzi i seguenti componenti fatturabili di Google Cloud:

BigQuery

Per generare una stima dei costi in base all'utilizzo previsto, utilizza il calcolatore prezzi.

I nuovi Google Cloud utenti potrebbero avere diritto a una prova gratuita.

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.

Go to project selector

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.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

Devi conoscere lo schema delle varianti BigQuery.

Visualizzare lo schema e i dati della tabella

Accedere alla tabella e visualizzare lo schema

La tabella Illumina Platinum Genomes platinum_genomes_deepvariant_variants_20180823 è disponibile pubblicamente.

Varianti e non varianti nella tabella

I dati Illumina Platinum Genomes utilizzano il formato gVCF, il che significa che nella tabella sono presenti righe che includono non varianti. Queste non varianti sono note anche come "chiamate di riferimento".

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

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

Il modo in cui vengono rappresentati i segmenti non varianti dipende in genere dal programma di chiamata delle varianti 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 varianti. I segmenti mostrano le seguenti informazioni:

Un blocco di riferimento di 10 basi sul cromosoma 1
Il blocco di riferimento inizia dalla posizione 1000
La base di riferimento nella posizione 1000 è un A
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 è una ARRAY di lunghezza 0.

reference_name	start_position	end_position	reference_bases	alternate_bases.alt
1	1000	1010	A

Nella tabella seguente, la colonna alternate_bases REPEATED RECORD ha lunghezza 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 di posizioni non varianti nel genoma, consulta la specifica VCF.

Visualizzare i 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 .

Vai alla pagina BigQuery

Vengono visualizzate le informazioni sulla tabella. La tabella contiene 19,6 GB di dati e più di 105.000.000 di 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 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.

Conteggio delle righe totali nella tabella

Per visualizzare il numero di righe nella tabella:

Vai alla pagina BigQuery nella console Google Cloud .

Vai alla pagina BigQuery
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

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

Per conteggiare il numero di chiamate di varianti, esegui una query sul numero di elementi all'interno delle colonne ARRAY. Puoi farlo in diversi modi, mostrati di seguito. Ogni query restituisce il valore 182.104.652, il che significa che nel set di dati è presente una media di 1,7 chiamate di varianti per riga.

Somma delle lunghezze degli array `call`

Conta il numero totale di chiamate di varianti in tutti i campioni sommando 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` ogni riga

Conta il numero totale di chiamate di varianti in tutti i campioni utilizzando un 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 esegue 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 è conteggiare i valori name nella colonna call. Ogni colonna call deve avere un singolo valore name, in modo da 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 con e senza varianti

Per conteggiare il numero di segmenti con e senza variazioni nella tabella, esegui prima una query per filtrare i segmenti senza variazioni:

#standardSQL
SELECT
  COUNT(1) AS number_of_real_variants
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.alternate_bases) AS alt
          WHERE
            alt.alt NOT IN ("<NON_REF>", "<*>"))

La query restituisce il seguente risultato:

Riga	number_of_real_variants
1	38549388

Come mostrato in Conteggio delle chiamate di varianti�, il numero totale di chiamate di varianti nella tabella è 182.104.652, quindi il risultato mostra che la maggior parte delle righe della tabella sono segmenti non 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 righe in cui la colonna alternate_bases ha un valore che è una variante vera, il che significa che non è un valore di marcatore speciale come <*> o <NON_REF>.

Per ogni riga della tabella, viene eseguita una sottoquery sulla colonna alternate_bases di quella riga, che restituisce il valore 1 per ogni valore di alternate_bases che non sia <NON_REF o <*>. Il numero di righe restituite dalla sottoquery è il numero di segmenti delle varianti.

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

Se sommiamo il numero di varianti reali (38.549.388) al numero di segmenti non varianti (143.555.264), otteniamo il numero totale di chiamate di varianti.

Conteggio delle varianti chiamate 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 per i quali sono state effettuate chiamate rispetto alle varianti.

Ogni variante della tabella ha zero o più valori per call.name. Un determinato valore call.name può essere visualizzato in più righe.

Per conteggiare il numero di righe in cui viene visualizzato ogni set 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 una persona 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

In genere, gli esseri umani non hanno le 30 milioni di varianti mostrate nei valori di call_count_for_call_set. Filtra i segmenti senza variazioni per conteggiare solo le righe con variazioni:

#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 valore più tipico per un essere umano. Continua alla sezione successiva per filtrare le varianti vere in base al genotipo.

Filtrare le varianti vere per genotipo

Le varianti nella tabella includono le chiamate non effettuate, rappresentate da un valore genotype pari a -1. Queste varianti non sono considerate vere varianti per gli individui, quindi devi filtrarle. Le varianti vere possono includere solo chiamate con genotipi maggiori di zero. Se una chiamata include solo genotipi che sono no-call (-1) o di riferimento (0), non sono vere varianti.

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 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	number_of_callsets
1	6

Conteggio delle 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 genotipo maggiore di 0.
Raggruppa le righe delle varianti per cromosoma e conta ogni gruppo.

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END

L'esecuzione della query restituisce il nome del cromosoma (reference_name) e il numero di righe di varianti per ciascun cromosoma:

Riga	reference_name	number_of_variant_rows
1	chr1	615000
2	chr2	646401
3	chr3	542315
4	chr4	578600
5	chr5	496202
…	…	…

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

`FILTER`ing for high quality variant calls

Quando analizzi le varianti, potresti voler 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 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 non restituisce alcun risultato.

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

Conteggio di tutte le chiamate di varianti vere di alta qualità per ogni campione

La seguente query mostra come conteggiare tutte le chiamate (varianti e non varianti) per ogni campione. Omette qualsiasi chiamata con un filtro non PASS e include solo le chiamate con almeno una variante 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

Comprimere le query

Man mano che le query diventano più complesse, è importante mantenerle concise per assicurarsi che la loro logica sia corretta e semplice da seguire.

Il seguente esempio mostra come partire da una query che conta il numero di varianti per cromosoma e, passo dopo passo, come comprimerla utilizzando la sintassi SQL e le funzioni definite dall'utente.

Come spiegato nella sezione sul 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 per cromosoma e conta ogni 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 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é 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 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 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 numericamente, 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 modifica quindi le funzioni GROUP BY e ORDER BY per utilizzare l'alias chromosome calcolato. L'output continua a essere 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 l'output numericamente, converti la colonna chromosome da stringa a 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 aggiungere uno "0" ai cromosomi da 1 a 9 e rimuovere il prefisso "chr":

#standardSQL
SELECT
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END AS chromosome,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  chromosome
ORDER BY
  chromosome

La query restituisce l'output corretto:

Riga	cromosoma	number_of_variant_rows
1	01	615000
2	02	646401
3	03	542315
4	04	578600
5	05	496202
…	…	…

La query utilizza la funzione SAFE_CAST, che restituisce NULL per i cromosomi X, Y e M anziché restituire un errore.

Come ultimo miglioramento 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 Compressione delle query mostra come creare una query complessa, ma la query è eccessivamente 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 rendere la query più concisa, ma utilizza una funzione definita in JavaScript:

#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
  RETURNS STRING LANGUAGE js AS """
  // Remove the leading "chr" (if any) in the reference_name
  var chr = reference_name.replace(/^chr/, '');

  // If the chromosome is 1 - 9, prepend a "0" since
  // "2" sorts after "10", but "02" sorts before "10".
  if (chr.length == 1 && '123456789'.indexOf(chr) >= 0) {
    return '0' + chr;
  }

  return chr;
""";

SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY SortableChromosome(reference_name)

Entrambe le query restituiscono il risultato corretto e la loro logica è più concisa.

Migliorare le prestazioni delle query e ridurre i costi

I prezzi di BigQuery si basano sul numero di byte elaborati per una query. Le prestazioni delle query migliorano quando la quantità di dati elaborati viene ridotta. BigQuery fornisce dati su quanti secondi sono trascorsi dall'inizio di una query e quanti byte ha elaborato la query. Per informazioni sull'ottimizzazione delle query, consulta la spiegazione del piano di query BigQuery.

Alcuni degli esempi in questa pagina, come Conteggio delle chiamate di 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 diverse query e verifica quanti byte di dati elaborano.

Esegui la pulizia

Al termine del tutorial, puoi liberare spazio eliminando le risorse che hai creato in modo che non utilizzino più la quota e non generino addebiti. Le seguenti sezioni descrivono come eliminare o disattivare queste risorse.

Il modo più semplice per eliminare la fatturazione è quello di eliminare il progetto 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à descritte in questo documento, quando lo elimini, elimini anche tutto il lavoro che hai 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.

In the Google Cloud console, go to the Manage resources page.
Go to Manage resources
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.

Guida avanzata per analizzare le varianti con BigQuery Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.

Obiettivi

Costi

Prima di iniziare

Visualizzare lo schema e i dati della tabella

Accedere alla tabella e visualizzare lo schema

Varianti e non varianti nella tabella

Visualizzare i dati della tabella

Esecuzione di query sulla tabella

Conteggio delle righe totali nella tabella

Conteggio delle chiamate di varianti nella tabella

Somma delle lunghezze degli array call

JOIN ogni riga

Conteggio di name in una colonna call

Conteggio dei segmenti con e senza varianti

Conteggio delle varianti chiamate da ogni campione

Filtrare le varianti vere per genotipo

Conteggio dei campioni nella tabella

Conteggio delle varianti per cromosoma

Conteggio delle varianti di alta qualità per campione

Eseguire query sulle chiamate con più valori FILTER

FILTERing for high quality variant calls

Conteggio di tutte le chiamate di alta qualità per ogni campione

Conteggio di tutte le chiamate di varianti vere di alta qualità per ogni campione

Best practice

Comprimere le query

Scrittura di funzioni definite dall'utente

Migliorare le prestazioni delle query e ridurre i costi

Esegui la pulizia

Passaggi successivi

Guida avanzata per analizzare le varianti con BigQuery

Somma delle lunghezze degli array `call`

`JOIN` ogni riga

Conteggio di `name` in una colonna `call`

Eseguire query sulle chiamate con più valori `FILTER`

`FILTER`ing for high quality variant calls