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 ottenere un elenco di nomi di geni ed eseguire le seguenti operazioni:

  • Trova i rari SSN che si sovrappongono ai geni.
  • Trova 100.000 paia di basi su entrambi i lati di un gene per l'intero genoma dei campioni.

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

I dati provengono dalla tabella di annotazione Tute Genomics con quasi 9 miliardi di righe e dal set di dati Illumina Platinum Genomes. Se non hai familiarità con questi set di dati, consulta i seguenti link:

Esegui una query su una tabella in linea

L'esempio seguente utilizza una tabella a intervalli definita nella query, denominata 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 di BigQuery

  2. Fai clic su Crea query.

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

    #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 10 secondi ed elabora circa 334 GB di dati. I risultati della query identificano varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.

    Espandi la seguente sezione per visualizzare i risultati della query:

    Risultati delle query

    Chr Inizio Rif Alt Funz. 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 G intronico PR 0,001 2 4
    1 156744826 T G intronico PR 0,002 0 8
    1 156779764 G 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 G A esonico NTRK1 0,0067 fallimento 2 4
    1 156842064 C T intronico NTRK1 0,0014 1 2
    1 156843438 C A esonico NTRK1 0,0032 fallimento 1 2
    1 156845773 C T intronico NTRK1 0,001 2 4
    1 156873318 T C intronico PEAR1 0.01 4 8
    1 156922740 G A intronico ARHGEF11 0,007 1 2
    1 156930100 C T intronico ARHGEF11 0,001 2 4
    2 113901230 G A intergenico IL1RN,PSD4 0,0082 1 2
    2 113953418 C A intronico PSD4 0,001 2 4
    2 113967621 G C intergenico PSD4,PAX8 0,002 0 6
    2 113967624 T C intergenico PSD4,PAX8 0,002 0 2
    2 113980967 G 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 G A intergenico PAX8,CBWD2 0,0051 1 2
    2 114105670 A T intergenico PAX8,CBWD2 0,001 1 2
    2 114111325 G T intergenico PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergenico SYN2,PPARG 0,0089 2 4
    3 12277958 A G intergenico SYN2,PPARG 0,002 1 2
    3 12296019 G A intergenico SYN2,PPARG 0,002 2 4
    3 12316549 G C intergenico SYN2,PPARG 0,002 1 2
    3 12335681 T G 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 G A intronico PPARG 0,0051 2 4
    3 12404394 G A intronico PPARG 0,001 1 2
    3 12410289 G A intronico PPARG 0,008 2 4
    3 12431381 C T intronico PPARG 0,0061 2 4
    3 12447267 G 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 G A intergenico PPARG,TSEN2 0.01 1 2
    3 12503201 G A intergenico PPARG,TSEN2 0,0099 2 4
    3 12530460 A G intronico TSEN2 0,0092 2 4
    3 12531167 A G intronico TSEN2 0,0099 2 4
    3 12557737 A G intronico TSEN2 0,001 2 4
    3 59636143 A G intergenico C3orf67,FHIT 0,003 3 6
    3 59645934 A C intergenico C3orf67,FHIT 0,004 1 2
    3 59646893 G A intergenico C3orf67,FHIT 0,002 1 2
    3 59697024 A G intergenico C3orf67,FHIT 0,0072 1 2
    3 59701013 G A intergenico C3orf67,FHIT 0,004 2 4
    3 59733945 A G intergenico C3orf67,FHIT 0,001 2 4
    3 59747482 C T intronico ADATTA 0,001 2 4
    3 59750635 A G intronico ADATTA 0,003 1 2
    3 59757776 C T intronico ADATTA 0,001 2 4
    3 59770612 G A intronico ADATTA 0,001 2 4
    3 59804444 G C intronico ADATTA 0,001 2 4
    3 59819769 T C intronico ADATTA 0,001 2 4
    3 59884396 C T intronico ADATTA 0,001 2 4
    3 59960728 A C intronico ADATTA 0.01 1 2
    3 59970345 G A intronico ADATTA 0,002 1 2
    3 59972417 T A intronico ADATTA 0,0072 0 2
    3 60104328 C A intronico ADATTA 0.01 2 4
    3 60139062 G A intronico ADATTA 0.01 0 2
    3 60158066 C T intronico ADATTA 0,001 1 2
    3 60169285 C T intronico ADATTA 0,005 1 2
    3 60216185 T C intronico ADATTA 0,002 1 2
    3 60226380 G A intronico ADATTA 0,007 2 4
    3 60234539 C A intronico ADATTA 0,002 1 2
    3 60247464 A C intronico ADATTA 0,004 2 4
    3 60269926 A G intronico ADATTA 0,007 2 4
    3 60271228 G T intronico ADATTA 0,007 2 4
    3 60286972 T C intronico ADATTA 0,001 2 4
    3 60301412 C G intronico ADATTA 0,001 1 2
    3 60312251 C T intronico ADATTA 0,0099 1 2
    3 60317682 A G intronico ADATTA 0,008 1 2
    3 60328557 C G intronico ADATTA 0,0043 2 4
    3 60342562 C T intronico ADATTA 0,006 1 2
    3 60400033 G A intronico ADATTA 0,004 2 4
    3 60435819 C T intronico ADATTA 0,006 2 4
    3 60435820 G T intronico ADATTA 0,004 1 2
    3 60441288 T C intronico ADATTA 0,006 2 4
    3 60444465 C A intronico ADATTA 0.01 1 2
    3 60444575 C T intronico ADATTA 0,001 1 2
    3 60450581 T C intronico ADATTA 0.01 1 2
    3 60456571 G A intronico ADATTA 0,001 2 4
    3 60473568 C G intronico ADATTA 0,001 1 2
    3 60487557 T C intronico ADATTA 0,001 1 2
    3 60559705 A G intronico ADATTA 0,002 2 4
    3 60570764 T C intronico ADATTA 0,008 2 4
    3 60582100 C T intronico ADATTA 0,001 1 2
    3 60587192 G A intronico ADATTA 0,004 1 2
    3 60599869 G A intronico ADATTA 0,0086 2 4
    3 60603091 C T intronico ADATTA 0,001 2 4
    3 60603250 A T intronico ADATTA 0,0099 1 2
    3 60609831 T G intronico ADATTA 0,001 2 4
    3 60619756 G T intronico ADATTA 0,0015 2 4
    3 60680758 C T intronico ADATTA 0,0089 2 4
    3 60702243 G C intronico ADATTA 0,001 2 4
    3 60702532 A G intronico ADATTA 0,001 1 2
    3 60714328 A T intronico ADATTA 0,004 1 2
    3 60725297 G A intronico ADATTA 0,001 1 2
    3 60726640 G A intronico ADATTA 0.01 2 4
    3 60795144 A G intronico ADATTA 0,001 2 4
    3 60807171 A G intronico ADATTA 0,001 1 2
    3 60813868 T C intronico ADATTA 0,001 1 2
    3 60826546 C G intronico ADATTA 0,0023 1 2
    3 60837392 C T intronico ADATTA 0,001 1 2
    3 60846310 A G intronico ADATTA 0.01 0 2
    3 60850985 C T intronico ADATTA 0,004 1 2
    3 60852559 T C intronico ADATTA 0,008 1 2
    3 60871759 T C intronico ADATTA 0,004 1 2
    3 60884396 C T intronico ADATTA 0,002 2 4
    3 60897092 C A intronico ADATTA 0,001 2 4
    3 60940759 C T intronico ADATTA 0,0089 1 2
    3 60982595 A G intronico ADATTA 0,003 2 4
    3 60999283 G A intronico ADATTA 0,001 1 2
    3 61042977 A G intronico ADATTA 0,001 2 4
    3 61043349 T C intronico ADATTA 0,001 2 4
    3 61044789 A C intronico ADATTA 0,001 2 4
    3 61141621 G A intronico ADATTA 0,003 1 2
    3 61148655 G C intronico ADATTA 0,001 2 4
    3 61170747 C T intronico ADATTA 0,003 1 2
    3 61189473 C G intronico ADATTA 0,0099 1 2
    3 61190425 C T intronico ADATTA 0,0023 2 4
    3 61193853 C T intronico ADATTA 0,0099 0 2
    3 61194793 C T intronico ADATTA 0,007 0 2
    3 61194840 A G intronico ADATTA 0,0099 0 2
    3 61194886 T A intronico ADATTA 0,0099 0 2
    3 61201777 C T intronico ADATTA 0,001 2 4
    3 61202292 T C intronico ADATTA 0,007 1 2
    3 61232806 G C intronico ADATTA 0,0099 1 2
    3 61232910 C T intronico ADATTA 0,0099 1 2
    3 61235824 A T intronico ADATTA 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 G C intergenico FHIT,PTPRG 0,001 2 4
    3 61330545 G C intergenico FHIT,PTPRG 0,001 2 4
    3 61335803 G A intergenico FHIT,PTPRG 0,001 2 4

    L'esecuzione di una query simile che utilizza i dati della fase 3 di 1000 genomi richiede circa 90 secondi ed elabora circa 3,38 TB di dati.

Utilizzare una tabella materializzata

Quando lavori con i big data su larga scala, puoi materializzare una tabella a intervalli ed eseguire 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 l'opzione 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.

Query su una tabella materializzata con geni specifici

I passaggi seguenti mostrano come materializzare una nuova tabella a intervalli contenente un elenco di geni specifici della 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 Crea 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 a 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 query seguente:

    #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 10 secondi ed elabora circa 334 GB di dati. I risultati della query identificano varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.

    Espandi la seguente sezione per visualizzare i risultati della query:

    Risultati delle query

    Chr Inizio Rif Alt Funz. 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 G intronico PR 0,001 2 4
    1 156744826 T G intronico PR 0,002 0 8
    1 156779764 G 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 G A esonico NTRK1 0,0067 fallimento 2 4
    1 156842064 C T intronico NTRK1 0,0014 1 2
    1 156843438 C A esonico NTRK1 0,0032 fallimento 1 2
    1 156845773 C T intronico NTRK1 0,001 2 4
    1 156873318 T C intronico PEAR1 0.01 4 8
    1 156922740 G A intronico ARHGEF11 0,007 1 2
    1 156930100 C T intronico ARHGEF11 0,001 2 4
    2 113901230 G A intergenico IL1RN,PSD4 0,0082 1 2
    2 113953418 C A intronico PSD4 0,001 2 4
    2 113967621 G C intergenico PSD4,PAX8 0,002 0 6
    2 113967624 T C intergenico PSD4,PAX8 0,002 0 2
    2 113980967 G 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 G A intergenico PAX8,CBWD2 0,0051 1 2
    2 114105670 A T intergenico PAX8,CBWD2 0,001 1 2
    2 114111325 G T intergenico PAX8,CBWD2 0,001 1 2
    3 12265797 C T intergenico SYN2,PPARG 0,0089 2 4
    3 12277958 A G intergenico SYN2,PPARG 0,002 1 2
    3 12296019 G A intergenico SYN2,PPARG 0,002 2 4
    3 12316549 G C intergenico SYN2,PPARG 0,002 1 2
    3 12335681 T G 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 G A intronico PPARG 0,0051 2 4
    3 12404394 G A intronico PPARG 0,001 1 2
    3 12410289 G A intronico PPARG 0,008 2 4
    3 12431381 C T intronico PPARG 0,0061 2 4
    3 12447267 G 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 G A intergenico PPARG,TSEN2 0.01 1 2
    3 12503201 G A intergenico PPARG,TSEN2 0,0099 2 4
    3 12530460 A G intronico TSEN2 0,0092 2 4
    3 12531167 A G intronico TSEN2 0,0099 2 4
    3 12557737 A G intronico TSEN2 0,001 2 4
    3 59636143 A G intergenico C3orf67,FHIT 0,003 3 6
    3 59645934 A C intergenico C3orf67,FHIT 0,004 1 2
    3 59646893 G A intergenico C3orf67,FHIT 0,002 1 2
    3 59697024 A G intergenico C3orf67,FHIT 0,0072 1 2
    3 59701013 G A intergenico C3orf67,FHIT 0,004 2 4
    3 59733945 A G intergenico C3orf67,FHIT 0,001 2 4
    3 59747482 C T intronico ADATTA 0,001 2 4
    3 59750635 A G intronico ADATTA 0,003 1 2
    3 59757776 C T intronico ADATTA 0,001 2 4
    3 59770612 G A intronico ADATTA 0,001 2 4
    3 59804444 G C intronico ADATTA 0,001 2 4
    3 59819769 T C intronico ADATTA 0,001 2 4
    3 59884396 C T intronico ADATTA 0,001 2 4
    3 59960728 A C intronico ADATTA 0.01 1 2
    3 59970345 G A intronico ADATTA 0,002 1 2
    3 59972417 T A intronico ADATTA 0,0072 0 2
    3 60104328 C A intronico ADATTA 0.01 2 4
    3 60139062 G A intronico ADATTA 0.01 0 2
    3 60158066 C T intronico ADATTA 0,001 1 2
    3 60169285 C T intronico ADATTA 0,005 1 2
    3 60216185 T C intronico ADATTA 0,002 1 2
    3 60226380 G A intronico ADATTA 0,007 2 4
    3 60234539 C A intronico ADATTA 0,002 1 2
    3 60247464 A C intronico ADATTA 0,004 2 4
    3 60269926 A G intronico ADATTA 0,007 2 4
    3 60271228 G T intronico ADATTA 0,007 2 4
    3 60286972 T C intronico ADATTA 0,001 2 4
    3 60301412 C G intronico ADATTA 0,001 1 2
    3 60312251 C T intronico ADATTA 0,0099 1 2
    3 60317682 A G intronico ADATTA 0,008 1 2
    3 60328557 C G intronico ADATTA 0,0043 2 4
    3 60342562 C T intronico ADATTA 0,006 1 2
    3 60400033 G A intronico ADATTA 0,004 2 4
    3 60435819 C T intronico ADATTA 0,006 2 4
    3 60435820 G T intronico ADATTA 0,004 1 2
    3 60441288 T C intronico ADATTA 0,006 2 4
    3 60444465 C A intronico ADATTA 0.01 1 2
    3 60444575 C T intronico ADATTA 0,001 1 2
    3 60450581 T C intronico ADATTA 0.01 1 2
    3 60456571 G A intronico ADATTA 0,001 2 4
    3 60473568 C G intronico ADATTA 0,001 1 2
    3 60487557 T C intronico ADATTA 0,001 1 2
    3 60559705 A G intronico ADATTA 0,002 2 4
    3 60570764 T C intronico ADATTA 0,008 2 4
    3 60582100 C T intronico ADATTA 0,001 1 2
    3 60587192 G A intronico ADATTA 0,004 1 2
    3 60599869 G A intronico ADATTA 0,0086 2 4
    3 60603091 C T intronico ADATTA 0,001 2 4
    3 60603250 A T intronico ADATTA 0,0099 1 2
    3 60609831 T G intronico ADATTA 0,001 2 4
    3 60619756 G T intronico ADATTA 0,0015 2 4
    3 60680758 C T intronico ADATTA 0,0089 2 4
    3 60702243 G C intronico ADATTA 0,001 2 4
    3 60702532 A G intronico ADATTA 0,001 1 2
    3 60714328 A T intronico ADATTA 0,004 1 2
    3 60725297 G A intronico ADATTA 0,001 1 2
    3 60726640 G A intronico ADATTA 0.01 2 4
    3 60795144 A G intronico ADATTA 0,001 2 4
    3 60807171 A G intronico ADATTA 0,001 1 2
    3 60813868 T C intronico ADATTA 0,001 1 2
    3 60826546 C G intronico ADATTA 0,0023 1 2
    3 60837392 C T intronico ADATTA 0,001 1 2
    3 60846310 A G intronico ADATTA 0.01 0 2
    3 60850985 C T intronico ADATTA 0,004 1 2
    3 60852559 T C intronico ADATTA 0,008 1 2
    3 60871759 T C intronico ADATTA 0,004 1 2
    3 60884396 C T intronico ADATTA 0,002 2 4
    3 60897092 C A intronico ADATTA 0,001 2 4
    3 60940759 C T intronico ADATTA 0,0089 1 2
    3 60982595 A G intronico ADATTA 0,003 2 4
    3 60999283 G A intronico ADATTA 0,001 1 2
    3 61042977 A G intronico ADATTA 0,001 2 4
    3 61043349 T C intronico ADATTA 0,001 2 4
    3 61044789 A C intronico ADATTA 0,001 2 4
    3 61141621 G A intronico ADATTA 0,003 1 2
    3 61148655 G C intronico ADATTA 0,001 2 4
    3 61170747 C T intronico ADATTA 0,003 1 2
    3 61189473 C G intronico ADATTA 0,0099 1 2
    3 61190425 C T intronico ADATTA 0,0023 2 4
    3 61193853 C T intronico ADATTA 0,0099 0 2
    3 61194793 C T intronico ADATTA 0,007 0 2
    3 61194840 A G intronico ADATTA 0,0099 0 2
    3 61194886 T A intronico ADATTA 0,0099 0 2
    3 61201777 C T intronico ADATTA 0,001 2 4
    3 61202292 T C intronico ADATTA 0,007 1 2
    3 61232806 G C intronico ADATTA 0,0099 1 2
    3 61232910 C T intronico ADATTA 0,0099 1 2
    3 61235824 A T intronico ADATTA 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 G C intergenico FHIT,PTPRG 0,001 2 4
    3 61330545 G C intergenico FHIT,PTPRG 0,001 2 4
    3 61335803 G A intergenico FHIT,PTPRG 0,001 2 4

    L'esecuzione di una query simile che utilizza i dati della fase 3 di 1000 genomi richiede circa 90 secondi 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 casualmente 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 di BigQuery

    2. Fai clic su Crea query.

    3. Nel campo Nuova query, esegui la query seguente, che materializza parte della tabella silver-wall-555:TuteTable.hg19 in una nuova tabella a 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 query seguente:

    #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 10 secondi ed elabora circa 334 GB di dati. I risultati della query identificano varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.

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

    Risultati delle query

    Chr Inizio Rif Alt Funz. Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 2925355 C A intergenico TTC34,ACTRT2 0,001 2 4
    1 2933170 G A intergenico TTC34,ACTRT2 0,0083 0 4
    1 2944477 G 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 G C ncRNA_exonic LINC00982 0,002 4 6
    1 3006466 G A intronico PRDM16 0,0098 1 2
    1 3011333 G T intronico PRDM16 0,004 1 2
    1 3019659 C T intronico PRDM16 0,0031 1 2
    1 3036896 G A intronico PRDM16 0,001 1 2
    1 3037388 G A intronico PRDM16 0,002 2 4
    1 3041250 T G intronico PRDM16 0,006 2 4
    1 3042502 A T intronico PRDM16 0,003 4 6
    1 3053713 A C intronico PRDM16 0,002 1 2
    1 3063109 C T intronico PRDM16 0,002 0 2
    1 3063593 T C intronico PRDM16 0,003 1 2
    1 3076439 C T intronico PRDM16 0,001 2 4
    1 3078960 G A intronico PRDM16 0,007 2 4
    1 3084268 A C intronico PRDM16 0,005 0 2
    1 3084492 T C intronico PRDM16 0,0015 0 2
    1 3084786 T C intronico PRDM16 0,0015 0 4
    1 3111119 G A intronico PRDM16 0,003 1 2
    1 3111643 C T intronico PRDM16 0,0041 1 2
    1 3114807 G A intronico PRDM16 0,0041 1 2
    1 3165530 C T intronico PRDM16 0,0089 1 2
    1 3169325 G A intronico PRDM16 0,008 2 4
    1 3179623 C T intronico PRDM16 0,003 2 4
    1 3181097 C T intronico PRDM16 0,001 2 4
    1 3194000 G C intronico PRDM16 0,005 2 4
    1 3195769 T C intronico PRDM16 0,002 1 2
    1 3197351 C T intronico PRDM16 0,0061 1 2
    1 3224100 C A intronico PRDM16 0,003 2 4
    1 3228644 G T intronico PRDM16 0,001 2 4
    1 3234045 G A intronico PRDM16 0,002 1 2
    1 3235971 G A intronico PRDM16 0,0089 1 2
    1 3274115 C T intronico PRDM16 0,001 2 4
    1 3291388 G A intronico PRDM16 0,002 2 4
    1 3295658 A C intronico PRDM16 0,0068 0 6
    1 3295937 A C intronico PRDM16 0,0068 0 2
    1 3296205 T C intronico PRDM16 0,0083 0 2
    1 3315690 G A intronico PRDM16 0,001 2 4
    1 3329212 G A esonico PRDM16 0,0031 fallimento 1 2
    1 3331787 C T intronico PRDM16 0,0099 1 2
    1 3370316 G C upstream ARHGEF16 0,001 2 4
    1 3379560 A G intronico ARHGEF16 0,0051 0 6
    1 3391174 C T intronico ARHGEF16 0,006 1 2
    1 3413873 G A esonico MEGF6 0,003 fallimento 1 2
    1 3416272 C T esonico MEGF6 0,0072 silenzioso 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 esonico HNRNPCL1,LOC649330 0,006 fallimento 0 10
    1 12907518 C A esonico HNRNPCL1,LOC649330 1,0E-4 fallimento 0 10
    1 12908499 G C intronico HNRNPCL1 0,0031 0 8
    1 12931660 G C intergenico PRAMEF2,PRAMEF4 0,004 1 2
    1 12937721 G T intergenico PRAMEF2,PRAMEF4 0,0038 0 2
    1 12940827 G T intronico PRAMEF4 0,007 2 4
    1 12942759 T G intronico PRAMEF4 0,0076 0 10
    1 12942805 T G intronico PRAMEF4 0,0061 0 12
    1 12942812 G A intronico PRAMEF4 0,0061 0 12
    1 12942875 A G intronico PRAMEF4 0,0068 0 6
    1 12942912 G C intronico PRAMEF4 2.0E-4 0 2
    1 12942937 A T esonico PRAMEF4 0,0029 fallimento 0 2
    1 12942940 T G esonico PRAMEF4 0,0038 fallimento 0 2
    1 12943940 T C intronico PRAMEF4 0,0015 0 12
    1 12944138 A G intronico PRAMEF4 8.0E-4 0 12
    1 12944234 G A intronico PRAMEF4 0,0015 0 12
    1 12944589 T G intronico PRAMEF4 0,003 0 4
    1 12944845 A C intronico PRAMEF4 0,0014 0 6
    1 12946439 T C upstream PRAMEF4 0,0029 0 10
    1 12946833 G A upstream PRAMEF4 0,001 0 8
    1 12946835 T A upstream PRAMEF4 0,004 0 12
    1 12995204 G T intergenico 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 intergenico PRAMEF6,LOC391003 0,0015 0 10
    1 13038503 G A UTR3 LOC391003 0,0072 1 2
    1 13051650 C T intergenico LOC391003,PRAMEF5 0,002 2 4
    1 15706063 G A intronico FHAD1 0,0029 1 2
    1 15713292 C T intronico FHAD1 0,001 1 2
    1 15766541 G C intronico CTRC 0,001 1 2
    1 15782601 T C upstream CELA2A 0,0038 1 2
    1 15828125 G A 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 intronico DNAJC16 0,001 1 2
    1 15876704 G 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 G 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 G intergenico ZBTB40,EPHA8 0,007 1 2
    1 22875103 C G intergenico ZBTB40,EPHA8 0,007 1 2
    1 22906403 C T intronico EPHA8 0,008 2 4
    1 22912956 G A intronico EPHA8 0,001 1 2
    1 22917007 C T intronico EPHA8 0,001 2 4
    1 22927240 G A esonico EPHA8 0,0013 fallimento 2 4
    1 22932265 G A intergenico EPHA8,MIR6127 0,0089 2 4
    1 22944057 C T intergenico EPHA8,MIR6127 0,0089 2 4
    1 22978799 A G 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 G 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 G A intergenico C1orf94,GJB5 0,0014 2 4
    1 35186166 G A intergenico C1orf94,GJB5 0,0099 2 4
    1 35186520 A C intergenico C1orf94,GJB5 0,002 2 4
    1 35196361 G A intergenico C1orf94,GJB5 0,0099 2 4
    1 35223545 C T esonico GJB5 0,001 silenzioso 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 intergenico GJB4,GJB3 0,0043 1 2
    1 35232954 T C intergenico GJB4,GJB3 0,003 1 2
    1 35237986 G 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,GJ4 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 G A intronico DLGAP3 0,007 2 4
    1 35371634 T A upstream DLGAP3 0,0015 0 4
    1 39253519 G A intergenico LINC01343,RRAGC 0,005 1 2
    1 39288829 G A intergenico LINC01343,RRAGC 0,0051 1 2
    1 39289832 A C intergenico LINC01343,RRAGC 0,002 0 2
    1 39312638 G A intronico RRAGC 0,0038 2 4
    1 39361372 G A intronico RHBDL2 0,005 1 2
    1 39363826 T G 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 G intergenico RHBDL2,AKIRIN1 0,001 2 4
    1 39475057 G A intergenico AKIRIN1,NDUFS5 0.01 1 2
    1 39485016 C T intergenico AKIRIN1,NDUFS5 0,001 2 4
    1 39488137 A G intergenico AKIRIN1,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 intronico NSUN4 0,0014 1 2
    1 46817258 A G intronico NSUN4 0,005 0 2
    1 46843158 T C intergenico NSUN4,FAAH 0,001 1 2
    1 46933509 A G intergenico LINC01398,DMBX1 0,002 1 2
    1 46935021 G 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 G 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 G intronico GLMN 0,001 2 4
    1 92764270 G C intronico GLMN 0,001 2 4
    1 92802210 G A intronico RPAP2 0,0072 1 2
    1 92820663 T A intronico RPAP2 0,0058 1 2
    1 92820664 G T intronico RPAP2 0,0058 1 2
    1 92820953 G A intronico RPAP2 0,007 2 4
    1 92824766 A G intronico RPAP2 0,0058 1 2
    1 92849183 C A intronico RPAP2 0.01 2 4
    1 92850696 C G intronico RPAP2 0,0023 1 2
    1 92861357 T C intergenico RPAP2,GFI1 0.01 2 4
    1 92877460 C G intergenico RPAP2,GFI1 0,002 1 2
    1 92880643 A G intergenico RPAP2,GFI1 0,001 2 4
    1 92911540 G 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 G A intronico GFI1 0,008 2 4
    1 92964788 G A intergenico GFI1 e EVI5 0,0023 1 2
    1 92977480 C T UTR3 EVI5 0,002 1 2
    1 92985213 C T intronico EVI5 0,001 2 4
    1 92988342 C T intronico EVI5 0,008 2 4
    1 92992283 G A intronico EVI5 0.01 2 4
    1 92999760 C T intronico EVI5 0,003 1 2
    1 93005149 G C 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 G A intergenico KCNA3,CD53 0,0014 2 4
    1 111411924 C T intergenico KCNA3,CD53 0,003 1 2
    1 111441850 C G 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 G intergenico CD53,LRIF1 0,001 2 4
    1 111525974 G A intergenico LRIF1,DRAM2 0,002 2 4
    1 111574573 G T intergenico LRIF1,DRAM2 0,0072 2 4
    1 111574594 T A intergenico LRIF1,DRAM2 0,005 1 2
    1 111574647 G A intergenico LRIF1,DRAM2 0,005 1 2
    1 111591746 T A intergenico LRIF1,DRAM2 0,005 1 2
    1 111601459 A G intergenico LRIF1,DRAM2 0,005 1 2
    1 111604748 G C intergenico 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 intergenico RAP1A,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 intronico DDX20 0,0043 1 2
    1 112307213 A C intronico DDX20 0,0043 1 2
    1 112309436 G T esonico DDX20 0.0 fallimento 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 G T ncRNA_exonic KCND3-IT1 0,001 1 2
    1 113520038 G A intergenico SLC16A1-AS1,LOC100996251 0,0023 1 2

    L'esecuzione di una query simile che utilizza i dati della fase 3 di 1000 genomi richiede circa 90 secondi ed elabora circa 3,38 TB di dati.