Esta página foi traduzida pela API Cloud Translation.
Switch to English

Como gerar JOINs de intervalo com o BigQuery

Com o BigQuery, execute uma consulta de JOIN em variantes com dados descritos por intervalos de região genômica ou sobreposições. Nesta página, você verá como usar uma consulta de JOIN complexa para receber uma lista de nomes de genes e fazer o seguinte:

  • Encontrar os SNPs raros que se sobrepõem aos genes
  • Encontrar 100.000 pares de bases em ambos os lados de um gene para todas as amostras do genoma

Há três consultas apresentadas, cada uma demonstrando como o BigQuery escalona em diferentes tamanhos de dados genômicos:

Os dados são provenientes da tabela de anotações Tute Genomics de quase 9 bilhões de linhas e do conjunto de dados Illumina Platinum Genomes. Se você não estiver familiarizado com esses conjuntos de dados, consulte os links a seguir:

Como executar o intervalo JOIN usando uma tabela em linha

Este exemplo usa uma tabela de intervalo definida na consulta, chamada intervals, e mostra como executar uma consulta de JOIN com uma tabela contendo variantes de Illumina Platinum Genomes:

  1. Acesse a IU do BigQuery.

    Acessar a IU do BigQuery

  2. Clique em Escrever consulta.

  3. Na caixa Nova consulta, insira a seguinte consulta:

    #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. Clique em Executar consulta. A consulta leva aproximadamente dez segundos para ser executada e processa cerca de 334 GB de dados. Os resultados da consulta identificam variantes raras dentro da coorte que se sobrepõem às regiões de interesse.

    Uma consulta semelhante usando dados de Fase 3 do 1000 Genomes leva cerca de 90 segundos para ser executada e processa cerca de 3,38 TB de dados.

Como usar uma tabela materializada

Ao trabalhar com Big Data em escala, materialize uma tabela de intervalo e execute uma consulta JOIN na nova tabela.

Como executar o intervalo JOIN com genes específicos

O exemplo a seguir mostra como materializar uma nova tabela de intervalos que contém uma lista de genes específicos da tabela silver-wall-555:TuteTable.hg19:

  1. Acesse a IU do BigQuery.

    Acessar a IU do BigQuery

  2. Crie um conjunto de dados concluindo as seguintes etapas:

    1. Clique no ícone de seta para baixo ao lado do projeto do Google Cloud na navegação e em Criar novo conjunto de dados.

    2. Para ID do conjunto de dados, insira genomics.

    3. Deixe as outras configurações padrão como estão e clique em OK.

  3. Crie a tabela de intervalos concluindo as seguintes etapas:

    1. No BigQuery, clique em Escrever consulta.

    2. Na caixa Nova consulta, insira a consulta a seguir, que materializa parte da tabela silver-wall-555:TuteTable.hg19 para um novo intervalo de tabela 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. Clique em Run.
  4. Na caixa Nova consulta, insira a seguinte consulta:

    #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. Clique em Executar consulta. A consulta leva aproximadamente dez segundos para ser executada e processa cerca de 334 GB de dados. Os resultados da consulta identificam variantes raras dentro da coorte que se sobrepõem às regiões de interesse.

    Uma consulta semelhante usando dados da Fase 3 do 1000 Genomes leva cerca de 90 segundos para ser executada e processa cerca de 3,38 TB de dados.

Como executar o intervalo JOIN com genes aleatórios

O exemplo a seguir mostra como executar um JOIN de intervalo em uma tabela materializada que contém 250 genes selecionados aleatoriamente da tabela silver-wall-555:TuteTable.hg19 (em inglês):

  1. Acesse a IU do BigQuery.

    Acessar a IU do BigQuery

  2. Crie um conjunto de dados concluindo as seguintes etapas:

    1. Clique no ícone de seta para baixo ao lado do projeto do Google Cloud na navegação e em Criar novo conjunto de dados.

    2. Para ID do conjunto de dados, insira genomics.

    3. Deixe as outras configurações padrão como estão e clique em OK.

  3. Crie a tabela de intervalos concluindo as seguintes etapas:

    1. No BigQuery, clique em Escrever consulta.

    2. Na caixa Nova consulta, insira a consulta a seguir, que materializa parte da tabela silver-wall-555:TuteTable.hg19 para um novo intervalo de tabela 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. Clique em Run.
  4. Na caixa Nova consulta, insira a seguinte consulta:

    #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. Clique em Executar consulta. A consulta leva aproximadamente dez segundos para ser executada e processa cerca de 334 GB de dados. Os resultados da consulta identificam variantes raras dentro da coorte que se sobrepõem às regiões de interesse.

    Uma consulta semelhante usando dados de Fase 3 do 1000 Genomes leva cerca de 90 segundos para ser executada e processa cerca de 3,38 TB de dados.