Executar JOINs de intervalo com o BigQuery

Use o BigQuery para executar uma consulta 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:

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

Este guia mostra exemplos de três consultas. Cada consulta demonstra como o BigQuery é escalonado em diferentes tamanhos de dados genômicos:

Os dados vêm da tabela de anotações do 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:

Consultar uma tabela em linha

O exemplo a seguir 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 página do BigQuery no Console do Cloud.

    Acesse a página do BigQuery

  2. Clique em Compose query.

  3. No campo Nova consulta, execute 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 Run query. A consulta leva cerca de 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.

    Expanda a seção a seguir para ver os resultados da consulta:

    Resultados da consulta

    Clas Início Ref Alt Diversão Gene PopFreqMax ExonicFunc num_variant_alleles número_de_tudos totais
    1 156699757 T C introdutório RRNAD1 0,002 2 4
    1 156705390 C T introdutório RRNAD1 8.0E-4 0 2
    1 156714207 T C introdutório ALG 0,003 0 6
    1 156714440 A C introdutório ALG 0,0068 0 12
    1 156723870 C T intergênio HDGF e PRCC 0,006 1 2
    1 156724456 C T intergênio HDGF e PRCC 0,002 2 4
    1 156733988 C T intergênio HDGF e PRCC 0,001 1 2
    1 156742258 T G introdutório PRCC 0,001 2 4
    1 156744826 T G introdutório PRCC 0,002 0 8
    1 156779764 G A introdutório SH2D2A 0,001 2 4
    1 156783454 A C introdutório SH2D2A 0,0014 1 2
    1 156786144 C T introdutório NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T introdutório NTRK1 0,002 1 2
    1 156815332 A C introdutório INSRAR,NTRK1 0,003 0 2
    1 156830778 G A exonic NTRK1 0,0067 multa 2 4
    1 156842064 C T introdutório NTRK1 0,0014 1 2
    1 156843438 C A exonic NTRK1 0,0032 multa 1 2
    1 156845773 C T introdutório NTRK1 0,001 2 4
    1 156873318 T C introdutório PEAR1 0,01 4 8
    1 156922740 G A introdutório ARHGEF11 0,007 1 2
    1 156930100 C T introdutório ARHGEF11 0,001 2 4
    2 113901230 G A intergênio IL1RN,PSD4 0,0082 1 2
    2 113953418 C A introdutório PSD4 0,001 2 4
    2 113967621 G C intergênio PSD4,PAX8 0,002 0 6
    2 113967624 T C intergênio PSD4,PAX8 0,002 0 2
    2 113980967 G A introdutório PAX8 0,002 2 4
    2 113994010 A C ncRNA_exonico PAX8-AS1 0,001 0 4
    2 113997745 C A ncRNA_exonico PAX8-AS1 0,001 2 4
    2 114061327 T C intergênio PAX8,CBWD2 0,001 2 4
    2 114084018 A C intergênio PAX8,CBWD2 0,0045 0 4
    2 114099037 G A intergênio PAX8,CBWD2 0,005 1 2
    2 114105670 A T intergênio PAX8,CBWD2 0,001 1 2
    2 114111325 G T intergênio PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergênio SYN2,PPARG 0,0089 2 4
    3 12277958 A G intergênio SYN2,PPARG 0,002 1 2
    3 12296019 G A intergênio SYN2,PPARG 0,002 2 4
    3 12316549 G C intergênio SYN2,PPARG 0,002 1 2
    3 12335681 T G introdutório PPP 0,0092 2 4
    3 12348795 T C introdutório PPP 0,0014 1 2
    3 12353106 T C introdutório PPP 0,001 2 4
    3 12403825 G A introdutório PPP 0,005 2 4
    3 12404394 G A introdutório PPP 0,001 1 2
    3 12410289 G A introdutório PPP 0,008 2 4
    3 12431381 C T introdutório PPP 0,0061 2 4
    3 12447267 G A introdutório PPP 0,0089 2 4
    3 12449379 C T introdutório PPP 0,0092 2 4
    3 12450848 C A introdutório PPP 0,0092 2 4
    3 12462847 T C introdutório PPP 0,002 1 2
    3 12492797 G A intergênio PPARG,ENCEN2 0,01 1 2
    3 12503201 G A intergênio PPARG,ENCEN2 0,0099 2 4
    3 12530460 A G introdutório TEMP2 0,0092 2 4
    3 12531167 A G introdutório TEMP2 0,0099 2 4
    3 12557737 A G introdutório TEMP2 0,001 2 4
    3 59636143 A G intergênio C3orf67 (FHI) 0,003 3 6
    3 59645934 A C intergênio C3orf67 (FHI) 0,004 1 2
    3 59646893 G A intergênio C3orf67 (FHI) 0,002 1 2
    3 59697024 A G intergênio C3orf67 (FHI) 0,0072 1 2
    3 59701013 G A intergênio C3orf67 (FHI) 0,004 2 4
    3 59733945 A G intergênio C3orf67 (FHI) 0,001 2 4
    3 59747482 C T introdutório HIIT 0,001 2 4
    3 59750635 A G introdutório HIIT 0,003 1 2
    3 59757776 C T introdutório HIIT 0,001 2 4
    3 59770612 G A introdutório HIIT 0,001 2 4
    3 59804444 G C introdutório HIIT 0,001 2 4
    3 59819769 T C introdutório HIIT 0,001 2 4
    3 59884396 C T introdutório HIIT 0,001 2 4
    3 59960728 A C introdutório HIIT 0,01 1 2
    3 59970345 G A introdutório HIIT 0,002 1 2
    3 59972417 T A introdutório HIIT 0,0072 0 2
    3 60104328 C A introdutório HIIT 0,01 2 4
    3 60139062 G A introdutório HIIT 0,01 0 2
    3 60158066 C T introdutório HIIT 0,001 1 2
    3 60169285 C T introdutório HIIT 0,005 1 2
    3 60216185 T C introdutório HIIT 0,002 1 2
    3 60226380 G A introdutório HIIT 0,007 2 4
    3 60234539 C A introdutório HIIT 0,002 1 2
    3 60247464 A C introdutório HIIT 0,004 2 4
    3 60269926 A G introdutório HIIT 0,007 2 4
    3 60271228 G T introdutório HIIT 0,007 2 4
    3 60286972 T C introdutório HIIT 0,001 2 4
    3 60301412 C G introdutório HIIT 0,001 1 2
    3 60312251 C T introdutório HIIT 0,0099 1 2
    3 60317682 A G introdutório HIIT 0,008 1 2
    3 60328557 C G introdutório HIIT 0,0043 2 4
    3 60342562 C T introdutório HIIT 0,006 1 2
    3 60400033 G A introdutório HIIT 0,004 2 4
    3 60435819 C T introdutório HIIT 0,006 2 4
    3 60435820 G T introdutório HIIT 0,004 1 2
    3 60441288 T C introdutório HIIT 0,006 2 4
    3 60444465 C A introdutório HIIT 0,01 1 2
    3 60444575 C T introdutório HIIT 0,001 1 2
    3 60450581 T C introdutório HIIT 0,01 1 2
    3 60456571 G A introdutório HIIT 0,001 2 4
    3 60473568 C G introdutório HIIT 0,001 1 2
    3 60487557 T C introdutório HIIT 0,001 1 2
    3 60559705 A G introdutório HIIT 0,002 2 4
    3 60570764 T C introdutório HIIT 0,008 2 4
    3 60582100 C T introdutório HIIT 0,001 1 2
    3 60587192 G A introdutório HIIT 0,004 1 2
    3 60599869 G A introdutório HIIT 0,0086 2 4
    3 60603091 C T introdutório HIIT 0,001 2 4
    3 60603250 A T introdutório HIIT 0,0099 1 2
    3 60609831 T G introdutório HIIT 0,001 2 4
    3 60619756 G T introdutório HIIT 0,0015 2 4
    3 60680758 C T introdutório HIIT 0,0089 2 4
    3 60702243 G C introdutório HIIT 0,001 2 4
    3 60702532 A G introdutório HIIT 0,001 1 2
    3 60714328 A T introdutório HIIT 0,004 1 2
    3 60725297 G A introdutório HIIT 0,001 1 2
    3 60726640 G A introdutório HIIT 0,01 2 4
    3 60795144 A G introdutório HIIT 0,001 2 4
    3 60807171 A G introdutório HIIT 0,001 1 2
    3 60813868 T C introdutório HIIT 0,001 1 2
    3 60826546 C G introdutório HIIT 0,0023 1 2
    3 60837392 C T introdutório HIIT 0,001 1 2
    3 60846310 A G introdutório HIIT 0,01 0 2
    3 60850985 C T introdutório HIIT 0,004 1 2
    3 60852559 T C introdutório HIIT 0,008 1 2
    3 60871759 T C introdutório HIIT 0,004 1 2
    3 60884396 C T introdutório HIIT 0,002 2 4
    3 60897092 C A introdutório HIIT 0,001 2 4
    3 60940759 C T introdutório HIIT 0,0089 1 2
    3 60982595 A G introdutório HIIT 0,003 2 4
    3 60999283 G A introdutório HIIT 0,001 1 2
    3 61042977 A G introdutório HIIT 0,001 2 4
    3 61043349 T C introdutório HIIT 0,001 2 4
    3 61044789 A C introdutório HIIT 0,001 2 4
    3 61141621 G A introdutório HIIT 0,003 1 2
    3 61148655 G C introdutório HIIT 0,001 2 4
    3 61170747 C T introdutório HIIT 0,003 1 2
    3 61189473 C G introdutório HIIT 0,0099 1 2
    3 61190425 C T introdutório HIIT 0,0023 2 4
    3 61193853 C T introdutório HIIT 0,0099 0 2
    3 61194793 C T introdutório HIIT 0,007 0 2
    3 61194840 A G introdutório HIIT 0,0099 0 2
    3 61194886 T A introdutório HIIT 0,0099 0 2
    3 61201777 C T introdutório HIIT 0,001 2 4
    3 61202292 T C introdutório HIIT 0,007 1 2
    3 61232806 G C introdutório HIIT 0,0099 1 2
    3 61232910 C T introdutório HIIT 0,0099 1 2
    3 61235824 A T introdutório HIIT 0,001 2 4
    3 61283810 A C intergênio HIIT,PTPRG 0,0089 1 2
    3 61293731 T A intergênio HIIT,PTPRG 0,0089 2 4
    3 61296730 C T intergênio HIIT,PTPRG 0,001 1 2
    3 61326341 C T intergênio HIIT,PTPRG 0,004 2 4
    3 61326620 T C intergênio HIIT,PTPRG 0,01 1 2
    3 61327649 G C intergênio HIIT,PTPRG 0,001 2 4
    3 61330545 G C intergênio HIIT,PTPRG 0,001 2 4
    3 61335803 G A intergênio HIIT,PTPRG 0,001 2 4

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

Usar uma tabela materializada

Ao trabalhar com Big Data em escala, materialize uma tabela de intervalo e execute uma consulta JOIN na nova tabela. Antes de continuar com o restante desta seção, crie um conjunto de dados seguindo estas etapas:

  1. Abra a página do BigQuery no Console do Cloud.

    Acesse a página do BigQuery

  2. No painel Explorador, selecione o projeto em que você quer criar o conjunto de dados.

  3. Expanda a opção Ações e clique em Criar conjunto de dados.

  4. Na página Criar conjunto de dados, faça o seguinte:

    1. Para o código do conjunto de dados, insira genomics.
    2. Deixe as outras configurações padrão como estão.
    3. Clique em Criar conjunto de dados.

Consulte uma tabela materializada com genes específicos

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

  1. Para criar a tabela de intervalo, siga estas etapas:

    1. Abra a página do BigQuery no Console do Cloud.

      Acesse a página do BigQuery

    2. Clique em Compose query.

    3. No campo Nova consulta, execute a consulta a seguir. A consulta 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 );
      
    4. Clique em Run query. A consulta retorna o seguinte resultado:

    This statement created a new table named PROJECT_ID:genomics.myIntervalTable.
    
  2. No campo Nova consulta, execute 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;
    
  3. Clique em Run query. A consulta leva cerca de 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.

    Expanda a seção a seguir para ver os resultados da consulta:

    Resultados da consulta

    Clas Início Ref Alt Diversão Gene PopFreqMax ExonicFunc num_variant_alleles número_de_tudos totais
    1 156699757 T C introdutório RRNAD1 0,002 2 4
    1 156705390 C T introdutório RRNAD1 8.0E-4 0 2
    1 156714207 T C introdutório ALG 0,003 0 6
    1 156714440 A C introdutório ALG 0,0068 0 12
    1 156723870 C T intergênio HDGF e PRCC 0,006 1 2
    1 156724456 C T intergênio HDGF e PRCC 0,002 2 4
    1 156733988 C T intergênio HDGF e PRCC 0,001 1 2
    1 156742258 T G introdutório PRCC 0,001 2 4
    1 156744826 T G introdutório PRCC 0,002 0 8
    1 156779764 G A introdutório SH2D2A 0,001 2 4
    1 156783454 A C introdutório SH2D2A 0,0014 1 2
    1 156786144 C T introdutório NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T introdutório NTRK1 0,002 1 2
    1 156815332 A C introdutório INSRAR,NTRK1 0,003 0 2
    1 156830778 G A exonic NTRK1 0,0067 multa 2 4
    1 156842064 C T introdutório NTRK1 0,0014 1 2
    1 156843438 C A exonic NTRK1 0,0032 multa 1 2
    1 156845773 C T introdutório NTRK1 0,001 2 4
    1 156873318 T C introdutório PEAR1 0,01 4 8
    1 156922740 G A introdutório ARHGEF11 0,007 1 2
    1 156930100 C T introdutório ARHGEF11 0,001 2 4
    2 113901230 G A intergênio IL1RN,PSD4 0,0082 1 2
    2 113953418 C A introdutório PSD4 0,001 2 4
    2 113967621 G C intergênio PSD4,PAX8 0,002 0 6
    2 113967624 T C intergênio PSD4,PAX8 0,002 0 2
    2 113980967 G A introdutório PAX8 0,002 2 4
    2 113994010 A C ncRNA_exonico PAX8-AS1 0,001 0 4
    2 113997745 C A ncRNA_exonico PAX8-AS1 0,001 2 4
    2 114061327 T C intergênio PAX8,CBWD2 0,001 2 4
    2 114084018 A C intergênio PAX8,CBWD2 0,0045 0 4
    2 114099037 G A intergênio PAX8,CBWD2 0,005 1 2
    2 114105670 A T intergênio PAX8,CBWD2 0,001 1 2
    2 114111325 G T intergênio PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergênio SYN2,PPARG 0,0089 2 4
    3 12277958 A G intergênio SYN2,PPARG 0,002 1 2
    3 12296019 G A intergênio SYN2,PPARG 0,002 2 4
    3 12316549 G C intergênio SYN2,PPARG 0,002 1 2
    3 12335681 T G introdutório PPP 0,0092 2 4
    3 12348795 T C introdutório PPP 0,0014 1 2
    3 12353106 T C introdutório PPP 0,001 2 4
    3 12403825 G A introdutório PPP 0,005 2 4
    3 12404394 G A introdutório PPP 0,001 1 2
    3 12410289 G A introdutório PPP 0,008 2 4
    3 12431381 C T introdutório PPP 0,0061 2 4
    3 12447267 G A introdutório PPP 0,0089 2 4
    3 12449379 C T introdutório PPP 0,0092 2 4
    3 12450848 C A introdutório PPP 0,0092 2 4
    3 12462847 T C introdutório PPP 0,002 1 2
    3 12492797 G A intergênio PPARG,ENCEN2 0,01 1 2
    3 12503201 G A intergênio PPARG,ENCEN2 0,0099 2 4
    3 12530460 A G introdutório TEMP2 0,0092 2 4
    3 12531167 A G introdutório TEMP2 0,0099 2 4
    3 12557737 A G introdutório TEMP2 0,001 2 4
    3 59636143 A G intergênio C3orf67 (FHI) 0,003 3 6
    3 59645934 A C intergênio C3orf67 (FHI) 0,004 1 2
    3 59646893 G A intergênio C3orf67 (FHI) 0,002 1 2
    3 59697024 A G intergênio C3orf67 (FHI) 0,0072 1 2
    3 59701013 G A intergênio C3orf67 (FHI) 0,004 2 4
    3 59733945 A G intergênio C3orf67 (FHI) 0,001 2 4
    3 59747482 C T introdutório HIIT 0,001 2 4
    3 59750635 A G introdutório HIIT 0,003 1 2
    3 59757776 C T introdutório HIIT 0,001 2 4
    3 59770612 G A introdutório HIIT 0,001 2 4
    3 59804444 G C introdutório HIIT 0,001 2 4
    3 59819769 T C introdutório HIIT 0,001 2 4
    3 59884396 C T introdutório HIIT 0,001 2 4
    3 59960728 A C introdutório HIIT 0,01 1 2
    3 59970345 G A introdutório HIIT 0,002 1 2
    3 59972417 T A introdutório HIIT 0,0072 0 2
    3 60104328 C A introdutório HIIT 0,01 2 4
    3 60139062 G A introdutório HIIT 0,01 0 2
    3 60158066 C T introdutório HIIT 0,001 1 2
    3 60169285 C T introdutório HIIT 0,005 1 2
    3 60216185 T C introdutório HIIT 0,002 1 2
    3 60226380 G A introdutório HIIT 0,007 2 4
    3 60234539 C A introdutório HIIT 0,002 1 2
    3 60247464 A C introdutório HIIT 0,004 2 4
    3 60269926 A G introdutório HIIT 0,007 2 4
    3 60271228 G T introdutório HIIT 0,007 2 4
    3 60286972 T C introdutório HIIT 0,001 2 4
    3 60301412 C G introdutório HIIT 0,001 1 2
    3 60312251 C T introdutório HIIT 0,0099 1 2
    3 60317682 A G introdutório HIIT 0,008 1 2
    3 60328557 C G introdutório HIIT 0,0043 2 4
    3 60342562 C T introdutório HIIT 0,006 1 2
    3 60400033 G A introdutório HIIT 0,004 2 4
    3 60435819 C T introdutório HIIT 0,006 2 4
    3 60435820 G T introdutório HIIT 0,004 1 2
    3 60441288 T C introdutório HIIT 0,006 2 4
    3 60444465 C A introdutório HIIT 0,01 1 2
    3 60444575 C T introdutório HIIT 0,001 1 2
    3 60450581 T C introdutório HIIT 0,01 1 2
    3 60456571 G A introdutório HIIT 0,001 2 4
    3 60473568 C G introdutório HIIT 0,001 1 2
    3 60487557 T C introdutório HIIT 0,001 1 2
    3 60559705 A G introdutório HIIT 0,002 2 4
    3 60570764 T C introdutório HIIT 0,008 2 4
    3 60582100 C T introdutório HIIT 0,001 1 2
    3 60587192 G A introdutório HIIT 0,004 1 2
    3 60599869 G A introdutório HIIT 0,0086 2 4
    3 60603091 C T introdutório HIIT 0,001 2 4
    3 60603250 A T introdutório HIIT 0,0099 1 2
    3 60609831 T G introdutório HIIT 0,001 2 4
    3 60619756 G T introdutório HIIT 0,0015 2 4
    3 60680758 C T introdutório HIIT 0,0089 2 4
    3 60702243 G C introdutório HIIT 0,001 2 4
    3 60702532 A G introdutório HIIT 0,001 1 2
    3 60714328 A T introdutório HIIT 0,004 1 2
    3 60725297 G A introdutório HIIT 0,001 1 2
    3 60726640 G A introdutório HIIT 0,01 2 4
    3 60795144 A G introdutório HIIT 0,001 2 4
    3 60807171 A G introdutório HIIT 0,001 1 2
    3 60813868 T C introdutório HIIT 0,001 1 2
    3 60826546 C G introdutório HIIT 0,0023 1 2
    3 60837392 C T introdutório HIIT 0,001 1 2
    3 60846310 A G introdutório HIIT 0,01 0 2
    3 60850985 C T introdutório HIIT 0,004 1 2
    3 60852559 T C introdutório HIIT 0,008 1 2
    3 60871759 T C introdutório HIIT 0,004 1 2
    3 60884396 C T introdutório HIIT 0,002 2 4
    3 60897092 C A introdutório HIIT 0,001 2 4
    3 60940759 C T introdutório HIIT 0,0089 1 2
    3 60982595 A G introdutório HIIT 0,003 2 4
    3 60999283 G A introdutório HIIT 0,001 1 2
    3 61042977 A G introdutório HIIT 0,001 2 4
    3 61043349 T C introdutório HIIT 0,001 2 4
    3 61044789 A C introdutório HIIT 0,001 2 4
    3 61141621 G A introdutório HIIT 0,003 1 2
    3 61148655 G C introdutório HIIT 0,001 2 4
    3 61170747 C T introdutório HIIT 0,003 1 2
    3 61189473 C G introdutório HIIT 0,0099 1 2
    3 61190425 C T introdutório HIIT 0,0023 2 4
    3 61193853 C T introdutório HIIT 0,0099 0 2
    3 61194793 C T introdutório HIIT 0,007 0 2
    3 61194840 A G introdutório HIIT 0,0099 0 2
    3 61194886 T A introdutório HIIT 0,0099 0 2
    3 61201777 C T introdutório HIIT 0,001 2 4
    3 61202292 T C introdutório HIIT 0,007 1 2
    3 61232806 G C introdutório HIIT 0,0099 1 2
    3 61232910 C T introdutório HIIT 0,0099 1 2
    3 61235824 A T introdutório HIIT 0,001 2 4
    3 61283810 A C intergênio HIIT,PTPRG 0,0089 1 2
    3 61293731 T A intergênio HIIT,PTPRG 0,0089 2 4
    3 61296730 C T intergênio HIIT,PTPRG 0,001 1 2
    3 61326341 C T intergênio HIIT,PTPRG 0,004 2 4
    3 61326620 T C intergênio HIIT,PTPRG 0,01 1 2
    3 61327649 G C intergênio HIIT,PTPRG 0,001 2 4
    3 61330545 G C intergênio HIIT,PTPRG 0,001 2 4
    3 61335803 G A intergênio HIIT,PTPRG 0,001 2 4

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

Consulte uma tabela materializada com 250 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. Para criar a tabela de intervalo, siga estas etapas:

    1. Abra a página do BigQuery no Console do Cloud.

      Acesse a página do BigQuery

    2. Clique em Compose query.

    3. No campo Nova consulta, execute a seguinte consulta, 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 query. A consulta retorna o seguinte resultado:
      This statement created a new table named PROJECT_ID:genomics.randomGenesIntervalTable.
      
  2. No campo Nova consulta, execute 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;
    
  3. Clique em Run query. A consulta leva cerca de 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.

    Expanda a seção a seguir para ver os resultados truncados da consulta:

    Resultados da consulta

    Clas Início Ref Alt Diversão Gene PopFreqMax ExonicFunc num_variant_alleles número_de_tudos totais
    1 2925355 C A intergênio TTC34,CTCT2 0,001 2 4
    1 2933170 G A intergênio TTC34,CTCT2 0,0083 0 4
    1 2944477 G A intergênio ACTRT2.LINC00982 0,003 4 6
    1 2967591 A T intergênio ACTRT2.LINC00982 0,0092 1 2
    1 2975255 T C downstream LINC00982 0,0082 1 2
    1 2977223 C T cRNA_introc. LINC00982 0,0072 1 2
    1 2978803 G C ncRNA_exonico LINC00982 0,002 4 6
    1 3006466 G A introdutório PRDM16 0,0098 1 2
    1 3011333 G T introdutório PRDM16 0,004 1 2
    1 3019659 C T introdutório PRDM16 0,0031 1 2
    1 3036896 G A introdutório PRDM16 0,001 1 2
    1 3037388 G A introdutório PRDM16 0,002 2 4
    1 3041250 T G introdutório PRDM16 0,006 2 4
    1 3042502 A T introdutório PRDM16 0,003 4 6
    1 3053713 A C introdutório PRDM16 0,002 1 2
    1 3063109 C T introdutório PRDM16 0,002 0 2
    1 3063593 T C introdutório PRDM16 0,003 1 2
    1 3076439 C T introdutório PRDM16 0,001 2 4
    1 3078960 G A introdutório PRDM16 0,007 2 4
    1 3084268 A C introdutório PRDM16 0,005 0 2
    1 3084492 T C introdutório PRDM16 0,0015 0 2
    1 3084786 T C introdutório PRDM16 0,0015 0 4
    1 3111119 G A introdutório PRDM16 0,003 1 2
    1 3111643 C T introdutório PRDM16 0,0041 1 2
    1 3114807 G A introdutório PRDM16 0,0041 1 2
    1 3165530 C T introdutório PRDM16 0,0089 1 2
    1 3169325 G A introdutório PRDM16 0,008 2 4
    1 3179623 C T introdutório PRDM16 0,003 2 4
    1 3181097 C T introdutório PRDM16 0,001 2 4
    1 3194000 G C introdutório PRDM16 0,005 2 4
    1 3195769 T C introdutório PRDM16 0,002 1 2
    1 3197351 C T introdutório PRDM16 0,0061 1 2
    1 3224100 C A introdutório PRDM16 0,003 2 4
    1 3228644 G T introdutório PRDM16 0,001 2 4
    1 3234045 G A introdutório PRDM16 0,002 1 2
    1 3235971 G A introdutório PRDM16 0,0089 1 2
    1 3274115 C T introdutório PRDM16 0,001 2 4
    1 3291388 G A introdutório PRDM16 0,002 2 4
    1 3295658 A C introdutório PRDM16 0,0068 0 6
    1 3295937 A C introdutório PRDM16 0,0068 0 2
    1 3296205 T C introdutório PRDM16 0,0083 0 2
    1 3315690 G A introdutório PRDM16 0,001 2 4
    1 3329212 G A exonic PRDM16 0,0031 multa 1 2
    1 3331787 C T introdutório PRDM16 0,0099 1 2
    1 3370316 G C upstream ARHGEF16 0,001 2 4
    1 3379560 A G introdutório ARHGEF16 0,005 0 6
    1 3391174 C T introdutório ARHGEF16 0,006 1 2
    1 3413873 G A exonic MEGF6 0,003 multa 1 2
    1 3416272 C T exonic MEGF6 0,0072 silencioso 2 4
    1 3417122 G A introdutório MEGF6 0,0038 2 4
    1 3436219 G A introdutório MEGF6 0,0046 2 4
    1 12907456 A G exonic HNRNPCL1,LOC649330 0,006 multa 0 10
    1 12907518 C A exonic HNRNPCL1,LOC649330 1,0 a 4 multa 0 10
    1 12908499 G C introdutório HNRNPCL1 0,0031 0 8
    1 12931660 G C intergênio PRAMEF2,PRAMEF4 0,004 1 2
    1 12937721 G T intergênio PRAMEF2,PRAMEF4 0,0038 0 2
    1 12940827 G T introdutório PRAMEF4 0,007 2 4
    1 12942759 T G introdutório PRAMEF4 0,0076 0 10
    1 12942805 T G introdutório PRAMEF4 0,0061 0 12
    1 12942812 G A introdutório PRAMEF4 0,0061 0 12
    1 12942875 A G introdutório PRAMEF4 0,0068 0 6
    1 12942912 G C introdutório PRAMEF4 2,0 e-4 0 2
    1 12942937 A T exonic PRAMEF4 0,0029 multa 0 2
    1 12942940 T G exonic PRAMEF4 0,0038 multa 0 2
    1 12943940 T C introdutório PRAMEF4 0,0015 0 12
    1 12944138 A G introdutório PRAMEF4 8.0E-4 0 12
    1 12944234 G A introdutório PRAMEF4 0,0015 0 12
    1 12944589 T G introdutório PRAMEF4 0,003 0 4
    1 12944845 A C introdutório PRAMEF4 0,0014 0 6
    1 12946439 T C upstream PRAMEF4 0,0029 0 10
    1 12946833 G A upstream PRAMEF4 0,001 0 8
    1 12946835 T A upstream PRAMEF4 0,004 0 12
    1 12995204 G T intergênio PRAMEF8,PRAMEF6 0,003 1 4
    1 12997638 T C downstream PRAMEF6,PRAMEF9 0,003 2 4
    1 13007841 G C upstream PRAMEF6 0,0043 0 8
    1 13019228 T A intergênio PRAMEF6,LOC393003 0,0015 0 10
    1 13038503 G A UTR3 LO3960003 0,0072 1 2
    1 13051650 C T intergênio LO3939003,PRAMEF5 0,002 2 4
    1 15706063 G A introdutório FHAD1 0,0029 1 2
    1 15713292 C T introdutório FHAD1 0,001 1 2
    1 15766541 G C introdutório CTRC 0,001 1 2
    1 15782601 T C upstream CELA2A 0,0038 1 2
    1 15828125 G A introdutório SPSP9 0,0014 2 4
    1 15831037 G A introdutório SPSP9 0,0099 1 2
    1 15840513 T G introdutório SPSP9 0,0043 2 4
    1 15868742 G A introdutório DNAJC16 0,001 1 2
    1 15876704 G A introdutório DNAJC16 0,001 1 2
    1 15900342 C A introdutório DESENVOLVIMENTO 0,001 1 2
    1 15906257 T C introdutório DESENVOLVIMENTO 8.0E-4 1 2
    1 15911897 A G upstream DESENVOLVIMENTO 0,0043 2 4
    1 22764178 C T intergênio WNT4,ZBTB40 0,001 2 4
    1 22791939 C T introdutório ZBTB40 0,0089 2 4
    1 22874394 C G intergênio ZBTB40,EPHA8 0,007 1 2
    1 22875103 C G intergênio ZBTB40,EPHA8 0,007 1 2
    1 22906403 C T introdutório EPHA8 0,008 2 4
    1 22912956 G A introdutório EPHA8 0,001 1 2
    1 22917007 C T introdutório EPHA8 0,001 2 4
    1 22927240 G A exonic EPHA8 0,0013 multa 2 4
    1 22932265 G A intergênio EPHA8,MICR6127 0,0089 2 4
    1 22944057 C T intergênio EPHA8,MICR6127 0,0089 2 4
    1 22978799 A G upstream C1QB 0,0099 2 4
    1 35170588 C T intergênio C1orf94,GJB5 0,01 1 2
    1 35172426 C T intergênio C1orf94,GJB5 0,008 1 2
    1 35172447 G A intergênio C1orf94,GJB5 0,001 1 2
    1 35175302 C T intergênio C1orf94,GJB5 0,008 1 2
    1 35177410 A T intergênio C1orf94,GJB5 0,001 1 2
    1 35178768 C T intergênio C1orf94,GJB5 0,0014 2 4
    1 35179362 G A intergênio C1orf94,GJB5 0,0014 2 4
    1 35186166 G A intergênio C1orf94,GJB5 0,0099 2 4
    1 35186520 A C intergênio C1orf94,GJB5 0,002 2 4
    1 35196361 G A intergênio C1orf94,GJB5 0,0099 2 4
    1 35223545 C T exonic JOB5 0,001 silencioso 1 2
    1 35224029 G A UTR3 JOB5 0,003 1 2
    1 35227895 T C UTR3 JJ4 5,0-4 1 2
    1 35230455 G T intergênio GJB4,JB3 0,0043 1 2
    1 35232954 T C intergênio GJB4,JB3 0,003 1 2
    1 35237986 G A intergênio GJB4,JB3 0,0014 1 2
    1 35245522 C T intergênio GJB4,JB3 0,001 1 2
    1 35256979 C T intergênio JAB3,GGGA 0,002 2 4
    1 35263872 C T intergênio GJA4,SMIM12 5,0-4 2 4
    1 35323895 A C introdutório SMIM12 0,0027 2 4
    1 35369676 G A introdutório DLGAP3 0,007 2 4
    1 35371634 T A upstream DLGAP3 0,0015 0 4
    1 39253519 G A intergênio LINC01343,ROTAAO 0,005 1 2
    1 39288829 G A intergênio LINC01343,ROTAAO 0,005 1 2
    1 39289832 A C intergênio LINC01343,ROTAAO 0,002 0 2
    1 39312638 G A introdutório RRACO 0,0038 2 4
    1 39361372 G A introdutório HIDL 0,005 1 2
    1 39363826 T G introdutório HIDL 0,0029 1 2
    1 39367555 T C introdutório HIDL 0,007 2 4
    1 39369531 T C introdutório HIDL 0,001 2 4
    1 39370202 T C introdutório HIDL 0,01 1 2
    1 39449101 A G intergênio CHHDL2,AKIRIN1 0,001 2 4
    1 39475057 G A intergênio AKIRIN1,NDUFS5 0,01 1 2
    1 39485016 C T intergênio AKIRIN1,NDUFS5 0,001 2 4
    1 39488137 A G intergênio AKIRIN1,NDUFS5 0,001 2 4
    1 39499212 A C introdutório NDUFS5 0,001 0 2
    1 39500605 C G downstream NDUFS5 0,002 0 10
    1 46813814 T C introdutório NSUN4 0,0014 1 2
    1 46817258 A G introdutório NSUN4 0,005 0 2
    1 46843158 T C intergênio NSUN4.FAAH 0,001 1 2
    1 46933509 A G intergênio LINC01398;DMBX1 0,002 1 2
    1 46935021 G A intergênio LINC01398;DMBX1 0,004 2 4
    1 46939253 T A intergênio LINC01398;DMBX1 0,004 2 4
    1 46951788 C A intergênio LINC01398;DMBX1 0,002 2 4
    1 46980864 G C downstream DMBX1 0,003 1 2
    1 46989657 T C intergênio DMBX1,MKNK1-AS1 0,007 1 2
    1 46994678 C T intergênio DMBX1,MKNK1-AS1 0,002 1 2
    1 46999438 T C intergênio DMBX1,MKNK1-AS1 0,002 1 2
    1 92761505 A G introdutório LGL 0,001 2 4
    1 92764270 G C introdutório LGL 0,001 2 4
    1 92802210 G A introdutório RPAP2 0,0072 1 2
    1 92820663 T A introdutório RPAP2 0,0058 1 2
    1 92820664 G T introdutório RPAP2 0,0058 1 2
    1 92820953 G A introdutório RPAP2 0,007 2 4
    1 92824766 A G introdutório RPAP2 0,0058 1 2
    1 92849183 C A introdutório RPAP2 0,01 2 4
    1 92850696 C G introdutório RPAP2 0,0023 1 2
    1 92861357 T C intergênio RPAP2,GFI1 0,01 2 4
    1 92877460 C G intergênio RPAP2,GFI1 0,002 1 2
    1 92880643 A G intergênio RPAP2,GFI1 0,001 2 4
    1 92911540 G A intergênio RPAP2,GFI1 0,004 2 4
    1 92911721 A C intergênio RPAP2,GFI1 0,0031 0 8
    1 92918277 C T intergênio RPAP2,GFI1 0,001 2 4
    1 92950920 G A introdutório GFI1 0,008 2 4
    1 92964788 G A intergênio GFI1,EVI5 0,0023 1 2
    1 92977480 C T UTR3 EVI5 0,002 1 2
    1 92985213 C T introdutório EVI5 0,001 2 4
    1 92988342 C T introdutório EVI5 0,008 2 4
    1 92992283 G A introdutório EVI5 0,01 2 4
    1 92999760 C T introdutório EVI5 0,003 1 2
    1 93005149 G C introdutório EVI5 0,003 0 4
    1 93018543 A T introdutório EVI5 0,01 2 4
    1 93033744 C T introdutório EVI5 0,001 2 4
    1 111400296 G A intergênio KCNA3,CD53 0,0014 2 4
    1 111411924 C T intergênio KCNA3,CD53 0,003 1 2
    1 111441850 C G UTR3 CD53 0,003 2 4
    1 111451527 C T intergênio CD53,LRIF1 0,008 2 4
    1 111454082 C A intergênio CD53,LRIF1 0,001 2 4
    1 111466506 A G intergênio CD53,LRIF1 0,001 2 4
    1 111525974 G A intergênio LRIF1,DRAM2 0,002 2 4
    1 111574573 G T intergênio LRIF1,DRAM2 0,0072 2 4
    1 111574594 T A intergênio LRIF1,DRAM2 0,005 1 2
    1 111574647 G A intergênio LRIF1,DRAM2 0,005 1 2
    1 111591746 T A intergênio LRIF1,DRAM2 0,005 1 2
    1 111601459 A G intergênio LRIF1,DRAM2 0,005 1 2
    1 111604748 G C intergênio LRIF1,DRAM2 0,005 1 2
    1 112191526 T G introdutório RAP1A 0,001 2 4
    1 112206765 A G introdutório RAP1A 0,0043 1 2
    1 112226517 G A introdutório RAP1A 0,001 0 2
    1 112263324 G T intergênio AP1A,FAM212B 0,003 2 4
    1 112264843 G A UTR3 FAM212B 0,001 1 2
    1 112285810 C T cRNA_introc. FAM212B-AS1 0,004 1 2
    1 112304285 T C introdutório DDX20 0,0043 1 2
    1 112307213 A C introdutório DDX20 0,0043 1 2
    1 112309436 G T exonic DDX20 0.0 multa 1 2
    1 112317384 T C intergênio DDX20,KCND3 0,0014 1 2
    1 112381367 C T introdutório KCND3 0,002 1 2
    1 112396571 G T ncRNA_exonico KCND3-IT1 0,001 1 2
    1 113520038 G A intergênio SL16A1-AS1,LOC100996251 0,0023 1 2

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