Ejecuta 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:

  • Encontrar los SNP poco frecuentes que se superponen en los genes
  • 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 demuestra cómo BigQuery escala sobre diferentes tamaños de datos genómicos:

Los datos provienen de la tabla de Tute Genomics Annotation, de aproximadamente 9,000 millones de filas y del 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

El siguiente ejemplo utiliza una tabla de intervalo definida en la consulta, llamada intervals y muestra cómo ejecutar una consulta de JOIN con una tabla que contienen variantes de Platinum Genomes de Illumina:

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

    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. Haz clic en Ejecutar consulta. La consulta tarda aproximadamente 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 Iniciar Ref Alt Func Gen PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C íntronico RRNAD1 0.002 2 4
    1 156705390 C T intronico RRNAD1 8.0E‐4 0 2
    1 156714207 T C intronico HDGF 0.003 0 6
    1 156714440 A C intronico 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 íntronico PRCC 0.001 2 4
    1 156744826 T G íntronico PRCC 0.002 0 8
    1 156779764 G A íntronico SH2D2A 0.001 2 4
    1 156783454 A C íntronico SH2D2A 0.0014 1 2
    1 156786144 C T intronico NTRK1 y SH2D2A 0.0031 2 4
    1 156790510 A T íntronico NTRK1 0.002 1 2
    1 156815332 A C íntronico INSRR,NTRK1 0.003 0 2
    1 156830778 G A exónico NTRK1 0.0067 de cambio de sentido 2 4
    1 156842064 C T íntronico NTRK1 0.0014 1 2
    1 156843438 C A exónico NTRK1 0.0032 de cambio de sentido 1 2
    1 156845773 C T íntronico NTRK1 0.001 2 4
    1 156873318 T C intronico PEAR1 0.01 4 8
    1 156922740 G A íntronico ARHGEF11 0.007 1 2
    1 156930100 C T íntronico ARHGEF11 0.001 2 4
    2 113901230 G A intergénico IL1RN,PSD4 0.0082 1 2
    2 113953418 C A intronico PSD4 0.001 2 4
    2 113967621 G C intergénico PSD4 y PAX8 0.002 0 6
    2 113967624 T C intergénico PSD4 y PAX8 0.002 0 2
    2 113980967 G A íntronico 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 y CBWD2 0.001 2 4
    2 114084018 A C intergénico PAX8 y CBWD2 0.0045 0 4
    2 114099037 G A intergénico PAX8,CBWD2 0.0051 1 2
    2 114105670 A T intergénico PAX8 y CBWD2 0.001 1 2
    2 114111325 G T intergénico PAX8 y 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 intronico PPARG 0.0092 2 4
    3 12348795 T C íntronico PPARG 0.0014 1 2
    3 12353106 T C íntronico PPARG 0.001 2 4
    3 12403825 G A íntronico PPARG 0.0051 2 4
    3 12404394 G A íntronico PPARG 0.001 1 2
    3 12410289 G A íntronico PPARG 0.008 2 4
    3 12431381 C T íntronico PPARG 0.0061 2 4
    3 12447267 G A íntronico PPARG 0.0089 2 4
    3 12449379 C T íntronico PPARG 0.0092 2 4
    3 12450848 C A íntronico PPARG 0.0092 2 4
    3 12462847 T C íntronico 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 íntronico TSEN2 0.0092 2 4
    3 12531167 A G íntronico TSEN2 0.0099 2 4
    3 12557737 A G íntronico 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 íntronico FHIT 0.001 2 4
    3 59750635 A G intronico FHIT 0.003 1 2
    3 59757776 C T íntronico FHIT 0.001 2 4
    3 59770612 G A íntronico FHIT 0.001 2 4
    3 59804444 G C íntronico FHIT 0.001 2 4
    3 59819769 T C íntronico FHIT 0.001 2 4
    3 59884396 C T íntronico FHIT 0.001 2 4
    3 59960728 A C íntronico FHIT 0.01 1 2
    3 59970345 G A íntronico FHIT 0.002 1 2
    3 59972417 T A íntronico FHIT 0.0072 0 2
    3 60104328 C A íntronico FHIT 0.01 2 4
    3 60139062 G A íntronico FHIT 0.01 0 2
    3 60158066 C T íntronico FHIT 0.001 1 2
    3 60169285 C T íntronico FHIT 0.005 1 2
    3 60216185 T C íntronico FHIT 0.002 1 2
    3 60226380 G A íntronico FHIT 0.007 2 4
    3 60234539 C A íntronico FHIT 0.002 1 2
    3 60247464 A C íntronico FHIT 0.004 2 4
    3 60269926 A G intronico FHIT 0.007 2 4
    3 60271228 G T íntronico FHIT 0.007 2 4
    3 60286972 T C íntronico FHIT 0.001 2 4
    3 60301412 C G intronico FHIT 0.001 1 2
    3 60312251 C T íntronico 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 íntronico FHIT 0.006 1 2
    3 60400033 G A íntronico FHIT 0.004 2 4
    3 60435819 C T íntronico FHIT 0.006 2 4
    3 60435820 G T íntronico FHIT 0.004 1 2
    3 60441288 T C íntronico FHIT 0.006 2 4
    3 60444465 C A íntronico FHIT 0.01 1 2
    3 60444575 C T íntronico FHIT 0.001 1 2
    3 60450581 T C íntronico FHIT 0.01 1 2
    3 60456571 G A íntronico FHIT 0.001 2 4
    3 60473568 C G intronico FHIT 0.001 1 2
    3 60487557 T C íntronico FHIT 0.001 1 2
    3 60559705 A G intronico FHIT 0.002 2 4
    3 60570764 T C íntronico FHIT 0.008 2 4
    3 60582100 C T íntronico FHIT 0.001 1 2
    3 60587192 G A íntronico FHIT 0.004 1 2
    3 60599869 G A íntronico FHIT 0.0086 2 4
    3 60603091 C T íntronico FHIT 0.001 2 4
    3 60603250 A T íntronico FHIT 0.0099 1 2
    3 60609831 T G intronico FHIT 0.001 2 4
    3 60619756 G T íntronico FHIT 0.0015 2 4
    3 60680758 C T íntronico FHIT 0.0089 2 4
    3 60702243 G C íntronico FHIT 0.001 2 4
    3 60702532 A G intronico FHIT 0.001 1 2
    3 60714328 A T íntronico FHIT 0.004 1 2
    3 60725297 G A íntronico FHIT 0.001 1 2
    3 60726640 G A íntronico 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 íntronico FHIT 0.001 1 2
    3 60826546 C G intronico FHIT 0.0023 1 2
    3 60837392 C T íntronico FHIT 0.001 1 2
    3 60846310 A G intronico FHIT 0.01 0 2
    3 60850985 C T íntronico FHIT 0.004 1 2
    3 60852559 T C íntronico FHIT 0.008 1 2
    3 60871759 T C íntronico FHIT 0.004 1 2
    3 60884396 C T íntronico FHIT 0.002 2 4
    3 60897092 C A íntronico FHIT 0.001 2 4
    3 60940759 C T íntronico FHIT 0.0089 1 2
    3 60982595 A G intronico FHIT 0.003 2 4
    3 60999283 G A íntronico FHIT 0.001 1 2
    3 61042977 A G intronico FHIT 0.001 2 4
    3 61043349 T C íntronico FHIT 0.001 2 4
    3 61044789 A C íntronico FHIT 0.001 2 4
    3 61141621 G A íntronico FHIT 0.003 1 2
    3 61148655 G C íntronico FHIT 0.001 2 4
    3 61170747 C T íntronico FHIT 0.003 1 2
    3 61189473 C G intronico FHIT 0.0099 1 2
    3 61190425 C T íntronico FHIT 0.0023 2 4
    3 61193853 C T íntronico FHIT 0.0099 0 2
    3 61194793 C T íntronico FHIT 0.007 0 2
    3 61194840 A G intronico FHIT 0.0099 0 2
    3 61194886 T A íntronico FHIT 0.0099 0 2
    3 61201777 C T íntronico FHIT 0.001 2 4
    3 61202292 T C íntronico FHIT 0.007 1 2
    3 61232806 G C íntronico FHIT 0.0099 1 2
    3 61232910 C T íntronico FHIT 0.0099 1 2
    3 61235824 A T íntronico 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

    Una consulta similar con los datos de la fase 3 de 1,000 genomas 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 ejecuta una consulta JOIN en la tabla nueva. Antes de continuar con el resto de esta sección, sigue estos pasos para crear un conjunto de datos:

  1. Abre la página BigQuery en la consola de Google Cloud.

    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 tal como está.
    3. Haz clic en Crear conjunto de datos.

Realiza consultas en una tabla materializada con genes específicos

Los siguientes pasos muestran cómo materializar una nueva tabla de intervalo que contiene una lista de genes específicos del silver-wall-555:TuteTable.hg19.

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

    1. Abre la página BigQuery en la consola de Google Cloud.

      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. Haz clic en Ejecutar consulta. 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. Haz clic en Ejecutar consulta. La consulta tarda aproximadamente 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 Iniciar Ref Alt Func Gen PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C íntronico RRNAD1 0.002 2 4
    1 156705390 C T intronico RRNAD1 8.0E‐4 0 2
    1 156714207 T C intronico HDGF 0.003 0 6
    1 156714440 A C intronico 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 íntronico PRCC 0.001 2 4
    1 156744826 T G íntronico PRCC 0.002 0 8
    1 156779764 G A íntronico SH2D2A 0.001 2 4
    1 156783454 A C íntronico SH2D2A 0.0014 1 2
    1 156786144 C T intronico NTRK1 y SH2D2A 0.0031 2 4
    1 156790510 A T íntronico NTRK1 0.002 1 2
    1 156815332 A C íntronico INSRR,NTRK1 0.003 0 2
    1 156830778 G A exónico NTRK1 0.0067 de cambio de sentido 2 4
    1 156842064 C T íntronico NTRK1 0.0014 1 2
    1 156843438 C A exónico NTRK1 0.0032 de cambio de sentido 1 2
    1 156845773 C T íntronico NTRK1 0.001 2 4
    1 156873318 T C intronico PEAR1 0.01 4 8
    1 156922740 G A íntronico ARHGEF11 0.007 1 2
    1 156930100 C T íntronico ARHGEF11 0.001 2 4
    2 113901230 G A intergénico IL1RN,PSD4 0.0082 1 2
    2 113953418 C A intronico PSD4 0.001 2 4
    2 113967621 G C intergénico PSD4 y PAX8 0.002 0 6
    2 113967624 T C intergénico PSD4 y PAX8 0.002 0 2
    2 113980967 G A íntronico 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 y CBWD2 0.001 2 4
    2 114084018 A C intergénico PAX8 y CBWD2 0.0045 0 4
    2 114099037 G A intergénico PAX8,CBWD2 0.0051 1 2
    2 114105670 A T intergénico PAX8 y CBWD2 0.001 1 2
    2 114111325 G T intergénico PAX8 y 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 intronico PPARG 0.0092 2 4
    3 12348795 T C íntronico PPARG 0.0014 1 2
    3 12353106 T C íntronico PPARG 0.001 2 4
    3 12403825 G A íntronico PPARG 0.0051 2 4
    3 12404394 G A íntronico PPARG 0.001 1 2
    3 12410289 G A íntronico PPARG 0.008 2 4
    3 12431381 C T íntronico PPARG 0.0061 2 4
    3 12447267 G A íntronico PPARG 0.0089 2 4
    3 12449379 C T íntronico PPARG 0.0092 2 4
    3 12450848 C A íntronico PPARG 0.0092 2 4
    3 12462847 T C íntronico 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 íntronico TSEN2 0.0092 2 4
    3 12531167 A G íntronico TSEN2 0.0099 2 4
    3 12557737 A G íntronico 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 íntronico FHIT 0.001 2 4
    3 59750635 A G intronico FHIT 0.003 1 2
    3 59757776 C T íntronico FHIT 0.001 2 4
    3 59770612 G A íntronico FHIT 0.001 2 4
    3 59804444 G C íntronico FHIT 0.001 2 4
    3 59819769 T C íntronico FHIT 0.001 2 4
    3 59884396 C T íntronico FHIT 0.001 2 4
    3 59960728 A C íntronico FHIT 0.01 1 2
    3 59970345 G A íntronico FHIT 0.002 1 2
    3 59972417 T A íntronico FHIT 0.0072 0 2
    3 60104328 C A íntronico FHIT 0.01 2 4
    3 60139062 G A íntronico FHIT 0.01 0 2
    3 60158066 C T íntronico FHIT 0.001 1 2
    3 60169285 C T íntronico FHIT 0.005 1 2
    3 60216185 T C íntronico FHIT 0.002 1 2
    3 60226380 G A íntronico FHIT 0.007 2 4
    3 60234539 C A íntronico FHIT 0.002 1 2
    3 60247464 A C íntronico FHIT 0.004 2 4
    3 60269926 A G intronico FHIT 0.007 2 4
    3 60271228 G T íntronico FHIT 0.007 2 4
    3 60286972 T C íntronico FHIT 0.001 2 4
    3 60301412 C G intronico FHIT 0.001 1 2
    3 60312251 C T íntronico 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 íntronico FHIT 0.006 1 2
    3 60400033 G A íntronico FHIT 0.004 2 4
    3 60435819 C T íntronico FHIT 0.006 2 4
    3 60435820 G T íntronico FHIT 0.004 1 2
    3 60441288 T C íntronico FHIT 0.006 2 4
    3 60444465 C A íntronico FHIT 0.01 1 2
    3 60444575 C T íntronico FHIT 0.001 1 2
    3 60450581 T C íntronico FHIT 0.01 1 2
    3 60456571 G A íntronico FHIT 0.001 2 4
    3 60473568 C G intronico FHIT 0.001 1 2
    3 60487557 T C íntronico FHIT 0.001 1 2
    3 60559705 A G intronico FHIT 0.002 2 4
    3 60570764 T C íntronico FHIT 0.008 2 4
    3 60582100 C T íntronico FHIT 0.001 1 2
    3 60587192 G A íntronico FHIT 0.004 1 2
    3 60599869 G A íntronico FHIT 0.0086 2 4
    3 60603091 C T íntronico FHIT 0.001 2 4
    3 60603250 A T íntronico FHIT 0.0099 1 2
    3 60609831 T G intronico FHIT 0.001 2 4
    3 60619756 G T íntronico FHIT 0.0015 2 4
    3 60680758 C T íntronico FHIT 0.0089 2 4
    3 60702243 G C íntronico FHIT 0.001 2 4
    3 60702532 A G intronico FHIT 0.001 1 2
    3 60714328 A T íntronico FHIT 0.004 1 2
    3 60725297 G A íntronico FHIT 0.001 1 2
    3 60726640 G A íntronico 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 íntronico FHIT 0.001 1 2
    3 60826546 C G intronico FHIT 0.0023 1 2
    3 60837392 C T íntronico FHIT 0.001 1 2
    3 60846310 A G intronico FHIT 0.01 0 2
    3 60850985 C T íntronico FHIT 0.004 1 2
    3 60852559 T C íntronico FHIT 0.008 1 2
    3 60871759 T C íntronico FHIT 0.004 1 2
    3 60884396 C T íntronico FHIT 0.002 2 4
    3 60897092 C A íntronico FHIT 0.001 2 4
    3 60940759 C T íntronico FHIT 0.0089 1 2
    3 60982595 A G intronico FHIT 0.003 2 4
    3 60999283 G A íntronico FHIT 0.001 1 2
    3 61042977 A G intronico FHIT 0.001 2 4
    3 61043349 T C íntronico FHIT 0.001 2 4
    3 61044789 A C íntronico FHIT 0.001 2 4
    3 61141621 G A íntronico FHIT 0.003 1 2
    3 61148655 G C íntronico FHIT 0.001 2 4
    3 61170747 C T íntronico FHIT 0.003 1 2
    3 61189473 C G intronico FHIT 0.0099 1 2
    3 61190425 C T íntronico FHIT 0.0023 2 4
    3 61193853 C T íntronico FHIT 0.0099 0 2
    3 61194793 C T íntronico FHIT 0.007 0 2
    3 61194840 A G intronico FHIT 0.0099 0 2
    3 61194886 T A íntronico FHIT 0.0099 0 2
    3 61201777 C T íntronico FHIT 0.001 2 4
    3 61202292 T C íntronico FHIT 0.007 1 2
    3 61232806 G C íntronico FHIT 0.0099 1 2
    3 61232910 C T íntronico FHIT 0.0099 1 2
    3 61235824 A T íntronico 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

    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 intervalo en un objeto que contiene 250 genes seleccionados al azar del silver-wall-555:TuteTable.hg19.

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

    1. Abre la página BigQuery en la consola de Google Cloud.

      Ir a la página de BigQuery

    2. Haz clic en Redactar consulta.

    3. En el campo Consulta nueva, ejecuta la siguiente consulta, 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. Haz clic en Ejecutar consulta. 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. Haz clic en Ejecutar consulta. La consulta tarda aproximadamente 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 truncados de la consulta:

    Resultados de la consulta

    Chr Iniciar Ref Alt Func Gen 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 íntronico 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 íntronico PRDM16 0.001 1 2
    1 3037388 G A íntronico PRDM16 0.002 2 4
    1 3041250 T G íntronico PRDM16 0.006 2 4
    1 3042502 A T intronico PRDM16 0.003 4 6
    1 3053713 A C íntronico PRDM16 0.002 1 2
    1 3063109 C T intronico PRDM16 0.002 0 2
    1 3063593 T C íntronico PRDM16 0.003 1 2
    1 3076439 C T intronico PRDM16 0.001 2 4
    1 3078960 G A íntronico PRDM16 0.007 2 4
    1 3084268 A C íntronico PRDM16 0.005 0 2
    1 3084492 T C íntronico PRDM16 0.0015 0 2
    1 3084786 T C íntronico PRDM16 0.0015 0 4
    1 3111119 G A íntronico PRDM16 0.003 1 2
    1 3111643 C T intronico PRDM16 0.0041 1 2
    1 3114807 G A íntronico PRDM16 0.0041 1 2
    1 3165530 C T intronico PRDM16 0.0089 1 2
    1 3169325 G A íntronico 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 íntronico PRDM16 0.005 2 4
    1 3195769 T C íntronico PRDM16 0.002 1 2
    1 3197351 C T intronico PRDM16 0.0061 1 2
    1 3224100 C A íntronico PRDM16 0.003 2 4
    1 3228644 G T intronico PRDM16 0.001 2 4
    1 3234045 G A íntronico PRDM16 0.002 1 2
    1 3235971 G A íntronico PRDM16 0.0089 1 2
    1 3274115 C T intronico PRDM16 0.001 2 4
    1 3291388 G A íntronico PRDM16 0.002 2 4
    1 3295658 A C íntronico PRDM16 0.0068 0 6
    1 3295937 A C íntronico PRDM16 0.0068 0 2
    1 3296205 T C íntronico PRDM16 0.0083 0 2
    1 3315690 G A íntronico PRDM16 0.001 2 4
    1 3329212 G A exónico PRDM16 0.0031 de cambio de sentido 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 íntronico ARHGEF16 0.0051 0 6
    1 3391174 C T íntronico ARHGEF16 0.006 1 2
    1 3413873 G A exónico MEGF6 0.003 de cambio de sentido 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 y LOC649330 0.006 error 0 10
    1 12907518 C A exónico HNRNPCL1,LOC649330 1.0E-4 error 0 10
    1 12908499 G C intronico 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 íntronico PRAMEF4 0.0076 0 10
    1 12942805 T G íntronico PRAMEF4 0.0061 0 12
    1 12942812 G A intronico PRAMEF4 0.0061 0 12
    1 12942875 A G íntronico 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 de cambio de sentido 0 2
    1 12942940 T G exónico PRAMEF4 0.0038 de cambio de sentido 0 2
    1 12943940 T C intronico PRAMEF4 0.0015 0 12
    1 12944138 A G íntronico PRAMEF4 8.0E-4 0 12
    1 12944234 G A intronico PRAMEF4 0.0015 0 12
    1 12944589 T G íntronico 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 íntronico FHAD1 0.0029 1 2
    1 15713292 C T intronico FHAD1 0.001 1 2
    1 15766541 G C íntronico CTRC 0.001 1 2
    1 15782601 T C upstream CELA2A 0.0038 1 2
    1 15828125 G A intronico CASP9 0.0014 2 4
    1 15831037 G A intronico CASP9 0.0099 1 2
    1 15840513 T G intronico CASP9 0.0043 2 4
    1 15868742 G A íntronico DNAJC16 0.001 1 2
    1 15876704 G A íntronico DNAJC16 0.001 1 2
    1 15900342 C A íntronico 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 intronico ZBTB40 0.0089 2 4
    1 22874394 C G intergénico ZBTB40 y EPHA8 0.007 1 2
    1 22875103 C G intergénico ZBTB40 y EPHA8 0.007 1 2
    1 22906403 C T íntronico EPHA8 0.008 2 4
    1 22912956 G A intronico EPHA8 0.001 1 2
    1 22917007 C T íntronico EPHA8 0.001 2 4
    1 22927240 G A exónico EPHA8 0.0013 error 2 4
    1 22932265 G A intergénico EPHA8 y MIR6127 0.0089 2 4
    1 22944057 C T intergénico EPHA8 y MIR6127 0.0089 2 4
    1 22978799 A G upstream C1QB 0.0099 2 4
    1 35170588 C T intergénico C1orf94 y 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 y GJB5 0.0099 2 4
    1 35186520 A C intergénico C1orf94 y GJB5 0.002 2 4
    1 35196361 G A intergénico C1orf94 y 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 y GJB3 0.0043 1 2
    1 35232954 T C intergénico GJB4 y GJB3 0.003 1 2
    1 35237986 G A intergénico GJB4 y GJB3 0.0014 1 2
    1 35245522 C T intergénico GJB4 y GJB3 0.001 1 2
    1 35256979 C T intergénico GJB3 y GJA4 0.002 2 4
    1 35263872 C T intergénico GJA4 y SMIM12 5.0E-4 2 4
    1 35323895 A C íntronico SMIM12 0.0027 2 4
    1 35369676 G A íntronico 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 íntronico RRAGC 0.0038 2 4
    1 39361372 G A íntronico RHBDL2 0.005 1 2
    1 39363826 T G íntronico RHBDL2 0.0029 1 2
    1 39367555 T C intronico RHBDL2 0.007 2 4
    1 39369531 T C intronico RHBDL2 0.001 2 4
    1 39370202 T C intronico RHBDL2 0.01 1 2
    1 39449101 A G intergénico RHBDL2 y AKIRIN1 0.001 2 4
    1 39475057 G A intergénico AKIRIN1 y NDUFS5 0.01 1 2
    1 39485016 C T intergénico AKIRIN1 y NDUFS5 0.001 2 4
    1 39488137 A G intergénico AKIRIN1 y NDUFS5 0.001 2 4
    1 39499212 A C intronico NDUFS5 0.001 0 2
    1 39500605 C G downstream NDUFS5 0.002 0 10
    1 46813814 T C íntronico NSUN4 0.0014 1 2
    1 46817258 A G íntronico 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 intronico GLMN 0.001 2 4
    1 92764270 G C íntronico 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 íntronico RPAP2 0.0058 1 2
    1 92820953 G A intronico RPAP2 0.007 2 4
    1 92824766 A G íntronico RPAP2 0.0058 1 2
    1 92849183 C A intronico RPAP2 0.01 2 4
    1 92850696 C G íntronico RPAP2 0.0023 1 2
    1 92861357 T C intergénico RPAP2,GFI1 0.01 2 4
    1 92877460 C G intergénico RPAP2,GFI1 0.002 1 2
    1 92880643 A G intergénico RPAP2,GFI1 0.001 2 4
    1 92911540 G A intergénico RPAP2,GFI1 0.004 2 4
    1 92911721 A C intergénico RPAP2,GFI1 0.0031 0 8
    1 92918277 C T intergénico RPAP2,GFI1 0.001 2 4
    1 92950920 G A íntronico 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 íntronico EVI5 0.001 2 4
    1 92988342 C T íntronico EVI5 0.008 2 4
    1 92992283 G A íntronico EVI5 0.01 2 4
    1 92999760 C T íntronico EVI5 0.003 1 2
    1 93005149 G C intronico EVI5 0.003 0 4
    1 93018543 A T íntronico EVI5 0.01 2 4
    1 93033744 C T íntronico EVI5 0.001 2 4
    1 111400296 G A intergénico KCNA3,CD53 0.0014 2 4
    1 111411924 C T intergénico KCNA3,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,DRAM2 0.002 2 4
    1 111574573 G T intergénico LRIF1,DRAM2 0.0072 2 4
    1 111574594 T A intergénico LRIF1,DRAM2 0.005 1 2
    1 111574647 G A intergénico LRIF1,DRAM2 0.005 1 2
    1 111591746 T A intergénico LRIF1,DRAM2 0.005 1 2
    1 111601459 A G intergénico LRIF1,DRAM2 0.005 1 2
    1 111604748 G C intergénico LRIF1,DRAM2 0.005 1 2
    1 112191526 T G intronico RAP1A 0.001 2 4
    1 112206765 A G intronico RAP1A 0.0043 1 2
    1 112226517 G A intronico RAP1A 0.001 0 2
    1 112263324 G T intergénico RAP1A y FAM212B 0.003 2 4
    1 112264843 G A UTR3 FAM212B 0.001 1 2
    1 112285810 C T ncRNA_intronic FAM212B-AS1 0.004 1 2
    1 112304285 T C íntronico DDX20 0.0043 1 2
    1 112307213 A C íntronico DDX20 0.0043 1 2
    1 112309436 G T exónico DDX20 0.0 de cambio de sentido 1 2
    1 112317384 T C intergénico DDX20,KCND3 0.0014 1 2
    1 112381367 C T íntronico KCND3 0.002 1 2
    1 112396571 G T ncRNA_exonic CCND3-IT1 0.001 1 2
    1 113520038 G A intergénico SLC16A1-AS1,LOC100996251 0.0023 1 2

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