Esecuzione di JOIN di intervallo con BigQuery

Puoi utilizzare BigQuery per eseguire una query JOIN sulle varianti con dati descritti in base a intervalli di regione genomici o sovrapposizioni. Questa pagina mostra come utilizzare una query JOIN complessa per creare un elenco di nomi dei geni e fare quanto segue:

  • Trova i rari Ops che si sovrappongono ai geni.
  • Trova 100.000 coppie di basi su entrambi i lati di un gene per l'intero genoma.

Questa guida mostra esempi di tre query. Ogni query mostra come BigQuery sia in grado di scalare su diverse dimensioni dei dati genomici:

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

Eseguire 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 Genomi al platino di Illuminazione:

  1. Vai alla 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:

    #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 le varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.

    Espandi la sezione seguente per visualizzare i risultati della query:

    Risultati delle query

    Chr Avvia Rif Alt Func. Gene PopFreqMax ExonicFunc num_varianti_allele num_alle_totali
    1 156699757 T C introduzione RRNAD1 0,002 2 4
    1 156705390 C T introduzione RRNAD1 8,0E-4 0 2
    1 156714207 T C introduzione HDG 0,003 0 6
    1 156714440 A C introduzione HDG 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 introduzione PRCC 0,001 2 4
    1 156744826 T G introduzione PRCC 0,002 0 8
    1 156779764 G A introduzione SH2D2A 0,001 2 4
    1 156783454 A C introduzione SH2D2A 0,0014 1 2
    1 156786144 C T introduzione NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T introduzione 1 0,002 1 2
    1 156815332 A C introduzione INSRR,NTRK1 0,003 0 2
    1 156830778 G A esonico 1 0,0067 Misense 2 4
    1 156842064 C T introduzione 1 0,0014 1 2
    1 156843438 C A esonico 1 0,0032 Misense 1 2
    1 156845773 C T introduzione 1 0,001 2 4
    1 156873318 T C introduzione PERA1 0,01 4 8
    1 156922740 G A introduzione ARHGEF11 0,007 1 2
    1 156930100 C T introduzione ARHGEF11 0,001 2 4
    2 113901230 G A intergenico IL1RN,PSD4 0,0082 1 2
    2 113953418 C A introduzione 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 introduzione PAX8 0,002 2 4
    2 113994010 A C ncRNA_esonenico PAX8-AS1 0,001 0 4
    2 113997745 C A ncRNA_esonenico 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 introduzione PPARG 0,0092 2 4
    3 12348795 T C introduzione PPARG 0,0014 1 2
    3 12353106 T C introduzione PPARG 0,001 2 4
    3 12403825 G A introduzione PPARG 0,0051 2 4
    3 12404394 G A introduzione PPARG 0,001 1 2
    3 12410289 G A introduzione PPARG 0,008 2 4
    3 12431381 C T introduzione PPARG 0,0061 2 4
    3 12447267 G A introduzione PPARG 0,0089 2 4
    3 12449379 C T introduzione PPARG 0,0092 2 4
    3 12450848 C A introduzione PPARG 0,0092 2 4
    3 12462847 T C introduzione 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 introduzione TSEN2 0,0092 2 4
    3 12531167 A G introduzione TSEN2 0,0099 2 4
    3 12557737 A G introduzione 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 introduzione Resistente 0,001 2 4
    3 59750635 A G introduzione Resistente 0,003 1 2
    3 59757776 C T introduzione Resistente 0,001 2 4
    3 59770612 G A introduzione Resistente 0,001 2 4
    3 59804444 G C introduzione Resistente 0,001 2 4
    3 59819769 T C introduzione Resistente 0,001 2 4
    3 59884396 C T introduzione Resistente 0,001 2 4
    3 59960728 A C introduzione Resistente 0,01 1 2
    3 59970345 G A introduzione Resistente 0,002 1 2
    3 59972417 T A introduzione Resistente 0,0072 0 2
    3 60104328 C A introduzione Resistente 0,01 2 4
    3 60139062 G A introduzione Resistente 0,01 0 2
    3 60158066 C T introduzione Resistente 0,001 1 2
    3 60169285 C T introduzione Resistente 0,005 1 2
    3 60216185 T C introduzione Resistente 0,002 1 2
    3 60226380 G A introduzione Resistente 0,007 2 4
    3 60234539 C A introduzione Resistente 0,002 1 2
    3 60247464 A C introduzione Resistente 0,004 2 4
    3 60269926 A G introduzione Resistente 0,007 2 4
    3 60271228 G T introduzione Resistente 0,007 2 4
    3 60286972 T C introduzione Resistente 0,001 2 4
    3 60301412 C G introduzione Resistente 0,001 1 2
    3 60312251 C T introduzione Resistente 0,0099 1 2
    3 60317682 A G introduzione Resistente 0,008 1 2
    3 60328557 C G introduzione Resistente 0,0043 2 4
    3 60342562 C T introduzione Resistente 0,006 1 2
    3 60400033 G A introduzione Resistente 0,004 2 4
    3 60435819 C T introduzione Resistente 0,006 2 4
    3 60435820 G T introduzione Resistente 0,004 1 2
    3 60441288 T C introduzione Resistente 0,006 2 4
    3 60444465 C A introduzione Resistente 0,01 1 2
    3 60444575 C T introduzione Resistente 0,001 1 2
    3 60450581 T C introduzione Resistente 0,01 1 2
    3 60456571 G A introduzione Resistente 0,001 2 4
    3 60473568 C G introduzione Resistente 0,001 1 2
    3 60487557 T C introduzione Resistente 0,001 1 2
    3 60559705 A G introduzione Resistente 0,002 2 4
    3 60570764 T C introduzione Resistente 0,008 2 4
    3 60582100 C T introduzione Resistente 0,001 1 2
    3 60587192 G A introduzione Resistente 0,004 1 2
    3 60599869 G A introduzione Resistente 0,0086 2 4
    3 60603091 C T introduzione Resistente 0,001 2 4
    3 60603250 A T introduzione Resistente 0,0099 1 2
    3 60609831 T G introduzione Resistente 0,001 2 4
    3 60619756 G T introduzione Resistente 0,0015 2 4
    3 60680758 C T introduzione Resistente 0,0089 2 4
    3 60702243 G C introduzione Resistente 0,001 2 4
    3 60702532 A G introduzione Resistente 0,001 1 2
    3 60714328 A T introduzione Resistente 0,004 1 2
    3 60725297 G A introduzione Resistente 0,001 1 2
    3 60726640 G A introduzione Resistente 0,01 2 4
    3 60795144 A G introduzione Resistente 0,001 2 4
    3 60807171 A G introduzione Resistente 0,001 1 2
    3 60813868 T C introduzione Resistente 0,001 1 2
    3 60826546 C G introduzione Resistente 0,0023 1 2
    3 60837392 C T introduzione Resistente 0,001 1 2
    3 60846310 A G introduzione Resistente 0,01 0 2
    3 60850985 C T introduzione Resistente 0,004 1 2
    3 60852559 T C introduzione Resistente 0,008 1 2
    3 60871759 T C introduzione Resistente 0,004 1 2
    3 60884396 C T introduzione Resistente 0,002 2 4
    3 60897092 C A introduzione Resistente 0,001 2 4
    3 60940759 C T introduzione Resistente 0,0089 1 2
    3 60982595 A G introduzione Resistente 0,003 2 4
    3 60999283 G A introduzione Resistente 0,001 1 2
    3 61042977 A G introduzione Resistente 0,001 2 4
    3 61043349 T C introduzione Resistente 0,001 2 4
    3 61044789 A C introduzione Resistente 0,001 2 4
    3 61141621 G A introduzione Resistente 0,003 1 2
    3 61148655 G C introduzione Resistente 0,001 2 4
    3 61170747 C T introduzione Resistente 0,003 1 2
    3 61189473 C G introduzione Resistente 0,0099 1 2
    3 61190425 C T introduzione Resistente 0,0023 2 4
    3 61193853 C T introduzione Resistente 0,0099 0 2
    3 61194793 C T introduzione Resistente 0,007 0 2
    3 61194840 A G introduzione Resistente 0,0099 0 2
    3 61194886 T A introduzione Resistente 0,0099 0 2
    3 61201777 C T introduzione Resistente 0,001 2 4
    3 61202292 T C introduzione Resistente 0,007 1 2
    3 61232806 G C introduzione Resistente 0,0099 1 2
    3 61232910 C T introduzione Resistente 0,0099 1 2
    3 61235824 A T introduzione Resistente 0,001 2 4
    3 61283810 A C intergenico FHIT,PTPR 0,0089 1 2
    3 61293731 T A intergenico FHIT,PTPR 0,0089 2 4
    3 61296730 C T intergenico FHIT,PTPR 0,001 1 2
    3 61326341 C T intergenico FHIT,PTPR 0,004 2 4
    3 61326620 T C intergenico FHIT,PTPR 0,01 1 2
    3 61327649 G C intergenico FHIT,PTPR 0,001 2 4
    3 61330545 G C intergenico FHIT,PTPR 0,001 2 4
    3 61335803 G A intergenico FHIT,PTPR 0,001 2 4

    Una query simile che utilizza i dati della Fase 3 di 1000 genomi richiede circa 90 secondi per essere eseguita ed elabora circa 3,38 TB di dati.

Utilizzare una tabella materializzata

Quando lavori con 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 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.

Eseguire una query su una tabella materializzata con geni specifici

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

  1. Per creare la tabella degli intervalli, procedi nel seguente modo:

    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. La query materializza parte della tabella silver-wall-555:TuteTable.hg19 in una nuova tabella a intervalli di 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 le varianti rare all'interno della coorte che si sovrappongono alle regioni di interesse.

    Espandi la sezione seguente per visualizzare i risultati della query:

    Risultati delle query

    Chr Avvia Rif Alt Func. Gene PopFreqMax ExonicFunc num_varianti_allele num_alle_totali
    1 156699757 T C introduzione RRNAD1 0,002 2 4
    1 156705390 C T introduzione RRNAD1 8,0E-4 0 2
    1 156714207 T C introduzione HDG 0,003 0 6
    1 156714440 A C introduzione HDG 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 introduzione PRCC 0,001 2 4
    1 156744826 T G introduzione PRCC 0,002 0 8
    1 156779764 G A introduzione SH2D2A 0,001 2 4
    1 156783454 A C introduzione SH2D2A 0,0014 1 2
    1 156786144 C T introduzione NTRK1,SH2D2A 0,0031 2 4
    1 156790510 A T introduzione 1 0,002 1 2
    1 156815332 A C introduzione INSRR,NTRK1 0,003 0 2
    1 156830778 G A esonico 1 0,0067 Misense 2 4
    1 156842064 C T introduzione 1 0,0014 1 2
    1 156843438 C A esonico 1 0,0032 Misense 1 2
    1 156845773 C T introduzione 1 0,001 2 4
    1 156873318 T C introduzione PERA1 0,01 4 8
    1 156922740 G A introduzione ARHGEF11 0,007 1 2
    1 156930100 C T introduzione ARHGEF11 0,001 2 4
    2 113901230 G A intergenico IL1RN,PSD4 0,0082 1 2
    2 113953418 C A introduzione 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 introduzione PAX8 0,002 2 4
    2 113994010 A C ncRNA_esonenico PAX8-AS1 0,001 0 4
    2 113997745 C A ncRNA_esonenico 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 introduzione PPARG 0,0092 2 4
    3 12348795 T C introduzione PPARG 0,0014 1 2
    3 12353106 T C introduzione PPARG 0,001 2 4
    3 12403825 G A introduzione PPARG 0,0051 2 4
    3 12404394 G A introduzione PPARG 0,001 1 2
    3 12410289 G A introduzione PPARG 0,008 2 4
    3 12431381 C T introduzione PPARG 0,0061 2 4
    3 12447267 G A introduzione PPARG 0,0089 2 4
    3 12449379 C T introduzione PPARG 0,0092 2 4
    3 12450848 C A introduzione PPARG 0,0092 2 4
    3 12462847 T C introduzione 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 introduzione TSEN2 0,0092 2 4
    3 12531167 A G introduzione TSEN2 0,0099 2 4
    3 12557737 A G introduzione 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 introduzione Resistente 0,001 2 4
    3 59750635 A G introduzione Resistente 0,003 1 2
    3 59757776 C T introduzione Resistente 0,001 2 4
    3 59770612 G A introduzione Resistente 0,001 2 4
    3 59804444 G C introduzione Resistente 0,001 2 4
    3 59819769 T C introduzione Resistente 0,001 2 4
    3 59884396 C T introduzione Resistente 0,001 2 4
    3 59960728 A C introduzione Resistente 0,01 1 2
    3 59970345 G A introduzione Resistente 0,002 1 2
    3 59972417 T A introduzione Resistente 0,0072 0 2
    3 60104328 C A introduzione Resistente 0,01 2 4
    3 60139062 G A introduzione Resistente 0,01 0 2
    3 60158066 C T introduzione Resistente 0,001 1 2
    3 60169285 C T introduzione Resistente 0,005 1 2
    3 60216185 T C introduzione Resistente 0,002 1 2
    3 60226380 G A introduzione Resistente 0,007 2 4
    3 60234539 C A introduzione Resistente 0,002 1 2
    3 60247464 A C introduzione Resistente 0,004 2 4
    3 60269926 A G introduzione Resistente 0,007 2 4
    3 60271228 G T introduzione Resistente 0,007 2 4
    3 60286972 T C introduzione Resistente 0,001 2 4
    3 60301412 C G introduzione Resistente 0,001 1 2
    3 60312251 C T introduzione Resistente 0,0099 1 2
    3 60317682 A G introduzione Resistente 0,008 1 2
    3 60328557 C G introduzione Resistente 0,0043 2 4
    3 60342562 C T introduzione Resistente 0,006 1 2
    3 60400033 G A introduzione Resistente 0,004 2 4
    3 60435819 C T introduzione Resistente 0,006 2 4
    3 60435820 G T introduzione Resistente 0,004 1 2
    3 60441288 T C introduzione Resistente 0,006 2 4
    3 60444465 C A introduzione Resistente 0,01 1 2
    3 60444575 C T introduzione Resistente 0,001 1 2
    3 60450581 T C introduzione Resistente 0,01 1 2
    3 60456571 G A introduzione Resistente 0,001 2 4
    3 60473568 C G introduzione Resistente 0,001 1 2
    3 60487557 T C introduzione Resistente 0,001 1 2
    3 60559705 A G introduzione Resistente 0,002 2 4
    3 60570764 T C introduzione Resistente 0,008 2 4
    3 60582100 C T introduzione Resistente 0,001 1 2
    3 60587192 G A introduzione Resistente 0,004 1 2
    3 60599869 G A introduzione Resistente 0,0086 2 4
    3 60603091 C T introduzione Resistente 0,001 2 4
    3 60603250 A T introduzione Resistente 0,0099 1 2
    3 60609831 T G introduzione Resistente 0,001 2 4
    3 60619756 G T introduzione Resistente 0,0015 2 4
    3 60680758 C T introduzione Resistente 0,0089 2 4
    3 60702243 G C introduzione Resistente 0,001 2 4
    3 60702532 A G introduzione Resistente 0,001 1 2
    3 60714328 A T introduzione Resistente 0,004 1 2
    3 60725297 G A introduzione Resistente 0,001 1 2
    3 60726640 G A introduzione Resistente 0,01 2 4
    3 60795144 A G introduzione Resistente 0,001 2 4
    3 60807171 A G introduzione Resistente 0,001 1 2
    3 60813868 T C introduzione Resistente 0,001 1 2
    3 60826546 C G introduzione Resistente 0,0023 1 2
    3 60837392 C T introduzione Resistente 0,001 1 2
    3 60846310 A G introduzione Resistente 0,01 0 2
    3 60850985 C T introduzione Resistente 0,004 1 2
    3 60852559 T C introduzione Resistente 0,008 1 2
    3 60871759 T C introduzione Resistente 0,004 1 2
    3 60884396 C T introduzione Resistente 0,002 2 4
    3 60897092 C A introduzione Resistente 0,001 2 4
    3 60940759 C T introduzione Resistente 0,0089 1 2
    3 60982595 A G introduzione Resistente 0,003 2 4
    3 60999283 G A introduzione Resistente 0,001 1 2
    3 61042977 A G introduzione Resistente 0,001 2 4
    3 61043349 T C introduzione Resistente 0,001 2 4
    3 61044789 A C introduzione Resistente 0,001 2 4
    3 61141621 G A introduzione Resistente 0,003 1 2
    3 61148655 G C introduzione Resistente 0,001 2 4
    3 61170747 C T introduzione Resistente 0,003 1 2
    3 61189473 C G introduzione Resistente 0,0099 1 2
    3 61190425 C T introduzione Resistente 0,0023 2 4
    3 61193853 C T introduzione Resistente 0,0099 0 2
    3 61194793 C T introduzione Resistente 0,007 0 2
    3 61194840 A G introduzione Resistente 0,0099 0 2
    3 61194886 T A introduzione Resistente 0,0099 0 2
    3 61201777 C T introduzione Resistente 0,001 2 4
    3 61202292 T C introduzione Resistente 0,007 1 2
    3 61232806 G C introduzione Resistente 0,0099 1 2
    3 61232910 C T introduzione Resistente 0,0099 1 2
    3 61235824 A T introduzione Resistente 0,001 2 4
    3 61283810 A C intergenico FHIT,PTPR 0,0089 1 2
    3 61293731 T A intergenico FHIT,PTPR 0,0089 2 4
    3 61296730 C T intergenico FHIT,PTPR 0,001 1 2
    3 61326341 C T intergenico FHIT,PTPR 0,004 2 4
    3 61326620 T C intergenico FHIT,PTPR 0,01 1 2
    3 61327649 G C intergenico FHIT,PTPR 0,001 2 4
    3 61330545 G C intergenico FHIT,PTPR 0,001 2 4
    3 61335803 G A intergenico FHIT,PTPR 0,001 2 4

    Una query simile che utilizza i dati della Fase 3 di 1000 genomi richiede circa 90 secondi per essere eseguita ed elabora circa 3,38 TB di dati.

Esegui una query su una tabella materializzata con 250 geni casuali

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

  1. Per creare la tabella degli intervalli, procedi nel seguente modo:

    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 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 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 Avvia Rif Alt Func. Gene PopFreqMax ExonicFunc num_varianti_allele num_alle_totali
    1 2925355 C A intergenico TTC34,CTR2 0,001 2 4
    1 2933170 G A intergenico TTC34,CTR2 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_intronico LINC00982 0,0072 1 2
    1 2978803 G C ncRNA_esonenico LINC00982 0,002 4 6
    1 3006466 G A introduzione PRDM16 0,0098 1 2
    1 3011333 G T introduzione PRDM16 0,004 1 2
    1 3019659 C T introduzione PRDM16 0,0031 1 2
    1 3036896 G A introduzione PRDM16 0,001 1 2
    1 3037388 G A introduzione PRDM16 0,002 2 4
    1 3041250 T G introduzione PRDM16 0,006 2 4
    1 3042502 A T introduzione PRDM16 0,003 4 6
    1 3053713 A C introduzione PRDM16 0,002 1 2
    1 3063109 C T introduzione PRDM16 0,002 0 2
    1 3063593 T C introduzione PRDM16 0,003 1 2
    1 3076439 C T introduzione PRDM16 0,001 2 4
    1 3078960 G A introduzione PRDM16 0,007 2 4
    1 3084268 A C introduzione PRDM16 0,005 0 2
    1 3084492 T C introduzione PRDM16 0,0015 0 2
    1 3084786 T C introduzione PRDM16 0,0015 0 4
    1 3111119 G A introduzione PRDM16 0,003 1 2
    1 3111643 C T introduzione PRDM16 0,0041 1 2
    1 3114807 G A introduzione PRDM16 0,0041 1 2
    1 3165530 C T introduzione PRDM16 0,0089 1 2
    1 3169325 G A introduzione PRDM16 0,008 2 4
    1 3179623 C T introduzione PRDM16 0,003 2 4
    1 3181097 C T introduzione PRDM16 0,001 2 4
    1 3194000 G C introduzione PRDM16 0,005 2 4
    1 3195769 T C introduzione PRDM16 0,002 1 2
    1 3197351 C T introduzione PRDM16 0,0061 1 2
    1 3224100 C A introduzione PRDM16 0,003 2 4
    1 3228644 G T introduzione PRDM16 0,001 2 4
    1 3234045 G A introduzione PRDM16 0,002 1 2
    1 3235971 G A introduzione PRDM16 0,0089 1 2
    1 3274115 C T introduzione PRDM16 0,001 2 4
    1 3291388 G A introduzione PRDM16 0,002 2 4
    1 3295658 A C introduzione PRDM16 0,0068 0 6
    1 3295937 A C introduzione PRDM16 0,0068 0 2
    1 3296205 T C introduzione PRDM16 0,0083 0 2
    1 3315690 G A introduzione PRDM16 0,001 2 4
    1 3329212 G A esonico PRDM16 0,0031 Misense 1 2
    1 3331787 C T introduzione PRDM16 0,0099 1 2
    1 3370316 G C upstream ARHGEF16 0,001 2 4
    1 3379560 A G introduzione ARHGEF16 0,0051 0 6
    1 3391174 C T introduzione ARHGEF16 0,006 1 2
    1 3413873 G A esonico MegaF6 0,003 Misense 1 2
    1 3416272 C T esonico MegaF6 0,0072 silenzioso 2 4
    1 3417122 G A introduzione MegaF6 0,0038 2 4
    1 3436219 G A introduzione MegaF6 0,0046 2 4
    1 12907456 A G esonico HNRNPCL1,LOC649330 0,006 Misense 0 10
    1 12907518 C A esonico HNRNPCL1,LOC649330 1,0E-4 Misense 0 10
    1 12908499 G C introduzione 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 introduzione PRAMEF4 0,007 2 4
    1 12942759 T G introduzione PRAMEF4 0,0076 0 10
    1 12942805 T G introduzione PRAMEF4 0,0061 0 12
    1 12942812 G A introduzione PRAMEF4 0,0061 0 12
    1 12942875 A G introduzione PRAMEF4 0,0068 0 6
    1 12942912 G C introduzione PRAMEF4 2,0E-4 0 2
    1 12942937 A T esonico PRAMEF4 0,0029 Misense 0 2
    1 12942940 T G esonico PRAMEF4 0,0038 Misense 0 2
    1 12943940 T C introduzione PRAMEF4 0,0015 0 12
    1 12944138 A G introduzione PRAMEF4 8,0E-4 0 12
    1 12944234 G A introduzione PRAMEF4 0,0015 0 12
    1 12944589 T G introduzione PRAMEF4 0,003 0 4
    1 12944845 A C introduzione 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 LOC 391.003 0,0072 1 2
    1 13051650 C T intergenico LOC391003,PRAMEF5 0,002 2 4
    1 15706063 G A introduzione FHAD1 0,0029 1 2
    1 15713292 C T introduzione FHAD1 0,001 1 2
    1 15766541 G C introduzione CTR 0,001 1 2
    1 15782601 T C upstream CELA2A 0,0038 1 2
    1 15828125 G A introduzione CASP9 0,0014 2 4
    1 15831037 G A introduzione CASP9 0,0099 1 2
    1 15840513 T G introduzione CASP9 0,0043 2 4
    1 15868742 G A introduzione DNAJC16 0,001 1 2
    1 15876704 G A introduzione DNAJC16 0,001 1 2
    1 15900342 C A introduzione GMG 0,001 1 2
    1 15906257 T C introduzione GMG 8,0E-4 1 2
    1 15911897 A G upstream GMG 0,0043 2 4
    1 22764178 C T intergenico WNT4,ZBTB40 0,001 2 4
    1 22791939 C T introduzione 40 ZBTB 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 introduzione EPHA8 0,008 2 4
    1 22912956 G A introduzione EPHA8 0,001 1 2
    1 22917007 C T introduzione EPHA8 0,001 2 4
    1 22927240 G A esonico EPHA8 0,0013 Misense 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 e GJB3 0,0043 1 2
    1 35232954 T C intergenico GJB4 e GJB3 0,003 1 2
    1 35237986 G A intergenico GJB4 e GJB3 0,0014 1 2
    1 35245522 C T intergenico GJB4 e 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 introduzione SMIM12 0,0027 2 4
    1 35369676 G A introduzione 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 introduzione CRACCIERA 0,0038 2 4
    1 39361372 G A introduzione RHBDL2 0,005 1 2
    1 39363826 T G introduzione RHBDL2 0,0029 1 2
    1 39367555 T C introduzione RHBDL2 0,007 2 4
    1 39369531 T C introduzione RHBDL2 0,001 2 4
    1 39370202 T C introduzione RHBDL2 0,01 1 2
    1 39449101 A G intergenico RHBDL2,ACIRINA1 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 introduzione NDUFS5 0,001 0 2
    1 39500605 C G downstream NDUFS5 0,002 0 10
    1 46813814 T C introduzione NSUN - 4 0,0014 1 2
    1 46817258 A G introduzione NSUN - 4 0,005 0 2
    1 46843158 T C intergenico NSUN4,AHAH 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 introduzione GLMN 0,001 2 4
    1 92764270 G C introduzione GLMN 0,001 2 4
    1 92802210 G A introduzione RPAP2 0,0072 1 2
    1 92820663 T A introduzione RPAP2 0,0058 1 2
    1 92820664 G T introduzione RPAP2 0,0058 1 2
    1 92820953 G A introduzione RPAP2 0,007 2 4
    1 92824766 A G introduzione RPAP2 0,0058 1 2
    1 92849183 C A introduzione RPAP2 0,01 2 4
    1 92850696 C G introduzione 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 introduzione GFI 0,008 2 4
    1 92964788 G A intergenico GFI1,EVI5 0,0023 1 2
    1 92977480 C T UTR3 EV5 0,002 1 2
    1 92985213 C T introduzione EV5 0,001 2 4
    1 92988342 C T introduzione EV5 0,008 2 4
    1 92992283 G A introduzione EV5 0,01 2 4
    1 92999760 C T introduzione EV5 0,003 1 2
    1 93005149 G C introduzione EV5 0,003 0 4
    1 93018543 A T introduzione EV5 0,01 2 4
    1 93033744 C T introduzione EV5 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 introduzione RAP1A 0,001 2 4
    1 112206765 A G introduzione RAP1A 0,0043 1 2
    1 112226517 G A introduzione 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_intronico FAM212B-AS1 0,004 1 2
    1 112304285 T C introduzione DDX20 0,0043 1 2
    1 112307213 A C introduzione DDX20 0,0043 1 2
    1 112309436 G T esonico DDX20 0.0 Misense 1 2
    1 112317384 T C intergenico DDX20,KCND3 0,0014 1 2
    1 112381367 C T introduzione KCND3 0,002 1 2
    1 112396571 G T ncRNA_esonenico KCND3-IT1 0,001 1 2
    1 113520038 G A intergenico SLC16A1-AS1,LOC100996251 0,0023 1 2

    Una query simile che utilizza i dati della Fase 3 di 1000 genomi richiede circa 90 secondi per essere eseguita ed elabora circa 3,38 TB di dati.