Esecuzione di JOIN di intervallo con BigQuery

Puoi utilizzare BigQuery per eseguire una query JOIN su varianti con dati descritti da intervalli di regioni genomiche o sovrapposizioni. Questa pagina mostra come utilizzare una query JOIN complessa per prendere un elenco di nomi di geni ed eseguire quanto segue:

  • Trova gli SNP rari che si sovrappongono ai geni.
  • Trova 100.000 paia di basi su entrambi i lati di un gene per tutti i campioni del genoma.

Questa guida mostra esempi di tre query. Ogni query dimostra come BigQuery scala su diverse dimensioni di dati genomici:

I dati provengono dall'annotazione Tute Genomics, che comprende quasi 9 miliardi di righe e il set di dati Illumina Platinum Genomes. Se non hai familiarità con questi set di dati, consulta i seguenti link:

Eseguire query su una tabella in linea

L'esempio seguente utilizza una tabella di intervalli definita nella query, chiamata intervals, e mostra come eseguire una query JOIN con una tabella contenente varianti di Illumina Platinum Genomes:

  1. Vai alla pagina BigQuery nella console Google Cloud.

    Vai alla pagina BigQuery

  2. Fai clic su Componi query.

  3. Nel campo Nuova query, esegui questa query:

    #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. Fai clic su Esegui query. L'esecuzione della query richiede circa dieci secondi ed elabora circa 334 GB di dati. I risultati della query identificano varianti all'interno della coorte che si sovrappongono alle regioni di interesse.

    Espandi la sezione seguente per vedere i risultati della query:

    Risultati delle query

    Chr Inizia Rif Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C intronico 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 intergenico HDGF,PRCC 0,006 1 2
    1 156724456 C T intergenico HDGF,PRCC 0,002 2 4
    1 156733988 C T intergenico HDGF,PRCC 0,001 1 2
    1 156742258 T V intronico PRCC 0,001 2 4
    1 156744826 T V intronico PRCC 0,002 0 8
    1 156779764 V A intronico 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 intronico NTRK1 0,002 1 2
    1 156815332 A C intronico INSRR,NTRK1 0,003 0 2
    1 156830778 V A esonico NTRK1 0,0067 missense 2 4
    1 156842064 C T intronico NTRK1 0,0014 1 2
    1 156843438 C A esonico NTRK1 0,0032 di tipo missenso 1 2
    1 156845773 C T intronico NTRK1 0,001 2 4
    1 156873318 T C intronico PEAR1 0,01 4 8
    1 156922740 V A intronico ARHGEF11 0,007 1 2
    1 156930100 C T intronico ARHGEF11 0,001 2 4
    2 113901230 V A intergenico IL1RN,PSD4 0,0082 1 2
    2 113953418 C A intronico PSD4 0,001 2 4
    2 113967621 V C intergenico PSD4,PAX8 0,002 0 6
    2 113967624 T C intergenico PSD4,PAX8 0,002 0 2
    2 113980967 V A intronico 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 intergenico PAX8,CBWD2 0,001 2 4
    2 114084018 A C intergenico PAX8,CBWD2 0,0045 0 4
    2 114099037 V A intergenico PAX8,CBWD2 0,0051 1 2
    2 114105670 A T intergenico PAX8,CBWD2 0,001 1 2
    2 114111325 V T intergenico PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergenico SYN2,PPARG 0,0089 2 4
    3 12277958 A V intergenico SYN2,PPARG 0,002 1 2
    3 12296019 V A intergenico SYN2,PPARG 0,002 2 4
    3 12316549 V C intergenico SYN2,PPARG 0,002 1 2
    3 12335681 T V intronico 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 V A intronico PPARG 0,0051 2 4
    3 12404394 V A intronico PPARG 0,001 1 2
    3 12410289 V A intronico PPARG 0,008 2 4
    3 12431381 C T intronico PPARG 0,0061 2 4
    3 12447267 V A intronico PPARG 0,0089 2 4
    3 12449379 C T intronico PPARG 0,0092 2 4
    3 12450848 C A intronico PPARG 0,0092 2 4
    3 12462847 T C intronico PPARG 0,002 1 2
    3 12492797 V A intergenico PPARG,TSEN2 0,01 1 2
    3 12503201 V A intergenico PPARG,TSEN2 0,0099 2 4
    3 12530460 A V intronico TSEN2 0,0092 2 4
    3 12531167 A V intronico TSEN2 0,0099 2 4
    3 12557737 A V intronico TSEN2 0,001 2 4
    3 59636143 A V intergenico C3orf67,FHIT 0,003 3 6
    3 59645934 A C intergenico C3orf67,FHIT 0,004 1 2
    3 59646893 V A intergenico C3orf67,FHIT 0,002 1 2
    3 59697024 A V intergenico C3orf67,FHIT 0,0072 1 2
    3 59701013 V A intergenico C3orf67,FHIT 0,004 2 4
    3 59733945 A V intergenico C3orf67,FHIT 0,001 2 4
    3 59747482 C T intronico FHIT 0,001 2 4
    3 59750635 A V intronico FHIT 0,003 1 2
    3 59757776 C T intronico FHIT 0,001 2 4
    3 59770612 V A intronico FHIT 0,001 2 4
    3 59804444 V 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 V A intronico FHIT 0,002 1 2
    3 59972417 T A intronico FHIT 0,0072 0 2
    3 60104328 C A intronico FHIT 0,01 2 4
    3 60139062 V A intronico 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 V A intronico FHIT 0,007 2 4
    3 60234539 C A intronico FHIT 0,002 1 2
    3 60247464 A C intronico FHIT 0,004 2 4
    3 60269926 A V intronico FHIT 0,007 2 4
    3 60271228 V T intronico FHIT 0,007 2 4
    3 60286972 T C intronico FHIT 0,001 2 4
    3 60301412 C V intronico FHIT 0,001 1 2
    3 60312251 C T intronico FHIT 0,0099 1 2
    3 60317682 A V intronico FHIT 0,008 1 2
    3 60328557 C V intronico FHIT 0,0043 2 4
    3 60342562 C T intronico FHIT 0,006 1 2
    3 60400033 V A intronico FHIT 0,004 2 4
    3 60435819 C T intronico FHIT 0,006 2 4
    3 60435820 V T intronico FHIT 0,004 1 2
    3 60441288 T C intronico FHIT 0,006 2 4
    3 60444465 C A intronico 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 V A intronico FHIT 0,001 2 4
    3 60473568 C V intronico FHIT 0,001 1 2
    3 60487557 T C intronico FHIT 0,001 1 2
    3 60559705 A V 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 V A intronico FHIT 0,004 1 2
    3 60599869 V A intronico 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 V intronico FHIT 0,001 2 4
    3 60619756 V T intronico FHIT 0,0015 2 4
    3 60680758 C T intronico FHIT 0,0089 2 4
    3 60702243 V C intronico FHIT 0,001 2 4
    3 60702532 A V intronico FHIT 0,001 1 2
    3 60714328 A T intronico FHIT 0,004 1 2
    3 60725297 V A intronico FHIT 0,001 1 2
    3 60726640 V A intronico FHIT 0,01 2 4
    3 60795144 A V intronico FHIT 0,001 2 4
    3 60807171 A V intronico FHIT 0,001 1 2
    3 60813868 T C intronico FHIT 0,001 1 2
    3 60826546 C V intronico FHIT 0,0023 1 2
    3 60837392 C T intronico FHIT 0,001 1 2
    3 60846310 A V 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 intronico FHIT 0,001 2 4
    3 60940759 C T intronico FHIT 0,0089 1 2
    3 60982595 A V intronico FHIT 0,003 2 4
    3 60999283 V A intronico FHIT 0,001 1 2
    3 61042977 A V 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 V A intronico FHIT 0,003 1 2
    3 61148655 V C intronico FHIT 0,001 2 4
    3 61170747 C T intronico FHIT 0,003 1 2
    3 61189473 C V 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 V intronico FHIT 0,0099 0 2
    3 61194886 T A intronico 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 V 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 intergenico FHIT,PTPRG 0,0089 1 2
    3 61293731 T A intergenico FHIT,PTPRG 0,0089 2 4
    3 61296730 C T intergenico FHIT,PTPRG 0,001 1 2
    3 61326341 C T intergenico FHIT,PTPRG 0,004 2 4
    3 61326620 T C intergenico FHIT,PTPRG 0,01 1 2
    3 61327649 V C intergenico FHIT,PTPRG 0,001 2 4
    3 61330545 V C intergenico FHIT,PTPRG 0,001 2 4
    3 61335803 V A intergenico FHIT,PTPRG 0,001 2 4

    Una query simile che utilizza i dati della fase 3 del progetto 1000 Genomes richiede circa 90 secondi per l'esecuzione ed elabora circa 3,38 TB di dati.

Usa una tabella materializzata

Quando lavori con i big data su larga scala, puoi materializzare una tabella a intervalli ed esegui una query JOIN sulla nuova tabella. Prima di continuare con il resto di questa sezione, crea un set di dati seguendo questi passaggi:

  1. Apri la pagina BigQuery nella console Google Cloud.

    Vai alla pagina di BigQuery

  2. Nel riquadro Explorer, seleziona il progetto in cui vuoi creare il set di dati.

  3. Espandi Azioni e fai clic su Crea set di dati.

  4. Nella pagina Crea set di dati:

    1. In ID set di dati, inserisci genomics.
    2. Lascia invariate le altre impostazioni predefinite.
    3. Fai clic su Crea set di dati.

Eseguire query su una tabella materializzata con geni specifici

I passaggi seguenti mostrano come materializzare una nuova tabella intervallo che contiene un elenco di geni specifici Tabella silver-wall-555:TuteTable.hg19.

  1. Per creare la tabella degli intervalli:

    1. Apri la pagina BigQuery nella console Google Cloud.

      Vai alla pagina di BigQuery

    2. Fai clic su Componi query.

    3. Nel campo Nuova query, esegui la seguente query. La query materializza parte della tabella silver-wall-555:TuteTable.hg19 in una nuova tabella di intervalli 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. Fai clic su Esegui query. La query restituisce il seguente risultato:

    This statement created a new table named PROJECT_ID:genomics.myIntervalTable.

  2. Nel campo Nuova query, esegui la seguente query:

    #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. Fai clic su Esegui query. L'esecuzione della query richiede circa dieci secondi ed elabora circa 334 GB di dati. I risultati della query identificano varianti all'interno della coorte che si sovrappongono alle regioni di interesse.

    Espandi la sezione seguente per vedere i risultati della query:

    Risultati delle query

    Chr Inizia Rif Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C intronico 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 intergenico HDGF,PRCC 0,006 1 2
    1 156724456 C T intergenico HDGF,PRCC 0,002 2 4
    1 156733988 C T intergenico HDGF,PRCC 0,001 1 2
    1 156742258 T V intronico PRCC 0,001 2 4
    1 156744826 T V intronico PRCC 0,002 0 8
    1 156779764 V A intronico 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 intronico NTRK1 0,002 1 2
    1 156815332 A C intronico INSRR,NTRK1 0,003 0 2
    1 156830778 V A esonico NTRK1 0,0067 missense 2 4
    1 156842064 C T intronico NTRK1 0,0014 1 2
    1 156843438 C A esonico NTRK1 0,0032 di tipo missenso 1 2
    1 156845773 C T intronico NTRK1 0,001 2 4
    1 156873318 T C intronico PEAR1 0,01 4 8
    1 156922740 V A intronico ARHGEF11 0,007 1 2
    1 156930100 C T intronico ARHGEF11 0,001 2 4
    2 113901230 V A intergenico IL1RN,PSD4 0,0082 1 2
    2 113953418 C A intronico PSD4 0,001 2 4
    2 113967621 V C intergenico PSD4,PAX8 0,002 0 6
    2 113967624 T C intergenico PSD4,PAX8 0,002 0 2
    2 113980967 V A intronico 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 intergenico PAX8,CBWD2 0,001 2 4
    2 114084018 A C intergenico PAX8,CBWD2 0,0045 0 4
    2 114099037 V A intergenico PAX8,CBWD2 0,0051 1 2
    2 114105670 A T intergenico PAX8,CBWD2 0,001 1 2
    2 114111325 V T intergenico PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergenico SYN2,PPARG 0,0089 2 4
    3 12277958 A V intergenico SYN2,PPARG 0,002 1 2
    3 12296019 V A intergenico SYN2,PPARG 0,002 2 4
    3 12316549 V C intergenico SYN2,PPARG 0,002 1 2
    3 12335681 T V intronico 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 V A intronico PPARG 0,0051 2 4
    3 12404394 V A intronico PPARG 0,001 1 2
    3 12410289 V A intronico PPARG 0,008 2 4
    3 12431381 C T intronico PPARG 0,0061 2 4
    3 12447267 V A intronico PPARG 0,0089 2 4
    3 12449379 C T intronico PPARG 0,0092 2 4
    3 12450848 C A intronico PPARG 0,0092 2 4
    3 12462847 T C intronico PPARG 0,002 1 2
    3 12492797 V A intergenico PPARG,TSEN2 0,01 1 2
    3 12503201 V A intergenico PPARG,TSEN2 0,0099 2 4
    3 12530460 A V intronico TSEN2 0,0092 2 4
    3 12531167 A V intronico TSEN2 0,0099 2 4
    3 12557737 A V intronico TSEN2 0,001 2 4
    3 59636143 A V intergenico C3orf67,FHIT 0,003 3 6
    3 59645934 A C intergenico C3orf67,FHIT 0,004 1 2
    3 59646893 V A intergenico C3orf67,FHIT 0,002 1 2
    3 59697024 A V intergenico C3orf67,FHIT 0,0072 1 2
    3 59701013 V A intergenico C3orf67,FHIT 0,004 2 4
    3 59733945 A V intergenico C3orf67,FHIT 0,001 2 4
    3 59747482 C T intronico FHIT 0,001 2 4
    3 59750635 A V intronico FHIT 0,003 1 2
    3 59757776 C T intronico FHIT 0,001 2 4
    3 59770612 V A intronico FHIT 0,001 2 4
    3 59804444 V 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 V A intronico FHIT 0,002 1 2
    3 59972417 T A intronico FHIT 0,0072 0 2
    3 60104328 C A intronico FHIT 0,01 2 4
    3 60139062 V A intronico 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 V A intronico FHIT 0,007 2 4
    3 60234539 C A intronico FHIT 0,002 1 2
    3 60247464 A C intronico FHIT 0,004 2 4
    3 60269926 A V intronico FHIT 0,007 2 4
    3 60271228 V T intronico FHIT 0,007 2 4
    3 60286972 T C intronico FHIT 0,001 2 4
    3 60301412 C V intronico FHIT 0,001 1 2
    3 60312251 C T intronico FHIT 0,0099 1 2
    3 60317682 A V intronico FHIT 0,008 1 2
    3 60328557 C V intronico FHIT 0,0043 2 4
    3 60342562 C T intronico FHIT 0,006 1 2
    3 60400033 V A intronico FHIT 0,004 2 4
    3 60435819 C T intronico FHIT 0,006 2 4
    3 60435820 V T intronico FHIT 0,004 1 2
    3 60441288 T C intronico FHIT 0,006 2 4
    3 60444465 C A intronico 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 V A intronico FHIT 0,001 2 4
    3 60473568 C V intronico FHIT 0,001 1 2
    3 60487557 T C intronico FHIT 0,001 1 2
    3 60559705 A V 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 V A intronico FHIT 0,004 1 2
    3 60599869 V A intronico 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 V intronico FHIT 0,001 2 4
    3 60619756 V T intronico FHIT 0,0015 2 4
    3 60680758 C T intronico FHIT 0,0089 2 4
    3 60702243 V C intronico FHIT 0,001 2 4
    3 60702532 A V intronico FHIT 0,001 1 2
    3 60714328 A T intronico FHIT 0,004 1 2
    3 60725297 V A intronico FHIT 0,001 1 2
    3 60726640 V A intronico FHIT 0,01 2 4
    3 60795144 A V intronico FHIT 0,001 2 4
    3 60807171 A V intronico FHIT 0,001 1 2
    3 60813868 T C intronico FHIT 0,001 1 2
    3 60826546 C V intronico FHIT 0,0023 1 2
    3 60837392 C T intronico FHIT 0,001 1 2
    3 60846310 A V 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 intronico FHIT 0,001 2 4
    3 60940759 C T intronico FHIT 0,0089 1 2
    3 60982595 A V intronico FHIT 0,003 2 4
    3 60999283 V A intronico FHIT 0,001 1 2
    3 61042977 A V 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 V A intronico FHIT 0,003 1 2
    3 61148655 V C intronico FHIT 0,001 2 4
    3 61170747 C T intronico FHIT 0,003 1 2
    3 61189473 C V 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 V intronico FHIT 0,0099 0 2
    3 61194886 T A intronico 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 V 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 intergenico FHIT,PTPRG 0,0089 1 2
    3 61293731 T A intergenico FHIT,PTPRG 0,0089 2 4
    3 61296730 C T intergenico FHIT,PTPRG 0,001 1 2
    3 61326341 C T intergenico FHIT,PTPRG 0,004 2 4
    3 61326620 T C intergenico FHIT,PTPRG 0,01 1 2
    3 61327649 V C intergenico FHIT,PTPRG 0,001 2 4
    3 61330545 V C intergenico FHIT,PTPRG 0,001 2 4
    3 61335803 V A intergenico FHIT,PTPRG 0,001 2 4

    Una query simile che utilizza i dati della fase 3 del progetto 1000 Genomes richiede circa 90 secondi per l'esecuzione ed elabora circa 3,38 TB di dati.

Query su una tabella materializzata con 250 geni casuali

L'esempio seguente mostra come eseguire un intervallo JOIN su una tabella materializzata contenente 250 geni selezionati in modo casuale dalla tabella silver-wall-555:TuteTable.hg19.

  1. Per creare la tabella degli intervalli:

    1. Apri la pagina BigQuery nella console Google Cloud.

      Vai alla pagina BigQuery

    2. Fai clic su Crea query.

    3. Nel campo Nuova query, esegui la seguente query, che si materializza parte della tabella Silver-wall-555:TuteTable.hg19 a un nuovo Tabella degli intervalli 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. Fai clic su Esegui query. La query restituisce il seguente risultato:
      This statement created a new table named PROJECT_ID:genomics.randomGenesIntervalTable.

  2. Nel campo Nuova query, esegui la seguente query:

    #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. Fai clic su Esegui query. L'esecuzione della query richiede circa dieci secondi ed elabora circa 334 GB di dati. I risultati della query identificano le varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.

    Espandi la sezione seguente per visualizzare i risultati troncati della query:

    Risultati delle query

    Chr Inizia Rif Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 2925355 C A intergenico TTC34,ACTRT2 0,001 2 4
    1 2933170 V A intergenico TTC34,ACTRT2 0,0083 0 4
    1 2944477 V A intergenico ACTRT2,LINC00982 0,003 4 6
    1 2967591 A T intergenico 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 V C ncRNA_exonic LINC00982 0,002 4 6
    1 3006466 V A intronico PRDM16 0,0098 1 2
    1 3011333 V T intronico PRDM16 0,004 1 2
    1 3019659 C T intronico PRDM16 0,0031 1 2
    1 3036896 V A intronico PRDM16 0,001 1 2
    1 3037388 V A intronico PRDM16 0,002 2 4
    1 3041250 T V 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 V 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 V A intronico PRDM16 0,003 1 2
    1 3111643 C T intronico PRDM16 0,0041 1 2
    1 3114807 V A intronico PRDM16 0,0041 1 2
    1 3165530 C T intronico PRDM16 0,0089 1 2
    1 3169325 V 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 V 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 V T intronico PRDM16 0,001 2 4
    1 3234045 V A intronico PRDM16 0,002 1 2
    1 3235971 V A intronico PRDM16 0,0089 1 2
    1 3274115 C T intronico PRDM16 0,001 2 4
    1 3291388 V 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 V A intronico PRDM16 0,001 2 4
    1 3329212 V A esogeno PRDM16 0,0031 missense 1 2
    1 3331787 C T intronico PRDM16 0,0099 1 2
    1 3370316 V C upstream ARHGEF16 0,001 2 4
    1 3379560 A V intronico ARHGEF16 0,0051 0 6
    1 3391174 C T intronico ARHGEF16 0,006 1 2
    1 3413873 V A esonico MEGF6 0,003 missense 1 2
    1 3416272 C T esonico MEGF6 0,0072 silenzioso 2 4
    1 3417122 V A intronico MEGF6 0,0038 2 4
    1 3436219 V A intronico MEGF6 0,0046 2 4
    1 12907456 A V esogeno HNRNPCL1,LOC649330 0,006 di tipo missenso 0 10
    1 12907518 C A esonico HNRNPCL1,LOC649330 1,0E-4 missense 0 10
    1 12908499 V C intronico HNRNPCL1 0,0031 0 8
    1 12931660 V C intergenico PRAMEF2,PRAMEF4 0,004 1 2
    1 12937721 V T intergenico PRAMEF2,PRAMEF4 0,0038 0 2
    1 12940827 V T intronico PRAMEF4 0,007 2 4
    1 12942759 T V intronico PRAMEF4 0,0076 0 10
    1 12942805 T V intronico PRAMEF4 0,0061 0 12
    1 12942812 V A intronico PRAMEF4 0,0061 0 12
    1 12942875 A V intronico PRAMEF4 0,0068 0 6
    1 12942912 V C intronico PRAMEF4 2,0E-4 0 2
    1 12942937 A T esogeno PRAMEF4 0,0029 missense 0 2
    1 12942940 T V esogeno PRAMEF4 0,0038 missense 0 2
    1 12943940 T C intronico PRAMEF4 0,0015 0 12
    1 12944138 A V intronico PRAMEF4 8,0E-4 0 12
    1 12944234 V A intronico PRAMEF4 0,0015 0 12
    1 12944589 T V 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 V A upstream PRAMEF4 0,001 0 8
    1 12946835 T A upstream PRAMEF4 0,004 0 12
    1 12995204 V T intergenico PRAMEF8,PRAMEF6 0,003 1 4
    1 12997638 T C downstream PRAMEF6,PRAMEF9 0,003 2 4
    1 13007841 V C upstream PRAMEF6 0,0043 0 8
    1 13019228 T A intergenico PRAMEF6,LOC391003 0,0015 0 10
    1 13038503 V A UTR3 LOC391003 0,0072 1 2
    1 13051650 C T intergenico LOC391003,PRAMEF5 0,002 2 4
    1 15706063 V A intronico FHAD1 0,0029 1 2
    1 15713292 C T intronico FHAD1 0,001 1 2
    1 15766541 V C intronico CTRC 0,001 1 2
    1 15782601 T C upstream CELA2A 0,0038 1 2
    1 15828125 V A intronico CASP9 0,0014 2 4
    1 15831037 V A intronico CASP9 0,0099 1 2
    1 15840513 T V intronico CASP9 0,0043 2 4
    1 15868742 V A intronico DNAJC16 0,001 1 2
    1 15876704 V A intronico DNAJC16 0,001 1 2
    1 15900342 C A intronico AGMAT 0,001 1 2
    1 15906257 T C intronico AGMAT 8,0E-4 1 2
    1 15911897 A V upstream AGMAT 0,0043 2 4
    1 22764178 C T intergenico WNT4,ZBTB40 0,001 2 4
    1 22791939 C T intronico ZBTB40 0,0089 2 4
    1 22874394 C V intergenico ZBTB40,EPHA8 0,007 1 2
    1 22875103 C V intergenico ZBTB40,EPHA8 0,007 1 2
    1 22906403 C T intronico EPHA8 0,008 2 4
    1 22912956 V A intronico EPHA8 0,001 1 2
    1 22917007 C T intronico EPHA8 0,001 2 4
    1 22927240 V A esogeno EPHA8 0,0013 di tipo missenso 2 4
    1 22932265 V A intergenico EPHA8,MIR6127 0,0089 2 4
    1 22944057 C T intergenico EPHA8,MIR6127 0,0089 2 4
    1 22978799 A V upstream C1QB 0,0099 2 4
    1 35170588 C T intergenico C1orf94,GJB5 0,01 1 2
    1 35172426 C T intergenico C1orf94,GJB5 0,008 1 2
    1 35172447 V A intergenico C1orf94,GJB5 0,001 1 2
    1 35175302 C T intergenico C1orf94,GJB5 0,008 1 2
    1 35177410 A T intergenico C1orf94,GJB5 0,001 1 2
    1 35178768 C T intergenico C1orf94,GJB5 0,0014 2 4
    1 35179362 V A intergenico C1orf94,GJB5 0,0014 2 4
    1 35186166 V A intergenico C1orf94,GJB5 0,0099 2 4
    1 35186520 A C intergenico C1orf94,GJB5 0,002 2 4
    1 35196361 V A intergenico C1orf94,GJB5 0,0099 2 4
    1 35223545 C T esonico GJB5 0,001 silenzioso 1 2
    1 35224029 V A UTR3 GJB5 0,003 1 2
    1 35227895 T C UTR3 GJB4 5.0E-4 1 2
    1 35230455 V T intergenico GJB4,GJB3 0,0043 1 2
    1 35232954 T C intergenico GJB4,GJB3 0,003 1 2
    1 35237986 V A intergenico GJB4, GJB3 0,0014 1 2
    1 35245522 C T intergenico GJB4,GJB3 0,001 1 2
    1 35256979 C T intergenico GJB3,GJA4 0,002 2 4
    1 35263872 C T intergenico GJA4,SMIM12 5.0E-4 2 4
    1 35323895 A C intronico SMIM12 0,0027 2 4
    1 35369676 V A intronico DLGAP3 0,007 2 4
    1 35371634 T A upstream DLGAP3 0,0015 0 4
    1 39253519 V A intergenico LINC01343,RRAGC 0,005 1 2
    1 39288829 V A intergenico LINC01343,RRAGC 0,0051 1 2
    1 39289832 A C intergenico LINC01343,RRAGC 0,002 0 2
    1 39312638 V A intronico RRAGC 0,0038 2 4
    1 39361372 V A intronico RHBDL2 0,005 1 2
    1 39363826 T V intronico 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 V intergenico RHBDL2,AKIRIN1 0,001 2 4
    1 39475057 V A intergenico AKIRIN1,NDUFS5 0,01 1 2
    1 39485016 C T intergenico AKIRIN1,NDUFS5 0,001 2 4
    1 39488137 A V intergenico AKIRIN1,NDUFS5 0,001 2 4
    1 39499212 A C intronico NDUFS5 0,001 0 2
    1 39500605 C V downstream NDUFS5 0,002 0 10
    1 46813814 T C intronico NSUN4 0,0014 1 2
    1 46817258 A V intronico NSUN4 0,005 0 2
    1 46843158 T C intergenico NSUN4,FAAH 0,001 1 2
    1 46933509 A V intergenico LINC01398,DMBX1 0,002 1 2
    1 46935021 V A intergenico LINC01398,DMBX1 0,004 2 4
    1 46939253 T A intergenico LINC01398,DMBX1 0,004 2 4
    1 46951788 C A intergenico LINC01398,DMBX1 0,002 2 4
    1 46980864 V C downstream DMBX1 0,003 1 2
    1 46989657 T C intergenico DMBX1,MKNK1-AS1 0,007 1 2
    1 46994678 C T intergenico DMBX1,MKNK1-AS1 0,002 1 2
    1 46999438 T C intergenico DMBX1,MKNK1-AS1 0,002 1 2
    1 92761505 A V intronico GLMN 0,001 2 4
    1 92764270 V C intronico GLMN 0,001 2 4
    1 92802210 V A intronico RPAP2 0,0072 1 2
    1 92820663 T A intronico RPAP2 0,0058 1 2
    1 92820664 V T intronico RPAP2 0,0058 1 2
    1 92820953 V A intronico RPAP2 0,007 2 4
    1 92824766 A V intronico RPAP2 0,0058 1 2
    1 92849183 C A intronico RPAP2 0,01 2 4
    1 92850696 C V intronico RPAP2 0,0023 1 2
    1 92861357 T C intergenico RPAP2,GFI1 0,01 2 4
    1 92877460 C V intergenico RPAP2,GFI1 0,002 1 2
    1 92880643 A V intergenico RPAP2,GFI1 0,001 2 4
    1 92911540 V A intergenico RPAP2,GFI1 0,004 2 4
    1 92911721 A C intergenico RPAP2,GFI1 0,0031 0 8
    1 92918277 C T intergenico RPAP2,GFI1 0,001 2 4
    1 92950920 V A intronico GFI1 0,008 2 4
    1 92964788 V A intergenico 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 V A intronico EVI5 0,01 2 4
    1 92999760 C T intronico EVI5 0,003 1 2
    1 93005149 V C intronico 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 V A intergenico KCNA3,CD53 0,0014 2 4
    1 111411924 C T intergenico KCNA3, CD53 0,003 1 2
    1 111441850 C V UTR3 CD53 0,003 2 4
    1 111451527 C T intergenico CD53,LRIF1 0,008 2 4
    1 111454082 C A intergenico CD53,LRIF1 0,001 2 4
    1 111466506 A V intergenico CD53,LRIF1 0,001 2 4
    1 111525974 V A intergenico LRIF1,DRAM2 0,002 2 4
    1 111574573 V T intergenico LRIF1,DRAM2 0,0072 2 4
    1 111574594 T A intergenico LRIF1,DRAM2 0,005 1 2
    1 111574647 V A intergenico LRIF1,DRAM2 0,005 1 2
    1 111591746 T A intergenico LRIF1,DRAM2 0,005 1 2
    1 111601459 A V intergenico LRIF1,DRAM2 0,005 1 2
    1 111604748 V C intergenico LRIF1,DRAM2 0,005 1 2
    1 112191526 T V intronico RAP1A 0,001 2 4
    1 112206765 A V intronico RAP1A 0,0043 1 2
    1 112226517 V A intronico RAP1A 0,001 0 2
    1 112263324 V T intergenico RAP1A,FAM212B 0,003 2 4
    1 112264843 V A UTR3 FAM212B 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 V T esogeno DDX20 0.0 missense 1 2
    1 112317384 T C intergenico DDX20,KCND3 0,0014 1 2
    1 112381367 C T intronico KCND3 0,002 1 2
    1 112396571 V T ncRNA_exonic KCND3-IT1 0,001 1 2
    1 113520038 V A intergenico SLC16A1-AS1,LOC100996251 0,0023 1 2

    Una query simile utilizzando i dati della fase 3 di 1000 genomi prende circa 90 secondi per l'esecuzione ed elabora circa 3,38 TB di dati.