Puoi utilizzare BigQuery per eseguire una query JOIN su varianti con dati descritti da intervalli di regioni genomiche o sovrapposizioni. Questa pagina mostra come
utilizzare una query JOIN complessa per prendere un elenco di nomi di geni ed eseguire quanto segue:
- Trova gli SNP rari che si sovrappongono ai geni.
- Trova 100.000 paia di basi su entrambi i lati di un gene per tutti i campioni del genoma.
Questa guida mostra esempi di tre query. Ogni query mostra come BigQuery si espande su dimensioni diverse dei dati genomici:
- Esegui una query su una tabella in linea.
- Esegui una query su una tabella materializzata con geni specifici.
- Esegui una query su una tabella materializzata con 250 geni casuali.
I dati provengono dalla tabella di annotazione Tute Genomics con quasi 9 miliardi di righe e dal set di dati Illumina Platinum Genomes. Se non hai familiarità con questi set di dati, consulta i seguenti link:
- Esplorazione della variabilità genetica con Google Genomics e Tute dal blog Google Cloud
- Illumina Platinum Genomes nella sezione sui set di dati pubblici
Eseguire query su una tabella in linea
L'esempio seguente utilizza una tabella di intervalli definita nella query, chiamata
intervals, e mostra come eseguire una query JOIN con una tabella
contenente varianti di
Illumina Platinum Genomes:
Vai alla pagina BigQuery nella console Google Cloud.
Fai clic su Componi query.
Nel campo Nuova query, esegui la seguente query:
#standardSQL WITH -- -- Retrieve the variants in this cohort, flattening by alternate bases and -- counting affected alleles. variants AS ( SELECT REPLACE(reference_name, 'chr', '') as reference_name, start_position, end_position, reference_bases, alternate_bases.alt AS alt, (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles, (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ), -- -- Define an inline table that uses five rows -- selected from silver-wall-555.TuteTable.hg19. intervals AS ( SELECT * FROM UNNEST ([ STRUCT<Gene STRING, Chr STRING, gene_start INT64, gene_end INT64, region_start INT64, region_end INT64> ('PRCC', '1', 156736274, 156771607, 156636274, 156871607), ('NTRK1', '1', 156785541, 156852640, 156685541, 156952640), ('PAX8', '2', 113972574, 114037496, 113872574, 114137496), ('FHIT', '3', 59734036, 61238131, 59634036, 61338131), ('PPARG', '3', 12328349, 12476853, 12228349, 12576853) ])), -- -- JOIN the variants with the genomic intervals overlapping -- the genes of interest. -- -- The JOIN criteria is complicated because the task is to see if -- an SNP overlaps an interval. With standard SQL you can use complex -- JOIN predicates, including arbitrary expressions. gene_variants AS ( SELECT reference_name, start_position, reference_bases, alt, num_variant_alleles, total_num_alleles FROM variants INNER JOIN intervals ON variants.reference_name = intervals.Chr AND intervals.region_start <= variants.start_position AND intervals.region_end >= variants.end_position ) -- -- And finally JOIN the variants in the regions of interest -- with annotations for rare variants. SELECT DISTINCT Chr, annots.Start AS Start, Ref, annots.Alt, Func, Gene, PopFreqMax, ExonicFunc, num_variant_alleles, total_num_alleles FROM `silver-wall-555.TuteTable.hg19` AS annots INNER JOIN gene_variants AS vars ON vars.reference_name = annots.Chr AND vars.start_position = annots.Start AND vars.reference_bases = annots.Ref AND vars.alt = annots.Alt WHERE -- Retrieve annotations for rare variants only. PopFreqMax <= 0.01 ORDER BY Chr, Start;
Fai clic su Esegui query. L'esecuzione della query richiede circa dieci secondi ed elabora circa 334 GB di dati. I risultati della query identificano le varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.
Espandi la seguente sezione per visualizzare i risultati della query:
Risultati delle query
Chr Inizia Rif Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 156699757 T C intronico RRNAD1 0,002 2 4 1 156705390 C T intronico RRNAD1 8,0E-4 0 2 1 156714207 T C intronico HDGF 0,003 0 6 1 156714440 A C intronico HDGF 0,0068 0 12 1 156723870 C T intergenico HDGF,PRCC 0,006 1 2 1 156724456 C T intergenico HDGF,PRCC 0,002 2 4 1 156733988 C T intergenico HDGF,PRCC 0,001 1 2 1 156742258 T V intronico PRCC 0,001 2 4 1 156744826 T V intronico PRCC 0,002 0 8 1 156779764 V A intronico SH2D2A 0,001 2 4 1 156783454 A C intronico SH2D2A 0,0014 1 2 1 156786144 C T intronico NTRK1,SH2D2A 0,0031 2 4 1 156790510 A T intronico NTRK1 0,002 1 2 1 156815332 A C intronico INSRR,NTRK1 0,003 0 2 1 156830778 V A esogeno NTRK1 0,0067 di tipo missenso 2 4 1 156842064 C T intronico NTRK1 0,0014 1 2 1 156843438 C A esogeno NTRK1 0,0032 di tipo missenso 1 2 1 156845773 C T intronico NTRK1 0,001 2 4 1 156873318 T C intronico PEAR1 0,01 4 8 1 156922740 V A intronico ARHGEF11 0,007 1 2 1 156930100 C T intronico ARHGEF11 0,001 2 4 2 113901230 V A intergenico IL1RN,PSD4 0,0082 1 2 2 113953418 C A intronico PSD4 0,001 2 4 2 113967621 V C intergenico PSD4,PAX8 0,002 0 6 2 113967624 T C intergenico PSD4,PAX8 0,002 0 2 2 113980967 V A intronico PAX8 0,002 2 4 2 113994010 A C ncRNA_exonic PAX8-AS1 0,001 0 4 2 113997745 C A ncRNA_exonic PAX8-AS1 0,001 2 4 2 114061327 T C intergenico PAX8,CBWD2 0,001 2 4 2 114084018 A C intergenico PAX8,CBWD2 0,0045 0 4 2 114099037 V A intergenico PAX8,CBWD2 0,0051 1 2 2 114105670 A T intergenico PAX8,CBWD2 0,001 1 2 2 114111325 V T intergenico PAX8,CBWD2 0,001 1 2 3 12265797 C T intergenico SYN2,PPARG 0,0089 2 4 3 12277958 A V intergenico SYN2,PPARG 0,002 1 2 3 12296019 V A intergenico SYN2,PPARG 0,002 2 4 3 12316549 V C intergenico SYN2,PPARG 0,002 1 2 3 12335681 T V intronico PPARG 0,0092 2 4 3 12348795 T C intronico PPARG 0,0014 1 2 3 12353106 T C intronico PPARG 0,001 2 4 3 12403825 V A intronico PPARG 0,0051 2 4 3 12404394 V A intronico PPARG 0,001 1 2 3 12410289 V A intronico PPARG 0,008 2 4 3 12431381 C T intronico PPARG 0,0061 2 4 3 12447267 V A intronico PPARG 0,0089 2 4 3 12449379 C T intronico PPARG 0,0092 2 4 3 12450848 C A intronico PPARG 0,0092 2 4 3 12462847 T C intronico PPARG 0,002 1 2 3 12492797 V A intergenico PPARG,TSEN2 0,01 1 2 3 12503201 V A intergenico PPARG,TSEN2 0,0099 2 4 3 12530460 A V intronico TSEN2 0,0092 2 4 3 12531167 A V intronico TSEN2 0,0099 2 4 3 12557737 A V intronico TSEN2 0,001 2 4 3 59636143 A V intergenico C3orf67,FHIT 0,003 3 6 3 59645934 A C intergenico C3orf67,FHIT 0,004 1 2 3 59646893 V A intergenico C3orf67,FHIT 0,002 1 2 3 59697024 A V intergenico C3orf67,FHIT 0,0072 1 2 3 59701013 V A intergenico C3orf67,FHIT 0,004 2 4 3 59733945 A V intergenico C3orf67,FHIT 0,001 2 4 3 59747482 C T intronico FHIT 0,001 2 4 3 59750635 A V intronico FHIT 0,003 1 2 3 59757776 C T intronico FHIT 0,001 2 4 3 59770612 V A intronico FHIT 0,001 2 4 3 59804444 V C intronico FHIT 0,001 2 4 3 59819769 T C intronico FHIT 0,001 2 4 3 59884396 C T intronico FHIT 0,001 2 4 3 59960728 A C intronico FHIT 0,01 1 2 3 59970345 V A intronico FHIT 0,002 1 2 3 59972417 T A intronico FHIT 0,0072 0 2 3 60104328 C A intronico FHIT 0,01 2 4 3 60139062 V A intronico FHIT 0,01 0 2 3 60158066 C T intronico FHIT 0,001 1 2 3 60169285 C T intronico FHIT 0,005 1 2 3 60216185 T C intronico FHIT 0,002 1 2 3 60226380 V A intronico FHIT 0,007 2 4 3 60234539 C A intronico FHIT 0,002 1 2 3 60247464 A C intronico FHIT 0,004 2 4 3 60269926 A V intronico FHIT 0,007 2 4 3 60271228 V T intronico FHIT 0,007 2 4 3 60286972 T C intronico FHIT 0,001 2 4 3 60301412 C V intronico FHIT 0,001 1 2 3 60312251 C T intronico FHIT 0,0099 1 2 3 60317682 A V intronico FHIT 0,008 1 2 3 60328557 C V intronico FHIT 0,0043 2 4 3 60342562 C T intronico FHIT 0,006 1 2 3 60400033 V A intronico FHIT 0,004 2 4 3 60435819 C T intronico FHIT 0,006 2 4 3 60435820 V T intronico FHIT 0,004 1 2 3 60441288 T C intronico FHIT 0,006 2 4 3 60444465 C A intronico FHIT 0,01 1 2 3 60444575 C T intronico FHIT 0,001 1 2 3 60450581 T C intronico FHIT 0,01 1 2 3 60456571 V A intronico FHIT 0,001 2 4 3 60473568 C V intronico FHIT 0,001 1 2 3 60487557 T C intronico FHIT 0,001 1 2 3 60559705 A V intronico FHIT 0,002 2 4 3 60570764 T C intronico FHIT 0,008 2 4 3 60582100 C T intronico FHIT 0,001 1 2 3 60587192 V A intronico FHIT 0,004 1 2 3 60599869 V A intronico FHIT 0,0086 2 4 3 60603091 C T intronico FHIT 0,001 2 4 3 60603250 A T intronico FHIT 0,0099 1 2 3 60609831 T V intronico FHIT 0,001 2 4 3 60619756 V T intronico FHIT 0,0015 2 4 3 60680758 C T intronico FHIT 0,0089 2 4 3 60702243 V C intronico FHIT 0,001 2 4 3 60702532 A V intronico FHIT 0,001 1 2 3 60714328 A T intronico FHIT 0,004 1 2 3 60725297 V A intronico FHIT 0,001 1 2 3 60726640 V A intronico FHIT 0,01 2 4 3 60795144 A V intronico FHIT 0,001 2 4 3 60807171 A V intronico FHIT 0,001 1 2 3 60813868 T C intronico FHIT 0,001 1 2 3 60826546 C V intronico FHIT 0,0023 1 2 3 60837392 C T intronico FHIT 0,001 1 2 3 60846310 A V intronico FHIT 0,01 0 2 3 60850985 C T intronico FHIT 0,004 1 2 3 60852559 T C intronico FHIT 0,008 1 2 3 60871759 T C intronico FHIT 0,004 1 2 3 60884396 C T intronico FHIT 0,002 2 4 3 60897092 C A intronico FHIT 0,001 2 4 3 60940759 C T intronico FHIT 0,0089 1 2 3 60982595 A V intronico FHIT 0,003 2 4 3 60999283 V A intronico FHIT 0,001 1 2 3 61042977 A V intronico FHIT 0,001 2 4 3 61043349 T C intronico FHIT 0,001 2 4 3 61044789 A C intronico FHIT 0,001 2 4 3 61141621 V A intronico FHIT 0,003 1 2 3 61148655 V C intronico FHIT 0,001 2 4 3 61170747 C T intronico FHIT 0,003 1 2 3 61189473 C V intronico FHIT 0,0099 1 2 3 61190425 C T intronico FHIT 0,0023 2 4 3 61193853 C T intronico FHIT 0,0099 0 2 3 61194793 C T intronico FHIT 0,007 0 2 3 61194840 A V intronico FHIT 0,0099 0 2 3 61194886 T A intronico FHIT 0,0099 0 2 3 61201777 C T intronico FHIT 0,001 2 4 3 61202292 T C intronico FHIT 0,007 1 2 3 61232806 V C intronico FHIT 0,0099 1 2 3 61232910 C T intronico FHIT 0,0099 1 2 3 61235824 A T intronico FHIT 0,001 2 4 3 61283810 A C intergenico FHIT,PTPRG 0,0089 1 2 3 61293731 T A intergenico FHIT,PTPRG 0,0089 2 4 3 61296730 C T intergenico FHIT,PTPRG 0,001 1 2 3 61326341 C T intergenico FHIT,PTPRG 0,004 2 4 3 61326620 T C intergenico FHIT,PTPRG 0,01 1 2 3 61327649 V C intergenico FHIT,PTPRG 0,001 2 4 3 61330545 V C intergenico FHIT,PTPRG 0,001 2 4 3 61335803 V A intergenico FHIT,PTPRG 0,001 2 4 Una query simile che utilizza i dati della fase 3 del progetto 1000 Genomes richiede circa 90 secondi per l'esecuzione ed elabora circa 3,38 TB di dati.
Utilizzare una tabella materializzata
Quando lavori con big data su larga scala, puoi materializzare una tabella di intervalli
ed eseguire una query JOIN sulla nuova tabella. Prima di continuare con il resto
di questa sezione, crea un set di dati seguendo questi passaggi:
Apri la pagina BigQuery nella console Google Cloud.
Nel riquadro Explorer, seleziona il progetto in cui vuoi creare il set di dati.
Espandi l'opzione Azioni e fai clic su Crea set di dati.
Nella pagina Crea set di dati:
- In ID set di dati, inserisci
genomics. - Lascia invariate le altre impostazioni predefinite.
- Fai clic su Crea set di dati.
- In ID set di dati, inserisci
Esegui una query su una tabella materializzata con geni specifici
I passaggi riportati di seguito mostrano come materializzare una nuova tabella di intervalli che contiene un elenco di geni specifici della tabella silver-wall-555:TuteTable.hg19.
Per creare la tabella degli intervalli:
Apri la pagina BigQuery nella console Google Cloud.
Fai clic su Componi query.
Nel campo Nuova query, esegui la seguente query. La query materializza parte della tabella silver-wall-555:TuteTable.hg19 in una nuova tabella di intervalli
genomics.myIntervalTable.#standardSQL CREATE TABLE `genomics.myIntervalTable` AS ( SELECT Gene, Chr, MIN(Start) AS gene_start, MAX(`End`) AS gene_end, MIN(Start)-100000 AS region_start, MAX(`End`)+100000 AS region_end FROM `silver-wall-555.TuteTable.hg19` WHERE Gene IN ('APC', 'ATM', 'BMPR1A', 'BRCA1', 'BRCA2', 'CDK4', 'CDKN2A', 'CREBBP', 'EGFR', 'EP300', 'ETV6', 'FHIT', 'FLT3', 'HRAS', 'KIT', 'MET', 'MLH1', 'NTRK1', 'PAX8', 'PDGFRA', 'PPARG', 'PRCC', 'PRKAR1A', 'PTEN', 'RET', 'STK11', 'TFE3', 'TGFB1', 'TGFBR2', 'TP53', 'WWOX') GROUP BY Chr, Gene );
Fai clic su Esegui query. La query restituisce il seguente risultato:
This statement created a new table named PROJECT_ID:genomics.myIntervalTable.
Nel campo Nuova query, esegui la seguente query:
#standardSQL WITH -- -- Retrieve the variants in this cohort, flattening by alternate bases and -- counting affected alleles. variants AS ( SELECT REPLACE(reference_name, 'chr', '') as reference_name, start_position, end_position, reference_bases, alternate_bases.alt AS alt, (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles, (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ), -- -- JOIN the variants with the genomic intervals overlapping -- the genes of interest. -- -- The JOIN criteria is complicated because the task is to see if -- an SNP overlaps an interval. With standard SQL you can use complex -- JOIN predicates, including arbitrary expressions. gene_variants AS ( SELECT reference_name, start_position, reference_bases, alt, num_variant_alleles, total_num_alleles FROM variants INNER JOIN `genomics.myIntervalTable` AS intervals ON variants.reference_name = intervals.Chr AND intervals.region_start <= variants.start_position AND intervals.region_end >= variants.end_position ) -- -- And finally JOIN the variants in the regions of interest -- with annotations for rare variants. SELECT DISTINCT Chr, annots.Start AS Start, Ref, annots.Alt, Func, Gene, PopFreqMax, ExonicFunc, num_variant_alleles, total_num_alleles FROM `silver-wall-555.TuteTable.hg19` AS annots INNER JOIN gene_variants AS vars ON vars.reference_name = annots.Chr AND vars.start_position = annots.Start AND vars.reference_bases = annots.Ref AND vars.alt = annots.Alt WHERE -- Retrieve annotations for rare variants only. PopFreqMax <= 0.01 ORDER BY Chr, Start;
Fai clic su Esegui query. L'esecuzione della query richiede circa dieci secondi ed elabora circa 334 GB di dati. I risultati della query identificano le varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.
Espandi la seguente sezione per visualizzare i risultati della query:
Risultati delle query
Chr Inizia Rif Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 156699757 T C intronico RRNAD1 0,002 2 4 1 156705390 C T intronico RRNAD1 8,0E-4 0 2 1 156714207 T C intronico HDGF 0,003 0 6 1 156714440 A C intronico HDGF 0,0068 0 12 1 156723870 C T intergenico HDGF,PRCC 0,006 1 2 1 156724456 C T intergenico HDGF,PRCC 0,002 2 4 1 156733988 C T intergenico HDGF,PRCC 0,001 1 2 1 156742258 T V intronico PRCC 0,001 2 4 1 156744826 T V intronico PRCC 0,002 0 8 1 156779764 V A intronico SH2D2A 0,001 2 4 1 156783454 A C intronico SH2D2A 0,0014 1 2 1 156786144 C T intronico NTRK1,SH2D2A 0,0031 2 4 1 156790510 A T intronico NTRK1 0,002 1 2 1 156815332 A C intronico INSRR,NTRK1 0,003 0 2 1 156830778 V A esogeno NTRK1 0,0067 di tipo missenso 2 4 1 156842064 C T intronico NTRK1 0,0014 1 2 1 156843438 C A esogeno NTRK1 0,0032 di tipo missenso 1 2 1 156845773 C T intronico NTRK1 0,001 2 4 1 156873318 T C intronico PEAR1 0,01 4 8 1 156922740 V A intronico ARHGEF11 0,007 1 2 1 156930100 C T intronico ARHGEF11 0,001 2 4 2 113901230 V A intergenico IL1RN,PSD4 0,0082 1 2 2 113953418 C A intronico PSD4 0,001 2 4 2 113967621 V C intergenico PSD4,PAX8 0,002 0 6 2 113967624 T C intergenico PSD4,PAX8 0,002 0 2 2 113980967 V A intronico PAX8 0,002 2 4 2 113994010 A C ncRNA_exonic PAX8-AS1 0,001 0 4 2 113997745 C A ncRNA_exonic PAX8-AS1 0,001 2 4 2 114061327 T C intergenico PAX8,CBWD2 0,001 2 4 2 114084018 A C intergenico PAX8,CBWD2 0,0045 0 4 2 114099037 V A intergenico PAX8,CBWD2 0,0051 1 2 2 114105670 A T intergenico PAX8,CBWD2 0,001 1 2 2 114111325 V T intergenico PAX8,CBWD2 0,001 1 2 3 12265797 C T intergenico SYN2,PPARG 0,0089 2 4 3 12277958 A V intergenico SYN2,PPARG 0,002 1 2 3 12296019 V A intergenico SYN2,PPARG 0,002 2 4 3 12316549 V C intergenico SYN2,PPARG 0,002 1 2 3 12335681 T V intronico PPARG 0,0092 2 4 3 12348795 T C intronico PPARG 0,0014 1 2 3 12353106 T C intronico PPARG 0,001 2 4 3 12403825 V A intronico PPARG 0,0051 2 4 3 12404394 V A intronico PPARG 0,001 1 2 3 12410289 V A intronico PPARG 0,008 2 4 3 12431381 C T intronico PPARG 0,0061 2 4 3 12447267 V A intronico PPARG 0,0089 2 4 3 12449379 C T intronico PPARG 0,0092 2 4 3 12450848 C A intronico PPARG 0,0092 2 4 3 12462847 T C intronico PPARG 0,002 1 2 3 12492797 V A intergenico PPARG,TSEN2 0,01 1 2 3 12503201 V A intergenico PPARG,TSEN2 0,0099 2 4 3 12530460 A V intronico TSEN2 0,0092 2 4 3 12531167 A V intronico TSEN2 0,0099 2 4 3 12557737 A V intronico TSEN2 0,001 2 4 3 59636143 A V intergenico C3orf67,FHIT 0,003 3 6 3 59645934 A C intergenico C3orf67,FHIT 0,004 1 2 3 59646893 V A intergenico C3orf67,FHIT 0,002 1 2 3 59697024 A V intergenico C3orf67,FHIT 0,0072 1 2 3 59701013 V A intergenico C3orf67,FHIT 0,004 2 4 3 59733945 A V intergenico C3orf67,FHIT 0,001 2 4 3 59747482 C T intronico FHIT 0,001 2 4 3 59750635 A V intronico FHIT 0,003 1 2 3 59757776 C T intronico FHIT 0,001 2 4 3 59770612 V A intronico FHIT 0,001 2 4 3 59804444 V C intronico FHIT 0,001 2 4 3 59819769 T C intronico FHIT 0,001 2 4 3 59884396 C T intronico FHIT 0,001 2 4 3 59960728 A C intronico FHIT 0,01 1 2 3 59970345 V A intronico FHIT 0,002 1 2 3 59972417 T A intronico FHIT 0,0072 0 2 3 60104328 C A intronico FHIT 0,01 2 4 3 60139062 V A intronico FHIT 0,01 0 2 3 60158066 C T intronico FHIT 0,001 1 2 3 60169285 C T intronico FHIT 0,005 1 2 3 60216185 T C intronico FHIT 0,002 1 2 3 60226380 V A intronico FHIT 0,007 2 4 3 60234539 C A intronico FHIT 0,002 1 2 3 60247464 A C intronico FHIT 0,004 2 4 3 60269926 A V intronico FHIT 0,007 2 4 3 60271228 V T intronico FHIT 0,007 2 4 3 60286972 T C intronico FHIT 0,001 2 4 3 60301412 C V intronico FHIT 0,001 1 2 3 60312251 C T intronico FHIT 0,0099 1 2 3 60317682 A V intronico FHIT 0,008 1 2 3 60328557 C V intronico FHIT 0,0043 2 4 3 60342562 C T intronico FHIT 0,006 1 2 3 60400033 V A intronico FHIT 0,004 2 4 3 60435819 C T intronico FHIT 0,006 2 4 3 60435820 V T intronico FHIT 0,004 1 2 3 60441288 T C intronico FHIT 0,006 2 4 3 60444465 C A intronico FHIT 0,01 1 2 3 60444575 C T intronico FHIT 0,001 1 2 3 60450581 T C intronico FHIT 0,01 1 2 3 60456571 V A intronico FHIT 0,001 2 4 3 60473568 C V intronico FHIT 0,001 1 2 3 60487557 T C intronico FHIT 0,001 1 2 3 60559705 A V intronico FHIT 0,002 2 4 3 60570764 T C intronico FHIT 0,008 2 4 3 60582100 C T intronico FHIT 0,001 1 2 3 60587192 V A intronico FHIT 0,004 1 2 3 60599869 V A intronico FHIT 0,0086 2 4 3 60603091 C T intronico FHIT 0,001 2 4 3 60603250 A T intronico FHIT 0,0099 1 2 3 60609831 T V intronico FHIT 0,001 2 4 3 60619756 V T intronico FHIT 0,0015 2 4 3 60680758 C T intronico FHIT 0,0089 2 4 3 60702243 V C intronico FHIT 0,001 2 4 3 60702532 A V intronico FHIT 0,001 1 2 3 60714328 A T intronico FHIT 0,004 1 2 3 60725297 V A intronico FHIT 0,001 1 2 3 60726640 V A intronico FHIT 0,01 2 4 3 60795144 A V intronico FHIT 0,001 2 4 3 60807171 A V intronico FHIT 0,001 1 2 3 60813868 T C intronico FHIT 0,001 1 2 3 60826546 C V intronico FHIT 0,0023 1 2 3 60837392 C T intronico FHIT 0,001 1 2 3 60846310 A V intronico FHIT 0,01 0 2 3 60850985 C T intronico FHIT 0,004 1 2 3 60852559 T C intronico FHIT 0,008 1 2 3 60871759 T C intronico FHIT 0,004 1 2 3 60884396 C T intronico FHIT 0,002 2 4 3 60897092 C A intronico FHIT 0,001 2 4 3 60940759 C T intronico FHIT 0,0089 1 2 3 60982595 A V intronico FHIT 0,003 2 4 3 60999283 V A intronico FHIT 0,001 1 2 3 61042977 A V intronico FHIT 0,001 2 4 3 61043349 T C intronico FHIT 0,001 2 4 3 61044789 A C intronico FHIT 0,001 2 4 3 61141621 V A intronico FHIT 0,003 1 2 3 61148655 V C intronico FHIT 0,001 2 4 3 61170747 C T intronico FHIT 0,003 1 2 3 61189473 C V intronico FHIT 0,0099 1 2 3 61190425 C T intronico FHIT 0,0023 2 4 3 61193853 C T intronico FHIT 0,0099 0 2 3 61194793 C T intronico FHIT 0,007 0 2 3 61194840 A V intronico FHIT 0,0099 0 2 3 61194886 T A intronico FHIT 0,0099 0 2 3 61201777 C T intronico FHIT 0,001 2 4 3 61202292 T C intronico FHIT 0,007 1 2 3 61232806 V C intronico FHIT 0,0099 1 2 3 61232910 C T intronico FHIT 0,0099 1 2 3 61235824 A T intronico FHIT 0,001 2 4 3 61283810 A C intergenico FHIT,PTPRG 0,0089 1 2 3 61293731 T A intergenico FHIT,PTPRG 0,0089 2 4 3 61296730 C T intergenico FHIT,PTPRG 0,001 1 2 3 61326341 C T intergenico FHIT,PTPRG 0,004 2 4 3 61326620 T C intergenico FHIT,PTPRG 0,01 1 2 3 61327649 V C intergenico FHIT,PTPRG 0,001 2 4 3 61330545 V C intergenico FHIT,PTPRG 0,001 2 4 3 61335803 V A intergenico FHIT,PTPRG 0,001 2 4 Una query simile che utilizza i dati della fase 3 del progetto 1000 Genomes richiede circa 90 secondi per l'esecuzione ed elabora circa 3,38 TB di dati.
Esegui una query su una tabella materializzata con 250 geni casuali
L'esempio seguente mostra come eseguire un intervallo JOIN su una tabella materializzata contenente 250 geni selezionati in modo casuale dalla tabella silver-wall-555:TuteTable.hg19.
Per creare la tabella degli intervalli:
Apri la pagina BigQuery nella console Google Cloud.
Fai clic su Componi query.
Nel campo Nuova query, esegui la seguente query, che materializza parte della tabella silver-wall-555:TuteTable.hg19 in una nuova tabella di intervalli
genomics.randomGenesIntervalTable.#standardSQL CREATE TABLE `genomics.randomGenesIntervalTable` AS ( SELECT Gene, Chr, MIN(Start) AS gene_start, MAX(`End`) AS gene_end, MIN(Start) - 100000 AS region_start, MAX(`End`) + 100000 AS region_end FROM `silver-wall-555.TuteTable.hg19` WHERE Gene IN (SELECT Gene FROM `silver-wall-555.TuteTable.hg19` GROUP BY Gene LIMIT 250) GROUP BY Chr, Gene );
- Fai clic su Esegui query. La query restituisce il seguente risultato:
This statement created a new table named PROJECT_ID:genomics.randomGenesIntervalTable.
Nel campo Nuova query, esegui la seguente query:
#standardSQL WITH -- -- Retrieve the variants in this cohort, flattening by alternate bases and -- counting affected alleles. variants AS ( SELECT REPLACE(reference_name, 'chr', '') as reference_name, start_position, end_position, reference_bases, alternate_bases.alt AS alt, (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles, (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ), -- -- JOIN the variants with the genomic intervals overlapping -- the genes of interest. -- -- The JOIN criteria is complicated because the task is to see if -- an SNP overlaps an interval. With standard SQL you can use complex -- JOIN predicates, including arbitrary expressions. gene_variants AS ( SELECT reference_name, start_position, reference_bases, alt, num_variant_alleles, total_num_alleles FROM variants INNER JOIN `genomics.randomGenesIntervalTable` AS intervals ON variants.reference_name = intervals.Chr AND intervals.region_start <= variants.start_position AND intervals.region_end >= variants.end_position ) -- -- And finally JOIN the variants in the regions of interest -- with annotations for rare variants. SELECT DISTINCT Chr, annots.Start AS Start, Ref, annots.Alt, Func, Gene, PopFreqMax, ExonicFunc, num_variant_alleles, total_num_alleles FROM `silver-wall-555.TuteTable.hg19` AS annots INNER JOIN gene_variants AS vars ON vars.reference_name = annots.Chr AND vars.start_position = annots.Start AND vars.reference_bases = annots.Ref AND vars.alt = annots.Alt WHERE -- Retrieve annotations for rare variants only. PopFreqMax <= 0.01 ORDER BY Chr, Start;
Fai clic su Esegui query. L'esecuzione della query richiede circa dieci secondi ed elabora circa 334 GB di dati. I risultati della query identificano le varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.
Espandi la sezione seguente per visualizzare i risultati troncati della query:
Risultati delle query
Chr Inizia Rif Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 2925355 C A intergenico TTC34,ACTRT2 0,001 2 4 1 2933170 V A intergenico TTC34,ACTRT2 0,0083 0 4 1 2944477 V A intergenico ACTRT2,LINC00982 0,003 4 6 1 2967591 A T intergenico ACTRT2,LINC00982 0,0092 1 2 1 2975255 T C downstream LINC00982 0,0082 1 2 1 2977223 C T ncRNA_intronic LINC00982 0,0072 1 2 1 2978803 V C ncRNA_exonic LINC00982 0,002 4 6 1 3006466 V A intronico PRDM16 0,0098 1 2 1 3011333 V T intronico PRDM16 0,004 1 2 1 3019659 C T intronico PRDM16 0,0031 1 2 1 3036896 V A intronico PRDM16 0,001 1 2 1 3037388 V A intronico PRDM16 0,002 2 4 1 3041250 T V intronico PRDM16 0,006 2 4 1 3042502 A T intronico PRDM16 0,003 4 6 1 3053713 A C intronico PRDM16 0,002 1 2 1 3063109 C T intronico PRDM16 0,002 0 2 1 3063593 T C intronico PRDM16 0,003 1 2 1 3076439 C T intronico PRDM16 0,001 2 4 1 3078960 V A intronico PRDM16 0,007 2 4 1 3084268 A C intronico PRDM16 0,005 0 2 1 3084492 T C intronico PRDM16 0,0015 0 2 1 3084786 T C intronico PRDM16 0,0015 0 4 1 3111119 V A intronico PRDM16 0,003 1 2 1 3111643 C T intronico PRDM16 0,0041 1 2 1 3114807 V A intronico PRDM16 0,0041 1 2 1 3165530 C T intronico PRDM16 0,0089 1 2 1 3169325 V A intronico PRDM16 0,008 2 4 1 3179623 C T intronico PRDM16 0,003 2 4 1 3181097 C T intronico PRDM16 0,001 2 4 1 3194000 V C intronico PRDM16 0,005 2 4 1 3195769 T C intronico PRDM16 0,002 1 2 1 3197351 C T intronico PRDM16 0,0061 1 2 1 3224100 C A intronico PRDM16 0,003 2 4 1 3228644 V T intronico PRDM16 0,001 2 4 1 3234045 V A intronico PRDM16 0,002 1 2 1 3235971 V A intronico PRDM16 0,0089 1 2 1 3274115 C T intronico PRDM16 0,001 2 4 1 3291388 V A intronico PRDM16 0,002 2 4 1 3295658 A C intronico PRDM16 0,0068 0 6 1 3295937 A C intronico PRDM16 0,0068 0 2 1 3296205 T C intronico PRDM16 0,0083 0 2 1 3315690 V A intronico PRDM16 0,001 2 4 1 3329212 V A esogeno PRDM16 0,0031 di tipo missenso 1 2 1 3331787 C T intronico PRDM16 0,0099 1 2 1 3370316 V C upstream ARHGEF16 0,001 2 4 1 3379560 A V intronico ARHGEF16 0,0051 0 6 1 3391174 C T intronico ARHGEF16 0,006 1 2 1 3413873 V A esogeno MEGF6 0,003 di tipo missenso 1 2 1 3416272 C T esogeno MEGF6 0,0072 silenzioso 2 4 1 3417122 V A intronico MEGF6 0,0038 2 4 1 3436219 V A intronico MEGF6 0,0046 2 4 1 12907456 A V esogeno HNRNPCL1,LOC649330 0,006 di tipo missenso 0 10 1 12907518 C A esogeno HNRNPCL1,LOC649330 1.0E-4 di tipo missenso 0 10 1 12908499 V C intronico HNRNPCL1 0,0031 0 8 1 12931660 V C intergenico PRAMEF2,PRAMEF4 0,004 1 2 1 12937721 V T intergenico PRAMEF2,PRAMEF4 0,0038 0 2 1 12940827 V T intronico PRAMEF4 0,007 2 4 1 12942759 T V intronico PRAMEF4 0,0076 0 10 1 12942805 T V intronico PRAMEF4 0,0061 0 12 1 12942812 V A intronico PRAMEF4 0,0061 0 12 1 12942875 A V intronico PRAMEF4 0,0068 0 6 1 12942912 V C intronico PRAMEF4 2,0E-4 0 2 1 12942937 A T esogeno PRAMEF4 0,0029 di tipo missenso 0 2 1 12942940 T V esogeno PRAMEF4 0,0038 di tipo missenso 0 2 1 12943940 T C intronico PRAMEF4 0,0015 0 12 1 12944138 A V intronico PRAMEF4 8,0E-4 0 12 1 12944234 V A intronico PRAMEF4 0,0015 0 12 1 12944589 T V intronico PRAMEF4 0,003 0 4 1 12944845 A C intronico PRAMEF4 0,0014 0 6 1 12946439 T C upstream PRAMEF4 0,0029 0 10 1 12946833 V A upstream PRAMEF4 0,001 0 8 1 12946835 T A upstream PRAMEF4 0,004 0 12 1 12995204 V T intergenico PRAMEF8,PRAMEF6 0,003 1 4 1 12997638 T C downstream PRAMEF6,PRAMEF9 0,003 2 4 1 13007841 V C upstream PRAMEF6 0,0043 0 8 1 13019228 T A intergenico PRAMEF6,LOC391003 0,0015 0 10 1 13038503 V A UTR3 LOC391003 0,0072 1 2 1 13051650 C T intergenico LOC391003,PRAMEF5 0,002 2 4 1 15706063 V A intronico FHAD1 0,0029 1 2 1 15713292 C T intronico FHAD1 0,001 1 2 1 15766541 V C intronico CTRC 0,001 1 2 1 15782601 T C upstream CELA2A 0,0038 1 2 1 15828125 V A intronico CASP9 0,0014 2 4 1 15831037 V A intronico CASP9 0,0099 1 2 1 15840513 T V intronico CASP9 0,0043 2 4 1 15868742 V A intronico DNAJC16 0,001 1 2 1 15876704 V A intronico DNAJC16 0,001 1 2 1 15900342 C A intronico AGMAT 0,001 1 2 1 15906257 T C intronico AGMAT 8,0E-4 1 2 1 15911897 A V upstream AGMAT 0,0043 2 4 1 22764178 C T intergenico WNT4,ZBTB40 0,001 2 4 1 22791939 C T intronico ZBTB40 0,0089 2 4 1 22874394 C V intergenico ZBTB40,EPHA8 0,007 1 2 1 22875103 C V intergenico ZBTB40,EPHA8 0,007 1 2 1 22906403 C T intronico EPHA8 0,008 2 4 1 22912956 V A intronico EPHA8 0,001 1 2 1 22917007 C T intronico EPHA8 0,001 2 4 1 22927240 V A esogeno EPHA8 0,0013 di tipo missenso 2 4 1 22932265 V A intergenico EPHA8,MIR6127 0,0089 2 4 1 22944057 C T intergenico EPHA8,MIR6127 0,0089 2 4 1 22978799 A V upstream C1QB 0,0099 2 4 1 35170588 C T intergenico C1orf94,GJB5 0,01 1 2 1 35172426 C T intergenico C1orf94,GJB5 0,008 1 2 1 35172447 V A intergenico C1orf94,GJB5 0,001 1 2 1 35175302 C T intergenico C1orf94,GJB5 0,008 1 2 1 35177410 A T intergenico C1orf94,GJB5 0,001 1 2 1 35178768 C T intergenico C1orf94,GJB5 0,0014 2 4 1 35179362 V A intergenico C1orf94,GJB5 0,0014 2 4 1 35186166 V A intergenico C1orf94,GJB5 0,0099 2 4 1 35186520 A C intergenico C1orf94,GJB5 0,002 2 4 1 35196361 V A intergenico C1orf94,GJB5 0,0099 2 4 1 35223545 C T esogeno GJB5 0,001 silenzioso 1 2 1 35224029 V A UTR3 GJB5 0,003 1 2 1 35227895 T C UTR3 GJB4 5.0E-4 1 2 1 35230455 V T intergenico GJB4,GJB3 0,0043 1 2 1 35232954 T C intergenico GJB4,GJB3 0,003 1 2 1 35237986 V A intergenico GJB4,GJB3 0,0014 1 2 1 35245522 C T intergenico GJB4,GJB3 0,001 1 2 1 35256979 C T intergenico GJB3,GJA4 0,002 2 4 1 35263872 C T intergenico GJA4,SMIM12 5.0E-4 2 4 1 35323895 A C intronico SMIM12 0,0027 2 4 1 35369676 V A intronico DLGAP3 0,007 2 4 1 35371634 T A upstream DLGAP3 0,0015 0 4 1 39253519 V A intergenico LINC01343,RRAGC 0,005 1 2 1 39288829 V A intergenico LINC01343,RRAGC 0,0051 1 2 1 39289832 A C intergenico LINC01343,RRAGC 0,002 0 2 1 39312638 V A intronico RRAGC 0,0038 2 4 1 39361372 V A intronico RHBDL2 0,005 1 2 1 39363826 T V intronico RHBDL2 0,0029 1 2 1 39367555 T C intronico RHBDL2 0,007 2 4 1 39369531 T C intronico RHBDL2 0,001 2 4 1 39370202 T C intronico RHBDL2 0,01 1 2 1 39449101 A V intergenico RHBDL2,AKIRIN1 0,001 2 4 1 39475057 V A intergenico AKIRIN1,NDUFS5 0,01 1 2 1 39485016 C T intergenico AKIRIN1,NDUFS5 0,001 2 4 1 39488137 A V intergenico AKIRIN1,NDUFS5 0,001 2 4 1 39499212 A C intronico NDUFS5 0,001 0 2 1 39500605 C V downstream NDUFS5 0,002 0 10 1 46813814 T C intronico NSUN4 0,0014 1 2 1 46817258 A V intronico NSUN4 0,005 0 2 1 46843158 T C intergenico NSUN4,FAAH 0,001 1 2 1 46933509 A V intergenico LINC01398,DMBX1 0,002 1 2 1 46935021 V A intergenico LINC01398,DMBX1 0,004 2 4 1 46939253 T A intergenico LINC01398,DMBX1 0,004 2 4 1 46951788 C A intergenico LINC01398,DMBX1 0,002 2 4 1 46980864 V C downstream DMBX1 0,003 1 2 1 46989657 T C intergenico DMBX1,MKNK1-AS1 0,007 1 2 1 46994678 C T intergenico DMBX1,MKNK1-AS1 0,002 1 2 1 46999438 T C intergenico DMBX1,MKNK1-AS1 0,002 1 2 1 92761505 A V intronico GLMN 0,001 2 4 1 92764270 V C intronico GLMN 0,001 2 4 1 92802210 V A intronico RPAP2 0,0072 1 2 1 92820663 T A intronico RPAP2 0,0058 1 2 1 92820664 V T intronico RPAP2 0,0058 1 2 1 92820953 V A intronico RPAP2 0,007 2 4 1 92824766 A V intronico RPAP2 0,0058 1 2 1 92849183 C A intronico RPAP2 0,01 2 4 1 92850696 C V intronico RPAP2 0,0023 1 2 1 92861357 T C intergenico RPAP2,GFI1 0,01 2 4 1 92877460 C V intergenico RPAP2,GFI1 0,002 1 2 1 92880643 A V intergenico RPAP2,GFI1 0,001 2 4 1 92911540 V A intergenico RPAP2,GFI1 0,004 2 4 1 92911721 A C intergenico RPAP2,GFI1 0,0031 0 8 1 92918277 C T intergenico RPAP2,GFI1 0,001 2 4 1 92950920 V A intronico GFI1 0,008 2 4 1 92964788 V A intergenico GFI1,EVI5 0,0023 1 2 1 92977480 C T UTR3 EVI5 0,002 1 2 1 92985213 C T intronico EVI5 0,001 2 4 1 92988342 C T intronico EVI5 0,008 2 4 1 92992283 V A intronico EVI5 0,01 2 4 1 92999760 C T intronico EVI5 0,003 1 2 1 93005149 V C intronico EVI5 0,003 0 4 1 93018543 A T intronico EVI5 0,01 2 4 1 93033744 C T intronico EVI5 0,001 2 4 1 111400296 V A intergenico KCNA3,CD53 0,0014 2 4 1 111411924 C T intergenico KCNA3,CD53 0,003 1 2 1 111441850 C V UTR3 CD53 0,003 2 4 1 111451527 C T intergenico CD53,LRIF1 0,008 2 4 1 111454082 C A intergenico CD53,LRIF1 0,001 2 4 1 111466506 A V intergenico CD53,LRIF1 0,001 2 4 1 111525974 V A intergenico LRIF1,DRAM2 0,002 2 4 1 111574573 V T intergenico LRIF1,DRAM2 0,0072 2 4 1 111574594 T A intergenico LRIF1,DRAM2 0,005 1 2 1 111574647 V A intergenico LRIF1,DRAM2 0,005 1 2 1 111591746 T A intergenico LRIF1,DRAM2 0,005 1 2 1 111601459 A V intergenico LRIF1,DRAM2 0,005 1 2 1 111604748 V C intergenico LRIF1,DRAM2 0,005 1 2 1 112191526 T V intronico RAP1A 0,001 2 4 1 112206765 A V intronico RAP1A 0,0043 1 2 1 112226517 V A intronico RAP1A 0,001 0 2 1 112263324 V T intergenico RAP1A,FAM212B 0,003 2 4 1 112264843 V A UTR3 FAM212B 0,001 1 2 1 112285810 C T ncRNA_intronic FAM212B-AS1 0,004 1 2 1 112304285 T C intronico DDX20 0,0043 1 2 1 112307213 A C intronico DDX20 0,0043 1 2 1 112309436 V T esogeno DDX20 0.0 di tipo missenso 1 2 1 112317384 T C intergenico DDX20,KCND3 0,0014 1 2 1 112381367 C T intronico KCND3 0,002 1 2 1 112396571 V T ncRNA_exonic KCND3-IT1 0,001 1 2 1 113520038 V A intergenico SLC16A1-AS1,LOC100996251 0,0023 1 2 Una query simile che utilizza i dati della fase 3 del progetto 1000 Genomes richiede circa 90 secondi per l'esecuzione ed elabora circa 3,38 TB di dati.