Vous pouvez utiliser BigQuery pour exécuter une requête JOIN sur des variantes à l'aide de données décrites par des intervalles de régions génomiques ou des chevauchements. Cette page montre comment utiliser une requête JOIN complexe pour effectuer les opérations suivantes à partir d'une liste de noms de gènes :
- Recherchez 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 aux données génomiques de différentes tailles:
- Interrogez 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 d'annotation Tute Genomics, qui contient près de 9 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
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émarrer Ref Alt 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 intronic HDGF 0.003 0 6 1 156714440 A C intronic 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 intronic SH2D2A 0,001 2 4 1 156783454 A C intronic 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 intronic INSRR,NTRK1 0.003 0 2 1 156830778 G A exonique NTRK1 0,0067 Missense 2 4 1 156842064 C T intronique NTRK1 0,0014 1 2 1 156843438 C A exonique NTRK1 0,0032 erreur de sens 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 intronic 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 intronic PPARG 0,0092 2 4 3 12348795 T C intronic PPARG 0,0014 1 2 3 12353106 T C intronic PPARG 0,001 2 4 3 12403825 G A intronic PPARG 0,0051 2 4 3 12404394 G A intronic PPARG 0,001 1 2 3 12410289 G A intronic PPARG 0.008 2 4 3 12431381 C T intronic PPARG 0,0061 2 4 3 12447267 G A intronic PPARG 0,0089 2 4 3 12449379 C T intronic PPARG 0,0092 2 4 3 12450848 C A intronic PPARG 0,0092 2 4 3 12462847 T C intronic 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 intronic 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 intronic FHIT 0,001 2 4 3 59819769 T C intronic FHIT 0,001 2 4 3 59884396 C T intronique FHIT 0,001 2 4 3 59960728 A C intronic 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 intronic 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 intronic FHIT 0,004 2 4 3 60269926 A G intronic FHIT 0,007 2 4 3 60271228 G T intronique FHIT 0,007 2 4 3 60286972 T C intronic FHIT 0,001 2 4 3 60301412 C G intronic FHIT 0,001 1 2 3 60312251 C T intronique FHIT 0,0099 1 2 3 60317682 A G intronic FHIT 0.008 1 2 3 60328557 C G intronic 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 intronic 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 intronic FHIT 0,01 1 2 3 60456571 G A intronique FHIT 0,001 2 4 3 60473568 C G intronic FHIT 0,001 1 2 3 60487557 T C intronic FHIT 0,001 1 2 3 60559705 A G intronic FHIT 0.002 2 4 3 60570764 T C intronic 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 intronic 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 intronic FHIT 0,001 2 4 3 60702532 A G intronic 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 intronic FHIT 0,001 2 4 3 60807171 A G intronic FHIT 0,001 1 2 3 60813868 T C intronic FHIT 0,001 1 2 3 60826546 C G intronic FHIT 0,0023 1 2 3 60837392 C T intronique FHIT 0,001 1 2 3 60846310 A G intronic FHIT 0,01 0 2 3 60850985 C T intronique FHIT 0,004 1 2 3 60852559 T C intronic FHIT 0.008 1 2 3 60871759 T C intronic 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 intronic FHIT 0.003 2 4 3 60999283 G A intronique FHIT 0,001 1 2 3 61042977 A G intronic FHIT 0,001 2 4 3 61043349 T C intronic FHIT 0,001 2 4 3 61044789 A C intronic FHIT 0,001 2 4 3 61141621 G A intronique FHIT 0.003 1 2 3 61148655 G C intronic FHIT 0,001 2 4 3 61170747 C T intronique FHIT 0.003 1 2 3 61189473 C G intronic 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 intronic 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 intronic FHIT 0,007 1 2 3 61232806 G C intronic 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 Une requête similaire utilisant des données provenant de 1 000 génomes en Phase 3 prend environ 90 secondes pour s'exécuter et traite 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'intervalle et exécuter une requête JOIN sur la nouvelle table. Avant de poursuivre le 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. - Laissez les autres paramètres par défaut.
- Cliquez sur Créer un 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 qui contient une liste de gènes spécifiques 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 vers 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émarrer Ref Alt 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 intronic HDGF 0.003 0 6 1 156714440 A C intronic 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 intronic SH2D2A 0,001 2 4 1 156783454 A C intronic 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 intronic INSRR,NTRK1 0.003 0 2 1 156830778 G A exonique NTRK1 0,0067 Missense 2 4 1 156842064 C T intronique NTRK1 0,0014 1 2 1 156843438 C A exonique NTRK1 0,0032 erreur de sens 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 intronic 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 intronic PPARG 0,0092 2 4 3 12348795 T C intronic PPARG 0,0014 1 2 3 12353106 T C intronic PPARG 0,001 2 4 3 12403825 G A intronic PPARG 0,0051 2 4 3 12404394 G A intronic PPARG 0,001 1 2 3 12410289 G A intronic PPARG 0.008 2 4 3 12431381 C T intronic PPARG 0,0061 2 4 3 12447267 G A intronic PPARG 0,0089 2 4 3 12449379 C T intronic PPARG 0,0092 2 4 3 12450848 C A intronic PPARG 0,0092 2 4 3 12462847 T C intronic 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 intronic 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 intronic FHIT 0,001 2 4 3 59819769 T C intronic FHIT 0,001 2 4 3 59884396 C T intronique FHIT 0,001 2 4 3 59960728 A C intronic 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 intronic 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 intronic FHIT 0,004 2 4 3 60269926 A G intronic FHIT 0,007 2 4 3 60271228 G T intronique FHIT 0,007 2 4 3 60286972 T C intronic FHIT 0,001 2 4 3 60301412 C G intronic FHIT 0,001 1 2 3 60312251 C T intronique FHIT 0,0099 1 2 3 60317682 A G intronic FHIT 0.008 1 2 3 60328557 C G intronic 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 intronic 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 intronic FHIT 0,01 1 2 3 60456571 G A intronique FHIT 0,001 2 4 3 60473568 C G intronic FHIT 0,001 1 2 3 60487557 T C intronic FHIT 0,001 1 2 3 60559705 A G intronic FHIT 0.002 2 4 3 60570764 T C intronic 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 intronic 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 intronic FHIT 0,001 2 4 3 60702532 A G intronic 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 intronic FHIT 0,001 2 4 3 60807171 A G intronic FHIT 0,001 1 2 3 60813868 T C intronic FHIT 0,001 1 2 3 60826546 C G intronic FHIT 0,0023 1 2 3 60837392 C T intronique FHIT 0,001 1 2 3 60846310 A G intronic FHIT 0,01 0 2 3 60850985 C T intronique FHIT 0,004 1 2 3 60852559 T C intronic FHIT 0.008 1 2 3 60871759 T C intronic 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 intronic FHIT 0.003 2 4 3 60999283 G A intronique FHIT 0,001 1 2 3 61042977 A G intronic FHIT 0,001 2 4 3 61043349 T C intronic FHIT 0,001 2 4 3 61044789 A C intronic FHIT 0,001 2 4 3 61141621 G A intronique FHIT 0.003 1 2 3 61148655 G C intronic FHIT 0,001 2 4 3 61170747 C T intronique FHIT 0.003 1 2 3 61189473 C G intronic 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 intronic 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 intronic FHIT 0,007 1 2 3 61232806 G C intronic 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 Une requête similaire utilisant des données provenant de 1 000 génomes en Phase 3 prend environ 90 secondes pour s'exécuter et traite 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 se matérialise de la table silver-wall-555:TuteTable.hg19 vers Table de l'intervalle
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 environ dix secondes, pendant lesquelles sont traités 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émarrer Ref Alt 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 intronic PRDM16 0,004 1 2 1 3019659 C T intronic 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 intronic PRDM16 0.003 4 6 1 3053713 A C intronic PRDM16 0.002 1 2 1 3063109 C T intronic PRDM16 0.002 0 2 1 3063593 T C intronic PRDM16 0.003 1 2 1 3076439 C T intronic PRDM16 0,001 2 4 1 3078960 G A intronique PRDM16 0,007 2 4 1 3084268 A C intronic PRDM16 0,005 0 2 1 3084492 T C intronic PRDM16 0,0015 0 2 1 3084786 T C intronic PRDM16 0,0015 0 4 1 3111119 G A intronique PRDM16 0.003 1 2 1 3111643 C T intronic PRDM16 0,0041 1 2 1 3114807 G A intronique PRDM16 0,0041 1 2 1 3165530 C T intronic PRDM16 0,0089 1 2 1 3169325 G A intronique PRDM16 0.008 2 4 1 3179623 C T intronic PRDM16 0.003 2 4 1 3181097 C T intronic PRDM16 0,001 2 4 1 3194000 G C intronic PRDM16 0,005 2 4 1 3195769 T C intronic PRDM16 0.002 1 2 1 3197351 C T intronic PRDM16 0,0061 1 2 1 3224100 C A intronique PRDM16 0.003 2 4 1 3228644 G T intronic 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 intronic PRDM16 0,001 2 4 1 3291388 G A intronique PRDM16 0.002 2 4 1 3295658 A C intronic PRDM16 0,0068 0 6 1 3295937 A C intronic PRDM16 0,0068 0 2 1 3296205 T C intronic PRDM16 0,0083 0 2 1 3315690 G A intronique PRDM16 0,001 2 4 1 3329212 G A Exonic PRDM16 0,0031 erreur de sens 1 2 1 3331787 C T intronic 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 intronic ARHGEF16 0.006 1 2 1 3413873 G A exonique MEGF6 0.003 erreur de sens 1 2 1 3416272 C T exonique 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 erreur de sens 0 10 1 12907518 C A Exonic HNRNPCL1,LOC649330 1,0E-4 Missense 0 10 1 12908499 G C intronic 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 intronic PRAMEF4 0,007 2 4 1 12942759 T G intronic PRAMEF4 0,0076 0 10 1 12942805 T G intronic PRAMEF4 0,0061 0 12 1 12942812 G A intronic PRAMEF4 0,0061 0 12 1 12942875 A G intronic PRAMEF4 0,0068 0 6 1 12942912 G C intronique PRAMEF4 2.0E-4 0 2 1 12942937 A T Exonic PRAMEF4 0,0029 erreur de sens 0 2 1 12942940 T G exonique PRAMEF4 0,0038 Missense 0 2 1 12943940 T C intronique PRAMEF4 0,0015 0 12 1 12944138 A G intronic PRAMEF4 8.0E-4 0 12 1 12944234 G A intronic PRAMEF4 0,0015 0 12 1 12944589 T G intronic 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 intronic FHAD1 0,0029 1 2 1 15713292 C T intronic FHAD1 0,001 1 2 1 15766541 G C intronique CTRC 0,001 1 2 1 15782601 T C en amont CELA2A 0,0038 1 2 1 15828125 G A intronic CASP9 0,0014 2 4 1 15831037 G A intronic CASP9 0,0099 1 2 1 15840513 T G intronique CASP9 0,0043 2 4 1 15868742 G A intronic DNAJC16 0,001 1 2 1 15876704 G A intronic DNAJC16 0,001 1 2 1 15900342 C A intronique AGMAT 0,001 1 2 1 15906257 T C intronic AGMAT 8.0E-4 1 2 1 15911897 A G en amont AGMAT 0,0043 2 4 1 22764178 C T intergénique WNT4,ZBTB40 0,001 2 4 1 22791939 C T intronic 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 intronic EPHA8 0.008 2 4 1 22912956 G A intronique EPHA8 0,001 1 2 1 22917007 C T intronic EPHA8 0,001 2 4 1 22927240 G A Exonic EPHA8 0,0013 erreur de sens 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 intronic 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 intronic 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 intronic NDUFS5 0,001 0 2 1 39500605 C G en aval NDUFS5 0.002 0 10 1 46813814 T C intronic 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 intronic RPAP2 0,0072 1 2 1 92820663 T A intronic RPAP2 0,0058 1 2 1 92820664 G T intronic RPAP2 0,0058 1 2 1 92820953 G A intronic RPAP2 0,007 2 4 1 92824766 A G intronic RPAP2 0,0058 1 2 1 92849183 C A intronic RPAP2 0,01 2 4 1 92850696 C G intronic 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 intronic 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 intronic 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 intronic 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 exonique DDX20 0,0 erreur de sens 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.