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, mostramos como usar uma consulta JOIN complexa para receber uma lista de nomes de genes e fazer o seguinte:

  • Encontre os SNPs raros que se sobrepõem aos genes.
  • Encontrar 100.000 pares de bases em ambos os lados de um gene para o todo amostras de genoma.

Este guia mostra exemplos para três consultas. Cada consulta demonstra como O BigQuery escalona com 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:

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 JOIN com uma tabela contendo variantes de Genomes Platinum da Illumina:

  1. Acesse a página do BigQuery no console do Google Cloud.

    Acessar a página do BigQuery

  2. Clique em Escrever consulta.

  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 Executar consulta. 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.

    Abra a seção a seguir para conferir os resultados da consulta:

    Resultados da consulta

    Chr Iniciar Ref Alt Função Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C intronic RRNAD1 0,002 2 4
    1 156705390 C T intronic RRNAD1 8.0E-4 0 2
    1 156714207 T C intronic HDGF 0.003 0 6
    1 156714440 A C intronic HDGF 0,0068 0 12
    1 156723870 C T intergênico HDGF, PRCC 0,006 1 2
    1 156724456 C T intergênico HDGF,PRCC 0,002 2 4
    1 156733988 C T intergênico HDGF,PRCC 0,001 1 2
    1 156742258 T G intronic PRCC 0,001 2 4
    1 156744826 T G intronic PRCC 0,002 0 8
    1 156779764 G A intronic SH2D2A 0,001 2 4
    1 156783454 A C intronico SH2D2A 0,0014 1 2
    1 156786144 C T intronico NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T intronic NTRK1 0,002 1 2
    1 156815332 A C intronic INSRR,NTRK1 0.003 0 2
    1 156830778 G A exônico NTRK1 0,0067 missense 2 4
    1 156842064 C T intronic NTRK1 0,0014 1 2
    1 156843438 C A exônico NTRK1 0,0032 missense 1 2
    1 156845773 C T intronic NTRK1 0,001 2 4
    1 156873318 T C intronico PEAR1 0,01 4 8
    1 156922740 G A intronic ARHGEF11 0,007 1 2
    1 156930100 C T intronic ARHGEF11 0,001 2 4
    2 113901230 G A intergênico IL1RN e PSD4 0,0082 1 2
    2 113953418 C A intronico PSD4 0,001 2 4
    2 113967621 G C intergênico PSD4 e PAX8 0,002 0 6
    2 113967624 T C intergênico PSD4 e 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ênico PAX8,CBWD2 0,001 2 4
    2 114084018 A C intergênico PAX8,CBWD2 0,0045 0 4
    2 114099037 G A intergênico PAX8,CBWD2 0,0051 1 2
    2 114105670 A T intergênico PAX8,CBWD2 0,001 1 2
    2 114111325 G T intergênico PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergênico SYN2,PPARG 0,0089 2 4
    3 12277958 A G intergênico SYN2,PPARG 0,002 1 2
    3 12296019 G A intergênico SYN2,PPARG 0,002 2 4
    3 12316549 G C intergênico SYN2,PPARG 0,002 1 2
    3 12335681 T G intronic PPARG 0,0092 2 4
    3 12348795 T C intronico PPARG 0,0014 1 2
    3 12353106 T C intronico PPARG 0,001 2 4
    3 12403825 G A intronic PPARG 0,0051 2 4
    3 12404394 G A intronic PPARG 0,001 1 2
    3 12410289 G A intronic PPARG 0,008 2 4
    3 12431381 C T intronico PPARG 0,0061 2 4
    3 12447267 G A intronic PPARG 0,0089 2 4
    3 12449379 C T intronico PPARG 0,0092 2 4
    3 12450848 C A intronic PPARG 0,0092 2 4
    3 12462847 T C intronico PPARG 0,002 1 2
    3 12492797 G A intergênico PPARG,TSEN2 0,01 1 2
    3 12503201 G A intergênico PPARG,TSEN2 0,0099 2 4
    3 12530460 A G intronic TSEN2 0,0092 2 4
    3 12531167 A G intronic TSEN2 0,0099 2 4
    3 12557737 A G intronic TSEN2 0,001 2 4
    3 59636143 A G intergênico C3orf67,FHIT 0.003 3 6
    3 59645934 A C intergênico C3orf67,FHIT 0,004 1 2
    3 59646893 G A intergênico C3orf67,FHIT 0,002 1 2
    3 59697024 A G intergênico C3orf67,FHIT 0,0072 1 2
    3 59701013 G A intergênico C3orf67,FHIT 0,004 2 4
    3 59733945 A G intergênico C3orf67,FHIT 0,001 2 4
    3 59747482 C T intronico FHIT 0,001 2 4
    3 59750635 A G intronico FHIT 0.003 1 2
    3 59757776 C T intronico FHIT 0,001 2 4
    3 59770612 G A intronic FHIT 0,001 2 4
    3 59804444 G C intronico FHIT 0,001 2 4
    3 59819769 T C intronico FHIT 0,001 2 4
    3 59884396 C T intronico FHIT 0,001 2 4
    3 59960728 A C intronico FHIT 0,01 1 2
    3 59970345 G A intronic FHIT 0,002 1 2
    3 59972417 T A intronic FHIT 0,0072 0 2
    3 60104328 C A intronic FHIT 0,01 2 4
    3 60139062 G A intronic FHIT 0,01 0 2
    3 60158066 C T intronico FHIT 0,001 1 2
    3 60169285 C T intronico FHIT 0,005 1 2
    3 60216185 T C intronico FHIT 0,002 1 2
    3 60226380 G A intronic FHIT 0,007 2 4
    3 60234539 C A intronic FHIT 0,002 1 2
    3 60247464 A C intronico FHIT 0,004 2 4
    3 60269926 A G intronico FHIT 0,007 2 4
    3 60271228 G T intronico FHIT 0,007 2 4
    3 60286972 T C intronico FHIT 0,001 2 4
    3 60301412 C G intronico FHIT 0,001 1 2
    3 60312251 C T intronico FHIT 0,0099 1 2
    3 60317682 A G intronico FHIT 0,008 1 2
    3 60328557 C G intronico FHIT 0,0043 2 4
    3 60342562 C T intronico FHIT 0,006 1 2
    3 60400033 G A intronic FHIT 0,004 2 4
    3 60435819 C T intronico FHIT 0,006 2 4
    3 60435820 G T intronico FHIT 0,004 1 2
    3 60441288 T C intronico FHIT 0,006 2 4
    3 60444465 C A intronic FHIT 0,01 1 2
    3 60444575 C T intronico FHIT 0,001 1 2
    3 60450581 T C intronico FHIT 0,01 1 2
    3 60456571 G A intronic FHIT 0,001 2 4
    3 60473568 C G intronico FHIT 0,001 1 2
    3 60487557 T C intronico FHIT 0,001 1 2
    3 60559705 A G intronico FHIT 0,002 2 4
    3 60570764 T C intronico FHIT 0,008 2 4
    3 60582100 C T intronico FHIT 0,001 1 2
    3 60587192 G A intronic FHIT 0,004 1 2
    3 60599869 G A intronic FHIT 0,0086 2 4
    3 60603091 C T intronico FHIT 0,001 2 4
    3 60603250 A T intronico FHIT 0,0099 1 2
    3 60609831 T G intronico FHIT 0,001 2 4
    3 60619756 G T intronico FHIT 0,0015 2 4
    3 60680758 C T intronico FHIT 0,0089 2 4
    3 60702243 G C intronico FHIT 0,001 2 4
    3 60702532 A G intronico FHIT 0,001 1 2
    3 60714328 A T intronico FHIT 0,004 1 2
    3 60725297 G A intronic FHIT 0,001 1 2
    3 60726640 G A intronic FHIT 0,01 2 4
    3 60795144 A G intronico FHIT 0,001 2 4
    3 60807171 A G intronico FHIT 0,001 1 2
    3 60813868 T C intronico FHIT 0,001 1 2
    3 60826546 C G intronico FHIT 0,0023 1 2
    3 60837392 C T intronico FHIT 0,001 1 2
    3 60846310 A G intronico FHIT 0,01 0 2
    3 60850985 C T intronico FHIT 0,004 1 2
    3 60852559 T C intronico FHIT 0,008 1 2
    3 60871759 T C intronico FHIT 0,004 1 2
    3 60884396 C T intronico FHIT 0,002 2 4
    3 60897092 C A intronic FHIT 0,001 2 4
    3 60940759 C T intronico FHIT 0,0089 1 2
    3 60982595 A G intronico FHIT 0.003 2 4
    3 60999283 G A intronic FHIT 0,001 1 2
    3 61042977 A G intronico FHIT 0,001 2 4
    3 61043349 T C intronico FHIT 0,001 2 4
    3 61044789 A C intronico FHIT 0,001 2 4
    3 61141621 G A intronic FHIT 0.003 1 2
    3 61148655 G C intronico FHIT 0,001 2 4
    3 61170747 C T intronico FHIT 0.003 1 2
    3 61189473 C G intronico FHIT 0,0099 1 2
    3 61190425 C T intronico FHIT 0,0023 2 4
    3 61193853 C T intronico FHIT 0,0099 0 2
    3 61194793 C T intronico FHIT 0,007 0 2
    3 61194840 A G intronico FHIT 0,0099 0 2
    3 61194886 T A intronic FHIT 0,0099 0 2
    3 61201777 C T intronico FHIT 0,001 2 4
    3 61202292 T C intronico FHIT 0,007 1 2
    3 61232806 G C intronico FHIT 0,0099 1 2
    3 61232910 C T intronico FHIT 0,0099 1 2
    3 61235824 A T intronico FHIT 0,001 2 4
    3 61283810 A C intergênico FHIT, PTPRG 0,0089 1 2
    3 61293731 T A intergênico FHIT, PTPRG 0,0089 2 4
    3 61296730 C T intergênico FHIT, PTPRG 0,001 1 2
    3 61326341 C T intergênico FHIT,PTPRG 0,004 2 4
    3 61326620 T C intergênico FHIT,PTPRG 0,01 1 2
    3 61327649 G C intergênico FHIT, PTPRG 0,001 2 4
    3 61330545 G C intergênico FHIT, PTPRG 0,001 2 4
    3 61335803 G A intergênico FHIT, PTPRG 0,001 2 4

    Uma consulta semelhante usando dados de Fase 3 do 1.000 Genomas leva cerca de 90 segundos para ser executado e processa cerca de 3,38 TB de dados.

Usar uma tabela materializada

Ao trabalhar com Big Data em escala, é possível materializar uma tabela de intervalos 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 Google 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. Não altere as outras configurações.
    3. Clique em Criar conjunto de dados.

Consultar uma tabela materializada com genes específicos

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

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

    1. Abra a página do BigQuery no console do Google Cloud.

      Acessar a página do BigQuery

    2. Clique em Escrever consulta.

    3. No campo Nova consulta, execute a seguinte consulta. A consulta materializa parte da tabela silver-wall-555:TuteTable.hg19 em uma nova tabela de intervalo 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 Executar consulta. 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 Executar consulta. 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.

    Abra a seção a seguir para conferir os resultados da consulta:

    Resultados da consulta

    Chr Iniciar Ref Alt Função Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C intronic RRNAD1 0,002 2 4
    1 156705390 C T intronic RRNAD1 8.0E-4 0 2
    1 156714207 T C intronic HDGF 0.003 0 6
    1 156714440 A C intronic HDGF 0,0068 0 12
    1 156723870 C T intergênico HDGF, PRCC 0,006 1 2
    1 156724456 C T intergênico HDGF,PRCC 0,002 2 4
    1 156733988 C T intergênico HDGF,PRCC 0,001 1 2
    1 156742258 T G intronic PRCC 0,001 2 4
    1 156744826 T G intronic PRCC 0,002 0 8
    1 156779764 G A intronic SH2D2A 0,001 2 4
    1 156783454 A C intronico SH2D2A 0,0014 1 2
    1 156786144 C T intronico NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T intronic NTRK1 0,002 1 2
    1 156815332 A C intronic INSRR,NTRK1 0.003 0 2
    1 156830778 G A exônico NTRK1 0,0067 missense 2 4
    1 156842064 C T intronic NTRK1 0,0014 1 2
    1 156843438 C A exônico NTRK1 0,0032 missense 1 2
    1 156845773 C T intronic NTRK1 0,001 2 4
    1 156873318 T C intronico PEAR1 0,01 4 8
    1 156922740 G A intronic ARHGEF11 0,007 1 2
    1 156930100 C T intronic ARHGEF11 0,001 2 4
    2 113901230 G A intergênico IL1RN e PSD4 0,0082 1 2
    2 113953418 C A intronico PSD4 0,001 2 4
    2 113967621 G C intergênico PSD4 e PAX8 0,002 0 6
    2 113967624 T C intergênico PSD4 e 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ênico PAX8,CBWD2 0,001 2 4
    2 114084018 A C intergênico PAX8,CBWD2 0,0045 0 4
    2 114099037 G A intergênico PAX8,CBWD2 0,0051 1 2
    2 114105670 A T intergênico PAX8,CBWD2 0,001 1 2
    2 114111325 G T intergênico PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergênico SYN2,PPARG 0,0089 2 4
    3 12277958 A G intergênico SYN2,PPARG 0,002 1 2
    3 12296019 G A intergênico SYN2,PPARG 0,002 2 4
    3 12316549 G C intergênico SYN2,PPARG 0,002 1 2
    3 12335681 T G intronic PPARG 0,0092 2 4
    3 12348795 T C intronico PPARG 0,0014 1 2
    3 12353106 T C intronico PPARG 0,001 2 4
    3 12403825 G A intronic PPARG 0,0051 2 4
    3 12404394 G A intronic PPARG 0,001 1 2
    3 12410289 G A intronic PPARG 0,008 2 4
    3 12431381 C T intronico PPARG 0,0061 2 4
    3 12447267 G A intronic PPARG 0,0089 2 4
    3 12449379 C T intronico PPARG 0,0092 2 4
    3 12450848 C A intronic PPARG 0,0092 2 4
    3 12462847 T C intronico PPARG 0,002 1 2
    3 12492797 G A intergênico PPARG,TSEN2 0,01 1 2
    3 12503201 G A intergênico PPARG,TSEN2 0,0099 2 4
    3 12530460 A G intronic TSEN2 0,0092 2 4
    3 12531167 A G intronic TSEN2 0,0099 2 4
    3 12557737 A G intronic TSEN2 0,001 2 4
    3 59636143 A G intergênico C3orf67,FHIT 0.003 3 6
    3 59645934 A C intergênico C3orf67,FHIT 0,004 1 2
    3 59646893 G A intergênico C3orf67,FHIT 0,002 1 2
    3 59697024 A G intergênico C3orf67,FHIT 0,0072 1 2
    3 59701013 G A intergênico C3orf67,FHIT 0,004 2 4
    3 59733945 A G intergênico C3orf67,FHIT 0,001 2 4
    3 59747482 C T intronico FHIT 0,001 2 4
    3 59750635 A G intronico FHIT 0.003 1 2
    3 59757776 C T intronico FHIT 0,001 2 4
    3 59770612 G A intronic FHIT 0,001 2 4
    3 59804444 G C intronico FHIT 0,001 2 4
    3 59819769 T C intronico FHIT 0,001 2 4
    3 59884396 C T intronico FHIT 0,001 2 4
    3 59960728 A C intronico FHIT 0,01 1 2
    3 59970345 G A intronic FHIT 0,002 1 2
    3 59972417 T A intronic FHIT 0,0072 0 2
    3 60104328 C A intronic FHIT 0,01 2 4
    3 60139062 G A intronic FHIT 0,01 0 2
    3 60158066 C T intronico FHIT 0,001 1 2
    3 60169285 C T intronico FHIT 0,005 1 2
    3 60216185 T C intronico FHIT 0,002 1 2
    3 60226380 G A intronic FHIT 0,007 2 4
    3 60234539 C A intronic FHIT 0,002 1 2
    3 60247464 A C intronico FHIT 0,004 2 4
    3 60269926 A G intronico FHIT 0,007 2 4
    3 60271228 G T intronico FHIT 0,007 2 4
    3 60286972 T C intronico FHIT 0,001 2 4
    3 60301412 C G intronico FHIT 0,001 1 2
    3 60312251 C T intronico FHIT 0,0099 1 2
    3 60317682 A G intronico FHIT 0,008 1 2
    3 60328557 C G intronico FHIT 0,0043 2 4
    3 60342562 C T intronico FHIT 0,006 1 2
    3 60400033 G A intronic FHIT 0,004 2 4
    3 60435819 C T intronico FHIT 0,006 2 4
    3 60435820 G T intronico FHIT 0,004 1 2
    3 60441288 T C intronico FHIT 0,006 2 4
    3 60444465 C A intronic FHIT 0,01 1 2
    3 60444575 C T intronico FHIT 0,001 1 2
    3 60450581 T C intronico FHIT 0,01 1 2
    3 60456571 G A intronic FHIT 0,001 2 4
    3 60473568 C G intronico FHIT 0,001 1 2
    3 60487557 T C intronico FHIT 0,001 1 2
    3 60559705 A G intronico FHIT 0,002 2 4
    3 60570764 T C intronico FHIT 0,008 2 4
    3 60582100 C T intronico FHIT 0,001 1 2
    3 60587192 G A intronic FHIT 0,004 1 2
    3 60599869 G A intronic FHIT 0,0086 2 4
    3 60603091 C T intronico FHIT 0,001 2 4
    3 60603250 A T intronico FHIT 0,0099 1 2
    3 60609831 T G intronico FHIT 0,001 2 4
    3 60619756 G T intronico FHIT 0,0015 2 4
    3 60680758 C T intronico FHIT 0,0089 2 4
    3 60702243 G C intronico FHIT 0,001 2 4
    3 60702532 A G intronico FHIT 0,001 1 2
    3 60714328 A T intronico FHIT 0,004 1 2
    3 60725297 G A intronic FHIT 0,001 1 2
    3 60726640 G A intronic FHIT 0,01 2 4
    3 60795144 A G intronico FHIT 0,001 2 4
    3 60807171 A G intronico FHIT 0,001 1 2
    3 60813868 T C intronico FHIT 0,001 1 2
    3 60826546 C G intronico FHIT 0,0023 1 2
    3 60837392 C T intronico FHIT 0,001 1 2
    3 60846310 A G intronico FHIT 0,01 0 2
    3 60850985 C T intronico FHIT 0,004 1 2
    3 60852559 T C intronico FHIT 0,008 1 2
    3 60871759 T C intronico FHIT 0,004 1 2
    3 60884396 C T intronico FHIT 0,002 2 4
    3 60897092 C A intronic FHIT 0,001 2 4
    3 60940759 C T intronico FHIT 0,0089 1 2
    3 60982595 A G intronico FHIT 0.003 2 4
    3 60999283 G A intronic FHIT 0,001 1 2
    3 61042977 A G intronico FHIT 0,001 2 4
    3 61043349 T C intronico FHIT 0,001 2 4
    3 61044789 A C intronico FHIT 0,001 2 4
    3 61141621 G A intronic FHIT 0.003 1 2
    3 61148655 G C intronico FHIT 0,001 2 4
    3 61170747 C T intronico FHIT 0.003 1 2
    3 61189473 C G intronico FHIT 0,0099 1 2
    3 61190425 C T intronico FHIT 0,0023 2 4
    3 61193853 C T intronico FHIT 0,0099 0 2
    3 61194793 C T intronico FHIT 0,007 0 2
    3 61194840 A G intronico FHIT 0,0099 0 2
    3 61194886 T A intronic FHIT 0,0099 0 2
    3 61201777 C T intronico FHIT 0,001 2 4
    3 61202292 T C intronico FHIT 0,007 1 2
    3 61232806 G C intronico FHIT 0,0099 1 2
    3 61232910 C T intronico FHIT 0,0099 1 2
    3 61235824 A T intronico FHIT 0,001 2 4
    3 61283810 A C intergênico FHIT, PTPRG 0,0089 1 2
    3 61293731 T A intergênico FHIT, PTPRG 0,0089 2 4
    3 61296730 C T intergênico FHIT, PTPRG 0,001 1 2
    3 61326341 C T intergênico FHIT,PTPRG 0,004 2 4
    3 61326620 T C intergênico FHIT,PTPRG 0,01 1 2
    3 61327649 G C intergênico FHIT, PTPRG 0,001 2 4
    3 61330545 G C intergênico FHIT, PTPRG 0,001 2 4
    3 61335803 G A intergênico FHIT, PTPRG 0,001 2 4

    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.

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

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

    1. Abra a página do BigQuery no console do Google Cloud.

      Acessar a página do BigQuery

    2. Clique em Escrever consulta.

    3. No campo Nova consulta, execute a consulta a seguir, que materializa parte da tabela silver-wall-555:TuteTable.hg19 em uma nova tabela de intervalo 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 Executar consulta. 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 Executar consulta. 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 conferir os resultados truncados da consulta:

    Resultados da consulta

    Chr Iniciar Ref Alt Função Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 2925355 C A intergênico TTC34,ACTRT2 0,001 2 4
    1 2933170 G A intergênico TTC34,ACTRT2 0,0083 0 4
    1 2944477 G A intergênico ACTRT2,LINC00982 0.003 4 6
    1 2967591 A T intergênico ACTRT2,LINC00982 0,0092 1 2
    1 2975255 T C downstream 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 intronico PRDM16 0,0098 1 2
    1 3011333 G T intronico PRDM16 0,004 1 2
    1 3019659 C T intronico PRDM16 0,0031 1 2
    1 3036896 G A intronico PRDM16 0,001 1 2
    1 3037388 G A intronico PRDM16 0,002 2 4
    1 3041250 T G intronico PRDM16 0,006 2 4
    1 3042502 A T intronico PRDM16 0.003 4 6
    1 3053713 A C intronico PRDM16 0,002 1 2
    1 3063109 C T intronico PRDM16 0,002 0 2
    1 3063593 T C intronico PRDM16 0.003 1 2
    1 3076439 C T intronico PRDM16 0,001 2 4
    1 3078960 G A intronico PRDM16 0,007 2 4
    1 3084268 A C intronico PRDM16 0,005 0 2
    1 3084492 T C intronico PRDM16 0,0015 0 2
    1 3084786 T C intronico PRDM16 0,0015 0 4
    1 3111119 G A intronico PRDM16 0.003 1 2
    1 3111643 C T intronico PRDM16 0,0041 1 2
    1 3114807 G A intronico PRDM16 0,0041 1 2
    1 3165530 C T intronico PRDM16 0,0089 1 2
    1 3169325 G A intronico PRDM16 0,008 2 4
    1 3179623 C T intronico PRDM16 0.003 2 4
    1 3181097 C T intronico PRDM16 0,001 2 4
    1 3194000 G C intronico PRDM16 0,005 2 4
    1 3195769 T C intronico PRDM16 0,002 1 2
    1 3197351 C T intronico PRDM16 0,0061 1 2
    1 3224100 C A intronico PRDM16 0.003 2 4
    1 3228644 G T intronico PRDM16 0,001 2 4
    1 3234045 G A intronico PRDM16 0,002 1 2
    1 3235971 G A intronico PRDM16 0,0089 1 2
    1 3274115 C T intronico PRDM16 0,001 2 4
    1 3291388 G A intronico PRDM16 0,002 2 4
    1 3295658 A C intronico PRDM16 0,0068 0 6
    1 3295937 A C intronico PRDM16 0,0068 0 2
    1 3296205 T C intronico PRDM16 0,0083 0 2
    1 3315690 G A intronico PRDM16 0,001 2 4
    1 3329212 G A exônico PRDM16 0,0031 missense 1 2
    1 3331787 C T intronico PRDM16 0,0099 1 2
    1 3370316 G C upstream ARHGEF16 0,001 2 4
    1 3379560 A G intronico ARHGEF16 0,0051 0 6
    1 3391174 C T intronico ARHGEF16 0,006 1 2
    1 3413873 G A exônico MEGF6 0.003 missense 1 2
    1 3416272 C T exônico MEGF6 0,0072 silencioso 2 4
    1 3417122 G A intronico MEGF6 0,0038 2 4
    1 3436219 G A intronico MEGF6 0,0046 2 4
    1 12907456 A G exônico HNRNPCL1,LOC649330 0,006 missense 0 10
    1 12907518 C A exônico HNRNPCL1,LOC649330 1,0E-4 missense 0 10
    1 12908499 G C intronic HNRNPCL1 0,0031 0 8
    1 12931660 G C intergênico PRAMEF2,PRAMEF4 0,004 1 2
    1 12937721 G T intergênico PRAMEF2,PRAMEF4 0,0038 0 2
    1 12940827 G T intronico PRAMEF4 0,007 2 4
    1 12942759 T G intronico PRAMEF4 0,0076 0 10
    1 12942805 T G intronico PRAMEF4 0,0061 0 12
    1 12942812 G A intronic PRAMEF4 0,0061 0 12
    1 12942875 A G intronico PRAMEF4 0,0068 0 6
    1 12942912 G C intronico PRAMEF4 2,0E-4 0 2
    1 12942937 A T exônico PRAMEF4 0,0029 missense 0 2
    1 12942940 T G exônico PRAMEF4 0,0038 missense 0 2
    1 12943940 T C intronico PRAMEF4 0,0015 0 12
    1 12944138 A G intronico PRAMEF4 8,0E-4 0 12
    1 12944234 G A intronic PRAMEF4 0,0015 0 12
    1 12944589 T G intronico PRAMEF4 0.003 0 4
    1 12944845 A C intronico 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ênico 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ênico PRAMEF6,LOC391003 0,0015 0 10
    1 13038503 G A UTR3 LOC391003 0,0072 1 2
    1 13051650 C T intergênico LOC391003,PRAMEF5 0,002 2 4
    1 15706063 G A intronico FHAD1 0,0029 1 2
    1 15713292 C T intronico FHAD1 0,001 1 2
    1 15766541 G C intronico CTRC 0,001 1 2
    1 15782601 T C upstream CELA2A 0,0038 1 2
    1 15828125 G A intronic CASP9 0,0014 2 4
    1 15831037 G A intronic CASP9 0,0099 1 2
    1 15840513 T G intronico CASP9 0,0043 2 4
    1 15868742 G A intronic DNAJC16 0,001 1 2
    1 15876704 G A intronic DNAJC16 0,001 1 2
    1 15900342 C A intronic AGMAT 0,001 1 2
    1 15906257 T C intronico AGMAT 8,0E-4 1 2
    1 15911897 A G upstream AGMAT 0,0043 2 4
    1 22764178 C T intergênico WNT4,ZBTB40 0,001 2 4
    1 22791939 C T intronic ZBTB40 0,0089 2 4
    1 22874394 C G intergênico ZBTB40,EPHA8 0,007 1 2
    1 22875103 C G intergênico ZBTB40,EPHA8 0,007 1 2
    1 22906403 C T intronico EPHA8 0,008 2 4
    1 22912956 G A intronico EPHA8 0,001 1 2
    1 22917007 C T intronico EPHA8 0,001 2 4
    1 22927240 G A exônico EPHA8 0,0013 missense 2 4
    1 22932265 G A intergênico EPHA8, MIR6127 0,0089 2 4
    1 22944057 C T intergênico EPHA8, MIR6127 0,0089 2 4
    1 22978799 A G upstream C1QB 0,0099 2 4
    1 35170588 C T intergênico C1orf94,GJB5 0,01 1 2
    1 35172426 C T intergênico C1orf94,GJB5 0,008 1 2
    1 35172447 G A intergênico C1orf94,GJB5 0,001 1 2
    1 35175302 C T intergênico C1orf94,GJB5 0,008 1 2
    1 35177410 A T intergênico C1orf94,GJB5 0,001 1 2
    1 35178768 C T intergênico C1orf94,GJB5 0,0014 2 4
    1 35179362 G A intergênico C1orf94,GJB5 0,0014 2 4
    1 35186166 G A intergênico C1orf94,GJB5 0,0099 2 4
    1 35186520 A C intergênico C1orf94,GJB5 0,002 2 4
    1 35196361 G A intergênico C1orf94,GJB5 0,0099 2 4
    1 35223545 C T exônico GJB5 0,001 silencioso 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ênico GJB4 e GJB3 0,0043 1 2
    1 35232954 T C intergênico GJB4 e GJB3 0.003 1 2
    1 35237986 G A intergênico GJB4, GJB3 0,0014 1 2
    1 35245522 C T intergênico GJB4, GJB3 0,001 1 2
    1 35256979 C T intergênico GJB3 e GJA4 0,002 2 4
    1 35263872 C T intergênico GJA4,SMIM12 5,0E-4 2 4
    1 35323895 A C intronico SMIM12 0,0027 2 4
    1 35369676 G A intronic DLGAP3 0,007 2 4
    1 35371634 T A upstream DLGAP3 0,0015 0 4
    1 39253519 G A intergênico LINC01343,RRAGC 0,005 1 2
    1 39288829 G A intergênico LINC01343,RRAGC 0,0051 1 2
    1 39289832 A C intergênico LINC01343,RRAGC 0,002 0 2
    1 39312638 G A intronico RRAGC 0,0038 2 4
    1 39361372 G A intronic RHBDL2 0,005 1 2
    1 39363826 T G intronic RHBDL2 0,0029 1 2
    1 39367555 T C intronic RHBDL2 0,007 2 4
    1 39369531 T C intronic RHBDL2 0,001 2 4
    1 39370202 T C intronic RHBDL2 0,01 1 2
    1 39449101 A G intergênico RHBDL2,AKIRIN1 0,001 2 4
    1 39475057 G A intergênico AKIRIN1,NDUFS5 0,01 1 2
    1 39485016 C T intergênico AKIRIN1,NDUFS5 0,001 2 4
    1 39488137 A G intergênico AKIRIN1,NDUFS5 0,001 2 4
    1 39499212 A C intronic NDUFS5 0,001 0 2
    1 39500605 C G downstream NDUFS5 0,002 0 10
    1 46813814 T C intronic NSUN4 0,0014 1 2
    1 46817258 A G intronic NSUN4 0,005 0 2
    1 46843158 T C intergênico NSUN4,FAAH 0,001 1 2
    1 46933509 A G intergênico LINC01398,DMBX1 0,002 1 2
    1 46935021 G A intergênico LINC01398,DMBX1 0,004 2 4
    1 46939253 T A intergênico LINC01398,DMBX1 0,004 2 4
    1 46951788 C A intergênico LINC01398,DMBX1 0,002 2 4
    1 46980864 G C downstream DMBX1 0.003 1 2
    1 46989657 T C intergênico DMBX1,MKNK1-AS1 0,007 1 2
    1 46994678 C T intergênico DMBX1,MKNK1-AS1 0,002 1 2
    1 46999438 T C intergênico DMBX1,MKNK1-AS1 0,002 1 2
    1 92761505 A G intronic GLMN 0,001 2 4
    1 92764270 G C intronico GLMN 0,001 2 4
    1 92802210 G A intronico RPAP2 0,0072 1 2
    1 92820663 T A intronico RPAP2 0,0058 1 2
    1 92820664 G T intronico RPAP2 0,0058 1 2
    1 92820953 G A intronico RPAP2 0,007 2 4
    1 92824766 A G intronic RPAP2 0,0058 1 2
    1 92849183 C A intronico RPAP2 0,01 2 4
    1 92850696 C G intronic RPAP2 0,0023 1 2
    1 92861357 T C intergênico RPAP2,GFI1 0,01 2 4
    1 92877460 C G intergênico RPAP2 e GFI1 0,002 1 2
    1 92880643 A G intergênico RPAP2 e GFI1 0,001 2 4
    1 92911540 G A intergênico RPAP2 e GFI1 0,004 2 4
    1 92911721 A C intergênico RPAP2 e GFI1 0,0031 0 8
    1 92918277 C T intergênico RPAP2 e GFI1 0,001 2 4
    1 92950920 G A intronico GFI1 0,008 2 4
    1 92964788 G A intergênico GFI1,EVI5 0,0023 1 2
    1 92977480 C T UTR3 EVI5 0,002 1 2
    1 92985213 C T intronico EVI5 0,001 2 4
    1 92988342 C T intronico EVI5 0,008 2 4
    1 92992283 G A intronico EVI5 0,01 2 4
    1 92999760 C T intronico EVI5 0.003 1 2
    1 93005149 G C intronic EVI5 0.003 0 4
    1 93018543 A T intronico EVI5 0,01 2 4
    1 93033744 C T intronico EVI5 0,001 2 4
    1 111400296 G A intergênico KCNA3 e CD53 0,0014 2 4
    1 111411924 C T intergênico KCNA3 e CD53 0.003 1 2
    1 111441850 C G UTR3 CD53 0.003 2 4
    1 111451527 C T intergênico CD53,LRIF1 0,008 2 4
    1 111454082 C A intergênico CD53/LRIF1 0,001 2 4
    1 111466506 A G intergênico CD53/LRIF1 0,001 2 4
    1 111525974 G A intergênico LRIF1 e DRAM2 0,002 2 4
    1 111574573 G T intergênico LRIF1, DRAM2 0,0072 2 4
    1 111574594 T A intergênico LRIF1 e DRAM2 0,005 1 2
    1 111574647 G A intergênico LRIF1 e DRAM2 0,005 1 2
    1 111591746 T A intergênico LRIF1 e DRAM2 0,005 1 2
    1 111601459 A G intergênico LRIF1 e DRAM2 0,005 1 2
    1 111604748 G C intergênico LRIF1 e DRAM2 0,005 1 2
    1 112191526 T G intronic RAP1A 0,001 2 4
    1 112206765 A G intronic RAP1A 0,0043 1 2
    1 112226517 G A intronico RAP1A 0,001 0 2
    1 112263324 G T intergênico RAP1A,FAM212B 0.003 2 4
    1 112264843 G A UTR3 FAM 212 0,001 1 2
    1 112285810 C T ncRNA_intronic FAM212B-AS1 0,004 1 2
    1 112304285 T C intronico DDX20 0,0043 1 2
    1 112307213 A C intronico DDX20 0,0043 1 2
    1 112309436 G T exônico DDX20 0,0 missense 1 2
    1 112317384 T C intergênico DDX20,KCND3 0,0014 1 2
    1 112381367 C T intronic KCND3 0,002 1 2
    1 112396571 G T ncRNA_exonic KCND3-IT1 0,001 1 2
    1 113520038 G A intergênico SLC16A1-AS1,LOC100996251 0,0023 1 2

    Uma consulta semelhante usando dados de Fase 3 do 1.000 Genomas leva cerca de 90 segundos para ser executado e processa cerca de 3,38 TB de dados.