Ejecución de operaciones de JOIN en intervalos con BigQuery

Puedes usar BigQuery para ejecutar una consulta de JOIN en las variantes con los datos descritos por intervalos de región genómica o superposiciones. En esta página, se explica cómo usar una consulta de JOIN compleja para tomar una lista de genes humanos y hacer lo siguiente:

  • Busca los SNP poco frecuentes que se superponen.
  • Encontrar 100,000 pares de bases en cada lado de un gen para las muestras de genomas completos.

En esta guía, se muestran ejemplos de tres consultas. Cada consulta muestra cómo BigQuery escala de acuerdo con distintos tamaños de datos genómicos:

Los datos provienen de la tabla de anotaciones de Tute Genomics de casi 9 mil millones y el conjunto de datos de Platinum Genomes de Illumina. Si no estás familiarizado con estos conjuntos de datos, consulta estos vínculos:

Consulta una tabla intercalada

En el siguiente ejemplo, se utiliza una tabla de intervalo definida en la consulta, llamada intervals, y muestra cómo ejecutar una consulta de JOIN con una tabla que contiene variantes provenientes de Platinum Genomes de Illumina.:

  1. Ve a la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. Haz clic en Redactar consulta.

  3. En el campo Consulta nueva, ejecuta la siguiente 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. Haga clic en Run query. La consulta tarda unos diez segundos en ejecutarse y procesa alrededor de 334 GB de datos. Los resultados de la consulta identifican variantes poco frecuentes dentro de la cohorte que se superponen en las regiones de interés.

    Expande la siguiente sección para ver los resultados de la consulta:

    Resultados de la consulta

    Chr Inicio Ref Alt Func Gen PopFreqMax ExonicFunc núm_variant_alleles total_núm_alelos
    1 156699757 T C introductoria RRNAD1 0.002 2 4
    1 156705390 C T introductoria RRNAD1 8.0E-4 0 2
    1 156714207 T C introductoria HDGF 0.003 0 6
    1 156714440 A C introductoria HDGF 0.0068 0 12
    1 156723870 C T intergenético HDGF,PRCC 0.006 1 2
    1 156724456 C T intergenético HDGF,PRCC 0.002 2 4
    1 156733988 C T intergenético HDGF,PRCC 0.001 1 2
    1 156742258 T G introductoria PRCC 0.001 2 4
    1 156744826 T G introductoria PRCC 0.002 0 8
    1 156779764 G A introductoria SH2D2A 0.001 2 4
    1 156783454 A C introductoria SH2D2A 0.0014 1 2
    1 156786144 C T introductoria NTRK1,SH2D2A 0.0031 2 4
    1 156790510 A T introductoria NTRK1 0.002 1 2
    1 156815332 A C introductoria INTELR,NTRK1 0.003 0 2
    1 156830778 G A exontivo NTRK1 0.0067 discoble 2 4
    1 156842064 C T introductoria NTRK1 0.0014 1 2
    1 156843438 C A exontivo NTRK1 0.0032 discoble 1 2
    1 156845773 C T introductoria NTRK1 0.001 2 4
    1 156873318 T C introductoria PEAR1 0.01 4 8
    1 156922740 G A introductoria ARHGEF11 0.007 1 2
    1 156930100 C T introductoria ARHGEF11 0.001 2 4
    2 113901230 G A intergenético IL1RN,PSD4 0.0082 1 2
    2 113953418 C A introductoria PSD4 0.001 2 4
    2 113967621 G C intergenético PSD4,PAX8 0.002 0 6
    2 113967624 T C intergenético PSD4,PAX8 0.002 0 2
    2 113980967 G A introductoria PAX8 0.002 2 4
    2 113994010 A C NTRN_exonía PAX8-AS1 0.001 0 4
    2 113997745 C A NTRN_exonía PAX8-AS1 0.001 2 4
    2 114061327 T C intergenético PAX8,CBWD2 0.001 2 4
    2 114084018 A C intergenético PAX8,CBWD2 0.0045 0 4
    2 114099037 G A intergenético PAX8,CBWD2 0.0051 1 2
    2 114105670 A T intergenético PAX8,CBWD2 0.001 1 2
    2 114111325 G T intergenético PAX8,CBWD2 0.001 1 2
    3 12265797 C T intergenético SYN2,PPARG 0.0089 2 4
    3 12277958 A G intergenético SYN2,PPARG 0.002 1 2
    3 12296019 G A intergenético SYN2,PPARG 0.002 2 4
    3 12316549 G C intergenético SYN2,PPARG 0.002 1 2
    3 12335681 T G introductoria PAGO 0.0092 2 4
    3 12348795 T C introductoria PAGO 0.0014 1 2
    3 12353106 T C introductoria PAGO 0.001 2 4
    3 12403825 G A introductoria PAGO 0.0051 2 4
    3 12404394 G A introductoria PAGO 0.001 1 2
    3 12410289 G A introductoria PAGO 0.008 2 4
    3 12431381 C T introductoria PAGO 0.0061 2 4
    3 12447267 G A introductoria PAGO 0.0089 2 4
    3 12449379 C T introductoria PAGO 0.0092 2 4
    3 12450848 C A introductoria PAGO 0.0092 2 4
    3 12462847 T C introductoria PAGO 0.002 1 2
    3 12492797 G A intergenético PPARG,TSEN2 0.01 1 2
    3 12503201 G A intergenético PPARG,TSEN2 0.0099 2 4
    3 12530460 A G introductoria TSEN2 0.0092 2 4
    3 12531167 A G introductoria TSEN2 0.0099 2 4
    3 12557737 A G introductoria TSEN2 0.001 2 4
    3 59636143 A G intergenético C3orf67,FHIT 0.003 3 6
    3 59645934 A C intergenético C3orf67,FHIT 0.004 1 2
    3 59646893 G A intergenético C3orf67,FHIT 0.002 1 2
    3 59697024 A G intergenético C3orf67,FHIT 0.0072 1 2
    3 59701013 G A intergenético C3orf67,FHIT 0.004 2 4
    3 59733945 A G intergenético C3orf67,FHIT 0.001 2 4
    3 59747482 C T introductoria GRAVE 0.001 2 4
    3 59750635 A G introductoria GRAVE 0.003 1 2
    3 59757776 C T introductoria GRAVE 0.001 2 4
    3 59770612 G A introductoria GRAVE 0.001 2 4
    3 59804444 G C introductoria GRAVE 0.001 2 4
    3 59819769 T C introductoria GRAVE 0.001 2 4
    3 59884396 C T introductoria GRAVE 0.001 2 4
    3 59960728 A C introductoria GRAVE 0.01 1 2
    3 59970345 G A introductoria GRAVE 0.002 1 2
    3 59972417 T A introductoria GRAVE 0.0072 0 2
    3 60104328 C A introductoria GRAVE 0.01 2 4
    3 60139062 G A introductoria GRAVE 0.01 0 2
    3 60158066 C T introductoria GRAVE 0.001 1 2
    3 60169285 C T introductoria GRAVE 0.005 1 2
    3 60216185 T C introductoria GRAVE 0.002 1 2
    3 60226380 G A introductoria GRAVE 0.007 2 4
    3 60234539 C A introductoria GRAVE 0.002 1 2
    3 60247464 A C introductoria GRAVE 0.004 2 4
    3 60269926 A G introductoria GRAVE 0.007 2 4
    3 60271228 G T introductoria GRAVE 0.007 2 4
    3 60286972 T C introductoria GRAVE 0.001 2 4
    3 60301412 C G introductoria GRAVE 0.001 1 2
    3 60312251 C T introductoria GRAVE 0.0099 1 2
    3 60317682 A G introductoria GRAVE 0.008 1 2
    3 60328557 C G introductoria GRAVE 0.0043 2 4
    3 60342562 C T introductoria GRAVE 0.006 1 2
    3 60400033 G A introductoria GRAVE 0.004 2 4
    3 60435819 C T introductoria GRAVE 0.006 2 4
    3 60435820 G T introductoria GRAVE 0.004 1 2
    3 60441288 T C introductoria GRAVE 0.006 2 4
    3 60444465 C A introductoria GRAVE 0.01 1 2
    3 60444575 C T introductoria GRAVE 0.001 1 2
    3 60450581 T C introductoria GRAVE 0.01 1 2
    3 60456571 G A introductoria GRAVE 0.001 2 4
    3 60473568 C G introductoria GRAVE 0.001 1 2
    3 60487557 T C introductoria GRAVE 0.001 1 2
    3 60559705 A G introductoria GRAVE 0.002 2 4
    3 60570764 T C introductoria GRAVE 0.008 2 4
    3 60582100 C T introductoria GRAVE 0.001 1 2
    3 60587192 G A introductoria GRAVE 0.004 1 2
    3 60599869 G A introductoria GRAVE 0.0086 2 4
    3 60603091 C T introductoria GRAVE 0.001 2 4
    3 60603250 A T introductoria GRAVE 0.0099 1 2
    3 60609831 T G introductoria GRAVE 0.001 2 4
    3 60619756 G T introductoria GRAVE 0.0015 2 4
    3 60680758 C T introductoria GRAVE 0.0089 2 4
    3 60702243 G C introductoria GRAVE 0.001 2 4
    3 60702532 A G introductoria GRAVE 0.001 1 2
    3 60714328 A T introductoria GRAVE 0.004 1 2
    3 60725297 G A introductoria GRAVE 0.001 1 2
    3 60726640 G A introductoria GRAVE 0.01 2 4
    3 60795144 A G introductoria GRAVE 0.001 2 4
    3 60807171 A G introductoria GRAVE 0.001 1 2
    3 60813868 T C introductoria GRAVE 0.001 1 2
    3 60826546 C G introductoria GRAVE 0.0023 1 2
    3 60837392 C T introductoria GRAVE 0.001 1 2
    3 60846310 A G introductoria GRAVE 0.01 0 2
    3 60850985 C T introductoria GRAVE 0.004 1 2
    3 60852559 T C introductoria GRAVE 0.008 1 2
    3 60871759 T C introductoria GRAVE 0.004 1 2
    3 60884396 C T introductoria GRAVE 0.002 2 4
    3 60897092 C A introductoria GRAVE 0.001 2 4
    3 60940759 C T introductoria GRAVE 0.0089 1 2
    3 60982595 A G introductoria GRAVE 0.003 2 4
    3 60999283 G A introductoria GRAVE 0.001 1 2
    3 61042977 A G introductoria GRAVE 0.001 2 4
    3 61043349 T C introductoria GRAVE 0.001 2 4
    3 61044789 A C introductoria GRAVE 0.001 2 4
    3 61141621 G A introductoria GRAVE 0.003 1 2
    3 61148655 G C introductoria GRAVE 0.001 2 4
    3 61170747 C T introductoria GRAVE 0.003 1 2
    3 61189473 C G introductoria GRAVE 0.0099 1 2
    3 61190425 C T introductoria GRAVE 0.0023 2 4
    3 61193853 C T introductoria GRAVE 0.0099 0 2
    3 61194793 C T introductoria GRAVE 0.007 0 2
    3 61194840 A G introductoria GRAVE 0.0099 0 2
    3 61194886 T A introductoria GRAVE 0.0099 0 2
    3 61201777 C T introductoria GRAVE 0.001 2 4
    3 61202292 T C introductoria GRAVE 0.007 1 2
    3 61232806 G C introductoria GRAVE 0.0099 1 2
    3 61232910 C T introductoria GRAVE 0.0099 1 2
    3 61235824 A T introductoria GRAVE 0.001 2 4
    3 61283810 A C intergenético PTITITIN, 0.0089 1 2
    3 61293731 T A intergenético PTITITIN, 0.0089 2 4
    3 61296730 C T intergenético PTITITIN, 0.001 1 2
    3 61326341 C T intergenético PTITITIN, 0.004 2 4
    3 61326620 T C intergenético PTITITIN, 0.01 1 2
    3 61327649 G C intergenético PTITITIN, 0.001 2 4
    3 61330545 G C intergenético PTITITIN, 0.001 2 4
    3 61335803 G A intergenético PTITITIN, 0.001 2 4

    Una consulta similar que use los datos de la fase 3 de 1,000 genomas tarda aproximadamente 90 segundos en ejecutarse y procesa alrededor de 3.38 TB de datos.

Usa una tabla materializada

Cuando trabajas con macrodatos a gran escala, puedes materializar una tabla de intervalo y ejecutar una consulta de JOIN en la tabla nueva. Antes de continuar con el resto de esta sección, crea un conjunto de datos mediante los siguientes pasos:

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página de BigQuery

  2. En el panel Explorador, selecciona el proyecto en el que deseas crear el conjunto de datos.

  3. Expande la opción Acciones y haz clic en Crear conjunto de datos.

  4. En la página Crear conjunto de datos:

    1. En ID del conjunto de datos, ingresa genomics.
    2. Deja el resto de la configuración predeterminada como está.
    3. Haga clic en Crear conjunto de datos.

Consulta en una tabla materializada con genes específicos.

En los siguientes pasos, se muestra cómo materializar una tabla de intervalo nueva que contenga una lista de genes específicos de la tabla silver-wall-555:TuteTable.hg19.

  1. Para crear la tabla de intervalo, sigue estos pasos:

    1. Abre la página de BigQuery en Cloud Console.

      Ir a la página de BigQuery

    2. Haz clic en Redactar consulta.

    3. En el campo Consulta nueva, ejecuta la siguiente consulta. La consulta materializa parte de la tabla silver-wall-555:TuteTable.hg19 en una nueva tabla 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. Haga clic en Run query. La consulta muestra el siguiente resultado:

    This statement created a new table named PROJECT_ID:genomics.myIntervalTable.
    
  2. En el campo Consulta nueva, ejecuta la siguiente 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. Haga clic en Run query. La consulta tarda unos diez segundos en ejecutarse y procesa alrededor de 334 GB de datos. Los resultados de la consulta identifican variantes poco frecuentes dentro de la cohorte que se superponen en las regiones de interés.

    Expande la siguiente sección para ver los resultados de la consulta:

    Resultados de la consulta

    Chr Inicio Ref Alt Func Gen PopFreqMax ExonicFunc núm_variant_alleles total_núm_alelos
    1 156699757 T C introductoria RRNAD1 0.002 2 4
    1 156705390 C T introductoria RRNAD1 8.0E-4 0 2
    1 156714207 T C introductoria HDGF 0.003 0 6
    1 156714440 A C introductoria HDGF 0.0068 0 12
    1 156723870 C T intergenético HDGF,PRCC 0.006 1 2
    1 156724456 C T intergenético HDGF,PRCC 0.002 2 4
    1 156733988 C T intergenético HDGF,PRCC 0.001 1 2
    1 156742258 T G introductoria PRCC 0.001 2 4
    1 156744826 T G introductoria PRCC 0.002 0 8
    1 156779764 G A introductoria SH2D2A 0.001 2 4
    1 156783454 A C introductoria SH2D2A 0.0014 1 2
    1 156786144 C T introductoria NTRK1,SH2D2A 0.0031 2 4
    1 156790510 A T introductoria NTRK1 0.002 1 2
    1 156815332 A C introductoria INTELR,NTRK1 0.003 0 2
    1 156830778 G A exontivo NTRK1 0.0067 discoble 2 4
    1 156842064 C T introductoria NTRK1 0.0014 1 2
    1 156843438 C A exontivo NTRK1 0.0032 discoble 1 2
    1 156845773 C T introductoria NTRK1 0.001 2 4
    1 156873318 T C introductoria PEAR1 0.01 4 8
    1 156922740 G A introductoria ARHGEF11 0.007 1 2
    1 156930100 C T introductoria ARHGEF11 0.001 2 4
    2 113901230 G A intergenético IL1RN,PSD4 0.0082 1 2
    2 113953418 C A introductoria PSD4 0.001 2 4
    2 113967621 G C intergenético PSD4,PAX8 0.002 0 6
    2 113967624 T C intergenético PSD4,PAX8 0.002 0 2
    2 113980967 G A introductoria PAX8 0.002 2 4
    2 113994010 A C NTRN_exonía PAX8-AS1 0.001 0 4
    2 113997745 C A NTRN_exonía PAX8-AS1 0.001 2 4
    2 114061327 T C intergenético PAX8,CBWD2 0.001 2 4
    2 114084018 A C intergenético PAX8,CBWD2 0.0045 0 4
    2 114099037 G A intergenético PAX8,CBWD2 0.0051 1 2
    2 114105670 A T intergenético PAX8,CBWD2 0.001 1 2
    2 114111325 G T intergenético PAX8,CBWD2 0.001 1 2
    3 12265797 C T intergenético SYN2,PPARG 0.0089 2 4
    3 12277958 A G intergenético SYN2,PPARG 0.002 1 2
    3 12296019 G A intergenético SYN2,PPARG 0.002 2 4
    3 12316549 G C intergenético SYN2,PPARG 0.002 1 2
    3 12335681 T G introductoria PAGO 0.0092 2 4
    3 12348795 T C introductoria PAGO 0.0014 1 2
    3 12353106 T C introductoria PAGO 0.001 2 4
    3 12403825 G A introductoria PAGO 0.0051 2 4
    3 12404394 G A introductoria PAGO 0.001 1 2
    3 12410289 G A introductoria PAGO 0.008 2 4
    3 12431381 C T introductoria PAGO 0.0061 2 4
    3 12447267 G A introductoria PAGO 0.0089 2 4
    3 12449379 C T introductoria PAGO 0.0092 2 4
    3 12450848 C A introductoria PAGO 0.0092 2 4
    3 12462847 T C introductoria PAGO 0.002 1 2
    3 12492797 G A intergenético PPARG,TSEN2 0.01 1 2
    3 12503201 G A intergenético PPARG,TSEN2 0.0099 2 4
    3 12530460 A G introductoria TSEN2 0.0092 2 4
    3 12531167 A G introductoria TSEN2 0.0099 2 4
    3 12557737 A G introductoria TSEN2 0.001 2 4
    3 59636143 A G intergenético C3orf67,FHIT 0.003 3 6
    3 59645934 A C intergenético C3orf67,FHIT 0.004 1 2
    3 59646893 G A intergenético C3orf67,FHIT 0.002 1 2
    3 59697024 A G intergenético C3orf67,FHIT 0.0072 1 2
    3 59701013 G A intergenético C3orf67,FHIT 0.004 2 4
    3 59733945 A G intergenético C3orf67,FHIT 0.001 2 4
    3 59747482 C T introductoria GRAVE 0.001 2 4
    3 59750635 A G introductoria GRAVE 0.003 1 2
    3 59757776 C T introductoria GRAVE 0.001 2 4
    3 59770612 G A introductoria GRAVE 0.001 2 4
    3 59804444 G C introductoria GRAVE 0.001 2 4
    3 59819769 T C introductoria GRAVE 0.001 2 4
    3 59884396 C T introductoria GRAVE 0.001 2 4
    3 59960728 A C introductoria GRAVE 0.01 1 2
    3 59970345 G A introductoria GRAVE 0.002 1 2
    3 59972417 T A introductoria GRAVE 0.0072 0 2
    3 60104328 C A introductoria GRAVE 0.01 2 4
    3 60139062 G A introductoria GRAVE 0.01 0 2
    3 60158066 C T introductoria GRAVE 0.001 1 2
    3 60169285 C T introductoria GRAVE 0.005 1 2
    3 60216185 T C introductoria GRAVE 0.002 1 2
    3 60226380 G A introductoria GRAVE 0.007 2 4
    3 60234539 C A introductoria GRAVE 0.002 1 2
    3 60247464 A C introductoria GRAVE 0.004 2 4
    3 60269926 A G introductoria GRAVE 0.007 2 4
    3 60271228 G T introductoria GRAVE 0.007 2 4
    3 60286972 T C introductoria GRAVE 0.001 2 4
    3 60301412 C G introductoria GRAVE 0.001 1 2
    3 60312251 C T introductoria GRAVE 0.0099 1 2
    3 60317682 A G introductoria GRAVE 0.008 1 2
    3 60328557 C G introductoria GRAVE 0.0043 2 4
    3 60342562 C T introductoria GRAVE 0.006 1 2
    3 60400033 G A introductoria GRAVE 0.004 2 4
    3 60435819 C T introductoria GRAVE 0.006 2 4
    3 60435820 G T introductoria GRAVE 0.004 1 2
    3 60441288 T C introductoria GRAVE 0.006 2 4
    3 60444465 C A introductoria GRAVE 0.01 1 2
    3 60444575 C T introductoria GRAVE 0.001 1 2
    3 60450581 T C introductoria GRAVE 0.01 1 2
    3 60456571 G A introductoria GRAVE 0.001 2 4
    3 60473568 C G introductoria GRAVE 0.001 1 2
    3 60487557 T C introductoria GRAVE 0.001 1 2
    3 60559705 A G introductoria GRAVE 0.002 2 4
    3 60570764 T C introductoria GRAVE 0.008 2 4
    3 60582100 C T introductoria GRAVE 0.001 1 2
    3 60587192 G A introductoria GRAVE 0.004 1 2
    3 60599869 G A introductoria GRAVE 0.0086 2 4
    3 60603091 C T introductoria GRAVE 0.001 2 4
    3 60603250 A T introductoria GRAVE 0.0099 1 2
    3 60609831 T G introductoria GRAVE 0.001 2 4
    3 60619756 G T introductoria GRAVE 0.0015 2 4
    3 60680758 C T introductoria GRAVE 0.0089 2 4
    3 60702243 G C introductoria GRAVE 0.001 2 4
    3 60702532 A G introductoria GRAVE 0.001 1 2
    3 60714328 A T introductoria GRAVE 0.004 1 2
    3 60725297 G A introductoria GRAVE 0.001 1 2
    3 60726640 G A introductoria GRAVE 0.01 2 4
    3 60795144 A G introductoria GRAVE 0.001 2 4
    3 60807171 A G introductoria GRAVE 0.001 1 2
    3 60813868 T C introductoria GRAVE 0.001 1 2
    3 60826546 C G introductoria GRAVE 0.0023 1 2
    3 60837392 C T introductoria GRAVE 0.001 1 2
    3 60846310 A G introductoria GRAVE 0.01 0 2
    3 60850985 C T introductoria GRAVE 0.004 1 2
    3 60852559 T C introductoria GRAVE 0.008 1 2
    3 60871759 T C introductoria GRAVE 0.004 1 2
    3 60884396 C T introductoria GRAVE 0.002 2 4
    3 60897092 C A introductoria GRAVE 0.001 2 4
    3 60940759 C T introductoria GRAVE 0.0089 1 2
    3 60982595 A G introductoria GRAVE 0.003 2 4
    3 60999283 G A introductoria GRAVE 0.001 1 2
    3 61042977 A G introductoria GRAVE 0.001 2 4
    3 61043349 T C introductoria GRAVE 0.001 2 4
    3 61044789 A C introductoria GRAVE 0.001 2 4
    3 61141621 G A introductoria GRAVE 0.003 1 2
    3 61148655 G C introductoria GRAVE 0.001 2 4
    3 61170747 C T introductoria GRAVE 0.003 1 2
    3 61189473 C G introductoria GRAVE 0.0099 1 2
    3 61190425 C T introductoria GRAVE 0.0023 2 4
    3 61193853 C T introductoria GRAVE 0.0099 0 2
    3 61194793 C T introductoria GRAVE 0.007 0 2
    3 61194840 A G introductoria GRAVE 0.0099 0 2
    3 61194886 T A introductoria GRAVE 0.0099 0 2
    3 61201777 C T introductoria GRAVE 0.001 2 4
    3 61202292 T C introductoria GRAVE 0.007 1 2
    3 61232806 G C introductoria GRAVE 0.0099 1 2
    3 61232910 C T introductoria GRAVE 0.0099 1 2
    3 61235824 A T introductoria GRAVE 0.001 2 4
    3 61283810 A C intergenético PTITITIN, 0.0089 1 2
    3 61293731 T A intergenético PTITITIN, 0.0089 2 4
    3 61296730 C T intergenético PTITITIN, 0.001 1 2
    3 61326341 C T intergenético PTITITIN, 0.004 2 4
    3 61326620 T C intergenético PTITITIN, 0.01 1 2
    3 61327649 G C intergenético PTITITIN, 0.001 2 4
    3 61330545 G C intergenético PTITITIN, 0.001 2 4
    3 61335803 G A intergenético PTITITIN, 0.001 2 4

    Una consulta similar que use los datos de la fase 3 de 1,000 genomas tarda aproximadamente 90 segundos en ejecutarse y procesa alrededor de 3.38 TB de datos.

Consulta en una tabla materializada con 250 genes aleatorios

En el siguiente ejemplo, se muestra cómo ejecutar un JOIN de intervalos en una tabla materializada que contiene 250 genes seleccionados de forma aleatoria de la tabla silver-wall-555:TuteTable.hg19.

  1. Para crear la tabla de intervalo, sigue estos pasos:

    1. Abre la página de BigQuery en Cloud Console.

      Ir a la página de BigQuery

    2. Haz clic en Redactar consulta.

    3. En el campo Consulta nueva, ejecuta la consulta siguiente, que materializa parte de la tabla silver-wall-555:TuteTable.hg19 en una nueva tabla 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. Haga clic en Run query. La consulta muestra el siguiente resultado:
      This statement created a new table named PROJECT_ID:genomics.randomGenesIntervalTable.
      
  2. En el campo Consulta nueva, ejecuta la siguiente 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. Haga clic en Run query. La consulta tarda unos diez segundos en ejecutarse y procesa alrededor de 334 GB de datos. Los resultados de la consulta identifican variantes poco frecuentes dentro de la cohorte que se superponen en las regiones de interés.

    Expanda la siguiente sección para ver los resultados truncados de la consulta:

    Resultados de la consulta

    Chr Inicio Ref Alt Func Gen PopFreqMax ExonicFunc núm_variant_alleles total_núm_alelos
    1 2925355 C A intergenético TTC34,ACTRT2 0.001 2 4
    1 2933170 G A intergenético TTC34,ACTRT2 0.0083 0 4
    1 2944477 G A intergenético ACTRT2,LINC00982 0.003 4 6
    1 2967591 A T intergenético ACTRT2,LINC00982 0.0092 1 2
    1 2975255 T C descendente LINC00982 0.0082 1 2
    1 2977223 C T NTRN_introducción LINC00982 0.0072 1 2
    1 2978803 G C NTRN_exonía LINC00982 0.002 4 6
    1 3006466 G A introductoria PRDM16 0.0098 1 2
    1 3011333 G T introductoria PRDM16 0.004 1 2
    1 3019659 C T introductoria PRDM16 0.0031 1 2
    1 3036896 G A introductoria PRDM16 0.001 1 2
    1 3037388 G A introductoria PRDM16 0.002 2 4
    1 3041250 T G introductoria PRDM16 0.006 2 4
    1 3042502 A T introductoria PRDM16 0.003 4 6
    1 3053713 A C introductoria PRDM16 0.002 1 2
    1 3063109 C T introductoria PRDM16 0.002 0 2
    1 3063593 T C introductoria PRDM16 0.003 1 2
    1 3076439 C T introductoria PRDM16 0.001 2 4
    1 3078960 G A introductoria PRDM16 0.007 2 4
    1 3084268 A C introductoria PRDM16 0.005 0 2
    1 3084492 T C introductoria PRDM16 0.0015 0 2
    1 3084786 T C introductoria PRDM16 0.0015 0 4
    1 3111119 G A introductoria PRDM16 0.003 1 2
    1 3111643 C T introductoria PRDM16 0.0041 1 2
    1 3114807 G A introductoria PRDM16 0.0041 1 2
    1 3165530 C T introductoria PRDM16 0.0089 1 2
    1 3169325 G A introductoria PRDM16 0.008 2 4
    1 3179623 C T introductoria PRDM16 0.003 2 4
    1 3181097 C T introductoria PRDM16 0.001 2 4
    1 3194000 G C introductoria PRDM16 0.005 2 4
    1 3195769 T C introductoria PRDM16 0.002 1 2
    1 3197351 C T introductoria PRDM16 0.0061 1 2
    1 3224100 C A introductoria PRDM16 0.003 2 4
    1 3228644 G T introductoria PRDM16 0.001 2 4
    1 3234045 G A introductoria PRDM16 0.002 1 2
    1 3235971 G A introductoria PRDM16 0.0089 1 2
    1 3274115 C T introductoria PRDM16 0.001 2 4
    1 3291388 G A introductoria PRDM16 0.002 2 4
    1 3295658 A C introductoria PRDM16 0.0068 0 6
    1 3295937 A C introductoria PRDM16 0.0068 0 2
    1 3296205 T C introductoria PRDM16 0.0083 0 2
    1 3315690 G A introductoria PRDM16 0.001 2 4
    1 3329212 G A exontivo PRDM16 0.0031 discoble 1 2
    1 3331787 C T introductoria PRDM16 0.0099 1 2
    1 3370316 G C ascendente ARHGEF16 0.001 2 4
    1 3379560 A G introductoria ARHGEF16 0.0051 0 6
    1 3391174 C T introductoria ARHGEF16 0.006 1 2
    1 3413873 G A exontivo MEGF6 0.003 discoble 1 2
    1 3416272 C T exontivo MEGF6 0.0072 silencio 2 4
    1 3417122 G A introductoria MEGF6 0.0038 2 4
    1 3436219 G A introductoria MEGF6 0.0046 2 4
    1 12907456 A G exontivo HNRNPCL1,LOC649330 0.006 discoble 0 10
    1 12907518 C A exontivo HNRNPCL1,LOC649330 1.0E-4 discoble 0 10
    1 12908499 G C introductoria HNRNPCL1 0.0031 0 8
    1 12931660 G C intergenético PPRME2,PRAMEsF4 0.004 1 2
    1 12937721 G T intergenético PPRME2,PRAMEsF4 0.0038 0 2
    1 12940827 G T introductoria PRAMEF4 0.007 2 4
    1 12942759 T G introductoria PRAMEF4 0.0076 0 10
    1 12942805 T G introductoria PRAMEF4 0.0061 0 12
    1 12942812 G A introductoria PRAMEF4 0.0061 0 12
    1 12942875 A G introductoria PRAMEF4 0.0068 0 6
    1 12942912 G C introductoria PRAMEF4 2.0E-4 0 2
    1 12942937 A T exontivo PRAMEF4 0.0029 discoble 0 2
    1 12942940 T G exontivo PRAMEF4 0.0038 discoble 0 2
    1 12943940 T C introductoria PRAMEF4 0.0015 0 12
    1 12944138 A G introductoria PRAMEF4 8.0E-4 0 12
    1 12944234 G A introductoria PRAMEF4 0.0015 0 12
    1 12944589 T G introductoria PRAMEF4 0.003 0 4
    1 12944845 A C introductoria PRAMEF4 0.0014 0 6
    1 12946439 T C ascendente PRAMEF4 0.0029 0 10
    1 12946833 G A ascendente PRAMEF4 0.001 0 8
    1 12946835 T A ascendente PRAMEF4 0.004 0 12
    1 12995204 G T intergenético PPRMEF8,PARAMEF6 0.003 1 4
    1 12997638 T C descendente PPRMEF6,PARAMEF9 0.003 2 4
    1 13007841 G C ascendente PRAMEF6 0.0043 0 8
    1 13019228 T A intergenético PRAMEF6,LOC391003 0.0015 0 10
    1 13038503 G A UTR3 LOC391003 0.0072 1 2
    1 13051650 C T intergenético LOC391003,PRAMEF5 0.002 2 4
    1 15706063 G A introductoria FHAD1 0.0029 1 2
    1 15713292 C T introductoria FHAD1 0.001 1 2
    1 15766541 G C introductoria CTRC 0.001 1 2
    1 15782601 T C ascendente CEL2A 0.0038 1 2
    1 15828125 G A introductoria CASP9 0.0014 2 4
    1 15831037 G A introductoria CASP9 0.0099 1 2
    1 15840513 T G introductoria CASP9 0.0043 2 4
    1 15868742 G A introductoria ADNJC16 0.001 1 2
    1 15876704 G A introductoria ADNJC16 0.001 1 2
    1 15900342 C A introductoria PREMIO 0.001 1 2
    1 15906257 T C introductoria PREMIO 8.0E-4 1 2
    1 15911897 A G ascendente PREMIO 0.0043 2 4
    1 22764178 C T intergenético WNT4,ZBTB40 0.001 2 4
    1 22791939 C T introductoria ZBTB40 0.0089 2 4
    1 22874394 C G intergenético ZBTB40,EPHA8 0.007 1 2
    1 22875103 C G intergenético ZBTB40,EPHA8 0.007 1 2
    1 22906403 C T introductoria EPHA8 0.008 2 4
    1 22912956 G A introductoria EPHA8 0.001 1 2
    1 22917007 C T introductoria EPHA8 0.001 2 4
    1 22927240 G A exontivo EPHA8 0.0013 discoble 2 4
    1 22932265 G A intergenético EPHA8,MIR6127 0.0089 2 4
    1 22944057 C T intergenético EPHA8,MIR6127 0.0089 2 4
    1 22978799 A G ascendente C1QB 0.0099 2 4
    1 35170588 C T intergenético C1orf94,GJB5 0.01 1 2
    1 35172426 C T intergenético C1orf94,GJB5 0.008 1 2
    1 35172447 G A intergenético C1orf94,GJB5 0.001 1 2
    1 35175302 C T intergenético C1orf94,GJB5 0.008 1 2
    1 35177410 A T intergenético C1orf94,GJB5 0.001 1 2
    1 35178768 C T intergenético C1orf94,GJB5 0.0014 2 4
    1 35179362 G A intergenético C1orf94,GJB5 0.0014 2 4
    1 35186166 G A intergenético C1orf94,GJB5 0.0099 2 4
    1 35186520 A C intergenético C1orf94,GJB5 0.002 2 4
    1 35196361 G A intergenético C1orf94,GJB5 0.0099 2 4
    1 35223545 C T exontivo JJB5 0.001 silencio 1 2
    1 35224029 G A UTR3 JJB5 0.003 1 2
    1 35227895 T C UTR3 GJB4 5.0E-4 1 2
    1 35230455 G T intergenético GJB4,GJB3 0.0043 1 2
    1 35232954 T C intergenético GJB4,GJB3 0.003 1 2
    1 35237986 G A intergenético GJB4,GJB3 0.0014 1 2
    1 35245522 C T intergenético GJB4,GJB3 0.001 1 2
    1 35256979 C T intergenético GJB3,GJA4 0.002 2 4
    1 35263872 C T intergenético GJA4,SMIM12 5.0E-4 2 4
    1 35323895 A C introductoria SMIM12 0,20 2 4
    1 35369676 G A introductoria DLGAP3 0.007 2 4
    1 35371634 T A ascendente DLGAP3 0.0015 0 4
    1 39253519 G A intergenético LINC01343,RARAGC 0.005 1 2
    1 39288829 G A intergenético LINC01343,RARAGC 0.0051 1 2
    1 39289832 A C intergenético LINC01343,RARAGC 0.002 0 2
    1 39312638 G A introductoria RRAGC 0.0038 2 4
    1 39361372 G A introductoria RHBDL2 0.005 1 2
    1 39363826 T G introductoria RHBDL2 0.0029 1 2
    1 39367555 T C introductoria RHBDL2 0.007 2 4
    1 39369531 T C introductoria RHBDL2 0.001 2 4
    1 39370202 T C introductoria RHBDL2 0.01 1 2
    1 39449101 A G intergenético RHBDL2,AKIRIN1 0.001 2 4
    1 39475057 G A intergenético AKIRIN1,NDUFS5 0.01 1 2
    1 39485016 C T intergenético AKIRIN1,NDUFS5 0.001 2 4
    1 39488137 A G intergenético AKIRIN1,NDUFS5 0.001 2 4
    1 39499212 A C introductoria NDUFS5 0.001 0 2
    1 39500605 C G descendente NDUFS5 0.002 0 10
    1 46813814 T C introductoria NSUN4 0.0014 1 2
    1 46817258 A G introductoria NSUN4 0.005 0 2
    1 46843158 T C intergenético NSUN4,FAAH 0.001 1 2
    1 46933509 A G intergenético LINC01398,DMBX1 0.002 1 2
    1 46935021 G A intergenético LINC01398,DMBX1 0.004 2 4
    1 46939253 T A intergenético LINC01398,DMBX1 0.004 2 4
    1 46951788 C A intergenético LINC01398,DMBX1 0.002 2 4
    1 46980864 G C descendente DMBX1 0.003 1 2
    1 46989657 T C intergenético DMBX1,MKNK1-AS1 0.007 1 2
    1 46994678 C T intergenético DMBX1,MKNK1-AS1 0.002 1 2
    1 46999438 T C intergenético DMBX1,MKNK1-AS1 0.002 1 2
    1 92761505 A G introductoria NGAN 0.001 2 4
    1 92764270 G C introductoria NGAN 0.001 2 4
    1 92802210 G A introductoria RAP2 0.0072 1 2
    1 92820663 T A introductoria RAP2 0.0058 1 2
    1 92820664 G T introductoria RAP2 0.0058 1 2
    1 92820953 G A introductoria RAP2 0.007 2 4
    1 92824766 A G introductoria RAP2 0.0058 1 2
    1 92849183 C A introductoria RAP2 0.01 2 4
    1 92850696 C G introductoria RAP2 0.0023 1 2
    1 92861357 T C intergenético RPAP2,GFI1 0.01 2 4
    1 92877460 C G intergenético RPAP2,GFI1 0.002 1 2
    1 92880643 A G intergenético RPAP2,GFI1 0.001 2 4
    1 92911540 G A intergenético RPAP2,GFI1 0.004 2 4
    1 92911721 A C intergenético RPAP2,GFI1 0.0031 0 8
    1 92918277 C T intergenético RPAP2,GFI1 0.001 2 4
    1 92950920 G A introductoria GFI1 0.008 2 4
    1 92964788 G A intergenético GFI1,EVI5 0.0023 1 2
    1 92977480 C T UTR3 EVI5 0.002 1 2
    1 92985213 C T introductoria EVI5 0.001 2 4
    1 92988342 C T introductoria EVI5 0.008 2 4
    1 92992283 G A introductoria EVI5 0.01 2 4
    1 92999760 C T introductoria EVI5 0.003 1 2
    1 93005149 G C introductoria EVI5 0.003 0 4
    1 93018543 A T introductoria EVI5 0.01 2 4
    1 93033744 C T introductoria EVI5 0.001 2 4
    1 111400296 G A intergenético KCNA3,CD53 0.0014 2 4
    1 111411924 C T intergenético KCNA3,CD53 0.003 1 2
    1 111441850 C G UTR3 EC53 0.003 2 4
    1 111451527 C T intergenético EC53,LRIF1 0.008 2 4
    1 111454082 C A intergenético EC53,LRIF1 0.001 2 4
    1 111466506 A G intergenético EC53,LRIF1 0.001 2 4
    1 111525974 G A intergenético LRIF1,DRAM2 0.002 2 4
    1 111574573 G T intergenético LRIF1,DRAM2 0.0072 2 4
    1 111574594 T A intergenético LRIF1,DRAM2 0.005 1 2
    1 111574647 G A intergenético LRIF1,DRAM2 0.005 1 2
    1 111591746 T A intergenético LRIF1,DRAM2 0.005 1 2
    1 111601459 A G intergenético LRIF1,DRAM2 0.005 1 2
    1 111604748 G C intergenético LRIF1,DRAM2 0.005 1 2
    1 112191526 T G introductoria RA1A 0.001 2 4
    1 112206765 A G introductoria RA1A 0.0043 1 2
    1 112226517 G A introductoria RA1A 0.001 0 2
    1 112263324 G T intergenético RABA,FAM212B 0.003 2 4
    1 112264843 G A UTR3 FAM212B 0.001 1 2
    1 112285810 C T NTRN_introducción FAM212B-AS1 0.004 1 2
    1 112304285 T C introductoria DDX20 0.0043 1 2
    1 112307213 A C introductoria DDX20 0.0043 1 2
    1 112309436 G T exontivo DDX20 0.0 discoble 1 2
    1 112317384 T C intergenético DDX20,KCND3 0.0014 1 2
    1 112381367 C T introductoria KCND3 0.002 1 2
    1 112396571 G T NTRN_exonía KCND3-IT1 0.001 1 2
    1 113520038 G A intergenético SLC16A1-AS1,LOC100996251 0.0023 1 2

    Una consulta similar que use los datos de la fase 3 de 1,000 genomas tarda aproximadamente 90 segundos en ejecutarse y procesa alrededor de 3.38 TB de datos.