使用 BigQuery 运行间隔 JOIN

您可以使用 BigQuery 对带有基因组区域间隔所描述数据的变体运行 JOIN 查询。本页介绍了 使用复杂的 JOIN 查询获取基因名称列表，然后执行 以下：

  • 查找与基因重叠的罕见 SNP。
  • 在整个基因组样本中基因的任一侧找到 100000 个碱基对。

本指南展示了三个查询的示例。每个查询演示了 BigQuery 如何根据不同大小的基因组数据进行扩缩：

数据来自近 90 亿行的 Tute Genomics Annotation 表和 Illumina Platinum Genomes 数据集。如果您不熟悉这些数据集，请参阅以下链接：

查询内嵌表

本示例使用查询中定义的间隔表（称为 intervals），并演示如何对包含来自 Illumina Platinum Genomes 的变体的表运行 JOIN 查询：

  1. 在 Google Cloud 控制台中转到 BigQuery 页面。

    转到 BigQuery 页面

  2. 点击编写查询。

  3. 在 New 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. 点击运行查询。查询大约需要运行十秒钟，并处理约 334 GB 的数据。查询结果标识同类群组中与目标区域重叠的罕见变体。

    展开即可下部分以查看查询结果：

    查询结果

    Chr 开始 参考 Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C 入门 RRNAD1 0.002 2 4
    1 156705390 C T 入门 RRNAD1 8.0E-4 0 2
    1 156714207 T C 入门 HDGF 0.003 0 6
    1 156714440 A C 入门 HDGF 0.0068 0 12
    1 156723870 C T 基因间序列 HDGF、PRCC 0.006 1 2
    1 156724456 C T 基因间序列 HDGF、PRCC 0.002 2 4
    1 156733988 C T 基因间序列 HDGF、PRCC 0.001 1 2
    1 156742258 T G 入门 PRCC 0.001 2 4
    1 156744826 T G 入门 PRCC 0.002 0 8
    1 156779764 G A 入门 SH2D2A 0.001 2 4
    1 156783454 A C 入门 SH2D2A 0.0014 1 2
    1 156786144 C T 入门 NTRK1、SH2D2A 0.0031 2 4
    1 156790510 A T 入门 NTRK1 0.002 1 2
    1 156815332 A C 入门 INSRR、NTRK1 0.003 0 2
    1 156830778 G A 外显子 NTRK1 0.0067 错义 2 4
    1 156842064 C T 入门 NTRK1 0.0014 1 2
    1 156843438 C A 外显子 NTRK1 0.0032 错义 1 2
    1 156845773 C T 入门 NTRK1 0.001 2 4
    1 156873318 T C 入门 PEAR1 0.01 4 8
    1 156922740 G A 入门 ARHGEF11 0.007 1 2
    1 156930100 C T 入门 ARHGEF11 0.001 2 4
    2 113901230 G A 基因间序列 IL1RN、PSD4 0.0082 1 2
    2 113953418 C A 入门 PSD4 0.001 2 4
    2 113967621 G C 基因间序列 PSD4、PAX8 0.002 0 6
    2 113967624 T C 基因间序列 PSD4、PAX8 0.002 0 2
    2 113980967 G A 入门 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 基因间序列 PAX8,CBWD2 0.001 2 4
    2 114084018 A C 基因间序列 PAX8,CBWD2 0.0045 0 4
    2 114099037 G A 基因间序列 PAX8,CBWD2 0.0051 1 2
    2 114105670 A T 基因间序列 PAX8,CBWD2 0.001 1 2
    2 114111325 G T 基因间序列 PAX8,CBWD2 0.001 1 2
    3 12265797 C T 基因间序列 SYN2、PPARG 0.0089 2 4
    3 12277958 A G 基因间序列 SYN2、PPARG 0.002 1 2
    3 12296019 G A 基因间序列 SYN2、PPARG 0.002 2 4
    3 12316549 G C 基因间序列 SYN2、PPARG 0.002 1 2
    3 12335681 T G 入门 PPARG 0.0092 2 4
    3 12348795 T C 入门 PPARG 0.0014 1 2
    3 12353106 T C 入门 PPARG 0.001 2 4
    3 12403825 G A 入门 PPARG 0.0051 2 4
    3 12404394 G A 入门 PPARG 0.001 1 2
    3 12410289 G A 入门 PPARG 0.008 2 4
    3 12431381 C T 入门 PPARG 0.0061 2 4
    3 12447267 G A 入门 PPARG 0.0089 2 4
    3 12449379 C T 入门 PPARG 0.0092 2 4
    3 12450848 C A 入门 PPARG 0.0092 2 4
    3 12462847 T C 入门 PPARG 0.002 1 2
    3 12492797 G A 基因间序列 PPARG、TSEN2 0.01 1 2
    3 12503201 G A 基因间序列 PPARG、TSEN2 0.0099 2 4
    3 12530460 A G 入门 TSEN2 0.0092 2 4
    3 12531167 A G 入门 TSEN2 0.0099 2 4
    3 12557737 A G 入门 TSEN2 0.001 2 4
    3 59636143 A G 基因间序列 C3orf67、FHIT 0.003 3 6
    3 59645934 A C 基因间序列 C3orf67、FHIT 0.004 1 2
    3 59646893 G A 基因间序列 C3orf67、FHIT 0.002 1 2
    3 59697024 A G 基因间序列 C3orf67、FHIT 0.0072 1 2
    3 59701013 G A 基因间序列 C3orf67、FHIT 0.004 2 4
    3 59733945 A G 基因间序列 C3orf67、FHIT 0.001 2 4
    3 59747482 C T 入门 FHIT 0.001 2 4
    3 59750635 A G 入门 FHIT 0.003 1 2
    3 59757776 C T 入门 FHIT 0.001 2 4
    3 59770612 G A 入门 FHIT 0.001 2 4
    3 59804444 G C 入门 FHIT 0.001 2 4
    3 59819769 T C 入门 FHIT 0.001 2 4
    3 59884396 C T 入门 FHIT 0.001 2 4
    3 59960728 A C 入门 FHIT 0.01 1 2
    3 59970345 G A 入门 FHIT 0.002 1 2
    3 59972417 T A 入门 FHIT 0.0072 0 2
    3 60104328 C A 入门 FHIT 0.01 2 4
    3 60139062 G A 入门 FHIT 0.01 0 2
    3 60158066 C T 入门 FHIT 0.001 1 2
    3 60169285 C T 入门 FHIT 0.005 1 2
    3 60216185 T C 入门 FHIT 0.002 1 2
    3 60226380 G A 入门 FHIT 0.007 2 4
    3 60234539 C A 入门 FHIT 0.002 1 2
    3 60247464 A C 入门 FHIT 0.004 2 4
    3 60269926 A G 入门 FHIT 0.007 2 4
    3 60271228 G T 入门 FHIT 0.007 2 4
    3 60286972 T C 入门 FHIT 0.001 2 4
    3 60301412 C G 入门 FHIT 0.001 1 2
    3 60312251 C T 入门 FHIT 0.0099 1 2
    3 60317682 A G 入门 FHIT 0.008 1 2
    3 60328557 C G 入门 FHIT 0.0043 2 4
    3 60342562 C T 入门 FHIT 0.006 1 2
    3 60400033 G A 入门 FHIT 0.004 2 4
    3 60435819 C T 入门 FHIT 0.006 2 4
    3 60435820 G T 入门 FHIT 0.004 1 2
    3 60441288 T C 入门 FHIT 0.006 2 4
    3 60444465 C A 入门 FHIT 0.01 1 2
    3 60444575 C T 入门 FHIT 0.001 1 2
    3 60450581 T C 入门 FHIT 0.01 1 2
    3 60456571 G A 入门 FHIT 0.001 2 4
    3 60473568 C G 入门 FHIT 0.001 1 2
    3 60487557 T C 入门 FHIT 0.001 1 2
    3 60559705 A G 入门 FHIT 0.002 2 4
    3 60570764 T C 入门 FHIT 0.008 2 4
    3 60582100 C T 入门 FHIT 0.001 1 2
    3 60587192 G A 入门 FHIT 0.004 1 2
    3 60599869 G A 入门 FHIT 0.0086 2 4
    3 60603091 C T 入门 FHIT 0.001 2 4
    3 60603250 A T 入门 FHIT 0.0099 1 2
    3 60609831 T G 入门 FHIT 0.001 2 4
    3 60619756 G T 入门 FHIT 0.0015 2 4
    3 60680758 C T 入门 FHIT 0.0089 2 4
    3 60702243 G C 入门 FHIT 0.001 2 4
    3 60702532 A G 入门 FHIT 0.001 1 2
    3 60714328 A T 入门 FHIT 0.004 1 2
    3 60725297 G A 入门 FHIT 0.001 1 2
    3 60726640 G A 入门 FHIT 0.01 2 4
    3 60795144 A G 入门 FHIT 0.001 2 4
    3 60807171 A G 入门 FHIT 0.001 1 2
    3 60813868 T C 入门 FHIT 0.001 1 2
    3 60826546 C G 入门 FHIT 0.0023 1 2
    3 60837392 C T 入门 FHIT 0.001 1 2
    3 60846310 A G 入门 FHIT 0.01 0 2
    3 60850985 C T 入门 FHIT 0.004 1 2
    3 60852559 T C 入门 FHIT 0.008 1 2
    3 60871759 T C 入门 FHIT 0.004 1 2
    3 60884396 C T 入门 FHIT 0.002 2 4
    3 60897092 C A 入门 FHIT 0.001 2 4
    3 60940759 C T 入门 FHIT 0.0089 1 2
    3 60982595 A G 入门 FHIT 0.003 2 4
    3 60999283 G A 入门 FHIT 0.001 1 2
    3 61042977 A G 入门 FHIT 0.001 2 4
    3 61043349 T C 入门 FHIT 0.001 2 4
    3 61044789 A C 入门 FHIT 0.001 2 4
    3 61141621 G A 入门 FHIT 0.003 1 2
    3 61148655 G C 入门 FHIT 0.001 2 4
    3 61170747 C T 入门 FHIT 0.003 1 2
    3 61189473 C G 入门 FHIT 0.0099 1 2
    3 61190425 C T 入门 FHIT 0.0023 2 4
    3 61193853 C T 入门 FHIT 0.0099 0 2
    3 61194793 C T 入门 FHIT 0.007 0 2
    3 61194840 A G 入门 FHIT 0.0099 0 2
    3 61194886 T A 入门 FHIT 0.0099 0 2
    3 61201777 C T 入门 FHIT 0.001 2 4
    3 61202292 T C 入门 FHIT 0.007 1 2
    3 61232806 G C 入门 FHIT 0.0099 1 2
    3 61232910 C T 入门 FHIT 0.0099 1 2
    3 61235824 A T 入门 FHIT 0.001 2 4
    3 61283810 A C 基因间序列 FHIT、PTPRG 0.0089 1 2
    3 61293731 T A 基因间序列 FHIT、PTPRG 0.0089 2 4
    3 61296730 C T 基因间序列 FHIT、PTPRG 0.001 1 2
    3 61326341 C T 基因间序列 FHIT、PTPRG 0.004 2 4
    3 61326620 T C 基因间序列 FHIT、PTPRG 0.01 1 2
    3 61327649 G C 基因间序列 FHIT、PTPRG 0.001 2 4
    3 61330545 G C 基因间序列 FHIT、PTPRG 0.001 2 4
    3 61335803 G A 基因间序列 FHIT、PTPRG 0.001 2 4

    使用来自 1,000 Genomes Phase 3 的数据的类似查询大约需要运行 90 秒，并处理约 3.38 TB 的数据。

使用具体化表

在大规模处理大数据时，您可以具体化间隔表并对新表运行 JOIN 查询。在继续执行本部分的其余部分之前，请按照以下步骤创建数据集：

  1. 在 Google Cloud 控制台中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 在探索器面板中，选择您要在其中创建数据集的项目。

  3. 展开 操作选项，然后点击创建数据集。

  4. 在创建数据集页面中执行以下操作：

    1. 在数据集 ID 部分，输入 genomics。
    2. 保留其他默认设置。
    3. 点击创建数据集。

查询包含特定基因的具体化表

下面的示例介绍如何具体化一个新的间隔表，该表中包含来自 silver-wall-555:TuteTable.hg19 表的特定基因列表

  1. 如需创建间隔表，请按以下步骤操作：

    1. 在 Google Cloud 控制台中打开 BigQuery 页面。

      转到 BigQuery 页面

    2. 点击编写查询。

    3. 在 New query 字段中，运行以下查询。该查询将 silver-wall-555:TuteTable.hg19 表的一部分具体化为新的 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. 点击运行查询。查询将返回以下结果：

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

  2. 在 New 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. 点击运行查询。查询大约需要运行十秒钟，并处理约 334 GB 的数据。查询结果标识同类群组中与目标区域重叠的罕见变体。

    展开即可下部分以查看查询结果：

    查询结果

    Chr 开始 参考 Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 156699757 T C 入门 RRNAD1 0.002 2 4
    1 156705390 C T 入门 RRNAD1 8.0E-4 0 2
    1 156714207 T C 入门 HDGF 0.003 0 6
    1 156714440 A C 入门 HDGF 0.0068 0 12
    1 156723870 C T 基因间序列 HDGF、PRCC 0.006 1 2
    1 156724456 C T 基因间序列 HDGF、PRCC 0.002 2 4
    1 156733988 C T 基因间序列 HDGF、PRCC 0.001 1 2
    1 156742258 T G 入门 PRCC 0.001 2 4
    1 156744826 T G 入门 PRCC 0.002 0 8
    1 156779764 G A 入门 SH2D2A 0.001 2 4
    1 156783454 A C 入门 SH2D2A 0.0014 1 2
    1 156786144 C T 入门 NTRK1、SH2D2A 0.0031 2 4
    1 156790510 A T 入门 NTRK1 0.002 1 2
    1 156815332 A C 入门 INSRR、NTRK1 0.003 0 2
    1 156830778 G A 外显子 NTRK1 0.0067 错义 2 4
    1 156842064 C T 入门 NTRK1 0.0014 1 2
    1 156843438 C A 外显子 NTRK1 0.0032 错义 1 2
    1 156845773 C T 入门 NTRK1 0.001 2 4
    1 156873318 T C 入门 PEAR1 0.01 4 8
    1 156922740 G A 入门 ARHGEF11 0.007 1 2
    1 156930100 C T 入门 ARHGEF11 0.001 2 4
    2 113901230 G A 基因间序列 IL1RN、PSD4 0.0082 1 2
    2 113953418 C A 入门 PSD4 0.001 2 4
    2 113967621 G C 基因间序列 PSD4、PAX8 0.002 0 6
    2 113967624 T C 基因间序列 PSD4、PAX8 0.002 0 2
    2 113980967 G A 入门 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 基因间序列 PAX8,CBWD2 0.001 2 4
    2 114084018 A C 基因间序列 PAX8,CBWD2 0.0045 0 4
    2 114099037 G A 基因间序列 PAX8,CBWD2 0.0051 1 2
    2 114105670 A T 基因间序列 PAX8,CBWD2 0.001 1 2
    2 114111325 G T 基因间序列 PAX8,CBWD2 0.001 1 2
    3 12265797 C T 基因间序列 SYN2、PPARG 0.0089 2 4
    3 12277958 A G 基因间序列 SYN2、PPARG 0.002 1 2
    3 12296019 G A 基因间序列 SYN2、PPARG 0.002 2 4
    3 12316549 G C 基因间序列 SYN2、PPARG 0.002 1 2
    3 12335681 T G 入门 PPARG 0.0092 2 4
    3 12348795 T C 入门 PPARG 0.0014 1 2
    3 12353106 T C 入门 PPARG 0.001 2 4
    3 12403825 G A 入门 PPARG 0.0051 2 4
    3 12404394 G A 入门 PPARG 0.001 1 2
    3 12410289 G A 入门 PPARG 0.008 2 4
    3 12431381 C T 入门 PPARG 0.0061 2 4
    3 12447267 G A 入门 PPARG 0.0089 2 4
    3 12449379 C T 入门 PPARG 0.0092 2 4
    3 12450848 C A 入门 PPARG 0.0092 2 4
    3 12462847 T C 入门 PPARG 0.002 1 2
    3 12492797 G A 基因间序列 PPARG、TSEN2 0.01 1 2
    3 12503201 G A 基因间序列 PPARG、TSEN2 0.0099 2 4
    3 12530460 A G 入门 TSEN2 0.0092 2 4
    3 12531167 A G 入门 TSEN2 0.0099 2 4
    3 12557737 A G 入门 TSEN2 0.001 2 4
    3 59636143 A G 基因间序列 C3orf67、FHIT 0.003 3 6
    3 59645934 A C 基因间序列 C3orf67、FHIT 0.004 1 2
    3 59646893 G A 基因间序列 C3orf67、FHIT 0.002 1 2
    3 59697024 A G 基因间序列 C3orf67、FHIT 0.0072 1 2
    3 59701013 G A 基因间序列 C3orf67、FHIT 0.004 2 4
    3 59733945 A G 基因间序列 C3orf67、FHIT 0.001 2 4
    3 59747482 C T 入门 FHIT 0.001 2 4
    3 59750635 A G 入门 FHIT 0.003 1 2
    3 59757776 C T 入门 FHIT 0.001 2 4
    3 59770612 G A 入门 FHIT 0.001 2 4
    3 59804444 G C 入门 FHIT 0.001 2 4
    3 59819769 T C 入门 FHIT 0.001 2 4
    3 59884396 C T 入门 FHIT 0.001 2 4
    3 59960728 A C 入门 FHIT 0.01 1 2
    3 59970345 G A 入门 FHIT 0.002 1 2
    3 59972417 T A 入门 FHIT 0.0072 0 2
    3 60104328 C A 入门 FHIT 0.01 2 4
    3 60139062 G A 入门 FHIT 0.01 0 2
    3 60158066 C T 入门 FHIT 0.001 1 2
    3 60169285 C T 入门 FHIT 0.005 1 2
    3 60216185 T C 入门 FHIT 0.002 1 2
    3 60226380 G A 入门 FHIT 0.007 2 4
    3 60234539 C A 入门 FHIT 0.002 1 2
    3 60247464 A C 入门 FHIT 0.004 2 4
    3 60269926 A G 入门 FHIT 0.007 2 4
    3 60271228 G T 入门 FHIT 0.007 2 4
    3 60286972 T C 入门 FHIT 0.001 2 4
    3 60301412 C G 入门 FHIT 0.001 1 2
    3 60312251 C T 入门 FHIT 0.0099 1 2
    3 60317682 A G 入门 FHIT 0.008 1 2
    3 60328557 C G 入门 FHIT 0.0043 2 4
    3 60342562 C T 入门 FHIT 0.006 1 2
    3 60400033 G A 入门 FHIT 0.004 2 4
    3 60435819 C T 入门 FHIT 0.006 2 4
    3 60435820 G T 入门 FHIT 0.004 1 2
    3 60441288 T C 入门 FHIT 0.006 2 4
    3 60444465 C A 入门 FHIT 0.01 1 2
    3 60444575 C T 入门 FHIT 0.001 1 2
    3 60450581 T C 入门 FHIT 0.01 1 2
    3 60456571 G A 入门 FHIT 0.001 2 4
    3 60473568 C G 入门 FHIT 0.001 1 2
    3 60487557 T C 入门 FHIT 0.001 1 2
    3 60559705 A G 入门 FHIT 0.002 2 4
    3 60570764 T C 入门 FHIT 0.008 2 4
    3 60582100 C T 入门 FHIT 0.001 1 2
    3 60587192 G A 入门 FHIT 0.004 1 2
    3 60599869 G A 入门 FHIT 0.0086 2 4
    3 60603091 C T 入门 FHIT 0.001 2 4
    3 60603250 A T 入门 FHIT 0.0099 1 2
    3 60609831 T G 入门 FHIT 0.001 2 4
    3 60619756 G T 入门 FHIT 0.0015 2 4
    3 60680758 C T 入门 FHIT 0.0089 2 4
    3 60702243 G C 入门 FHIT 0.001 2 4
    3 60702532 A G 入门 FHIT 0.001 1 2
    3 60714328 A T 入门 FHIT 0.004 1 2
    3 60725297 G A 入门 FHIT 0.001 1 2
    3 60726640 G A 入门 FHIT 0.01 2 4
    3 60795144 A G 入门 FHIT 0.001 2 4
    3 60807171 A G 入门 FHIT 0.001 1 2
    3 60813868 T C 入门 FHIT 0.001 1 2
    3 60826546 C G 入门 FHIT 0.0023 1 2
    3 60837392 C T 入门 FHIT 0.001 1 2
    3 60846310 A G 入门 FHIT 0.01 0 2
    3 60850985 C T 入门 FHIT 0.004 1 2
    3 60852559 T C 入门 FHIT 0.008 1 2
    3 60871759 T C 入门 FHIT 0.004 1 2
    3 60884396 C T 入门 FHIT 0.002 2 4
    3 60897092 C A 入门 FHIT 0.001 2 4
    3 60940759 C T 入门 FHIT 0.0089 1 2
    3 60982595 A G 入门 FHIT 0.003 2 4
    3 60999283 G A 入门 FHIT 0.001 1 2
    3 61042977 A G 入门 FHIT 0.001 2 4
    3 61043349 T C 入门 FHIT 0.001 2 4
    3 61044789 A C 入门 FHIT 0.001 2 4
    3 61141621 G A 入门 FHIT 0.003 1 2
    3 61148655 G C 入门 FHIT 0.001 2 4
    3 61170747 C T 入门 FHIT 0.003 1 2
    3 61189473 C G 入门 FHIT 0.0099 1 2
    3 61190425 C T 入门 FHIT 0.0023 2 4
    3 61193853 C T 入门 FHIT 0.0099 0 2
    3 61194793 C T 入门 FHIT 0.007 0 2
    3 61194840 A G 入门 FHIT 0.0099 0 2
    3 61194886 T A 入门 FHIT 0.0099 0 2
    3 61201777 C T 入门 FHIT 0.001 2 4
    3 61202292 T C 入门 FHIT 0.007 1 2
    3 61232806 G C 入门 FHIT 0.0099 1 2
    3 61232910 C T 入门 FHIT 0.0099 1 2
    3 61235824 A T 入门 FHIT 0.001 2 4
    3 61283810 A C 基因间序列 FHIT、PTPRG 0.0089 1 2
    3 61293731 T A 基因间序列 FHIT、PTPRG 0.0089 2 4
    3 61296730 C T 基因间序列 FHIT、PTPRG 0.001 1 2
    3 61326341 C T 基因间序列 FHIT、PTPRG 0.004 2 4
    3 61326620 T C 基因间序列 FHIT、PTPRG 0.01 1 2
    3 61327649 G C 基因间序列 FHIT、PTPRG 0.001 2 4
    3 61330545 G C 基因间序列 FHIT、PTPRG 0.001 2 4
    3 61335803 G A 基因间序列 FHIT、PTPRG 0.001 2 4

    使用来自 1,000 Genomes Phase 3 的数据的类似查询大约需要运行 90 秒，并处理约 3.38 TB 的数据。

查询包含 250 个随机基因的具体化表

下面的示例介绍如何对具体化表运行间隔 JOIN，该具体化表中包含从 silver-wall-555:TuteTable.hg19 表中随机选出的 250 个基因

  1. 如需创建间隔表，请按以下步骤操作：

    1. 在 Google Cloud 控制台中打开 BigQuery 页面。

      转到 BigQuery 页面

    2. 点击编写查询。

    3. 在新查询字段中运行以下查询，该查询将 silver-wall-555:TuteTable.hg19 表的一部分具体化为一个新的 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. 点击运行查询。查询将返回以下结果：
      This statement created a new table named PROJECT_ID:genomics.randomGenesIntervalTable.

  2. 在 New 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. 点击运行查询。查询大约需要运行十秒钟，并处理约 334 GB 的数据。查询结果标识同类群组中与目标区域重叠的罕见变体。

    展开即可下部分以查看删减的查询结果：

    查询结果

    Chr 开始 参考 Alt Func Gene PopFreqMax ExonicFunc num_variant_alleles total_num_alleles
    1 2925355 C A 基因间序列 TTC34、ACTRT2 0.001 2 4
    1 2933170 G A 基因间序列 TTC34、ACTRT2 0.0083 0 4
    1 2944477 G A 基因间序列 ACTRT2、LINC00982 0.003 4 6
    1 2967591 A T 基因间序列 ACTRT2、LINC00982 0.0092 1 2
    1 2975255 T C 下行 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 入门 PRDM16 0.0098 1 2
    1 3011333 G T 入门 PRDM16 0.004 1 2
    1 3019659 C T 入门 PRDM16 0.0031 1 2
    1 3036896 G A 入门 PRDM16 0.001 1 2
    1 3037388 G A 入门 PRDM16 0.002 2 4
    1 3041250 T G 入门 PRDM16 0.006 2 4
    1 3042502 A T 入门 PRDM16 0.003 4 6
    1 3053713 A C 入门 PRDM16 0.002 1 2
    1 3063109 C T 入门 PRDM16 0.002 0 2
    1 3063593 T C 入门 PRDM16 0.003 1 2
    1 3076439 C T 入门 PRDM16 0.001 2 4
    1 3078960 G A 入门 PRDM16 0.007 2 4
    1 3084268 A C 入门 PRDM16 0.005 0 2
    1 3084492 T C 入门 PRDM16 0.0015 0 2
    1 3084786 T C 入门 PRDM16 0.0015 0 4
    1 3111119 G A 入门 PRDM16 0.003 1 2
    1 3111643 C T 入门 PRDM16 0.0041 1 2
    1 3114807 G A 入门 PRDM16 0.0041 1 2
    1 3165530 C T 入门 PRDM16 0.0089 1 2
    1 3169325 G A 入门 PRDM16 0.008 2 4
    1 3179623 C T 入门 PRDM16 0.003 2 4
    1 3181097 C T 入门 PRDM16 0.001 2 4
    1 3194000 G C 入门 PRDM16 0.005 2 4
    1 3195769 T C 入门 PRDM16 0.002 1 2
    1 3197351 C T 入门 PRDM16 0.0061 1 2
    1 3224100 C A 入门 PRDM16 0.003 2 4
    1 3228644 G T 入门 PRDM16 0.001 2 4
    1 3234045 G A 入门 PRDM16 0.002 1 2
    1 3235971 G A 入门 PRDM16 0.0089 1 2
    1 3274115 C T 入门 PRDM16 0.001 2 4
    1 3291388 G A 入门 PRDM16 0.002 2 4
    1 3295658 A C 入门 PRDM16 0.0068 0 6
    1 3295937 A C 入门 PRDM16 0.0068 0 2
    1 3296205 T C 入门 PRDM16 0.0083 0 2
    1 3315690 G A 入门 PRDM16 0.001 2 4
    1 3329212 G A 外显子 PRDM16 0.0031 错义 1 2
    1 3331787 C T 入门 PRDM16 0.0099 1 2
    1 3370316 G C 上行 ARHGEF16 0.001 2 4
    1 3379560 A G 入门 ARHGEF16 0.0051 0 6
    1 3391174 C T 入门 ARHGEF16 0.006 1 2
    1 3413873 G A 外显子 MEGF6 0.003 错义 1 2
    1 3416272 C T 外显子 MEGF6 0.0072 静音 2 4
    1 3417122 G A 入门 MEGF6 0.0038 2 4
    1 3436219 G A 入门 MEGF6 0.0046 2 4
    1 12907456 A G 外显子 HNRNPCL1、LOC649330 0.006 错义 0 10
    1 12907518 C A 外显子 HNRNPCL1、LOC649330 1.0E-4 错义 0 10
    1 12908499 G C 入门 HNRNPCL1 0.0031 0 8
    1 12931660 G C 基因间序列 PRAMEF2、PRAMEF4 0.004 1 2
    1 12937721 G T 基因间序列 PRAMEF2、PRAMEF4 0.0038 0 2
    1 12940827 G T 入门 PRAMEF4 0.007 2 4
    1 12942759 T G 入门 PRAMEF4 0.0076 0 10
    1 12942805 T G 入门 PRAMEF4 0.0061 0 12
    1 12942812 G A 入门 PRAMEF4 0.0061 0 12
    1 12942875 A G 入门 PRAMEF4 0.0068 0 6
    1 12942912 G C 入门 PRAMEF4 2.0E-4 0 2
    1 12942937 A T 外显子 PRAMEF4 0.0029 错义 0 2
    1 12942940 T G 外显子 PRAMEF4 0.0038 错义 0 2
    1 12943940 T C 入门 PRAMEF4 0.0015 0 12
    1 12944138 A G 入门 PRAMEF4 8.0E-4 0 12
    1 12944234 G A 入门 PRAMEF4 0.0015 0 12
    1 12944589 T G 入门 PRAMEF4 0.003 0 4
    1 12944845 A C 入门 PRAMEF4 0.0014 0 6
    1 12946439 T C 上行 PRAMEF4 0.0029 0 10
    1 12946833 G A 上行 PRAMEF4 0.001 0 8
    1 12946835 T A 上行 PRAMEF4 0.004 0 12
    1 12995204 G T 基因间序列 PRAMEF8、PRAMEF6 0.003 1 4
    1 12997638 T C 下行 PRAMEF6、PRAMEF9 0.003 2 4
    1 13007841 G C 上行 PRAMEF6 0.0043 0 8
    1 13019228 T A 基因间序列 PRAMEF6、LOC391003 0.0015 0 10
    1 13038503 G A UTR3 LOC391003 0.0072 1 2
    1 13051650 C T 基因间序列 LOC391003、PRAMEF5 0.002 2 4
    1 15706063 G A 入门 FHAD1 0.0029 1 2
    1 15713292 C T 入门 FHAD1 0.001 1 2
    1 15766541 G C 入门 CTRC 0.001 1 2
    1 15782601 T C 上行 CELA2A 0.0038 1 2
    1 15828125 G A 入门 CASP9 0.0014 2 4
    1 15831037 G A 入门 CASP9 0.0099 1 2
    1 15840513 T G 入门 CASP9 0.0043 2 4
    1 15868742 G A 入门 DNAJC16 0.001 1 2
    1 15876704 G A 入门 DNAJC16 0.001 1 2
    1 15900342 C A 入门 AGMAT 0.001 1 2
    1 15906257 T C 入门 AGMAT 8.0E-4 1 2
    1 15911897 A G 上行 AGMAT 0.0043 2 4
    1 22764178 C T 基因间序列 WNT4、ZBTB40 0.001 2 4
    1 22791939 C T 入门 ZBTB40 0.0089 2 4
    1 22874394 C G 基因间序列 ZBTB40、EPHA8 0.007 1 2
    1 22875103 C G 基因间序列 ZBTB40、EPHA8 0.007 1 2
    1 22906403 C T 入门 EPHA8 0.008 2 4
    1 22912956 G A 入门 EPHA8 0.001 1 2
    1 22917007 C T 入门 EPHA8 0.001 2 4
    1 22927240 G A 外显子 EPHA8 0.0013 错义 2 4
    1 22932265 G A 基因间序列 EPHA8、MIR6127 0.0089 2 4
    1 22944057 C T 基因间序列 EPHA8、MIR6127 0.0089 2 4
    1 22978799 A G 上行 C1QB 0.0099 2 4
    1 35170588 C T 基因间序列 C1orf94、GJB5 0.01 1 2
    1 35172426 C T 基因间序列 C1orf94、GJB5 0.008 1 2
    1 35172447 G A 基因间序列 C1orf94、GJB5 0.001 1 2
    1 35175302 C T 基因间序列 C1orf94、GJB5 0.008 1 2
    1 35177410 A T 基因间序列 C1orf94、GJB5 0.001 1 2
    1 35178768 C T 基因间序列 C1orf94、GJB5 0.0014 2 4
    1 35179362 G A 基因间序列 C1orf94、GJB5 0.0014 2 4
    1 35186166 G A 基因间序列 C1orf94、GJB5 0.0099 2 4
    1 35186520 A C 基因间序列 C1orf94、GJB5 0.002 2 4
    1 35196361 G A 基因间序列 C1orf94、GJB5 0.0099 2 4
    1 35223545 C T 外显子 GJB5 0.001 静音 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 基因间序列 GJB4、GJB3 0.0043 1 2
    1 35232954 T C 基因间序列 GJB4、GJB3 0.003 1 2
    1 35237986 G A 基因间序列 GJB4、GJB3 0.0014 1 2
    1 35245522 C T 基因间序列 GJB4、GJB3 0.001 1 2
    1 35256979 C T 基因间序列 GJB3、GJA4 0.002 2 4
    1 35263872 C T 基因间序列 GJA4、SMIM12 5.0E-4 2 4
    1 35323895 A C 入门 SMIM12 0.0027 2 4
    1 35369676 G A 入门 DLGAP3 0.007 2 4
    1 35371634 T A 上行 DLGAP3 0.0015 0 4
    1 39253519 G A 基因间序列 LINC01343、RRAGC 0.005 1 2
    1 39288829 G A 基因间序列 LINC01343、RRAGC 0.0051 1 2
    1 39289832 A C 基因间序列 LINC01343、RRAGC 0.002 0 2
    1 39312638 G A 入门 RRAGC 0.0038 2 4
    1 39361372 G A 入门 RHBDL2 0.005 1 2
    1 39363826 T G 入门 RHBDL2 0.0029 1 2
    1 39367555 T C 入门 RHBDL2 0.007 2 4
    1 39369531 T C 入门 RHBDL2 0.001 2 4
    1 39370202 T C 入门 RHBDL2 0.01 1 2
    1 39449101 A G 基因间序列 RHBDL2、AKIRIN1 0.001 2 4
    1 39475057 G A 基因间序列 AKIRIN1、NDUFS5 0.01 1 2
    1 39485016 C T 基因间序列 AKIRIN1、NDUFS5 0.001 2 4
    1 39488137 A G 基因间序列 AKIRIN1、NDUFS5 0.001 2 4
    1 39499212 A C 入门 NDUFS5 0.001 0 2
    1 39500605 C G 下行 NDUFS5 0.002 0 10
    1 46813814 T C 入门 NSUN4 0.0014 1 2
    1 46817258 A G 入门 NSUN4 0.005 0 2
    1 46843158 T C 基因间序列 NSUN4、FAAH 0.001 1 2
    1 46933509 A G 基因间序列 LINC01398、DMBX1 0.002 1 2
    1 46935021 G A 基因间序列 LINC01398、DMBX1 0.004 2 4
    1 46939253 T A 基因间序列 LINC01398、DMBX1 0.004 2 4
    1 46951788 C A 基因间序列 LINC01398、DMBX1 0.002 2 4
    1 46980864 G C 下行 DMBX1 0.003 1 2
    1 46989657 T C 基因间序列 DMBX1、MKNK1-AS1 0.007 1 2
    1 46994678 C T 基因间序列 DMBX1、MKNK1-AS1 0.002 1 2
    1 46999438 T C 基因间序列 DMBX1、MKNK1-AS1 0.002 1 2
    1 92761505 A G 入门 GLMN 0.001 2 4
    1 92764270 G C 入门 GLMN 0.001 2 4
    1 92802210 G A 入门 RPAP2 0.0072 1 2
    1 92820663 T A 入门 RPAP2 0.0058 1 2
    1 92820664 G T 入门 RPAP2 0.0058 1 2
    1 92820953 G A 入门 RPAP2 0.007 2 4
    1 92824766 A G 入门 RPAP2 0.0058 1 2
    1 92849183 C A 入门 RPAP2 0.01 2 4
    1 92850696 C G 入门 RPAP2 0.0023 1 2
    1 92861357 T C 基因间序列 RPAP2、GFI1 0.01 2 4
    1 92877460 C G 基因间序列 RPAP2、GFI1 0.002 1 2
    1 92880643 A G 基因间序列 RPAP2、GFI1 0.001 2 4
    1 92911540 G A 基因间序列 RPAP2、GFI1 0.004 2 4
    1 92911721 A C 基因间序列 RPAP2、GFI1 0.0031 0 8
    1 92918277 C T 基因间序列 RPAP2、GFI1 0.001 2 4
    1 92950920 G A 入门 GFI1 0.008 2 4
    1 92964788 G A 基因间序列 GFI1、EVI5 0.0023 1 2
    1 92977480 C T UTR3 EVI5 0.002 1 2
    1 92985213 C T 入门 EVI5 0.001 2 4
    1 92988342 C T 入门 EVI5 0.008 2 4
    1 92992283 G A 入门 EVI5 0.01 2 4
    1 92999760 C T 入门 EVI5 0.003 1 2
    1 93005149 G C 入门 EVI5 0.003 0 4
    1 93018543 A T 入门 EVI5 0.01 2 4
    1 93033744 C T 入门 EVI5 0.001 2 4
    1 111400296 G A 基因间序列 KCNA3、CD53 0.0014 2 4
    1 111411924 C T 基因间序列 KCNA3、CD53 0.003 1 2
    1 111441850 C G UTR3 CD53 0.003 2 4
    1 111451527 C T 基因间序列 CD53、LRIF1 0.008 2 4
    1 111454082 C A 基因间序列 CD53、LRIF1 0.001 2 4
    1 111466506 A G 基因间序列 CD53、LRIF1 0.001 2 4
    1 111525974 G A 基因间序列 LRIF1、DRAM2 0.002 2 4
    1 111574573 G T 基因间序列 LRIF1、DRAM2 0.0072 2 4
    1 111574594 T A 基因间序列 LRIF1、DRAM2 0.005 1 2
    1 111574647 G A 基因间序列 LRIF1、DRAM2 0.005 1 2
    1 111591746 T A 基因间序列 LRIF1、DRAM2 0.005 1 2
    1 111601459 A G 基因间序列 LRIF1、DRAM2 0.005 1 2
    1 111604748 G C 基因间序列 LRIF1、DRAM2 0.005 1 2
    1 112191526 T G 入门 RAP1A 0.001 2 4
    1 112206765 A G 入门 RAP1A 0.0043 1 2
    1 112226517 G A 入门 RAP1A 0.001 0 2
    1 112263324 G T 基因间序列 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 入门 DDX20 0.0043 1 2
    1 112307213 A C 入门 DDX20 0.0043 1 2
    1 112309436 G T 外显子 DDX20 0.0 错义 1 2
    1 112317384 T C 基因间序列 DDX20、KCND3 0.0014 1 2
    1 112381367 C T 入门 KCND3 0.002 1 2
    1 112396571 G T ncRNA_exonic KCND3-IT1 0.001 1 2
    1 113520038 G A 基因间序列 SLC16A1-AS1、LOC100996251 0.0023 1 2

    使用来自 1,000 Genomes Phase 3 的数据的类似查询大约需要运行 90 秒，并处理约 3.38 TB 的数据。