使用 BigQuery 运行间隔 JOIN

您可以使用 BigQuery 对带有基因组区域间隔所描述数据的变体运行 JOIN 查询。此页面介绍如何使用复杂 JOIN 查询获取基因名称列表并执行以下操作：

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

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

查询内嵌表。
查询包含特定基因的具体化表。
查询包含 250 个随机基因的具体化表。

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

Google Cloud 博客中的通过 Google Genomics 和 Tute 探索基因变异
公共数据集内的 Illumina Platinum Genomes 部分

查询内嵌表

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

转到 Cloud Console 中的 BigQuery 页面。

转到 BigQuery 页面
点击编写查询。

在 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;

点击运行查询。查询大约需要运行十秒钟，并处理约 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 查询。在继续执行本部分的其余部分之前，请按照以下步骤创建数据集：

在 Cloud Console 中打开 BigQuery 页面。

转到 BigQuery 页面
在浏览器面板中，选择您要在其中创建数据集的项目。

注意：默认体验是预览版 Cloud Console。如果您点击隐藏预览功能 (Hide preview features) 以转到正式版 Cloud Console，则改为执行以下步骤：

在导航面板的资源部分中，选择项目。
展开操作选项，然后点击创建数据集。
在创建数据集页面中执行以下操作：
1. 在数据集 ID 部分，输入 genomics。
2. 保留其他默认设置。
3. 点击创建数据集。

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

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

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

在 Cloud Console 中打开 BigQuery 页面。

转到 BigQuery 页面
点击编写查询。

在 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 );

点击运行查询。查询将返回以下结果：

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

在 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;

点击运行查询。查询大约需要运行十秒钟，并处理约 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 个基因

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

在 Cloud Console 中打开 BigQuery 页面。

转到 BigQuery 页面
点击编写查询。

在新查询字段中运行以下查询，该查询将 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 );

点击运行查询。查询将返回以下结果：

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

在 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;

点击运行查询。查询大约需要运行十秒钟，并处理约 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 的数据。