Cette page a été traduite par l'API Cloud Translation.
Switch to English

Exécuter des intervalles JOIN avec BigQuery

BigQuery vous permet d'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 :

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

Trois requêtes sont proposées. Chacune de ces requêtes 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 :

Exécuter l'intervalle JOIN à l'aide d'une table intégrée

Cet exemple 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 à l'interface utilisateur de BigQuery.

    Accéder à l'interface utilisateur de BigQuery

  2. Cliquez sur Saisir une requête.

  3. Dans le champ Nouvelle requête, saisissez 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.

    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

Travailler avec le big data à grande échelle vous permet de matérialiser une table d'intervalle et d'exécuter une requête JOIN sur la nouvelle table.

Exécuter l'intervalle JOIN avec des gènes spécifiques

L'exemple suivant montre 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. Accédez à l'interface utilisateur de BigQuery.

    Accéder à l'interface utilisateur de BigQuery

  2. Créez un ensemble de données de la manière suivante :

    1. Cliquez sur la flèche pointant vers le bas à côté de votre projet Google Cloud dans le volet de navigation, puis sélectionnez Créer un ensemble de données.

    2. Dans le champ Dataset ID (ID de l'ensemble de données), saisissez genomics.

    3. Laissez la valeur par défaut pour les autres paramètres et cliquez sur OK.

  3. Créez la table d'intervalle à l'aide des instructions ci-dessous :

    1. Dans BigQuery, cliquez sur Saisir une requête.

    2. Dans l'encadré Nouvelle requête, saisissez la requête suivante, qui 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 );
      
      1. Cliquez sur Exécuter la requête (Run Query).
  4. Dans le champ Nouvelle requête, saisissez 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;
    
  5. 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.

    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.

Exécuter l'intervalle JOIN avec des gènes aléatoires

L'exemple suivant montre comment exécuter l'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. Accédez à l'interface utilisateur de BigQuery.

    Accéder à l'interface utilisateur de BigQuery

  2. Créez un ensemble de données de la manière suivante :

    1. Cliquez sur la flèche pointant vers le bas à côté de votre projet Google Cloud dans le volet de navigation, puis sélectionnez Créer un ensemble de données.

    2. Dans le champ Dataset ID (ID de l'ensemble de données), saisissez genomics.

    3. Laissez la valeur par défaut pour les autres paramètres et cliquez sur OK.

  3. Créez la table d'intervalle à l'aide des instructions ci-dessous :

    1. Dans BigQuery, cliquez sur Saisir une requête.

    2. Dans l'encadré Nouvelle requête, saisissez 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 (Run Query).
  4. Dans le champ Nouvelle requête, saisissez 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;
    
  5. 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.

    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.