Exécuter des intervalles JOIN avec BigQuery

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'intégralité des échantillons de génome

Ce guide présente trois exemples de requêtes. Chaque requête montre comment BigQuery s'adapte aux données génomiques de différentes tailles:

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 :

Interroger une table intégrée

L'exemple suivant utilise une table d'intervalle définie dans la requête, nommée intervals, et montre comment exécuter une requête JOIN avec une table contenant des variantes de Platinum Genome d'Illumina:

  1. Accédez à la page BigQuery de la console Google Cloud.

    Accéder à la page "BigQuery"

  2. Cliquez sur Saisir une requête.

  3. 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;

  4. 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 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 intron RRNAD1 0.002 2 4
    1 156705390 C T intron RRNAD1 8,0E-4 0 2
    1 156714207 T C intron HDGF 0.003 0 6
    1 156714440 A C intron 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 intron PRCC 0,001 2 4
    1 156744826 T G intron PRCC 0.002 0 8
    1 156779764 G A intron SH2D2A 0,001 2 4
    1 156783454 A C intron SH2D2A 0,0014 1 2
    1 156786144 C T intron NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T intron NTRK1 0.002 1 2
    1 156815332 A C intron INSRR,NTRK1 0.003 0 2
    1 156830778 G A exonique NTRK1 0,0067 sens erroné 2 4
    1 156842064 C T intron NTRK1 0,0014 1 2
    1 156843438 C A exonique NTRK1 0,0032 sens erroné 1 2
    1 156845773 C T intron NTRK1 0,001 2 4
    1 156873318 T C intron PEAR1 0,01 4 8
    1 156922740 G A intron ARHGEF11 0,007 1 2
    1 156930100 C T intron ARHGEF11 0,001 2 4
    2 113901230 G A intergénique IL1RN,PSD4 0,0082 1 2
    2 113953418 C A intron 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 intron PPARG 0,0014 1 2
    3 12353106 T C intron PPARG 0,001 2 4
    3 12403825 G A intron PPARG 0,0051 2 4
    3 12404394 G A intron PPARG 0,001 1 2
    3 12410289 G A intron PPARG 0.008 2 4
    3 12431381 C T intron PPARG 0,0061 2 4
    3 12447267 G A intron PPARG 0,0089 2 4
    3 12449379 C T intron PPARG 0,0092 2 4
    3 12450848 C A intron PPARG 0,0092 2 4
    3 12462847 T C intron 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 intron TSEN2 0,0092 2 4
    3 12531167 A G intron TSEN2 0,0099 2 4
    3 12557737 A G intron 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 intron FHIT 0,001 2 4
    3 59750635 A G intronic FHIT 0.003 1 2
    3 59757776 C T intron FHIT 0,001 2 4
    3 59770612 G A intron FHIT 0,001 2 4
    3 59804444 G C intron FHIT 0,001 2 4
    3 59819769 T C intron FHIT 0,001 2 4
    3 59884396 C T intron FHIT 0,001 2 4
    3 59960728 A C intron FHIT 0,01 1 2
    3 59970345 G A intron FHIT 0.002 1 2
    3 59972417 T A intron FHIT 0,0072 0 2
    3 60104328 C A intron FHIT 0,01 2 4
    3 60139062 G A intron FHIT 0,01 0 2
    3 60158066 C T intron FHIT 0,001 1 2
    3 60169285 C T intron FHIT 0,005 1 2
    3 60216185 T C intron FHIT 0.002 1 2
    3 60226380 G A intron FHIT 0,007 2 4
    3 60234539 C A intron FHIT 0.002 1 2
    3 60247464 A C intron FHIT 0,004 2 4
    3 60269926 A G intronic FHIT 0,007 2 4
    3 60271228 G T intron FHIT 0,007 2 4
    3 60286972 T C intron FHIT 0,001 2 4
    3 60301412 C G intronic FHIT 0,001 1 2
    3 60312251 C T intron 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 intron FHIT 0.006 1 2
    3 60400033 G A intron FHIT 0,004 2 4
    3 60435819 C T intron FHIT 0.006 2 4
    3 60435820 G T intron FHIT 0,004 1 2
    3 60441288 T C intron FHIT 0.006 2 4
    3 60444465 C A intron FHIT 0,01 1 2
    3 60444575 C T intron FHIT 0,001 1 2
    3 60450581 T C intron FHIT 0,01 1 2
    3 60456571 G A intron FHIT 0,001 2 4
    3 60473568 C G intronic FHIT 0,001 1 2
    3 60487557 T C intron FHIT 0,001 1 2
    3 60559705 A G intronic FHIT 0.002 2 4
    3 60570764 T C intron FHIT 0.008 2 4
    3 60582100 C T intron FHIT 0,001 1 2
    3 60587192 G A intron FHIT 0,004 1 2
    3 60599869 G A intron FHIT 0,0086 2 4
    3 60603091 C T intron FHIT 0,001 2 4
    3 60603250 A T intron FHIT 0,0099 1 2
    3 60609831 T G intronic FHIT 0,001 2 4
    3 60619756 G T intron FHIT 0,0015 2 4
    3 60680758 C T intron FHIT 0,0089 2 4
    3 60702243 G C intron FHIT 0,001 2 4
    3 60702532 A G intronic FHIT 0,001 1 2
    3 60714328 A T intron FHIT 0,004 1 2
    3 60725297 G A intron FHIT 0,001 1 2
    3 60726640 G A intron 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 intron FHIT 0,001 1 2
    3 60826546 C G intronic FHIT 0,0023 1 2
    3 60837392 C T intron FHIT 0,001 1 2
    3 60846310 A G intronic FHIT 0,01 0 2
    3 60850985 C T intron FHIT 0,004 1 2
    3 60852559 T C intron FHIT 0.008 1 2
    3 60871759 T C intron FHIT 0,004 1 2
    3 60884396 C T intron FHIT 0.002 2 4
    3 60897092 C A intron FHIT 0,001 2 4
    3 60940759 C T intron FHIT 0,0089 1 2
    3 60982595 A G intronic FHIT 0.003 2 4
    3 60999283 G A intron FHIT 0,001 1 2
    3 61042977 A G intronic FHIT 0,001 2 4
    3 61043349 T C intron FHIT 0,001 2 4
    3 61044789 A C intron FHIT 0,001 2 4
    3 61141621 G A intron FHIT 0.003 1 2
    3 61148655 G C intron FHIT 0,001 2 4
    3 61170747 C T intron FHIT 0.003 1 2
    3 61189473 C G intronic FHIT 0,0099 1 2
    3 61190425 C T intron FHIT 0,0023 2 4
    3 61193853 C T intron FHIT 0,0099 0 2
    3 61194793 C T intron FHIT 0,007 0 2
    3 61194840 A G intronic FHIT 0,0099 0 2
    3 61194886 T A intron FHIT 0,0099 0 2
    3 61201777 C T intron FHIT 0,001 2 4
    3 61202292 T C intron FHIT 0,007 1 2
    3 61232806 G C intron FHIT 0,0099 1 2
    3 61232910 C T intron FHIT 0,0099 1 2
    3 61235824 A T intron 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'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:

  1. Ouvrez la page BigQuery dans la console Google Cloud.

    Accéder à BigQuery

  2. Dans le panneau Explorer, sélectionnez le projet dans lequel vous souhaitez créer l'ensemble de données.

  3. Développez l'option Actions, puis cliquez sur Créer un ensemble de données.

  4. Sur la page Create dataset (Créer un ensemble de données), procédez comme suit :

    1. Dans le champ ID de l'ensemble de données, saisissez genomics.
    2. Laissez les autres paramètres par défaut.
    3. Cliquez sur Créer un ensemble de données.

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 depuis la table silver-wall-555:TuteTable.hg19.

  1. Pour créer la table d'intervalle, procédez comme suit:

    1. Ouvrez la page BigQuery dans la console Google Cloud.

      Accéder à la page "BigQuery"

    2. Cliquez sur Saisir une requête.

    3. 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 );

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

  2. 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;

  3. 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 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 intron RRNAD1 0.002 2 4
    1 156705390 C T intron RRNAD1 8,0E-4 0 2
    1 156714207 T C intron HDGF 0.003 0 6
    1 156714440 A C intron 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 intron PRCC 0,001 2 4
    1 156744826 T G intron PRCC 0.002 0 8
    1 156779764 G A intron SH2D2A 0,001 2 4
    1 156783454 A C intron SH2D2A 0,0014 1 2
    1 156786144 C T intron NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T intron NTRK1 0.002 1 2
    1 156815332 A C intron INSRR,NTRK1 0.003 0 2
    1 156830778 G A exonique NTRK1 0,0067 sens erroné 2 4
    1 156842064 C T intron NTRK1 0,0014 1 2
    1 156843438 C A exonique NTRK1 0,0032 sens erroné 1 2
    1 156845773 C T intron NTRK1 0,001 2 4
    1 156873318 T C intron PEAR1 0,01 4 8
    1 156922740 G A intron ARHGEF11 0,007 1 2
    1 156930100 C T intron ARHGEF11 0,001 2 4
    2 113901230 G A intergénique IL1RN,PSD4 0,0082 1 2
    2 113953418 C A intron 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 intron PPARG 0,0014 1 2
    3 12353106 T C intron PPARG 0,001 2 4
    3 12403825 G A intron PPARG 0,0051 2 4
    3 12404394 G A intron PPARG 0,001 1 2
    3 12410289 G A intron PPARG 0.008 2 4
    3 12431381 C T intron PPARG 0,0061 2 4
    3 12447267 G A intron PPARG 0,0089 2 4
    3 12449379 C T intron PPARG 0,0092 2 4
    3 12450848 C A intron PPARG 0,0092 2 4
    3 12462847 T C intron 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 intron TSEN2 0,0092 2 4
    3 12531167 A G intron TSEN2 0,0099 2 4
    3 12557737 A G intron 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 intron FHIT 0,001 2 4
    3 59750635 A G intronic FHIT 0.003 1 2
    3 59757776 C T intron FHIT 0,001 2 4
    3 59770612 G A intron FHIT 0,001 2 4
    3 59804444 G C intron FHIT 0,001 2 4
    3 59819769 T C intron FHIT 0,001 2 4
    3 59884396 C T intron FHIT 0,001 2 4
    3 59960728 A C intron FHIT 0,01 1 2
    3 59970345 G A intron FHIT 0.002 1 2
    3 59972417 T A intron FHIT 0,0072 0 2
    3 60104328 C A intron FHIT 0,01 2 4
    3 60139062 G A intron FHIT 0,01 0 2
    3 60158066 C T intron FHIT 0,001 1 2
    3 60169285 C T intron FHIT 0,005 1 2
    3 60216185 T C intron FHIT 0.002 1 2
    3 60226380 G A intron FHIT 0,007 2 4
    3 60234539 C A intron FHIT 0.002 1 2
    3 60247464 A C intron FHIT 0,004 2 4
    3 60269926 A G intronic FHIT 0,007 2 4
    3 60271228 G T intron FHIT 0,007 2 4
    3 60286972 T C intron FHIT 0,001 2 4
    3 60301412 C G intronic FHIT 0,001 1 2
    3 60312251 C T intron 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 intron FHIT 0.006 1 2
    3 60400033 G A intron FHIT 0,004 2 4
    3 60435819 C T intron FHIT 0.006 2 4
    3 60435820 G T intron FHIT 0,004 1 2
    3 60441288 T C intron FHIT 0.006 2 4
    3 60444465 C A intron FHIT 0,01 1 2
    3 60444575 C T intron FHIT 0,001 1 2
    3 60450581 T C intron FHIT 0,01 1 2
    3 60456571 G A intron FHIT 0,001 2 4
    3 60473568 C G intronic FHIT 0,001 1 2
    3 60487557 T C intron FHIT 0,001 1 2
    3 60559705 A G intronic FHIT 0.002 2 4
    3 60570764 T C intron FHIT 0.008 2 4
    3 60582100 C T intron FHIT 0,001 1 2
    3 60587192 G A intron FHIT 0,004 1 2
    3 60599869 G A intron FHIT 0,0086 2 4
    3 60603091 C T intron FHIT 0,001 2 4
    3 60603250 A T intron FHIT 0,0099 1 2
    3 60609831 T G intronic FHIT 0,001 2 4
    3 60619756 G T intron FHIT 0,0015 2 4
    3 60680758 C T intron FHIT 0,0089 2 4
    3 60702243 G C intron FHIT 0,001 2 4
    3 60702532 A G intronic FHIT 0,001 1 2
    3 60714328 A T intron FHIT 0,004 1 2
    3 60725297 G A intron FHIT 0,001 1 2
    3 60726640 G A intron 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 intron FHIT 0,001 1 2
    3 60826546 C G intronic FHIT 0,0023 1 2
    3 60837392 C T intron FHIT 0,001 1 2
    3 60846310 A G intronic FHIT 0,01 0 2
    3 60850985 C T intron FHIT 0,004 1 2
    3 60852559 T C intron FHIT 0.008 1 2
    3 60871759 T C intron FHIT 0,004 1 2
    3 60884396 C T intron FHIT 0.002 2 4
    3 60897092 C A intron FHIT 0,001 2 4
    3 60940759 C T intron FHIT 0,0089 1 2
    3 60982595 A G intronic FHIT 0.003 2 4
    3 60999283 G A intron FHIT 0,001 1 2
    3 61042977 A G intronic FHIT 0,001 2 4
    3 61043349 T C intron FHIT 0,001 2 4
    3 61044789 A C intron FHIT 0,001 2 4
    3 61141621 G A intron FHIT 0.003 1 2
    3 61148655 G C intron FHIT 0,001 2 4
    3 61170747 C T intron FHIT 0.003 1 2
    3 61189473 C G intronic FHIT 0,0099 1 2
    3 61190425 C T intron FHIT 0,0023 2 4
    3 61193853 C T intron FHIT 0,0099 0 2
    3 61194793 C T intron FHIT 0,007 0 2
    3 61194840 A G intronic FHIT 0,0099 0 2
    3 61194886 T A intron FHIT 0,0099 0 2
    3 61201777 C T intron FHIT 0,001 2 4
    3 61202292 T C intron FHIT 0,007 1 2
    3 61232806 G C intron FHIT 0,0099 1 2
    3 61232910 C T intron FHIT 0,0099 1 2
    3 61235824 A T intron 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.

  1. Pour créer la table d'intervalle, procédez comme suit:

    1. Ouvrez la page BigQuery dans la console Google Cloud.

      Accéder à la page "BigQuery"

    2. Cliquez sur Saisir une requête.

    3. 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 );
      1. 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.

  2. 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;

  3. 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 intron PRDM16 0,0098 1 2
    1 3011333 G T intron PRDM16 0,004 1 2
    1 3019659 C T intron PRDM16 0,0031 1 2
    1 3036896 G A intron PRDM16 0,001 1 2
    1 3037388 G A intron PRDM16 0.002 2 4
    1 3041250 T G intronic PRDM16 0.006 2 4
    1 3042502 A T intron PRDM16 0.003 4 6
    1 3053713 A C intron PRDM16 0.002 1 2
    1 3063109 C T intron PRDM16 0.002 0 2
    1 3063593 T C intron PRDM16 0.003 1 2
    1 3076439 C T intron PRDM16 0,001 2 4
    1 3078960 G A intron PRDM16 0,007 2 4
    1 3084268 A C intron PRDM16 0,005 0 2
    1 3084492 T C intron PRDM16 0,0015 0 2
    1 3084786 T C intron PRDM16 0,0015 0 4
    1 3111119 G A intron PRDM16 0.003 1 2
    1 3111643 C T intron PRDM16 0,0041 1 2
    1 3114807 G A intron PRDM16 0,0041 1 2
    1 3165530 C T intron PRDM16 0,0089 1 2
    1 3169325 G A intron PRDM16 0.008 2 4
    1 3179623 C T intron PRDM16 0.003 2 4
    1 3181097 C T intron PRDM16 0,001 2 4
    1 3194000 G C intron PRDM16 0,005 2 4
    1 3195769 T C intron PRDM16 0.002 1 2
    1 3197351 C T intron PRDM16 0,0061 1 2
    1 3224100 C A intron PRDM16 0.003 2 4
    1 3228644 G T intron PRDM16 0,001 2 4
    1 3234045 G A intron PRDM16 0.002 1 2
    1 3235971 G A intron PRDM16 0,0089 1 2
    1 3274115 C T intron PRDM16 0,001 2 4
    1 3291388 G A intron PRDM16 0.002 2 4
    1 3295658 A C intron PRDM16 0,0068 0 6
    1 3295937 A C intron PRDM16 0,0068 0 2
    1 3296205 T C intron PRDM16 0,0083 0 2
    1 3315690 G A intron PRDM16 0,001 2 4
    1 3329212 G A exonique PRDM16 0,0031 sens erroné 1 2
    1 3331787 C T intron PRDM16 0,0099 1 2
    1 3370316 G C en amont ARHGEF16 0,001 2 4
    1 3379560 A G intron ARHGEF16 0,0051 0 6
    1 3391174 C T intron ARHGEF16 0.006 1 2
    1 3413873 G A exonique MEGF6 0.003 sens erroné 1 2
    1 3416272 C T exonique MEGF6 0,0072 silencieux 2 4
    1 3417122 G A intronic MEGF6 0,0038 2 4
    1 3436219 G A intronic MEGF6 0,0046 2 4
    1 12907456 A G exonique HNRNPCL1,LOC649330 0.006 sens erroné 0 10
    1 12907518 C A exonique HNRNPCL1,LOC649330 1,0E-4 sens erroné 0 10
    1 12908499 G C intron 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 intron PRAMEF4 0,007 2 4
    1 12942759 T G intron PRAMEF4 0,0076 0 10
    1 12942805 T G intron PRAMEF4 0,0061 0 12
    1 12942812 G A intron PRAMEF4 0,0061 0 12
    1 12942875 A G intron PRAMEF4 0,0068 0 6
    1 12942912 G C intron PRAMEF4 2,0E-4 0 2
    1 12942937 A T exonique PRAMEF4 0,0029 sens erroné 0 2
    1 12942940 T G exonique PRAMEF4 0,0038 sens erroné 0 2
    1 12943940 T C intron PRAMEF4 0,0015 0 12
    1 12944138 A G intron PRAMEF4 8,0E-4 0 12
    1 12944234 G A intron PRAMEF4 0,0015 0 12
    1 12944589 T G intron PRAMEF4 0.003 0 4
    1 12944845 A C intron 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 intron FHAD1 0,0029 1 2
    1 15713292 C T intron FHAD1 0,001 1 2
    1 15766541 G C intron CTRC 0,001 1 2
    1 15782601 T C en amont CELA2A 0,0038 1 2
    1 15828125 G A intron CASP9 0,0014 2 4
    1 15831037 G A intron CASP9 0,0099 1 2
    1 15840513 T G intron CASP9 0,0043 2 4
    1 15868742 G A intron DNAJC16 0,001 1 2
    1 15876704 G A intron DNAJC16 0,001 1 2
    1 15900342 C A intron AGMAT 0,001 1 2
    1 15906257 T C intron 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 intron 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 intron EPHA8 0.008 2 4
    1 22912956 G A intron EPHA8 0,001 1 2
    1 22917007 C T intron EPHA8 0,001 2 4
    1 22927240 G A exonique EPHA8 0,0013 sens erroné 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 exonique 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 intron SMIM12 0,0027 2 4
    1 35369676 G A intron 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 intron RRAGC 0,0038 2 4
    1 39361372 G A intron RHBDL2 0,005 1 2
    1 39363826 T G intron RHBDL2 0,0029 1 2
    1 39367555 T C intron RHBDL2 0,007 2 4
    1 39369531 T C intron RHBDL2 0,001 2 4
    1 39370202 T C intron 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 intron NSUN4 0,0014 1 2
    1 46817258 A G intronic 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 intronic GLMN 0,001 2 4
    1 92764270 G C intron 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 intron 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 intronic EVI5 0,001 2 4
    1 92988342 C T intronic EVI5 0.008 2 4
    1 92992283 G A intron EVI5 0,01 2 4
    1 92999760 C T intronic EVI5 0.003 1 2
    1 93005149 G C intronic EVI5 0.003 0 4
    1 93018543 A T intronic EVI5 0,01 2 4
    1 93033744 C T intronic 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 intron RAP1A 0,001 2 4
    1 112206765 A G intron RAP1A 0,0043 1 2
    1 112226517 G A intron 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 intron DDX20 0,0043 1 2
    1 112307213 A C intron DDX20 0,0043 1 2
    1 112309436 G T exonique DDX20 0,0 sens erroné 1 2
    1 112317384 T C intergénique DDX20,KCND3 0,0014 1 2
    1 112381367 C T intron 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.