Vous pouvez utiliser BigQuery pour exécuter une requête JOIN sur des variantes avec des données décrites par des intervalles de régions génomiques ou des chevauchements. Cette page explique comment utiliser une requête JOIN complexe pour effectuer les opérations suivantes à partir d'une liste de noms de gènes:
- Trouver les SNP rares qui chevauchent les gènes.
- Trouver 100 000 paires de base de chaque côté d'un gène pour l'ensemble des échantillons de génome.
Ce guide présente des exemples pour trois requêtes. Chaque requête montre comment BigQuery s'adapte à différentes tailles de données génomiques:
- Interroger une table intégrée
- Interroger une table matérialisée avec des gènes spécifiques
- Interroger une table matérialisée avec 250 gènes aléatoires.
Les données proviennent de la table Tute Genomics Annotation, composée de près de neuf milliards de lignes, et de l'ensemble de données Platinum Genome d'Illumina. Si vous n'êtes pas certain de connaître ces ensembles de données, référez-vous aux ressources suivantes :
- L'article de blog Google Cloud sur l'exploration des variations génétiques avec Google Genomics et Tute
- La partie Platinum Genome d'Illumina de la section sur les ensembles de données publics
Interroger une table intégrée
L'exemple suivant utilise une table d'intervalle définie dans la requête, appelée intervals, et montre comment exécuter une requête JOIN avec une table contenant des variantes de Platinum Genome d'Illumina:
Accédez à la page BigQuery dans la console Google Cloud.
Cliquez sur Saisir une requête.
Dans le champ Nouvelle requête, exécutez la requête suivante:
#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;Cliquez sur Exécuter la requête. L'exécution de la requête prend une dizaine de secondes et traite environ 334 Go de données. Les résultats de la requête identifient des variantes rares dans la cohorte qui chevauchent les régions d'intérêt.
Développez la section suivante pour afficher les résultats de la requête:
Résultats de la requête
Chr Début Ref Autre suggestion Func Gêne PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 156699757 T C intronique RRNAD1 0.002 2 4 1 156705390 C T intronique RRNAD1 8.0E-4 0 2 1 156714207 T C intronique HDGF 0.003 0 6 1 156714440 A C intronique HDGF 0,0068 0 12 1 156723870 C T intergénique HDGF,PRCC 0,006 1 2 1 156724456 C T intergénique HDGF,PRCC 0.002 2 4 1 156733988 C T intergénique HDGF,PRCC 0,001 1 2 1 156742258 T G intronique PRCC 0,001 2 4 1 156744826 T G intronique PRCC 0.002 0 8 1 156779764 G A intronique SH2D2A 0,001 2 4 1 156783454 A C intronique SH2D2A 0,0014 1 2 1 156786144 C T intronique NTRK1,SH2D2A 0,0031 2 4 1 156790510 A T intronique NTRK1 0.002 1 2 1 156815332 A C intronique INSRR,NTRK1 0.003 0 2 1 156830778 G A Exonic NTRK1 0,0067 Missense 2 4 1 156842064 C T intronique NTRK1 0,0014 1 2 1 156843438 C A Exonic NTRK1 0,0032 Missense 1 2 1 156845773 C T intronique NTRK1 0,001 2 4 1 156873318 T C intronique PEAR1 0,01 4 8 1 156922740 G A intronique ARHGEF11 0,007 1 2 1 156930100 C T intronique ARHGEF11 0,001 2 4 2 113901230 G A intergénique IL1RN,PSD4 0,0082 1 2 2 113953418 C A intronique PSD4 0,001 2 4 2 113967621 G C intergénique PSD4,PAX8 0.002 0 6 2 113967624 T C intergénique PSD4,PAX8 0.002 0 2 2 113980967 G A intronique 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 intergénique PAX8,CBWD2 0,001 2 4 2 114084018 A C intergénique PAX8,CBWD2 0,0045 0 4 2 114099037 G A intergénique PAX8,CBWD2 0,0051 1 2 2 114105670 A T intergénique PAX8,CBWD2 0,001 1 2 2 114111325 G T intergénique PAX8,CBWD2 0,001 1 2 3 12265797 C T intergénique SYN2,PPARG 0,0089 2 4 3 12277958 A G intergénique SYN2,PPARG 0.002 1 2 3 12296019 G A intergénique SYN2,PPARG 0.002 2 4 3 12316549 G C intergénique SYN2,PPARG 0.002 1 2 3 12335681 T G intronique PPARG 0,0092 2 4 3 12348795 T C intronique PPARG 0,0014 1 2 3 12353106 T C intronique PPARG 0,001 2 4 3 12403825 G A intronique PPARG 0,0051 2 4 3 12404394 G A intronique PPARG 0,001 1 2 3 12410289 G A intronique PPARG 0.008 2 4 3 12431381 C T intronique PPARG 0,0061 2 4 3 12447267 G A intronique PPARG 0,0089 2 4 3 12449379 C T intronique PPARG 0,0092 2 4 3 12450848 C A intronique PPARG 0,0092 2 4 3 12462847 T C intronique PPARG 0.002 1 2 3 12492797 G A intergénique PPARG,TSEN2 0,01 1 2 3 12503201 G A intergénique PPARG,TSEN2 0,0099 2 4 3 12530460 A G intronique TSEN2 0,0092 2 4 3 12531167 A G intronique TSEN2 0,0099 2 4 3 12557737 A G intronique TSEN2 0,001 2 4 3 59636143 A G intergénique C3orf67,FHIT 0.003 3 6 3 59645934 A C intergénique C3orf67,FHIT 0,004 1 2 3 59646893 G A intergénique C3orf67,FHIT 0.002 1 2 3 59697024 A G intergénique C3orf67,FHIT 0,0072 1 2 3 59701013 G A intergénique C3orf67,FHIT 0,004 2 4 3 59733945 A G intergénique C3orf67,FHIT 0,001 2 4 3 59747482 C T intronique FHIT 0,001 2 4 3 59750635 A G intronique FHIT 0.003 1 2 3 59757776 C T intronique FHIT 0,001 2 4 3 59770612 G A intronique FHIT 0,001 2 4 3 59804444 G C intronique FHIT 0,001 2 4 3 59819769 T C intronique FHIT 0,001 2 4 3 59884396 C T intronique FHIT 0,001 2 4 3 59960728 A C intronique FHIT 0,01 1 2 3 59970345 G A intronique FHIT 0.002 1 2 3 59972417 T A intronique FHIT 0,0072 0 2 3 60104328 C A intronique FHIT 0,01 2 4 3 60139062 G A intronique FHIT 0,01 0 2 3 60158066 C T intronique FHIT 0,001 1 2 3 60169285 C T intronique FHIT 0,005 1 2 3 60216185 T C intronique FHIT 0.002 1 2 3 60226380 G A intronique FHIT 0,007 2 4 3 60234539 C A intronique FHIT 0.002 1 2 3 60247464 A C intronique FHIT 0,004 2 4 3 60269926 A G intronique FHIT 0,007 2 4 3 60271228 G T intronique FHIT 0,007 2 4 3 60286972 T C intronique FHIT 0,001 2 4 3 60301412 C G intronique FHIT 0,001 1 2 3 60312251 C T intronique FHIT 0,0099 1 2 3 60317682 A G intronique FHIT 0.008 1 2 3 60328557 C G intronique FHIT 0,0043 2 4 3 60342562 C T intronique FHIT 0,006 1 2 3 60400033 G A intronique FHIT 0,004 2 4 3 60435819 C T intronique FHIT 0,006 2 4 3 60435820 G T intronique FHIT 0,004 1 2 3 60441288 T C intronique FHIT 0,006 2 4 3 60444465 C A intronique FHIT 0,01 1 2 3 60444575 C T intronique FHIT 0,001 1 2 3 60450581 T C intronique FHIT 0,01 1 2 3 60456571 G A intronique FHIT 0,001 2 4 3 60473568 C G intronique FHIT 0,001 1 2 3 60487557 T C intronique FHIT 0,001 1 2 3 60559705 A G intronique FHIT 0.002 2 4 3 60570764 T C intronique FHIT 0.008 2 4 3 60582100 C T intronique FHIT 0,001 1 2 3 60587192 G A intronique FHIT 0,004 1 2 3 60599869 G A intronique FHIT 0,0086 2 4 3 60603091 C T intronique FHIT 0,001 2 4 3 60603250 A T intronique FHIT 0,0099 1 2 3 60609831 T G intronique FHIT 0,001 2 4 3 60619756 G T intronique FHIT 0,0015 2 4 3 60680758 C T intronique FHIT 0,0089 2 4 3 60702243 G C intronique FHIT 0,001 2 4 3 60702532 A G intronique FHIT 0,001 1 2 3 60714328 A T intronique FHIT 0,004 1 2 3 60725297 G A intronique FHIT 0,001 1 2 3 60726640 G A intronique FHIT 0,01 2 4 3 60795144 A G intronique FHIT 0,001 2 4 3 60807171 A G intronique FHIT 0,001 1 2 3 60813868 T C intronique FHIT 0,001 1 2 3 60826546 C G intronique FHIT 0,0023 1 2 3 60837392 C T intronique FHIT 0,001 1 2 3 60846310 A G intronique FHIT 0,01 0 2 3 60850985 C T intronique FHIT 0,004 1 2 3 60852559 T C intronique FHIT 0.008 1 2 3 60871759 T C intronique FHIT 0,004 1 2 3 60884396 C T intronique FHIT 0.002 2 4 3 60897092 C A intronique FHIT 0,001 2 4 3 60940759 C T intronique FHIT 0,0089 1 2 3 60982595 A G intronique FHIT 0.003 2 4 3 60999283 G A intronique FHIT 0,001 1 2 3 61042977 A G intronique FHIT 0,001 2 4 3 61043349 T C intronique FHIT 0,001 2 4 3 61044789 A C intronique FHIT 0,001 2 4 3 61141621 G A intronique FHIT 0.003 1 2 3 61148655 G C intronique FHIT 0,001 2 4 3 61170747 C T intronique FHIT 0.003 1 2 3 61189473 C G intronique FHIT 0,0099 1 2 3 61190425 C T intronique FHIT 0,0023 2 4 3 61193853 C T intronique FHIT 0,0099 0 2 3 61194793 C T intronique FHIT 0,007 0 2 3 61194840 A G intronique FHIT 0,0099 0 2 3 61194886 T A intronique FHIT 0,0099 0 2 3 61201777 C T intronique FHIT 0,001 2 4 3 61202292 T C intronique FHIT 0,007 1 2 3 61232806 G C intronique FHIT 0,0099 1 2 3 61232910 C T intronique FHIT 0,0099 1 2 3 61235824 A T intronique FHIT 0,001 2 4 3 61283810 A C intergénique FHIT,PTPRG 0,0089 1 2 3 61293731 T A intergénique FHIT,PTPRG 0,0089 2 4 3 61296730 C T intergénique FHIT,PTPRG 0,001 1 2 3 61326341 C T intergénique FHIT,PTPRG 0,004 2 4 3 61326620 T C intergénique FHIT,PTPRG 0,01 1 2 3 61327649 G C intergénique FHIT,PTPRG 0,001 2 4 3 61330545 G C intergénique FHIT,PTPRG 0,001 2 4 3 61335803 G A intergénique FHIT,PTPRG 0,001 2 4 L'exécution d'une requête similaire utilisant des données de 1 000 génomes en Phase 3 prend environ 90 secondes, pendant lesquelles seront traités environ 3,38 To de données.
Utiliser une table matérialisée
Lorsque vous travaillez avec le big data à grande échelle, vous pouvez matérialiser une table d'intervalles et exécuter une requête JOIN sur la nouvelle table. Avant de passer au reste de cette section, créez un ensemble de données en procédant comme suit:
Ouvrez la page BigQuery dans la console Google Cloud.
Dans le panneau Explorer, sélectionnez le projet dans lequel vous souhaitez créer l'ensemble de données.
Développez l'option Actions, puis cliquez sur Créer un ensemble de données.
Sur la page Create dataset (Créer un ensemble de données), procédez comme suit :
- Dans le champ ID de l'ensemble de données, saisissez
genomics. - Ne modifiez aucun autre paramètre par défaut.
- Cliquez sur Créer l'ensemble de données.
- Dans le champ ID de l'ensemble de données, saisissez
Interroger une table matérialisée avec des gènes spécifiques
Les étapes suivantes montrent comment matérialiser une nouvelle table d'intervalle contenant une liste de gènes spécifiques de la table silver-wall-555:TuteTable.hg19.
Pour créer la table d'intervalles, procédez comme suit:
Ouvrez la page BigQuery dans la console Google Cloud.
Cliquez sur Saisir une requête.
Dans le champ Nouvelle requête, exécutez la requête suivante. La requête matérialise une partie de la table silver-wall-555:TuteTable.hg19 dans une nouvelle table d'intervalles
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 );Cliquez sur Exécuter la requête. La requête renvoie le résultat suivant :
This statement created a new table named PROJECT_ID:genomics.myIntervalTable.
Dans le champ Nouvelle requête, exécutez la requête suivante:
#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;Cliquez sur Exécuter la requête. L'exécution de la requête prend une dizaine de secondes et traite environ 334 Go de données. Les résultats de la requête identifient des variantes rares dans la cohorte qui chevauchent les régions d'intérêt.
Développez la section suivante pour afficher les résultats de la requête:
Résultats de la requête
Chr Début Ref Autre suggestion Func Gêne PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 156699757 T C intronique RRNAD1 0.002 2 4 1 156705390 C T intronique RRNAD1 8.0E-4 0 2 1 156714207 T C intronique HDGF 0.003 0 6 1 156714440 A C intronique HDGF 0,0068 0 12 1 156723870 C T intergénique HDGF,PRCC 0,006 1 2 1 156724456 C T intergénique HDGF,PRCC 0.002 2 4 1 156733988 C T intergénique HDGF,PRCC 0,001 1 2 1 156742258 T G intronique PRCC 0,001 2 4 1 156744826 T G intronique PRCC 0.002 0 8 1 156779764 G A intronique SH2D2A 0,001 2 4 1 156783454 A C intronique SH2D2A 0,0014 1 2 1 156786144 C T intronique NTRK1,SH2D2A 0,0031 2 4 1 156790510 A T intronique NTRK1 0.002 1 2 1 156815332 A C intronique INSRR,NTRK1 0.003 0 2 1 156830778 G A Exonic NTRK1 0,0067 Missense 2 4 1 156842064 C T intronique NTRK1 0,0014 1 2 1 156843438 C A Exonic NTRK1 0,0032 Missense 1 2 1 156845773 C T intronique NTRK1 0,001 2 4 1 156873318 T C intronique PEAR1 0,01 4 8 1 156922740 G A intronique ARHGEF11 0,007 1 2 1 156930100 C T intronique ARHGEF11 0,001 2 4 2 113901230 G A intergénique IL1RN,PSD4 0,0082 1 2 2 113953418 C A intronique PSD4 0,001 2 4 2 113967621 G C intergénique PSD4,PAX8 0.002 0 6 2 113967624 T C intergénique PSD4,PAX8 0.002 0 2 2 113980967 G A intronique 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 intergénique PAX8,CBWD2 0,001 2 4 2 114084018 A C intergénique PAX8,CBWD2 0,0045 0 4 2 114099037 G A intergénique PAX8,CBWD2 0,0051 1 2 2 114105670 A T intergénique PAX8,CBWD2 0,001 1 2 2 114111325 G T intergénique PAX8,CBWD2 0,001 1 2 3 12265797 C T intergénique SYN2,PPARG 0,0089 2 4 3 12277958 A G intergénique SYN2,PPARG 0.002 1 2 3 12296019 G A intergénique SYN2,PPARG 0.002 2 4 3 12316549 G C intergénique SYN2,PPARG 0.002 1 2 3 12335681 T G intronique PPARG 0,0092 2 4 3 12348795 T C intronique PPARG 0,0014 1 2 3 12353106 T C intronique PPARG 0,001 2 4 3 12403825 G A intronique PPARG 0,0051 2 4 3 12404394 G A intronique PPARG 0,001 1 2 3 12410289 G A intronique PPARG 0.008 2 4 3 12431381 C T intronique PPARG 0,0061 2 4 3 12447267 G A intronique PPARG 0,0089 2 4 3 12449379 C T intronique PPARG 0,0092 2 4 3 12450848 C A intronique PPARG 0,0092 2 4 3 12462847 T C intronique PPARG 0.002 1 2 3 12492797 G A intergénique PPARG,TSEN2 0,01 1 2 3 12503201 G A intergénique PPARG,TSEN2 0,0099 2 4 3 12530460 A G intronique TSEN2 0,0092 2 4 3 12531167 A G intronique TSEN2 0,0099 2 4 3 12557737 A G intronique TSEN2 0,001 2 4 3 59636143 A G intergénique C3orf67,FHIT 0.003 3 6 3 59645934 A C intergénique C3orf67,FHIT 0,004 1 2 3 59646893 G A intergénique C3orf67,FHIT 0.002 1 2 3 59697024 A G intergénique C3orf67,FHIT 0,0072 1 2 3 59701013 G A intergénique C3orf67,FHIT 0,004 2 4 3 59733945 A G intergénique C3orf67,FHIT 0,001 2 4 3 59747482 C T intronique FHIT 0,001 2 4 3 59750635 A G intronique FHIT 0.003 1 2 3 59757776 C T intronique FHIT 0,001 2 4 3 59770612 G A intronique FHIT 0,001 2 4 3 59804444 G C intronique FHIT 0,001 2 4 3 59819769 T C intronique FHIT 0,001 2 4 3 59884396 C T intronique FHIT 0,001 2 4 3 59960728 A C intronique FHIT 0,01 1 2 3 59970345 G A intronique FHIT 0.002 1 2 3 59972417 T A intronique FHIT 0,0072 0 2 3 60104328 C A intronique FHIT 0,01 2 4 3 60139062 G A intronique FHIT 0,01 0 2 3 60158066 C T intronique FHIT 0,001 1 2 3 60169285 C T intronique FHIT 0,005 1 2 3 60216185 T C intronique FHIT 0.002 1 2 3 60226380 G A intronique FHIT 0,007 2 4 3 60234539 C A intronique FHIT 0.002 1 2 3 60247464 A C intronique FHIT 0,004 2 4 3 60269926 A G intronique FHIT 0,007 2 4 3 60271228 G T intronique FHIT 0,007 2 4 3 60286972 T C intronique FHIT 0,001 2 4 3 60301412 C G intronique FHIT 0,001 1 2 3 60312251 C T intronique FHIT 0,0099 1 2 3 60317682 A G intronique FHIT 0.008 1 2 3 60328557 C G intronique FHIT 0,0043 2 4 3 60342562 C T intronique FHIT 0,006 1 2 3 60400033 G A intronique FHIT 0,004 2 4 3 60435819 C T intronique FHIT 0,006 2 4 3 60435820 G T intronique FHIT 0,004 1 2 3 60441288 T C intronique FHIT 0,006 2 4 3 60444465 C A intronique FHIT 0,01 1 2 3 60444575 C T intronique FHIT 0,001 1 2 3 60450581 T C intronique FHIT 0,01 1 2 3 60456571 G A intronique FHIT 0,001 2 4 3 60473568 C G intronique FHIT 0,001 1 2 3 60487557 T C intronique FHIT 0,001 1 2 3 60559705 A G intronique FHIT 0.002 2 4 3 60570764 T C intronique FHIT 0.008 2 4 3 60582100 C T intronique FHIT 0,001 1 2 3 60587192 G A intronique FHIT 0,004 1 2 3 60599869 G A intronique FHIT 0,0086 2 4 3 60603091 C T intronique FHIT 0,001 2 4 3 60603250 A T intronique FHIT 0,0099 1 2 3 60609831 T G intronique FHIT 0,001 2 4 3 60619756 G T intronique FHIT 0,0015 2 4 3 60680758 C T intronique FHIT 0,0089 2 4 3 60702243 G C intronique FHIT 0,001 2 4 3 60702532 A G intronique FHIT 0,001 1 2 3 60714328 A T intronique FHIT 0,004 1 2 3 60725297 G A intronique FHIT 0,001 1 2 3 60726640 G A intronique FHIT 0,01 2 4 3 60795144 A G intronique FHIT 0,001 2 4 3 60807171 A G intronique FHIT 0,001 1 2 3 60813868 T C intronique FHIT 0,001 1 2 3 60826546 C G intronique FHIT 0,0023 1 2 3 60837392 C T intronique FHIT 0,001 1 2 3 60846310 A G intronique FHIT 0,01 0 2 3 60850985 C T intronique FHIT 0,004 1 2 3 60852559 T C intronique FHIT 0.008 1 2 3 60871759 T C intronique FHIT 0,004 1 2 3 60884396 C T intronique FHIT 0.002 2 4 3 60897092 C A intronique FHIT 0,001 2 4 3 60940759 C T intronique FHIT 0,0089 1 2 3 60982595 A G intronique FHIT 0.003 2 4 3 60999283 G A intronique FHIT 0,001 1 2 3 61042977 A G intronique FHIT 0,001 2 4 3 61043349 T C intronique FHIT 0,001 2 4 3 61044789 A C intronique FHIT 0,001 2 4 3 61141621 G A intronique FHIT 0.003 1 2 3 61148655 G C intronique FHIT 0,001 2 4 3 61170747 C T intronique FHIT 0.003 1 2 3 61189473 C G intronique FHIT 0,0099 1 2 3 61190425 C T intronique FHIT 0,0023 2 4 3 61193853 C T intronique FHIT 0,0099 0 2 3 61194793 C T intronique FHIT 0,007 0 2 3 61194840 A G intronique FHIT 0,0099 0 2 3 61194886 T A intronique FHIT 0,0099 0 2 3 61201777 C T intronique FHIT 0,001 2 4 3 61202292 T C intronique FHIT 0,007 1 2 3 61232806 G C intronique FHIT 0,0099 1 2 3 61232910 C T intronique FHIT 0,0099 1 2 3 61235824 A T intronique FHIT 0,001 2 4 3 61283810 A C intergénique FHIT,PTPRG 0,0089 1 2 3 61293731 T A intergénique FHIT,PTPRG 0,0089 2 4 3 61296730 C T intergénique FHIT,PTPRG 0,001 1 2 3 61326341 C T intergénique FHIT,PTPRG 0,004 2 4 3 61326620 T C intergénique FHIT,PTPRG 0,01 1 2 3 61327649 G C intergénique FHIT,PTPRG 0,001 2 4 3 61330545 G C intergénique FHIT,PTPRG 0,001 2 4 3 61335803 G A intergénique FHIT,PTPRG 0,001 2 4 L'exécution d'une requête similaire utilisant des données de 1 000 génomes en Phase 3 prend environ 90 secondes, pendant lesquelles seront traités environ 3,38 To de données.
Interroger une table matérialisée avec 250 gènes aléatoires
L'exemple suivant montre comment exécuter un intervalle JOIN sur une table matérialisée contenant 250 gènes sélectionnés de manière aléatoire dans la table silver-wall-555:TuteTable.hg19.
Pour créer la table d'intervalles, procédez comme suit:
Ouvrez la page BigQuery dans la console Google Cloud.
Cliquez sur Saisir une requête.
Dans le champ Nouvelle requête, exécutez la requête suivante, qui matérialise une partie de la table silver-wall-555:TuteTable.hg19 vers une nouvelle table d'intervalles
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 );
- Cliquez sur Exécuter la requête. La requête renvoie le résultat suivant :
This statement created a new table named PROJECT_ID:genomics.randomGenesIntervalTable.
Dans le champ Nouvelle requête, exécutez la requête suivante:
#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;Cliquez sur Exécuter la requête. L'exécution de la requête prend une dizaine de secondes et traite environ 334 Go de données. Les résultats de la requête identifient des variantes rares dans la cohorte qui chevauchent les régions d'intérêt.
Développez la section suivante pour afficher les résultats tronqués de la requête:
Résultats de la requête
Chr Début Ref Autre suggestion Func Gêne PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 2925355 C A intergénique TTC34,ACTRT2 0,001 2 4 1 2933170 G A intergénique TTC34,ACTRT2 0,0083 0 4 1 2944477 G A intergénique ACTRT2,LINC00982 0.003 4 6 1 2967591 A T intergénique ACTRT2,LINC00982 0,0092 1 2 1 2975255 T C en aval LINC00982 0,0082 1 2 1 2977223 C T ncRNA_intronic LINC00982 0,0072 1 2 1 2978803 G C ncRNA_exonic LINC00982 0.002 4 6 1 3006466 G A intronique PRDM16 0,0098 1 2 1 3011333 G T intronique PRDM16 0,004 1 2 1 3019659 C T intronique PRDM16 0,0031 1 2 1 3036896 G A intronique PRDM16 0,001 1 2 1 3037388 G A intronique PRDM16 0.002 2 4 1 3041250 T G intronique PRDM16 0,006 2 4 1 3042502 A T intronique PRDM16 0.003 4 6 1 3053713 A C intronique PRDM16 0.002 1 2 1 3063109 C T intronique PRDM16 0.002 0 2 1 3063593 T C intronique PRDM16 0.003 1 2 1 3076439 C T intronique PRDM16 0,001 2 4 1 3078960 G A intronique PRDM16 0,007 2 4 1 3084268 A C intronique PRDM16 0,005 0 2 1 3084492 T C intronique PRDM16 0,0015 0 2 1 3084786 T C intronique PRDM16 0,0015 0 4 1 3111119 G A intronique PRDM16 0.003 1 2 1 3111643 C T intronique PRDM16 0,0041 1 2 1 3114807 G A intronique PRDM16 0,0041 1 2 1 3165530 C T intronique PRDM16 0,0089 1 2 1 3169325 G A intronique PRDM16 0.008 2 4 1 3179623 C T intronique PRDM16 0.003 2 4 1 3181097 C T intronique PRDM16 0,001 2 4 1 3194000 G C intronique PRDM16 0,005 2 4 1 3195769 T C intronique PRDM16 0.002 1 2 1 3197351 C T intronique PRDM16 0,0061 1 2 1 3224100 C A intronique PRDM16 0.003 2 4 1 3228644 G T intronique PRDM16 0,001 2 4 1 3234045 G A intronique PRDM16 0.002 1 2 1 3235971 G A intronique PRDM16 0,0089 1 2 1 3274115 C T intronique PRDM16 0,001 2 4 1 3291388 G A intronique PRDM16 0.002 2 4 1 3295658 A C intronique PRDM16 0,0068 0 6 1 3295937 A C intronique PRDM16 0,0068 0 2 1 3296205 T C intronique PRDM16 0,0083 0 2 1 3315690 G A intronique PRDM16 0,001 2 4 1 3329212 G A Exonic PRDM16 0,0031 Missense 1 2 1 3331787 C T intronique PRDM16 0,0099 1 2 1 3370316 G C en amont ARHGEF16 0,001 2 4 1 3379560 A G intronique ARHGEF16 0,0051 0 6 1 3391174 C T intronique ARHGEF16 0,006 1 2 1 3413873 G A Exonic MEGF6 0.003 Missense 1 2 1 3416272 C T Exonic MEGF6 0,0072 silencieux 2 4 1 3417122 G A intronique MEGF6 0,0038 2 4 1 3436219 G A intronique MEGF6 0,0046 2 4 1 12907456 A G Exonic HNRNPCL1,LOC649330 0,006 Missense 0 10 1 12907518 C A Exonic HNRNPCL1,LOC649330 1.0E-4 Missense 0 10 1 12908499 G C intronique HNRNPCL1 0,0031 0 8 1 12931660 G C intergénique PRAMEF2,PRAMEF4 0,004 1 2 1 12937721 G T intergénique PRAMEF2,PRAMEF4 0,0038 0 2 1 12940827 G T intronique PRAMEF4 0,007 2 4 1 12942759 T G intronique PRAMEF4 0,0076 0 10 1 12942805 T G intronique PRAMEF4 0,0061 0 12 1 12942812 G A intronique PRAMEF4 0,0061 0 12 1 12942875 A G intronique PRAMEF4 0,0068 0 6 1 12942912 G C intronique PRAMEF4 2.0E-4 0 2 1 12942937 A T Exonic PRAMEF4 0,0029 Missense 0 2 1 12942940 T G Exonic PRAMEF4 0,0038 Missense 0 2 1 12943940 T C intronique PRAMEF4 0,0015 0 12 1 12944138 A G intronique PRAMEF4 8.0E-4 0 12 1 12944234 G A intronique PRAMEF4 0,0015 0 12 1 12944589 T G intronique PRAMEF4 0.003 0 4 1 12944845 A C intronique PRAMEF4 0,0014 0 6 1 12946439 T C en amont PRAMEF4 0,0029 0 10 1 12946833 G A en amont PRAMEF4 0,001 0 8 1 12946835 T A en amont PRAMEF4 0,004 0 12 1 12995204 G T intergénique PRAMEF8,PRAMEF6 0.003 1 4 1 12997638 T C en aval PRAMEF6,PRAMEF9 0.003 2 4 1 13007841 G C en amont PRAMEF6 0,0043 0 8 1 13019228 T A intergénique PRAMEF6,LOC391003 0,0015 0 10 1 13038503 G A UTR3 LOC391003 0,0072 1 2 1 13051650 C T intergénique LOC391003,PRAMEF5 0.002 2 4 1 15706063 G A intronique FHAD1 0,0029 1 2 1 15713292 C T intronique FHAD1 0,001 1 2 1 15766541 G C intronique CTR 0,001 1 2 1 15782601 T C en amont CELA2A 0,0038 1 2 1 15828125 G A intronique CASP9 0,0014 2 4 1 15831037 G A intronique CASP9 0,0099 1 2 1 15840513 T G intronique CASP9 0,0043 2 4 1 15868742 G A intronique DNAJC16 0,001 1 2 1 15876704 G A intronique DNAJC16 0,001 1 2 1 15900342 C A intronique AGMAT (AGMAT) 0,001 1 2 1 15906257 T C intronique AGMAT (AGMAT) 8.0E-4 1 2 1 15911897 A G en amont AGMAT (AGMAT) 0,0043 2 4 1 22764178 C T intergénique WNT4,ZBTB40 0,001 2 4 1 22791939 C T intronique ZBTB40 0,0089 2 4 1 22874394 C G intergénique ZBTB40,EPHA8 0,007 1 2 1 22875103 C G intergénique ZBTB40,EPHA8 0,007 1 2 1 22906403 C T intronique EPHA8 0.008 2 4 1 22912956 G A intronique EPHA8 0,001 1 2 1 22917007 C T intronique EPHA8 0,001 2 4 1 22927240 G A Exonic EPHA8 0,0013 Missense 2 4 1 22932265 G A intergénique EPHA8,MIR6127 0,0089 2 4 1 22944057 C T intergénique EPHA8,MIR6127 0,0089 2 4 1 22978799 A G en amont C1QB 0,0099 2 4 1 35170588 C T intergénique C1orf94,GJB5 0,01 1 2 1 35172426 C T intergénique C1orf94,GJB5 0.008 1 2 1 35172447 G A intergénique C1orf94,GJB5 0,001 1 2 1 35175302 C T intergénique C1orf94,GJB5 0.008 1 2 1 35177410 A T intergénique C1orf94,GJB5 0,001 1 2 1 35178768 C T intergénique C1orf94,GJB5 0,0014 2 4 1 35179362 G A intergénique C1orf94,GJB5 0,0014 2 4 1 35186166 G A intergénique C1orf94,GJB5 0,0099 2 4 1 35186520 A C intergénique C1orf94,GJB5 0.002 2 4 1 35196361 G A intergénique C1orf94,GJB5 0,0099 2 4 1 35223545 C T Exonic GJB5 0,001 silencieux 1 2 1 35224029 G A UTR3 GJB5 0.003 1 2 1 35227895 T C UTR3 GJB4 5.0E-4 1 2 1 35230455 G T intergénique GJB4,GJB3 0,0043 1 2 1 35232954 T C intergénique GJB4,GJB3 0.003 1 2 1 35237986 G A intergénique GJB4,GJB3 0,0014 1 2 1 35245522 C T intergénique GJB4,GJB3 0,001 1 2 1 35256979 C T intergénique GJB3,GJA4 0.002 2 4 1 35263872 C T intergénique GJA4,SMIM12 5.0E-4 2 4 1 35323895 A C intronique SMIM12 0,0027 2 4 1 35369676 G A intronique DLGAP3 0,007 2 4 1 35371634 T A en amont DLGAP3 0,0015 0 4 1 39253519 G A intergénique LINC01343,RRAGC 0,005 1 2 1 39288829 G A intergénique LINC01343,RRAGC 0,0051 1 2 1 39289832 A C intergénique LINC01343,RRAGC 0.002 0 2 1 39312638 G A intronique RRAGC 0,0038 2 4 1 39361372 G A intronique RHBDL2 0,005 1 2 1 39363826 T G intronique RHBDL2 0,0029 1 2 1 39367555 T C intronique RHBDL2 0,007 2 4 1 39369531 T C intronique RHBDL2 0,001 2 4 1 39370202 T C intronique RHBDL2 0,01 1 2 1 39449101 A G intergénique RHBDL2,AKIRIN1 0,001 2 4 1 39475057 G A intergénique AKIRIN1,NDUFS5 0,01 1 2 1 39485016 C T intergénique AKIRIN1,NDUFS5 0,001 2 4 1 39488137 A G intergénique AKIRIN1,NDUFS5 0,001 2 4 1 39499212 A C intronique NDUFS5 0,001 0 2 1 39500605 C G en aval NDUFS5 0.002 0 10 1 46813814 T C intronique NSUN4 0,0014 1 2 1 46817258 A G intronique NSUN4 0,005 0 2 1 46843158 T C intergénique NSUN4,FAAH 0,001 1 2 1 46933509 A G intergénique LINC01398,DMBX1 0.002 1 2 1 46935021 G A intergénique LINC01398,DMBX1 0,004 2 4 1 46939253 T A intergénique LINC01398,DMBX1 0,004 2 4 1 46951788 C A intergénique LINC01398,DMBX1 0.002 2 4 1 46980864 G C en aval DMBX1 0.003 1 2 1 46989657 T C intergénique DMBX1,MKNK1-AS1 0,007 1 2 1 46994678 C T intergénique DMBX1,MKNK1-AS1 0.002 1 2 1 46999438 T C intergénique DMBX1,MKNK1-AS1 0.002 1 2 1 92761505 A G intronique GLMN 0,001 2 4 1 92764270 G C intronique GLMN 0,001 2 4 1 92802210 G A intronique RPAP2 0,0072 1 2 1 92820663 T A intronique RPAP2 0,0058 1 2 1 92820664 G T intronique RPAP2 0,0058 1 2 1 92820953 G A intronique RPAP2 0,007 2 4 1 92824766 A G intronique RPAP2 0,0058 1 2 1 92849183 C A intronique RPAP2 0,01 2 4 1 92850696 C G intronique RPAP2 0,0023 1 2 1 92861357 T C intergénique RPAP2,GFI1 0,01 2 4 1 92877460 C G intergénique RPAP2,GFI1 0.002 1 2 1 92880643 A G intergénique RPAP2,GFI1 0,001 2 4 1 92911540 G A intergénique RPAP2,GFI1 0,004 2 4 1 92911721 A C intergénique RPAP2,GFI1 0,0031 0 8 1 92918277 C T intergénique RPAP2,GFI1 0,001 2 4 1 92950920 G A intronique GFI1 0.008 2 4 1 92964788 G A intergénique GFI1,EVI5 0,0023 1 2 1 92977480 C T UTR3 EVI5 0.002 1 2 1 92985213 C T intronique EVI5 0,001 2 4 1 92988342 C T intronique EVI5 0.008 2 4 1 92992283 G A intronique EVI5 0,01 2 4 1 92999760 C T intronique EVI5 0.003 1 2 1 93005149 G C intronique EVI5 0.003 0 4 1 93018543 A T intronique EVI5 0,01 2 4 1 93033744 C T intronique EVI5 0,001 2 4 1 111400296 G A intergénique KCNA3,CD53 0,0014 2 4 1 111411924 C T intergénique KCNA3,CD53 0.003 1 2 1 111441850 C G UTR3 CD53 0.003 2 4 1 111451527 C T intergénique CD53,LRIF1 0.008 2 4 1 111454082 C A intergénique CD53,LRIF1 0,001 2 4 1 111466506 A G intergénique CD53,LRIF1 0,001 2 4 1 111525974 G A intergénique LRIF1,DRAM2 0.002 2 4 1 111574573 G T intergénique LRIF1,DRAM2 0,0072 2 4 1 111574594 T A intergénique LRIF1,DRAM2 0,005 1 2 1 111574647 G A intergénique LRIF1,DRAM2 0,005 1 2 1 111591746 T A intergénique LRIF1,DRAM2 0,005 1 2 1 111601459 A G intergénique LRIF1,DRAM2 0,005 1 2 1 111604748 G C intergénique LRIF1,DRAM2 0,005 1 2 1 112191526 T G intronique RAP1A 0,001 2 4 1 112206765 A G intronique RAP1A 0,0043 1 2 1 112226517 G A intronique RAP1A 0,001 0 2 1 112263324 G T intergénique RAP1A,FAM212B 0.003 2 4 1 112264843 G A UTR3 FAM212B 0,001 1 2 1 112285810 C T ncRNA_intronic FAM212B-AS1 0,004 1 2 1 112304285 T C intronique DDX20 0,0043 1 2 1 112307213 A C intronique DDX20 0,0043 1 2 1 112309436 G T Exonic DDX20 0,0 Missense 1 2 1 112317384 T C intergénique DDX20,KCND3 0,0014 1 2 1 112381367 C T intronique KCND3 0.002 1 2 1 112396571 G T ncRNA_exonic KCND3-IT1 0,001 1 2 1 113520038 G A intergénique SLC16A1-AS1,LOC100996251 0,0023 1 2 L'exécution d'une requête similaire utilisant des données de 1 000 génomes en Phase 3 prend environ 90 secondes, pendant lesquelles seront traités environ 3,38 To de données.